Integrating Dapper with SQL Server for High-Performance Data Access in .NET

By Robert F3 min readFebruary 25, 2026

Prerequisites

Before diving into the integration, ensure you have the following:

.NET SDK installed (version 5.0 or later)
SQL Server instance running
Basic understanding of SQL and C#

Setting Up the Project

Create a new .NET console application:

   dotnet new console -n DapperExample
   cd DapperExample

Add the Dapper NuGet package:

   dotnet add package Dapper

Add the System.Data.SqlClient package:

   dotnet add package System.Data.SqlClient

Database Setup

For this example, we will use a simple database with a Users table. You can create the table using the following SQL script:

CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

Basic Usage of Dapper

Now that the project is set up, let's explore how to use Dapper for basic CRUD operations.

Connecting to the Database

Create a class to manage the database connection:

using System.Data.SqlClient;

public class DatabaseConnection
{
    private readonly string _connectionString;

    public DatabaseConnection(string connectionString)
    {
        _connectionString = connectionString;
    }

    public SqlConnection GetConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

Inserting Data

To insert a new user into the Users table, use the following method:

using Dapper;

public class UserRepository
{
    private readonly DatabaseConnection _dbConnection;

    public UserRepository(DatabaseConnection dbConnection)
    {
        _dbConnection = dbConnection;
    }

    public void AddUser(string name, string email)
    {
        using var connection = _dbConnection.GetConnection();
        connection.Open();

        var sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
        var parameters = new { Name = name, Email = email };

        connection.Execute(sql, parameters);
    }
}

Retrieving Data

To fetch users from the database, you can implement the following method:

public IEnumerable<User> GetAllUsers()
{
    using var connection = _dbConnection.GetConnection();
    connection.Open();

    var sql = "SELECT * FROM Users";
    return connection.Query<User>(sql);
}

Updating Data

To update a user's information, you can use this method:

public void UpdateUser(int id, string name, string email)
{
    using var connection = _dbConnection.GetConnection();
    connection.Open();

    var sql = "UPDATE Users SET Name = @Name, Email = @Email WHERE Id = @Id";
    var parameters = new { Id = id, Name = name, Email = email };

    connection.Execute(sql, parameters);
}

Deleting Data

To delete a user, implement the following method:

public void DeleteUser(int id)
{
    using var connection = _dbConnection.GetConnection();
    connection.Open();

    var sql = "DELETE FROM Users WHERE Id = @Id";
    var parameters = new { Id = id };

    connection.Execute(sql, parameters);
}

Example Usage

Here’s how you can use the UserRepository class in your Main method:

class Program
{
    static void Main(string[] args)
    {
        var connectionString = "Your SQL Server connection string here";
        var dbConnection = new DatabaseConnection(connectionString);
        var userRepository = new UserRepository(dbConnection);

        // Adding a user
        userRepository.AddUser("John Doe", "[email protected]");

        // Retrieving users
        var users = userRepository.GetAllUsers();
        foreach (var user in users)
        {
            Console.WriteLine($"{user.Id}: {user.Name} - {user.Email}");
        }

        // Updating a user
        userRepository.UpdateUser(1, "Jane Doe", "[email protected]");

        // Deleting a user
        userRepository.DeleteUser(1);
    }
}

Best Practices

Use Parameterized Queries: Always use parameterized queries to prevent SQL injection attacks.
Manage Connections: Use using statements to ensure connections are properly disposed of.
Error Handling: Implement try-catch blocks to handle potential exceptions during database operations.
Logging: Consider logging database operations for better traceability and debugging.

Conclusion

Dapper is a powerful micro-ORM that simplifies data access while maintaining high performance. By following the examples provided, you can efficiently manage your SQL Server database operations in a .NET application. This lightweight solution is ideal for applications requiring speed and flexibility.