How to Connect SQL Server with .NET Web API Using Entity Framework Core

Introduction

In Day 10, we learned about Entity Framework Core and understood how it simplifies database operations using C# objects, DbContext, DbSet, and LINQ.

We also created an Employee Management application and explored how EF Core interacts with our application entities.

But there is one important thing we haven't completed yet.

How do we connect our .NET Web API application to an actual SQL Server database?

Until now, we have discussed the concepts and written the application code. Now it's time to configure the database connection and make our application communicate with SQL Server.

In this tutorial, we'll continue with the same Employee Management API and connect it to SQL Server using Entity Framework Core.

We'll cover the complete process step by step, from installing the required packages to testing database operations.

1. What Do We Need Before Connecting SQL Server?

Before starting, make sure you have the following tools installed:

Visual Studio or Visual Studio Code.
.NET SDK.
SQL Server.
SQL Server Management Studio (SSMS), for managing and inspecting the database.
An existing .NET Web API project.
Basic knowledge of Entity Framework Core.

We'll use the following technologies:

Technology	Purpose
.NET Web API	Build API endpoints
C#	Application programming language
Entity Framework Core	ORM for database operations
SQL Server	Store application data
SSMS	Manage and inspect the database

For this tutorial, we'll use SQL Server with Entity Framework Core.

2. Create the Employee Management Project Structure

We'll continue with the Employee Management API from our previous articles.

A simple project structure can look like this:

EmployeeManagementAPI
│
├── Controllers
│   └── EmployeeController.cs
│
├── Data
│   └── ApplicationDbContext.cs
│
├── DTOs
│   ├── CreateEmployeeRequestDto.cs
│   └── EmployeeResponseDto.cs
│
├── Entities
│   └── Employee.cs
│
├── Services
│   ├── IEmployeeService.cs
│   └── EmployeeService.cs
│
├── appsettings.json
│
└── Program.cs

Let's understand how these folders are used:

Controllers: Handle incoming HTTP requests.
Data: Contains the database context.
DTOs: Define the data transferred through API endpoints.
Entities: Contain application entity classes.
Services: Contain application and database-related business operations.
appsettings.json: Stores application configuration, including the database connection string.
Program.cs: Configures application services and middleware.

3. Install Entity Framework Core SQL Server Package

To connect our application to SQL Server, we need the SQL Server database provider for Entity Framework Core.

Open the terminal in your project directory.

Execute:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

We also need the EF Core design package for design-time operations such as migrations.

dotnet add package Microsoft.EntityFrameworkCore.Design

To execute Entity Framework Core commands through the .NET CLI, install the EF Core command-line tool if it isn't already installed.

dotnet tool install --global dotnet-ef

If you already have it installed, you can update it:

dotnet tool update --global dotnet-ef

Important: Keep your EF Core runtime, SQL Server provider, Design package, and EF Core CLI tool compatible. For a production project, use a consistent EF Core version supported by your target .NET framework.

4. Create the Employee Entity

Now let's create the Employee entity.

Inside the Entities folder, create a class named Employee.cs.

namespace EmployeeManagementAPI.Entities
{
    public class Employee
    {
        public int EmployeeId { get; set; }

        public string FullName { get; set; } = string.Empty;

        public string Email { get; set; } = string.Empty;

        public string Department { get; set; } = string.Empty;

        public decimal Salary { get; set; }
    }
}

This class represents the employee information that we want to store in SQL Server.

Entity Framework Core will use this class to map application data to a database table.

For this example, EmployeeId will be treated as the primary key by EF Core's conventions.

5. Create ApplicationDbContext

Now let's create our database context.

Inside the Data folder, create a class named ApplicationDbContext.cs.

using Microsoft.EntityFrameworkCore;
using EmployeeManagementAPI.Entities;

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

        public DbSet<Employee> Employees { get; set; }
    }
}

Let's understand this code.

DbContext

The ApplicationDbContext class inherits from DbContext.

It manages database operations and tracks changes to entities.

DbContextOptions

