How to Connect ASP.NET Core Web API to SQL Server Step by Step

Introduction

In modern application development, storing and retrieving data efficiently is essential. Most real-world applications—such as e-commerce platforms, banking systems, and enterprise tools—depend on databases to manage data.

One of the most commonly used databases with ASP.NET Core is SQL Server. Connecting your ASP.NET Core Web API to SQL Server allows you to perform operations like creating, reading, updating, and deleting data (CRUD operations).

In this guide, you will learn how to connect ASP.NET Core Web API to SQL Server step by step in simple words, using a clean and practical approach. This article is designed to be beginner-friendly while still following best practices used in real-world applications.

What Does It Mean to Connect ASP.NET Core Web API to SQL Server?

Understanding the Basics

Connecting ASP.NET Core Web API to SQL Server means enabling your application to communicate with a database.

Your API will:

Send data to SQL Server (Insert)
Retrieve data from SQL Server (Select)
Modify existing data (Update)
Remove data (Delete)

This communication is typically handled using Entity Framework Core (EF Core), which acts as a bridge between your application and the database.

Why Use SQL Server with ASP.NET Core?

Reliable and widely used database system
Strong integration with .NET ecosystem
Supports large-scale applications
Provides security and performance features

Prerequisites

What You Need Before Starting

Before connecting ASP.NET Core Web API to SQL Server, make sure you have:

.NET SDK installed
SQL Server installed (or SQL Server Express)
SQL Server Management Studio (SSMS) or Azure Data Studio
Basic understanding of ASP.NET Core Web API

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

Setting Up the Project

dotnet new webapi -n SqlConnectionDemo
cd SqlConnectionDemo

This will create a basic ASP.NET Core Web API project.

Step 2: Install Required NuGet Packages

Adding Entity Framework Core and SQL Server Provider

Install the following packages:

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

Why These Packages Are Needed

EF Core → ORM for database operations
SqlServer → Connects EF Core to SQL Server
Tools → Helps with migrations and database updates

Step 3: Create a Model (Entity Class)

Defining Your Data Structure

Create a folder named Models and add a class:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

What This Does

This class represents a table in SQL Server. Each property becomes a column.

Step 4: Create DbContext Class

Setting Up Database Context

Create a folder named Data and add:

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    public DbSet<Product> Products { get; set; }
}

Explanation

DbContext manages database connection
DbSet represents a table
EF Core uses this to perform operations

Step 5: Add Connection String in appsettings.json

Configuring SQL Server Connection

"ConnectionStrings": {
  "DefaultConnection": "Server=localhost;Database=ProductDb;Trusted_Connection=True;TrustServerCertificate=True;"
}

Key Parts Explained

Server → Your SQL Server instance
Database → Database name
Trusted_Connection → Windows authentication

Step 6: Register DbContext in Program.cs

Enabling Dependency Injection

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

What Happens Here

Registers database context
Connects application to SQL Server
Enables dependency injection

Step 7: Create Migration

Generating Database Schema

Run the following command:

dotnet ef migrations add InitialCreate

What Is Migration?

Migration creates database structure based on your models.

Step 8: Update Database

Applying Migration to SQL Server

dotnet ef database update

Result

Database is created
Tables are generated automatically

Step 9: Create API Controller

Building CRUD Endpoints

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly AppDbContext _context;

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

    [HttpGet]
    public IActionResult Get()
    {
        return Ok(_context.Products.ToList());
    }

    [HttpPost]
    public IActionResult Create(Product product)
    {
        _context.Products.Add(product);
        _context.SaveChanges();
        return Ok(product);
    }
}

What This Does

GET → Fetch data
POST → Insert data

Step 10: Test API

Using Postman or Swagger

Run your application
Open Swagger UI
Test endpoints

Expected Output

Data stored in SQL Server
Data retrieved successfully

Common Mistakes to Avoid

Common Errors Developers Face

Incorrect connection string
SQL Server not running
Missing EF Core packages
Forgetting migrations

Best Practices

Writing Clean and Scalable Code

Use Repository Pattern for large apps
Keep connection strings secure
Use async methods (ToListAsync, SaveChangesAsync)
Validate input data

Summary

Connecting ASP.NET Core Web API to SQL Server is a fundamental skill for building real-world applications. By using Entity Framework Core, you can easily map your models to database tables and perform CRUD operations efficiently. Following the step-by-step approach in this guide, you can create a strong backend that is secure, scalable, and production-ready. With proper configuration, migrations, and testing, your ASP.NET Core application can seamlessly interact with SQL Server for data-driven development.