Build an ASP.NET Core Web API with Dapper and SQL Server Stored Procedures

Introduction

In many applications, the API layer needs to communicate with a relational database to create, retrieve, update, and delete data.

In this article, we will build a simple ASP.NET Core Web API that performs CRUD operations on a Department table using Dapper and SQL Server Stored Procedures.

The example uses a simple layered structure:

Controller
    ↓
Service
    ↓
Repository
    ↓
Dapper
    ↓
SQL Server

The application will provide APIs to:

Get all departments.
Get a department by ID.
Create a department.
Update a department.
Soft-delete a department.

The goal is to demonstrate how Dapper can be used with stored procedures in an ASP.NET Core Web API.

Technologies Used

The example uses:

ASP.NET Core Web API
C#
Dapper
SQL Server
SQL Server Stored Procedures
Repository Pattern
Service Layer
Dependency Injection

Database Setup

First, create a database in SQL Server and create the Department table.

Create the Department Table

CREATE TABLE Department (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    Location VARCHAR(100),
    IsActive BIT DEFAULT 1
);

The table contains four columns:

Column	Type	Description
ID	INT	Primary key generated automatically
Name	VARCHAR(100)	Department name
Location	VARCHAR(100)	Department location
IsActive	BIT	Indicates whether the department is active

The IsActive column will be used for a soft delete instead of physically removing the record.

Create Stored Procedures

The application will use stored procedures for database operations.

1. GetDepartments

The following stored procedure retrieves all departments or a specific department when an ID is supplied.

CREATE PROCEDURE [dbo].[GetDepartments]
    @ID INT = NULL
AS
BEGIN
    SELECT
        ID,
        Name,
        Location,
        IsActive
    FROM Department
    WHERE (@ID IS NULL OR ID = @ID);
END

When @ID is NULL, all departments are returned.

When an ID is provided, only the matching department is returned.

2. Create or Update Department

The usp_CreateUpdateDepartment procedure handles both insert and update operations.

CREATE PROCEDURE [dbo].[usp_CreateUpdateDepartment]
    @id INT = NULL,
    @name VARCHAR(100) = NULL,
    @location VARCHAR(100) = NULL,
    @isactive BIT = NULL
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Department WHERE ID = @id)
    BEGIN
        UPDATE Department
        SET
            Location = @location,
            Name = @name,
            IsActive = @isactive
        WHERE ID = @id;

        SELECT @id;
    END
    ELSE
    BEGIN
        INSERT INTO Department (Name, Location, IsActive)
        VALUES (@name, @location, 1);

        SELECT CAST(SCOPE_IDENTITY() AS INT);
    END
END

SCOPE_IDENTITY() is used instead of @@IDENTITY because it returns the identity value generated in the current scope.

3. Delete Department

The delete operation performs a soft delete by changing IsActive to 0.

CREATE PROCEDURE [dbo].[usp_deleteDepartment]
    @id INT
AS
BEGIN
    UPDATE Department
    SET IsActive = 0
    WHERE ID = @id;

    SELECT @id;
END

The record remains in the database, but it is marked as inactive.

Create the ASP.NET Core Web API Project

Create a new ASP.NET Core Web API project using Visual Studio or the .NET CLI.

After creating the project, install the following NuGet packages:

Dapper
Microsoft.Data.SqlClient

Dapper provides lightweight object mapping and database access functionality.

Microsoft.Data.SqlClient provides SQL Server connectivity.

Create the Department Model

Create a class named Department.cs.

public class Department
{
    public int ID { get; set; }
    public string? Name { get; set; }
    public string? Location { get; set; }
    public bool IsActive { get; set; }
}

This class represents the Department table and is also used by Dapper to map query results.

Create the API Response Class

Create a class named APIResponse.cs to provide a consistent response structure.

using System.Net;

public class APIResponse<T>
{
    public HttpStatusCode StatusCode { get; set; }
    public string Message { get; set; }
    public T Data { get; set; }
    public string ErrorMsg { get; set; }

    public APIResponse(
        HttpStatusCode statusCode,
        string message,
        T data)
    {
        StatusCode = statusCode;
        Message = message;
        Data = data;
        ErrorMsg = null;
    }

    public APIResponse(
        HttpStatusCode statusCode,
        string message,
        string error)
    {
        StatusCode = statusCode;
        Message = message;
        Data = default;
        ErrorMsg = error;
    }

