ASP.NET Core  

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.

Membership not found