The options object provides configuration for the database context, including its database provider.

DbSet

public DbSet<Employee> Employees { get; set; }

This property represents the Employee entity collection managed by the context.

When using a relational database, EF Core maps it to an Employees table by convention.

6. Configure the SQL Server Connection String

Now comes one of the most important steps.

We need to tell our application where our SQL Server database is located and how to connect to it.

Open the appsettings.json file.

Add a ConnectionStrings section.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=YOUR_SERVER_NAME;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;"
  },

  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },

  "AllowedHosts": "*"
}

Let's understand the connection string properties.

Property	Meaning
Server	SQL Server instance name
Database	Database name
Trusted_Connection	Uses Windows authentication
TrustServerCertificate	Trusts the server certificate without validating its chain

Replace YOUR_SERVER_NAME with your actual SQL Server instance name.

For example, a local SQL Server Express instance might use:

Server=.\SQLEXPRESS;

A local default SQL Server instance might use:

Server=localhost;

If you're using SQL Server LocalDB, the server name may look like:

Server=(localdb)\MSSQLLocalDB;

Important security note: TrustServerCertificate=True is commonly used in local development environments. For production, configure proper SQL Server TLS certificates and certificate validation.

If you're using SQL Server authentication instead of Windows authentication, your connection string will be different.

For example:

Server=YOUR_SERVER_NAME;Database=EmployeeDB;User Id=YOUR_USERNAME;Password=YOUR_PASSWORD;Encrypt=True;TrustServerCertificate=False;

Do not store production database passwords directly in source-controlled configuration files. Use environment variables, .NET user secrets for development, or a secure secret-management service.

7. Register DbContext in Program.cs

Now that we have created the connection string, we need to register our ApplicationDbContext with the .NET dependency injection container.

Open Program.cs.

Add the following namespaces:

using EmployeeManagementAPI.Data;
using Microsoft.EntityFrameworkCore;

Now register the DbContext:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

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

var app = builder.Build();

app.MapControllers();

app.Run();

Let's understand the important part:

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

AddDbContext()

Registers ApplicationDbContext in the dependency injection container.

By default, the context is registered with a scoped lifetime.

UseSqlServer()

Configures Entity Framework Core to use SQL Server as its database provider.

GetConnectionString()

Reads the connection string named DefaultConnection from application configuration.

Now our Web API knows how to configure its database context when it is requested.

8. Register the Employee Service

In Day 8, we learned about Dependency Injection.

In Day 10, we used an EmployeeService to communicate with our database through ApplicationDbContext.

Now we need to register that service.

Add the following line to Program.cs:

builder.Services.AddScoped<IEmployeeService, EmployeeService>();

Make sure you also add the required namespace:

using EmployeeManagementAPI.Services;

Our application can now inject IEmployeeService into the EmployeeController.

The controller does not need to create the database context manually.

Instead, the service receives the configured ApplicationDbContext through constructor injection.

9. Create the Database Using EF Core Migrations

Now our application is configured to communicate with SQL Server.

But we haven't created the EmployeeDB database and Employees table yet.

We can use Entity Framework Core Migrations to create them.

Open the terminal in your project directory.

Step 1: Create the initial migration

dotnet ef migrations add InitialCreate

This command creates a migration based on the current EF Core model.

The migration contains the schema changes required to create the database objects.

Step 2: Apply the migration

dotnet ef database update

This command applies the pending migrations to the database configured in your connection string.

If the database does not exist and the configured SQL Server account has sufficient permissions, EF Core can create it.

Step 3: Verify the database

Open SQL Server Management Studio.

Connect to your SQL Server instance.

Expand:

Databases
    |
    EmployeeDB
        |
        Tables

You should find an Employees table.

You may also see an __EFMigrationsHistory table.

EF Core uses this table to keep track of migrations that have already been applied.

10. Perform Database Operations Using Entity Framework Core

Now let's understand how our Web API can perform database operations.

We'll continue using the EmployeeService from Day 10.

Create or update IEmployeeService.cs:

