High-Performance Data Access with Dapper in .NET

Dapper is a micro-ORM designed for speed and simplicity. Unlike full-featured ORMs such as Entity Framework, Dapper avoids complex object tracking and query generation overhead—instead, it extends IDbConnection with lightweight extension methods that execute raw SQL and map results directly to strongly typed objects.

It supports multiple relational databases (SQL Server, PostgreSQL, MySQL, SQLite, etc.) via standard ADO.NET providers and maintains excellent performance across high-throughput scenarios.

Getting Started

Install the package via NuGet:

dotnet add package Dapper

Connnection Handling

Dapper automatically manages connection state: if the connection is closed before execution, it opens it; after execution, it leaves the state unchanged (it does not auto-close). This gives developers fine-grained control while reducing boilerplate.

using var db = new SqlConnection(Configuration.GetConnectionString("Default")); // or ConfigurationManager.ConnectionStrings["SqlCon"].ConnectionString

Basic CRUD Operations

Assume the following table exists:

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

Insert

Single record insertion using anonymous parameter binding:

var newUser = new { Name = "Alice", Email = "alice@example.com", Location = "New York" };
db.Execute("INSERT INTO Users (Name, Email, Location) VALUES (@Name, @Email, @Location)", newUser);

When the POCO matches column names, you can pass the object directly:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Location { get; set; }
}

var user = new User { Name = "Bob", Email = "bob@example.com", Location = "Seattle" };
db.Execute("INSERT INTO Users (Name, Email, Location) VALUES (@Name, @Email, @Location)", user);

Batch insert using a list:

var users = new[]
{
    new User { Name = "Charlie", Email = "charlie@example.com", Location = "Chicago" },
    new User { Name = "Diana", Email = "diana@example.com", Location = "Austin" }
};

db.Execute("INSERT INTO Users (Name, Email, Location) VALUES (@Name, @Email, @Location)", users);

Udpate

Parameterized update with named placeholders:

var updateParams = new { Name = "Eve", Id = 5 };
db.Execute("UPDATE Users SET Name = @Name WHERE Id = @Id", updateParams);

Delete

Safe deletion using parameters:

db.Execute("DELETE FROM Users WHERE Id = @Id", new { Id = 3 });

Select

Fetch all records:

var allUsers = db.Query<User>("SELECT * FROM Users").ToList();

Fetch single entity by key:

var targetUser = db.QuerySingleOrDefault<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = 7 });

Fetch multiple matchnig records:

var filteredUsers = db.Query<User>("SELECT * FROM Users WHERE Name LIKE @Pattern", new { Pattern = "%a%" }).ToList();

IN-clause queries with array parameters (Dapper handles expansion automatically):

int[] ids = { 1, 4, 6, 9 };
var selectedUsers = db.Query<User>("SELECT * FROM Users WHERE Id IN @ids", new { ids }).ToList();

Tags: Dapper ADO.NET sql-server csharp ORM

Posted on Mon, 19 Oct 2026 16:43:04 +0000 by Cameri

Freaks City