Connecting ASP.NET Core to SQL Server: A Simple Walkthrough

Introduction

In full-stack development, your backend application needs to communicate with a database to store, retrieve, and manage data.

ASP.NET Core makes it easy to connect to SQL Server, one of the most popular relational databases.

This article explains step by step how beginners can connect ASP.NET Core to SQL Server, set up the database, and perform basic operations.

Step 1: Prerequisites

Before starting, make sure you have:

Visual Studio 2022 or VS Code installed.
SQL Server installed (Express or full version).
.NET 6 or .NET 7 SDK installed.

Step 2: Create a New ASP.NET Core Web API Project

Open a terminal or Visual Studio:

dotnet new webapi -n SqlServerDemo
cd SqlServerDemo
dotnet run

This creates a Web API project.
By default, it runs on https://localhost:5001.

Step 3: Install Entity Framework Core Packages

Entity Framework Core (EF Core) is an ORM that helps your application interact with SQL Server without writing raw SQL queries.

Install these packages using NuGet Package Manager or dotnet CLI:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

Microsoft.EntityFrameworkCore – Core EF library.
Microsoft.EntityFrameworkCore.SqlServer – SQL Server provider.
Microsoft.EntityFrameworkCore.Tools – For migrations and database updates.

Step 4: Create the Data Model

Create a folder Models and a class User.cs:

namespace SqlServerDemo.Models
{
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
    }
}

Each property represents a column in the database table.

Step 5: Create the Database Context

Create a folder Data and a class AppDbContext.cs:

using Microsoft.EntityFrameworkCore;
using SqlServerDemo.Models;

namespace SqlServerDemo.Data
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options)
            : base(options)
        {
        }

        public DbSet<User> Users { get; set; }
    }
}

DbSet<User> represents the Users table in SQL Server.
DbContext manages database connections and operations.

Step 6: Configure Connection String

In appsettings.json, add a connection string:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=SqlServerDemoDB;Trusted_Connection=True;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Server=localhost – SQL Server instance.
Database=SqlServerDemoDB – Name of the database. EF Core will create it if it does not exist.
Trusted_Connection=True – Uses Windows authentication.

Step 7: Register DbContext in Program.cs

Open Program.cs and add:

using Microsoft.EntityFrameworkCore;
using SqlServerDemo.Data;

var builder = WebApplication.CreateBuilder(args);

// Add DbContext with SQL Server
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddControllers();

var app = builder.Build();

app.MapControllers();

app.Run();

EF Core now knows how to connect to SQL Server using the connection string.

Step 8: Create the Database Using Migrations

Open terminal and run:

dotnet ef migrations add InitialCreate

This generates migration files that represent database structure.

Apply migration to create the database:

dotnet ef database update

EF Core creates the SqlServerDemoDB database and the Users table automatically.

Step 9: Create a Sample Controller

Create Controllers/UsersController.cs:

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using SqlServerDemo.Data;
using SqlServerDemo.Models;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace SqlServerDemo.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class UsersController : ControllerBase
    {
        private readonly AppDbContext _context;

        public UsersController(AppDbContext context)
        {
            _context = context;
        }

        [HttpGet]
        public async Task<IEnumerable<User>> GetUsers()
        {
            return await _context.Users.ToListAsync();
        }

        [HttpPost]
        public async Task<ActionResult<User>> AddUser(User user)
        {
            _context.Users.Add(user);
            await _context.SaveChangesAsync();
            return CreatedAtAction(nameof(GetUsers), new { id = user.Id }, user);
        }
    }
}

_context.Users is the Users table in the database.
ToListAsync() fetches all users asynchronously.
Add inserts a new user, and SaveChangesAsync() saves it in SQL Server.

Step 10: Test the API

Run the project.
Open Swagger (https://localhost:5001/swagger) or use Postman.
Use endpoints:

GET /api/users – Fetch all users.
POST /api/users – Add a new user:

{
  "name": "Rahul Sharma",
  "email": "[email protected]"
}

Verify that the data is saved in SQL Server Management Studio.

Step 11: Best Practices

Use Async Methods – Always use async/await with EF Core for performance.
Use Dependency Injection – Inject DbContext instead of creating it manually.
Store Connection Strings in appsettings.json – Avoid hardcoding credentials.
Use Migrations – Never create tables manually in production.
Use Repository Pattern – For larger projects, separate data access logic.

Conclusion

Connecting ASP.NET Core to SQL Server is straightforward:

Create models.
Create DbContext.
Configure connection string.
Use EF Core migrations to create the database.
Write controllers to perform CRUD operations.

Once you understand this workflow, you can build full-stack applications with Angular, React, or Vue that store and retrieve data from SQL Server via your ASP.NET Core backend.