Introduction
The main advantages of using design pattern are to keep the modules loosely coupled and to help developers address
change requests with minimal changes to the code. Also, the modules can be reusable in multiple projects, which speeds up development. There are many design patterns, and one advantage of using the repository design pattern is to
keep the data access logic centralized.
Initially, I struggled to understand both why
we need AutoMapper and how to configure dependency injections in .NET CORE. But
I received help from a senior architect, so now I am able to understand the
complete data flow. I know there are many articles on this topic, but they may
not answer all your questions and requirements.
In this article, I'm going to explain the data
flow so that you can understand it as well.

Step 1 - Create a New API Project in VS
In Visual Studio, click on File, go to New, and select the appropriate project template.

Click on OK. This opens up a new dialog. Select API and uncheck
“configure HTTPS” for now. Click on OK.

It creates a project and structure that looks like this.

Step 2 - Create a New Class Library Project for Data
Right click on your solution. Go to Add, and select New Project.
Select the Class Library project and Click OK.

Next, create a database in the SQL Server and create a test table with a
few columns as shown below.
How to Connect Your SQL DB With Your .NET Core Application
Next we need to scaffold the SQL database by running a command.
Click on Tools, go to NuGet Package Manager, and select Package Manager Console.
Run the below command. Before running this command make sure that you’ve selected “Data Class Library Project” in the top dropdown of the package manager console.
This will create your DBContext class and all the other table objects.
Open the created context file and remove the constructors without parameters
with the OnConfiguring method.
Next you need to create a DBContextPool in your API Project.
Go to your API Project and open the startup.cs file. Modify the ConfigureService method and create the DBContextPool like below.
The connection string is added in appsettings.json and referenced here.
Note
If you get any error with UseSqlServer, then import manually using Microsoft.EntityFrameworkCore.

How to Connect Your SQL DB With Your .NET Core Application
Next we need to scaffold the SQL database by running a command.
Click on Tools, go to NuGet Package Manager, and select Package Manager Console.
Run the below command. Before running this command make sure that you’ve selected “Data Class Library Project” in the top dropdown of the package manager console.
- Scaffold-DbContext "Server=tcp:xxxxxserver.database.windows.net;Database=xxxx_dev;User Id=xxxxxxx;Password=xxxxxx" Microsoft.EntityFrameworkCore.SqlServer -Context CsharpCornerContext -Force


Next you need to create a DBContextPool in your API Project.
Go to your API Project and open the startup.cs file. Modify the ConfigureService method and create the DBContextPool like below.
- services.AddEntityFrameworkSqlServer();
- services.AddDbContextPool<CsharoCornerContext>((serviceProvider, options) => { options.UseSqlServer(Configuration.GetConnectionString("connStr")); });
Note
If you get any error with UseSqlServer, then import manually using Microsoft.EntityFrameworkCore.
Step 3 - Create a New Class Library Project for the Repository
Follow the above steps to create a class library project.
Next, create a DBHelper Class (optional). Create a repository class named “Common Repository.”
Follow the above steps to create a class library project.
Next, create a DBHelper Class (optional). Create a repository class named “Common Repository.”

Next, inject your DBContext (and DBHelper if you configured it) into
your repository as shown below.
Create a common controller in your API Project.
Inject your common repository into a common controller as shown below.
- class CommonRepository : ICommonRepository
- {
- private readonly IDBHelper dBHelper;
- private CsharoCornerContext csharoCornerContext;
- public CommonRepository(IDBHelper dBHelper, CsharoCornerContext csharoCornerContext)
- {
- this.csharoCornerContext = csharoCornerContext;
- this.dBHelper = dBHelper;
- }
- }
- [Route("api/[controller]")]
- [ApiController]
- public class CommonController : ControllerBase
- {
- }
- [Route("api/[controller]")]
- [ApiController]
- public class CommonController : ControllerBase
- {
- private ICommonRepository commonRepository;
- public CommonController(ICommonRepository commonRepository)
- {
- this.commonRepository = commonRepository;
- }
- }




nil yPosted Aug 20, 2020, 8:33 AM
Good Article. Thanks! But you didnt mention why we need auto mapper?
purvang pandyaPosted Oct 18, 2019, 7:59 AM
It is very good article for beginners.my question is as per your structure what is the use of Model class library as we have both DTO class file and Result set and also you have set database table mapping and context.cs file in demo.Data Class library? you didn't write any code in Model Class library.
SamPosted Oct 7, 2019, 6:22 AM
Hi Veerendra there is one problem in async web api that is inserting duplicate record rather than putting condition in if block, if you are interested to resolve my problem i will send you code
Sanjit Kumar SinghPosted Jul 10, 2019, 10:22 AM
Awesome article
Matt LethargicPosted Jul 8, 2019, 2:03 AM
You really wouldn't want to return DTOs from your repositories, how are you ever going to mutate an entity if you do? And having repositories directly I'm your controllers is a bad idea as well, this places all of your business logic in the controllers and this isn't a good idea. If we look at something like hex architecture you'd want to keep logic inside a domain and your API becomes a port/way into that domain. If logic is present in controllers you then have to test them which can mean faking a load of unnecessary stuff.