How to Connect SQL Server Database in Asp.Net Core MVC

Introduction

ASP.NET Core is a popular framework for building web applications, and one of the supported databases for data storage is SQL Server. In this tutorial, we will learn how to connect a SQL Server database to an ASP.NET Core MVC application.

Prerequisites

Before proceeding with this tutorial, you should have the following tools and software installed on your system:

* Visual Studio 2019 (or later)

* SQL Server 2017 (or later)

Step 1: Create a new ASP.NET Core MVC project

To get started, launch Visual Studio and click on "Create a new project". In the "Create a new project" window, select "ASP.NET Core Web Application" and click on "Next".

In the "Configure your new project" window, enter a name for your project, select a location to save your project, and click on "Create".

In the "Create a new ASP.NET Core Web Application" window, select "Web Application (Model-View-Controller)" and click on "Create".

Step 2: Install the necessary packages

To connect a SQL Server database to an ASP.NET Core MVC application, we need to install the following packages:

* Microsoft.EntityFrameworkCore.SqlServer: This package provides support for Entity Framework Core to access SQL Server.

* Microsoft.EntityFrameworkCore.Tools: This package contains the tools that are required to create and update the database.

To install these packages, open the "Package Manager Console" window from the "Tools" menu and enter the following command:

Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 3.1.6

Install-Package Microsoft.EntityFrameworkCore.Tools -Version 3.1.6

Step 3: Create a database

To create a database, open SQL Server Management Studio and connect to your SQL Server instance. In the "Object Explorer" window, right-click on the "Databases" node and select "New Database".

In the "New Database" window, enter a name for your database and click on "OK".

Step 4: Add a model

To add a model to your ASP.NET Core MVC application, right-click on the "Models" folder and select "Add" -> "Class".

In the "Add New Item" window, enter a name for your model class and click on "Add".

In the model class, define the properties that you want to store in the database. For example:

public class Employee

{

public int Id { get; set; }

public string Name { get; set; }

public string Designation { get; set; }

}

Step 5: Add a database context

To connect to a database, we need to create a database context class that derives from the "DbContext" class. To add a database context class, right-click on the "Models" folder and select "Add" -> "Class".

In the "Add New Item" window, enter a name for your database context class and click on "Add".

In the database context class, add a DbSet property for each entity in the model. For example:

public class ApplicationDbContext : DbContext

{

public DbSet<Employee>

Employees { get; set; }

public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)

: base(options)

{

}

Step 6: Configure the database connection

To configure the database connection, open the "appsettings.json" file and add a connection string. The connection string should contain the server name, database name, and the authentication details.

For example:

"ConnectionStrings": {

"DefaultConnection": "Server=localhost;Database=EmployeeDB;Trusted_Connection=True;MultipleActiveResultSets=true"

}

Step 7: Register the database context

To use the database context in your ASP.NET Core MVC application, you need to register it in the "ConfigureServices" method of the "Startup" class.

To do this, open the "Startup" class and add the following code in the "ConfigureServices" method:

services.AddDbContext<ApplicationDbContext>(options =>

options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Step 8: Create a database table

To create a database table, you need to use the "Migration" feature of Entity Framework Core.

To add a migration, open the "Package Manager Console" window and enter the following command:

Add-Migration InitialCreate

This command will create a migration script that contains the SQL statements to create the database table.

To apply the migration, enter the following command:

Update-Database

This command will execute the SQL statements in the migration script to create the database table.

Step 9: CRUD operations

To perform CRUD (create, read, update, delete) operations on the database, you can use the methods of the database context class.

For example, to add a record to the database, you can use the following code:

using (var context = new ApplicationDbContext())

{

var employee = new Employee

{

Name = "John Doe",

Designation = "Manager"

};

context.Employees.Add(employee);

context.SaveChanges();

}

To read records from the database, you can use the following code:

using (var context = new ApplicationDbContext())

{

var employees = context.Employees.ToList();

}

To update a record, you can use the following code:

using (var context = new ApplicationDbContext())

{

var employee = context.Employees.FirstOrDefault(e => e.Id == 1);

if (employee != null)

{

employee.Name = "Jane Doe";

context.SaveChanges();

}

}

To delete a record, you can use the following code:

using (var context = new ApplicationDbContext())

{

var employee = context.Employees.FirstOrDefault(e => e.Id == 1);

if (employee != null)

{

context.Employees.Remove(employee);

context.SaveChanges();

}

}

Conclusion

In this tutorial, we learned how to connect a SQL Server database to an ASP.NET Core MVC application. We installed the necessary packages, created a database, added a model and a database context, configured the database connection, and performed CRUD operations on the database.

We hope this tutorial was helpful and you are now able to connect a SQL Server database to your ASP.NET Core MVC application.

Also, Bacancy is a leading name in offering the best-in-class .Net Development Services worldwide. They have a pool of talented Dot Net developers available to assist you throughout your development journey. If you are also looking to Hire .NET Developer for your next project, get in touch with them.