From SQL Server to MongoDB with asp.net core web API flavor and swagger seasoning

Ehsan Khosravi Esfarjani

6 min readJun 13, 2021

From SQL Server to MongoDB with asp.net core web API flavor and swagger seasoning

In this tutorial, I am going to import data as JSON datatype from SQL server to MongoDB according to the principles of data structure rules and schema modeling in NoSQL(document-based like MongoDB) and then by implementing a web API by asp.net core that is connected to MongoDB and then Then we use swagger for easy testing of crud operations in web API.

In the first step, we need to get the desired output from the SQL server, for which we need the following:

SQL Server 2016 or later versions

Adventureworks2017 database

MongoDB recommends to users 6 Rules of Thumb for MongoDB Schema that
It is as follows:

One: favor embedding unless there is a compelling reason not to
Two: needing to access an object on its own is a compelling reason not to embed it
Three: Arrays should not grow without bound. If there are more than a couple of hundred documents on the “many” side, don’t embed them; if there are more than a few thousand documents on the “many” side, don’t use an array of ObjectID references. High-cardinality arrays are a compelling reason not to embed.
Four: Don’t be afraid of application-level joins: if you index correctly and use the projection specifier (as shown in part 2) then application-level joins are barely more expensive than server-side joins in a relational database.
Five: Consider the write/read ratio when denormalizing. A field that will mostly be read and only seldom updated is a good candidate for denormalization: if you denormalize a field that is updated frequently then the extra work of finding and updating all the instances is likely to overwhelm the savings that you get from denormalizing.
Six: As always with MongoDB, how you model your data depends — entirely — on your particular application’s data access patterns. You want to structure your data to match the ways that your application queries and updates it.

before the start of the tutorial, we must Get acquainted with the equivalent terms in SQL server and MongoDB :

For this tutorial, we will use the Adventureworks2017 database. In this tutorial, we will use three tables that show to us sales order base on geography that are shown in the following diagram :

Sales.SalesTerritory :

Sales territory lookup table.

Sales.SalesOrderHeader :

General sales order information.

Sales.SalesOrderDetail :

Individual products associated with a specific sales order. See SalesOrderHeader.

To create JSON output of a set of tables that includes three levels and nested arrays, we must write a query that includes the desired fields in all three tables And we put a query in the stored procedure(TerritorySalesOrder), which you can see in the image below :

Microsoft in SQL server 2016 present JSON output ability that has two methods :

When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

To maintain full control over the format of the JSON output, use FOR JSON PATH. You can create wrapper objects and nest complex properties.
To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO.

You can see the output query as JSON in the below picture :

Note: many tools and methods exist for import migration from SQL Server to MongoDB like SSIS and studio3t, etc. but I decide to choose the simple and manual method for beginners.

In our method, after calling the stored procedure will create a JSON file be in the desired location by below T-SQL code and insert content :

**For Access to the windows environment and create files we need to enable** **xp_cmdshell. (for security reason disable this option later)**

**Call xp_CMDSHELL to create an output file by executing the territorySaleOrder stored procedure.**

Created JSON file(in below picture)

Before import file to MongoDB, we must create the desire database and collection in MongoDB like the below picture :

and import file in MongoDB :

you will see inserted documents in MongoDB Compass ( database: SalesTerritory, collection: SaleOrders):

The next step is to create a web API by asp.net core and we need VisualStudio2019 :

Choose Asp.net core Web API

specify project name and directory

specify framework version and Authentication type, etc.

We need two packages for this project :

Install-Package MongoDB.Driver Install-Package Swashbuckle.AspNetCore

set collection and database name and connection string in appsettings.json

create a folder called configuration and put a class inside it called StConfiguration

Next step: create the model classes inside the Entity folder named Territory, SaleOrderHeader, SaleOrderDetail. ( every property of the model class is equal to the collection’s field in MongoDB and for implementation, the hierarchal level must call child model class in parent model class.

SaleOrderDetail(grandson)=>SaleOrderHeader(dad)=>Territory(grandpa)

Create Territory model class and define Saleorderheader property as a child.

Note: BsonId, lets you specify which property will be act as a unique ID for your document. BsonRepresentation: For some .NET primitive types you can control what BSON type you want used to represent the value in the BSON document.

Create Saleorderheader model class and define Saleorderdetail property as a child.

Create Saleorderdetail model class.

create services folder and create an ItsService Interface to hold the CRUD operation methods.(Include various filtering , Projection(selective fields) , aggregation function(Max,Min,Sum), Update and Delete in three levels)