SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

In this article, I will provide information on various ways to connect to SQL Server Database and steps on how to retrieve data from a SQL Server Database using .NET Core & Entity Framework.

Below are the concepts/tools used in this blog.

  1. Visual Studio 2019
  2. NuGet Packages
  3. SQL Server 2016
  4. .NET Core 2.0
  5. NET Core Web Application
  6. Entity Framework
  7. C# Language

Create a project in Visual Studio 2019

To start, I'll create an “ASP.NET Core web application” project.

  1. Open Visual Studio.
  2. On the start window, choose "Create a new project".

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework
  1. On the "Create a new project" window, choose the ASP.NET Core Web Application template, and then choose "Next".

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  2. In the "Configure your new project" window, enter “SQLServerDataRetrieval” as the project name. Then, choose "Create".

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  3. In the "Create a new ASP.NET Core Web Application" window, verify that ASP.NET Core 2.0 or later appears in the top drop-down menu. Then, choose "Web Application" which includes example Razor Pages. Next, choose "Create".

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  4. Visual Studio opens your new project.

Add NuGet Packages to the above-created project

In Visual Studio menu, browse to Tools >> NuGet Package Manager >> Package Manager Console.

SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework 

Then, execute the below command to install the EntityFramework to provide a way to connect to SQL server:

Install-Package Microsoft.EntityFrameworkCore.SqlServer

For Entity Framework Core Tools to create a database from your EntityFramework Core model, execute the below command:

Install-Package Microsoft.EntityFrameworkCore.Tools

Various ways to connect to SQL Server

In previous versions of ASP.NET (.NET Frameworks), we used web.config for connection strings but in ASP.NET Core, we have the following ways to create the SQL Server database connection strings.

NOTE
Below 2 ways are using a class named “MyDbContext” which I would explain after both options are over.

Connection string in “Startup.cs” (Not recommended)

Hard coding the connection string into Startup.cs file as in the code below.

SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework
  1. var connectionString = @"Data Source=ABCD;Initial Catalog=Person2;Integrated Security=False;Persist Security Info=False;User ID=sa;Password=******";    
  2. services.AddDbContext<MyDbContext>(options => options.UseSqlServer(connectionString));    

Connection string from “appsettings.json” into “Startup.cs”

In startup.cs, the below code is required for informing the system that connection is defined in appsettings.json.

SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework
  1. services.AddDbContext<MyDbContext>(options =>    
  2. options.UseSqlServer(Configuration.GetConnectionString("connectionString")));    

where "connectionString" is defined in appsetting.json at the root directory of the application.

SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework
  1. {    
  2.   "Logging": {    
  3.     "IncludeScopes"false,    
  4.     "LogLevel": {    
  5.       "Default""Warning"    
  6.     }    
  7.   },    
  8.   "ConnectionStrings": {    
  9.     "connectionString""Data Source=ABCD;Initial Catalog=Person2;Integrated Security=False;Persist Security Info=False;User ID=sa;Password=******"    
  10.   }    
  11. }    

"MyDbContext" is our data context class and it looks like the following.

  1. public class MyDbContext : DbContext    
  2.     {    
  3.         public MyDbContext(DbContextOptions<MyDbContext> options)    
  4.             : base(options)    
  5.         { }    
  6.         public DbSet<MyUser> MyUser {getset;}    
  7.     }    

Scaffolding command (We would be using this way for our example)

Add a new folder on the Visual Studio solution and name it as "DBModels". Execute the below command in NuGet Package Manager Console.

Scaffold-DbContext “Server=RRR;Database=Person2;User ID= sa; Password=*****;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir DBModels

NOTE
If the above statement throws an error, then execute the below command in NuGet Package Manager Console.

Install-Package Microsoft.EntityFrameworkCore.Tools

After executing the above command, the Model classes and context classes are created inside the DBModels output folder.

NOTE
Make sure that the SQL Server table from which data has to be fetched has Primary KEY assigned to it, else it would throw an error.

SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework 

Now, using context classes and models, we can get the data from database like below.

SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework 


Display the data fetched from Database on the screen

 

I would be using Scaffolding again to achieve the CRUD operations for Model added in previous steps.
  1. Add a new folder under “Pages” and name it as per the model (Database table) you connected from SQL Server. For my example, I am naming it as “UPSErrors”.

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  2. Right-click on the “UPSErrors” folder >> Add >> New Scaffolded Item.
  3. In the "Add Scaffold" dialog, select “Razor Pages using Entity Framework (CRUD)” and double click on it.

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  4. On the "Add Razor Pages using Entity Framework (CRUD)" dialog, select the Model class on which you want to perform the CRUD operation from the drop-down.

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  5. After selecting a Model class, select the Data Context class from the drop-own and finally, click on “Add”.

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  6. The CRUD Operations files (Create.cshtml, Delete.cshtml, Details.cshtml, Edit.cshtml and Index.cshtml) would be created.

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  7. Make sure to check the “Startup.cs” for the below-mentioned line as it would be adding the Context related details to ConfigureServices.

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

  8. Test the files by right-clicking on the Index file and open it with the browser.

    SQL Server And CRUD Operations Using .NET Core 2 And Entity Framework

And, that's it. I hope you have learned something new from this article and will utilize this in your work.