    public static APIResponse<T> Success(
        HttpStatusCode statusCode,
        string message,
        T data)
    {
        return new APIResponse<T>(
            statusCode,
            message,
            data);
    }

    public static APIResponse<T> Errors(
        HttpStatusCode statusCode,
        string message,
        string error)
    {
        return new APIResponse<T>(
            statusCode,
            message,
            error);
    }
}

This allows the API to return information such as status, message, data, and error details in a common structure.

Create the Repository Interface

Create IDepartmentRepository.cs.

public interface IDepartmentRepository
{
    List<Department> GetAll();
    Department GetByID(int id);
    int Create(Department department);
    int Update(Department department);
    int Delete(int id);
}

The repository interface defines the database operations required by the application.

Create the Service Interface

Create IDepartmentService.cs.

public interface IDepartmentService
{
    List<Department> GetAll();
    Department GetByID(int id);
    int Create(Department department);
    int Update(Department department);
    int Delete(int id);
}

The service interface defines the operations exposed by the service layer.

Create DepartmentService.cs

The service layer communicates with the repository.

public class DepartmentService : IDepartmentService
{
    private readonly IDepartmentRepository _departmentRepository;

    public DepartmentService(
        IDepartmentRepository departmentRepository)
    {
        _departmentRepository = departmentRepository;
    }

    public int Create(Department department)
    {
        return _departmentRepository.Create(department);
    }

    public int Delete(int id)
    {
        return _departmentRepository.Delete(id);
    }

    public List<Department> GetAll()
    {
        return _departmentRepository.GetAll();
    }

    public Department GetByID(int id)
    {
        return _departmentRepository.GetByID(id);
    }

    public int Update(Department department)
    {
        return _departmentRepository.Update(department);
    }
}

The service layer currently delegates the operations directly to the repository. In a larger application, this layer can also contain business rules and validation.

Create DepartmentRepository.cs

The repository is responsible for communicating with SQL Server using Dapper.

using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;

public class DepartmentRepository : IDepartmentRepository
{
    private readonly string _connectionString;

    public DepartmentRepository(IConfiguration configuration)
    {
        _connectionString =
            configuration.GetConnectionString("DbConnection");
    }

    public List<Department> GetAll()
    {
        using var connection =
            new SqlConnection(_connectionString);

        return connection
            .Query<Department>(
                "GetDepartments",
                commandType: CommandType.StoredProcedure)
            .ToList();
    }

    public Department GetByID(int id)
    {
        using var connection =
            new SqlConnection(_connectionString);

        var parameters = new DynamicParameters();
        parameters.Add("ID", id);

        return connection
            .Query<Department>(
                "GetDepartments",
                parameters,
                commandType: CommandType.StoredProcedure)
            .FirstOrDefault();
    }

    public int Create(Department department)
    {
        using var connection =
            new SqlConnection(_connectionString);

        var parameters = new DynamicParameters();

        parameters.Add("name", department.Name);
        parameters.Add("location", department.Location);

        return connection
            .Query<int>(
                "usp_CreateUpdateDepartment",
                parameters,
                commandType: CommandType.StoredProcedure)
            .FirstOrDefault();
    }

    public int Update(Department department)
    {
        using var connection =
            new SqlConnection(_connectionString);

        var parameters = new DynamicParameters();

        parameters.Add("id", department.ID);
        parameters.Add("name", department.Name);
        parameters.Add("location", department.Location);
        parameters.Add("isactive", department.IsActive);

        return connection
            .Query<int>(
                "usp_CreateUpdateDepartment",
                parameters,
                commandType: CommandType.StoredProcedure)
            .FirstOrDefault();
    }

    public int Delete(int id)
    {
        using var connection =
            new SqlConnection(_connectionString);

        var parameters = new DynamicParameters();
        parameters.Add("id", id);

        return connection
            .Query<int>(
                "usp_deleteDepartment",
                parameters,
                commandType: CommandType.StoredProcedure)
            .FirstOrDefault();
    }
}

How Dapper Is Used

Dapper provides the Query<T>() method to execute a query or stored procedure and map the returned records to a C# object.

For example:

connection.Query<Department>(
    "GetDepartments",
    commandType: CommandType.StoredProcedure);

Dapper maps columns returned by SQL Server to matching properties in the Department class.

DynamicParameters is used to pass values to stored procedure parameters.

Create the Department Controller

Create a controller named DepartmentController.cs.

using Microsoft.AspNetCore.Mvc;
using System.Net;