using EmployeeManagementAPI.Entities;

namespace EmployeeManagementAPI.Services
{
    public interface IEmployeeService
    {
        Task<IEnumerable<Employee>> GetAllEmployeesAsync();

        Task<Employee?> GetEmployeeByIdAsync(int id);

        Task<Employee> CreateEmployeeAsync(Employee employee);
    }
}

Now create or update EmployeeService.cs:

using EmployeeManagementAPI.Data;
using EmployeeManagementAPI.Entities;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagementAPI.Services
{
    public class EmployeeService : IEmployeeService
    {
        private readonly ApplicationDbContext _context;

        public EmployeeService(ApplicationDbContext context)
        {
            _context = context;
        }

        public async Task<IEnumerable<Employee>> GetAllEmployeesAsync()
        {
            return await _context.Employees.ToListAsync();
        }

        public async Task<Employee?> GetEmployeeByIdAsync(int id)
        {
            return await _context.Employees.FindAsync(id);
        }

        public async Task<Employee> CreateEmployeeAsync(Employee employee)
        {
            _context.Employees.Add(employee);

            await _context.SaveChangesAsync();

            return employee;
        }
    }
}

Let's understand these operations.

Retrieve all employees

return await _context.Employees.ToListAsync();

Retrieves employee records from the database.

Retrieve an employee by ID

return await _context.Employees.FindAsync(id);

Finds an employee using its primary key.

Insert a new employee

_context.Employees.Add(employee);

await _context.SaveChangesAsync();

Adds the employee to the context and persists the change to SQL Server.

Notice that we use SaveChangesAsync() to save changes to the database.

11. Create an Employee Controller

Now let's expose these database operations through HTTP endpoints.

Create or update EmployeeController.cs inside the Controllers folder.

using Microsoft.AspNetCore.Mvc;
using EmployeeManagementAPI.DTOs;
using EmployeeManagementAPI.Entities;
using EmployeeManagementAPI.Services;

namespace EmployeeManagementAPI.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class EmployeeController : ControllerBase
    {
        private readonly IEmployeeService _employeeService;

        public EmployeeController(IEmployeeService employeeService)
        {
            _employeeService = employeeService;
        }

        [HttpGet]
        public async Task<IActionResult> GetAllEmployees()
        {
            var employees = await _employeeService.GetAllEmployeesAsync();

            var response = employees.Select(employee =>
                new EmployeeResponseDto
                {
                    EmployeeId = employee.EmployeeId,
                    FullName = employee.FullName,
                    Email = employee.Email,
                    Department = employee.Department,
                    Salary = employee.Salary
                });

            return Ok(response);
        }

        [HttpGet("{id:int}")]
        public async Task<IActionResult> GetEmployeeById(int id)
        {
            var employee = await _employeeService.GetEmployeeByIdAsync(id);

            if (employee == null)
            {
                return NotFound();
            }

            var response = new EmployeeResponseDto
            {
                EmployeeId = employee.EmployeeId,
                FullName = employee.FullName,
                Email = employee.Email,
                Department = employee.Department,
                Salary = employee.Salary
            };

            return Ok(response);
        }

        [HttpPost]
        public async Task<IActionResult> CreateEmployee(
            CreateEmployeeRequestDto request)
        {
            var employee = new Employee
            {
                FullName = request.FullName,
                Email = request.Email,
                Department = request.Department,
                Salary = request.Salary
            };

            var createdEmployee =
                await _employeeService.CreateEmployeeAsync(employee);

            var response = new EmployeeResponseDto
            {
                EmployeeId = createdEmployee.EmployeeId,
                FullName = createdEmployee.FullName,
                Email = createdEmployee.Email,
                Department = createdEmployee.Department,
                Salary = createdEmployee.Salary
            };

            return CreatedAtAction(
                nameof(GetEmployeeById),
                new { id = response.EmployeeId },
                response
            );
        }
    }
}

This controller uses:

GET to retrieve all employees.
GET with an ID to retrieve a particular employee.
POST to create a new employee.

