Connecting To Database Using .NET Core, SQL Server, And Scaffolding

In this blog, I'll explain how to retrieve data from a SQL Server Database using .NET Core and Entity Framework.

The idea of this post is to focus more on the C# side, so I won't provide the database scripts. In my case, I already have a database named "Persons2" with two simple tables - "Person" and "TestDate". However, I am going to use the "Person" table only to display data through C#.
 
So, if you have any existing database, you can use that one for this example. If not, you can create a new database with just one table (with any name you want and just 2 or 3 fields). 
 
Open Visual Studio and go to File-> New-> Project. Choose the "Console Application (.NET Core)" option and click OK.
 
Connection To Database Using .NET Core, SQL Server And Scaffolding 
 
The next step is to install a dependency. We will use NuGet Package Manager to install the  "Microsoft.EntityFrameworkCore.SqlServer" package. 
 
I have seen two different ways to make a connection to the SQL Server in .NET Core.
  1. through JSON Configuration File
  2. through Scaffolding command
In this case, we'll use the second option, i.e., Scaffolding command. 
 
Before the scaffold, please add a new folder on the Solution and name it "Models" or any other name you want (You will need to place this folder name in one of the parameters of the scaffold command that we are going to execute).
 
Now, open NuGet console (Tools -> Nuget Package Manager -> Package Manager Console).
 
Connection To Database Using .NET Core, SQL Server And Scaffolding 
 
Paste the following command into it (change your parameter values).
 
"Scaffold-DbContext "Server=RRR;Database=RRR;User Id= RRR; Password=RRR;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models"
 
Now, press Enter. Here are the details of each field mentioned in the above command.
 
Server -> name of your database host or IP address.
 
DataBase -> name of your database.
 
User Id -> your SQL Server login id.
 
Password -> your SQL Server password for the provided user Id.
 
-OutputDir -> a folder in Visual Studio where you want to place your Models classes (the folder that we created earlier).
 
If you are logging in through Windows Authentication method, then remove "User Id" and "Password" parameters
and place "Trusted_Connection=True" instead of these two fields. 
 
NOTE
If you get the following error when executing the command "scaffold-DBContext is not recognized...",
please execute the following command "Install-Package Microsoft.EntityFrameworkCore.Tools". Once installed, execute the scaffold command again.
 
Now, you will see your Models class and your context class inside the specified output folder.
 
Connection To Database Using .NET Core, SQL Server And Scaffolding
 
Now, using your context class and your models, you can get the data from database like below.
 
Connection To Database Using .NET Core, SQL Server And Scaffolding 
 
Connection To Database Using .NET Core, SQL Server And Scaffolding 
 
And, that's it. I hope you have learned something new from this post. I know it's something simple but I hope it is helpful to novice .NET Core programmers.