[Route("api/[controller]")]
[ApiController]
public class DepartmentController : ControllerBase
{
    private readonly IDepartmentService _departmentService;

    public DepartmentController(
        IDepartmentService departmentService)
    {
        _departmentService = departmentService;
    }

    [HttpGet("getAll")]
    public APIResponse<List<Department>> GetAll()
    {
        try
        {
            var response = _departmentService.GetAll();

            return APIResponse<List<Department>>.Success(
                HttpStatusCode.OK,
                "Success",
                response);
        }
        catch (Exception ex)
        {
            return APIResponse<List<Department>>.Errors(
                HttpStatusCode.InternalServerError,
                "Failed",
                ex.Message);
        }
    }

    [HttpGet("getByID")]
    public APIResponse<Department> GetByID(int id)
    {
        try
        {
            var response = _departmentService.GetByID(id);

            if (response == null)
            {
                return APIResponse<Department>.Errors(
                    HttpStatusCode.NotFound,
                    "Department not found",
                    $"Department with ID {id} was not found.");
            }

            return APIResponse<Department>.Success(
                HttpStatusCode.OK,
                "Success",
                response);
        }
        catch (Exception ex)
        {
            return APIResponse<Department>.Errors(
                HttpStatusCode.InternalServerError,
                "Failed",
                ex.Message);
        }
    }

    [HttpPost("create")]
    public APIResponse<int> Create(Department department)
    {
        try
        {
            var response = _departmentService.Create(department);

            if (response > 0)
            {
                return APIResponse<int>.Success(
                    HttpStatusCode.OK,
                    "Department created successfully",
                    response);
            }

            return APIResponse<int>.Errors(
                HttpStatusCode.BadRequest,
                "Department creation failed",
                response.ToString());
        }
        catch (Exception ex)
        {
            return APIResponse<int>.Errors(
                HttpStatusCode.InternalServerError,
                "Department creation failed",
                ex.Message);
        }
    }

    [HttpPost("update")]
    public APIResponse<int> Update(Department department)
    {
        try
        {
            var response = _departmentService.Update(department);

            if (response > 0)
            {
                return APIResponse<int>.Success(
                    HttpStatusCode.OK,
                    "Department updated successfully",
                    response);
            }

            return APIResponse<int>.Errors(
                HttpStatusCode.BadRequest,
                "Department update failed",
                response.ToString());
        }
        catch (Exception ex)
        {
            return APIResponse<int>.Errors(
                HttpStatusCode.InternalServerError,
                "Department update failed",
                ex.Message);
        }
    }

    [HttpDelete("delete")]
    public APIResponse<int> Delete(int id)
    {
        try
        {
            var response = _departmentService.Delete(id);

            if (response > 0)
            {
                return APIResponse<int>.Success(
                    HttpStatusCode.OK,
                    "Department deleted successfully",
                    response);
            }

            return APIResponse<int>.Errors(
                HttpStatusCode.BadRequest,
                "Department deletion failed",
                response.ToString());
        }
        catch (Exception ex)
        {
            return APIResponse<int>.Errors(
                HttpStatusCode.InternalServerError,
                "Department deletion failed",
                ex.Message);
        }
    }
}

Configure the Connection String

Add the SQL Server connection string to appsettings.json.

{
  "ConnectionStrings": {
    "DbConnection": "Data Source=YourServerName;Initial Catalog=DatabaseName;Integrated Security=True;Persist Security Info=True"
  }
}

Replace YourServerName and DatabaseName with the values for your environment.

For production applications, connection strings containing passwords or other secrets should not be committed directly to source control. Use an appropriate secret-management mechanism.

Register Dependencies

Open Program.cs and register the service and repository using dependency injection.

builder.Services.AddScoped<IDepartmentService, DepartmentService>();
builder.Services.AddScoped<IDepartmentRepository, DepartmentRepository>();

The dependency flow is:

DepartmentController
        ↓
IDepartmentService
        ↓
DepartmentService
        ↓
IDepartmentRepository
        ↓
DepartmentRepository
        ↓
Dapper
        ↓
SQL Server

When ASP.NET Core creates DepartmentController, the dependency injection container provides the required IDepartmentService, which in turn receives IDepartmentRepository.

Test the API

After configuring the database and running the application, the API can be tested using Swagger, Postman, or another HTTP client.

Assuming the application is running locally, the URLs will follow this structure:

