SQL Server And CRUD Operations Using .NET Core 2 And 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".
   
   
   

3. On the "Create a new project" window, choose the ASP.NET Core Web Application template, and then choose "Next".
   
   
   
   
4. In the "Configure your new project" window, enter “SQLServerDataRetrieval” as the project name. Then, choose "Create".
   
   
   
   
5. 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".
   
   
   
   
6. 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.

 

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.

var connectionString = @"Data Source=ABCD;Initial Catalog=Person2;Integrated Security=False;Persist Security Info=False;User ID=sa;Password=******";
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.

services.AddDbContext<MyDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("connectionString")));

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

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "connectionString": "Data Source=ABCD;Initial Catalog=Person2;Integrated Security=False;Persist Security Info=False;User ID=sa;Password=******"
  }
}

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

public class MyDbContext : DbContext
    {
        public MyDbContext(DbContextOptions<MyDbContext> options)
            : base(options)
        { }
        public DbSet<MyUser> MyUser {get; set;}
    }

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.

 

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

 

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”.
   
   
   
   
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.
   
   
   
   
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.
   
   
   
   
5. After selecting a Model class, select the Data Context class from the drop-own and finally, click on “Add”.
   
   
   
   
6. The CRUD Operations files (Create.cshtml, Delete.cshtml, Details.cshtml, Edit.cshtml and Index.cshtml) would be created.
   
   
   
   
7. Make sure to check the “Startup.cs” for the below-mentioned line as it would be adding the Context related details to ConfigureServices.
   
   
   
   
8. Test the files by right-clicking on the Index file and open it with the browser.
   
   
   

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