How to connect sql server database in ASP.NET Core MVC

If you have started working in ASP.NET Core, then you will probably need to know How to connect sql server database in ASP.NET Core MVC, so in this article, I have mentioned step by step procedure to connect to SQL Server database and display data in table using Entity framework Core, database first approach.

Suppose we have below SQL Server database table with data, which we will show in ASP.NET Core Razor View Table code

Step 1: Create a new project in Visual Studio 2019/2022, we will open Visual Studio 2022, Click on "Create new project" -> then select "ASP.NET Core (Model-View-Controller) template" -> in the Next step, Select .NET Core version (I am using .NET Core 6) -> Click "Create".

Step 2: Once Visual Studio has generated basic .NET Core MVC files, we will need to install below NuGet package, using "Tools" -> "Nuget package manager" -> "Package Manager Console"

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Step 3: Now we will create Order.cs to get Order database table data mapping it.

So in Solution Explorer, right-click on the "Models" Folder -> Select "Add" -> Then Select "Class", name it as Order.cs

namespace EFCoreDatabaseConnect.Models
{
    public class Orders
    {
        public int Id { get; set; }
        public string? Country { get; set; }
        public double? Price { get; set; }
        public int? OrderQuantity { get; set; }

    }
}

Now, we will also need to add another class in Models Folder, i.e, MyDbContext.cs which will help us connect our database with ASP.NET Core application

using Microsoft.EntityFrameworkCore;

namespace EFCoreDatabaseConnect.Models
{
        public class MyDbContext : DbContext
        {
            public MyDbContext(DbContextOptions<MyDbContext> options)
                : base(options)
            { }
            public DbSet<Orders> Orders { get; set; }
        }
}

Step 4: Navigate to appsettings.json and add below connection string

  "ConnectionStrings": {
    "DefaultConnection": "Server=DESKTOP-DOG5T0Q\\SQLEXPRESS;Database=OrderDetails;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

Step 5: Now, we will need to read the connection string in Program.cs (As I am using .NET 6, so there is no startup.cs) and add database context in the application, so C# Code would be as below

using Microsoft.EntityFrameworkCore;
using EFCoreDatabaseConnect.Models;

var builder = WebApplication.CreateBuilder(args);

//read connection string from appsettings.json
builder.Services.AddDbContext<MyDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

Step 6: Navigate to HomeController.cs, where we will add a database context variable and also add code to fetch Orders list from the database and pass it to View.

using EFCoreDatabaseConnect.Models;
using Microsoft.AspNetCore.Mvc;

namespace EFCoreDatabaseConnect.Controllers
{
    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;
        //create dbContext variable
        private readonly MyDbContext _context;

        public HomeController(ILogger<HomeController> logger, MyDbContext dbContext)
        {
            _logger = logger;
            _context = dbContext;
        }

        public IActionResult Index()
        {
           //fetch data from database table orders
            var orders = _context.Orders.ToList();
          
            return View(orders);
        }


    }
}

Step 7: In the View(Index.cshtml), we will get the Model List as IEnumerable and loop through data to show it in HTML table

@model IEnumerable<EFCoreDatabaseConnect.Models.Orders>
@{
    ViewData["Title"] = "Home Page";
}
<table class="table table-bordered table-hover">
    <thead>
        <tr>
            <td>Order Id</td>
            <td>Country</td>
            <td>Price</td>
            <td>Quantity</td>
        </tr>
    </thead>
    <tbody>
        @foreach(var order in Model)
        {
            <tr>
                <td>@order.Id</td>
                <td>@order.Country</td>
                <td>$@order.Price</td>
                <td>@order.OrderQuantity</td>
            </tr>
        }
    </tbody>
</table>

Once you will build and run above project, you will see output as below

You may also like to read:

Form Submit in ASP.NET Core MVC

DropDownList in ASP.NET Core MVC

Creating GridView in ASP.NET Core MVC with Paging

Creating Cascading Dropdown list in ASP.NET Core MVC

jQuery AJAX in ASP.NET Core MVC

Send Email in ASP.NET Core (With Attachments)

Read Values from appsettings.json in .NET Core

Run or Execute Stored Procedure using EF Core

Bulk Insert in ASP.NET Core MVC using Entity Framework Core

Read OR Generate QR Code in ASP.NET Core

Fibonacci series In C# (Various possible ways)

Dropdown List in MVC using Razor HTML.DropdownList and HTML.DropdownListFor

Save Selected Dropdown and Text value in database in ASP.NET Web-Forms