GET    /api/Department/getAll
GET    /api/Department/getByID?id=1
POST   /api/Department/create
POST   /api/Department/update
DELETE /api/Department/delete?id=1

The exact host and port depend on the local application configuration.

Get All Departments

Request:

GET /api/Department/getAll

Example response:

{
  "statusCode": 200,
  "message": "Success",
  "data": [
    {
      "id": 1,
      "name": "IT",
      "location": "Bangalore",
      "isActive": true
    },
    {
      "id": 2,
      "name": "HR",
      "location": "Delhi",
      "isActive": true
    }
  ],
  "errorMsg": null
}

Get Department by ID

Request:

GET /api/Department/getByID?id=1

Example response:

{
  "statusCode": 200,
  "message": "Success",
  "data": {
    "id": 1,
    "name": "IT",
    "location": "Bangalore",
    "isActive": true
  },
  "errorMsg": null
}

Create a Department

Request:

POST /api/Department/create

Request body:

{
  "name": "Finance",
  "location": "Mumbai",
  "isActive": true
}

The stored procedure inserts the department and returns the generated ID.

Example response:

{
  "statusCode": 200,
  "message": "Department created successfully",
  "data": 3,
  "errorMsg": null
}

Update a Department

Request:

POST /api/Department/update

Request body:

{
  "id": 3,
  "name": "Finance and Accounts",
  "location": "Mumbai",
  "isActive": true
}

The stored procedure checks whether the ID exists and updates the record.

Delete a Department

Request:

DELETE /api/Department/delete?id=3

The delete operation updates IsActive to 0.

This is known as a soft delete because the database record is not physically removed.

Important Improvements for Production

The example demonstrates the basic implementation, but a production API should consider several additional improvements.

Use Asynchronous Database Operations

For applications handling many concurrent requests, asynchronous APIs such as QueryAsync<T>() can be used to avoid blocking request threads while database operations are running.

Validate Request Data

The API should validate values such as:

Department name
Location
Department ID

For example, an empty department name should not be accepted.

Avoid Returning Raw Exception Messages

The sample returns ex.Message in the API response for demonstration purposes.

In production, returning raw exception details can expose internal implementation information. A safer approach is to log the exception and return a generic error message to the client.

Use Appropriate HTTP Status Codes

The sample can be further improved by returning standard HTTP responses such as:

200 OK for successful operations.
201 Created after creating a resource.
400 Bad Request for invalid input.
404 Not Found when a department does not exist.
500 Internal Server Error for unexpected server errors.

Use DTOs for API Contracts

For larger applications, separate request and response DTOs can prevent database models from becoming tightly coupled to public API contracts.

Advantages of Using Dapper With Stored Procedures

This approach provides several benefits:

Lightweight database access.
Simple object mapping.
Good control over SQL queries.
Stored procedures can centralize database operations.
Repository and service layers separate responsibilities.
Dependency injection makes components easier to replace and test.

Limitations and Considerations

Dapper does not provide the same level of abstraction as a full ORM such as Entity Framework Core.

When using stored procedures extensively, database logic can also become more dependent on SQL Server.

The appropriate approach depends on the application requirements, team experience, database architecture, and expected level of SQL control.

Project Structure

A simple project structure for this example can look like:

DepartmentApi/
│
├── Controllers/
│   └── DepartmentController.cs
│
├── Models/
│   ├── Department.cs
│   └── APIResponse.cs
│
├── Repositories/
│   ├── IDepartmentRepository.cs
│   └── DepartmentRepository.cs
│
├── Services/
│   ├── IDepartmentService.cs
│   └── DepartmentService.cs
│
├── Program.cs
└── appsettings.json

This structure keeps the API controller, business/service logic, and database access code separated.

Conclusion

In this article, we built a simple ASP.NET Core Web API using Dapper and SQL Server Stored Procedures.

The implementation demonstrated how to:

Create a SQL Server table.
Create stored procedures for CRUD operations.
Configure a database connection.
Use Dapper to execute stored procedures.
Map database results to C# objects.
Implement repository and service layers.
Register dependencies using dependency injection.
Expose CRUD operations through an ASP.NET Core controller.
Implement a soft-delete operation.

The example provides a starting point for applications where developers want lightweight database access while retaining direct control over SQL and stored procedures.

For a complete C# Corner submission, adding screenshots from the author's own Swagger/Postman execution and uploading the complete working project as a ZIP would make the tutorial more reproducible and directly address the practical aspect expected from a step-by-step article.