Notice that we are still using the request and response DTOs from Day 9.

This keeps our API contract separate from our database entity.

12. Test the API Using Swagger or Postman

Now let's run our application.

Execute:

dotnet run

Open the API's Swagger page if Swagger is configured in your project, or use Postman to test the endpoints.

Test 1: Get All Employees

GET /api/employee

Expected response:

[
  {
    "employeeId": 1,
    "fullName": "Rahul Sharma",
    "email": "rahul@example.com",
    "department": "IT",
    "salary": 55000
  }
]

Test 2: Get Employee By ID

GET /api/employee/1

Expected response:

{
  "employeeId": 1,
  "fullName": "Rahul Sharma",
  "email": "rahul@example.com",
  "department": "IT",
  "salary": 55000
}

Test 3: Create a New Employee

POST /api/employee
Content-Type: application/json

Request body:

{
  "fullName": "Amit Verma",
  "email": "amit@example.com",
  "department": "Development",
  "salary": 65000
}

Expected response:

201 Created

Sample response body:

{
  "employeeId": 2,
  "fullName": "Amit Verma",
  "email": "amit@example.com",
  "department": "Development",
  "salary": 65000
}

The actual generated EmployeeId depends on the database's current records.

You can also open SSMS and execute:

SELECT * FROM Employees;

You should see the employee records stored in SQL Server.

13. Common SQL Server Connection Errors

While connecting a .NET Web API application to SQL Server, developers may encounter some common errors.

Let's understand a few of them.

1. Cannot connect to SQL Server

Possible reasons:

SQL Server service is not running.
Incorrect server or instance name.
SQL Server network configuration is incorrect.
Firewall or network restrictions are blocking the connection.

Check your SQL Server instance name and confirm that the database service is running.

2. Login failed for user

This usually happens when the application cannot authenticate with SQL Server.

Check:

Username and password.
SQL Server authentication configuration.
Windows authentication permissions.
Database access permissions.

3. Database does not exist

If the database has not been created, verify your connection string and run the required EF Core migration.

4. Unable to resolve DbContext

This can happen when ApplicationDbContext has not been registered correctly in Program.cs.

Verify that you have registered it using AddDbContext().

5. SQL Server certificate error

Modern SQL Server connections use encryption settings.

In local development, you may encounter certificate trust errors if the SQL Server certificate is not trusted.

Configure a trusted certificate for your environment. Avoid disabling certificate validation in production.

14. Important Things to Remember

Before moving to the next article, let's revise what we learned:

Entity Framework Core uses database providers to communicate with databases.
Microsoft.EntityFrameworkCore.SqlServer is the SQL Server provider.
Connection strings define how the application connects to SQL Server.
ApplicationDbContext inherits from DbContext.
AddDbContext() registers the database context through Dependency Injection.
UseSqlServer() configures the SQL Server provider.
EF Core Migrations help create and update database schemas.
SaveChangesAsync() persists changes to the database.
LINQ queries allow us to retrieve data using C#.
DTOs help us control the data exposed through API endpoints.

Conclusion

Connecting SQL Server to a .NET Web API application is an important step in building real-world backend applications.

In this article, we configured the SQL Server connection string, installed the required Entity Framework Core packages, registered ApplicationDbContext through Dependency Injection, and used migrations to create our database schema.

We also implemented employee retrieval and creation operations using EF Core and tested the API endpoints.

Now our Employee Management API can communicate with a real SQL Server database.

But there is another important question.

What if our database already exists and contains tables, relationships, and data? Do we always need to create our database using C# entity classes?

Let's understand both approaches in our next article.

What's Next?

Day 12 – Code First vs Database First

In the next article, we'll explore the two common approaches to working with databases in Entity Framework Core: Code First and Database First. We'll understand how they work, when to use each approach, and how to choose the right approach for a real-world .NET Web API application.

Skills Tutorials – Learn • Build • Grow Together

Continue learning with our 30-day .NET Core Web API series.

Skills Tutorials

Programming