How To Connect MS SQL Server Database in Asp.net Core MVC Using C#.Net

In this article I will show you how you can connect to a MS  Sql server database and get the data stored in an object table in your asp.net core mvc web application. First we will create a new database in ms sql server and in this we will create a new table with some data.

Read more:

• Login Form In Asp.net Core 6 Using C# From Ms Sql Server Database
• Access Connection String In DbContext File From appsettings.json C# In Asp.Net Core 6
• How To Change Default Page Or View In Asp.Net Core 6

After creating this we will create a new asp.net core 6 project. In this we will add SQL server provider and Entity tool package. Please check the below packages.

 

Microsoft.EntityFrameworkCore.SqlServer Microsoft.EntityFrameworkCore.Tools

 

OR 

https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer/5.0.13 https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Tools/5.0.13

 

We need to install the package from package manager.

After installing the above packages we will get it in solution explorer.

Here for making connection with database by using entity framework we will run the Scaffold-DbContext. Please check the below command.

Scaffold-DbContext "Server=.\SQLEXPRESS;Database=TestDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DataDB

 

In above command we have given sql server connection string and then the provide name and output folder name. Here Output folder name is the folder name where we after execution of command all the DB entity object partial class with DBcontext file will be created.  This approach is called DB first approach.

Here we will check the entity object detail of employee.

using System; using System.Collections.Generic; namespace TestProject.DataDB {     public partial class Employee     {         public int Id { get; set; }         public string EmployeeName { get; set; }         public string Designation { get; set; }         public string Department { get; set; }         public int? Salery { get; set; }     } }

Here you can see the class property of employee is same as the fields we have defined in the SQL table.

Now we will will check the DB context file. In this file we will get the connection string and all the table object details.

using System; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata; namespace TestProject.DataDB {     public partial class TestDBContext : DbContext     {         public TestDBContext()         {         }          public TestDBContext(DbContextOptions<TestDBContext> options)            : base(options)         {         }          public virtual DbSet<Employee> Employees { get; set; }          protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)         {             if (!optionsBuilder.IsConfigured)             {                 optionsBuilder.UseSqlServer("ServerLAPTOP-DCE5GAKD\\SQLEXPRESS;Database=TestDB;Trusted_Connection=True;");             }         }          protected override void OnModelCreating(ModelBuilder modelBuilder)         {             modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");              modelBuilder.Entity<Employee>(entity =>             {                 entity.ToTable("Employee");                  entity.Property(e => e.Department)                     .HasMaxLength(50)                     .IsUnicode(false);                  entity.Property(e => e.Designation)                     .HasMaxLength(50)                     .IsUnicode(false);                  entity.Property(e => e.EmployeeName)                     .HasMaxLength(50)                     .IsUnicode(false);             });             OnModelCreatingPartial(modelBuilder);         }         partial void OnModelCreatingPartial(ModelBuilder modelBuilder);     } }

 

Above is the autogenerated file. In this check the highlighted part of the code. This the connection string.  Now add a controller and put the below ode to check the connection. In this we will access all the data present in the table.

TestDBContext testDBContext = new TestDBContext(); var empdata = testDBContext.Employees;

 

Now lets check the output.

Download