Connecting to SQL Server with the Dapper framework is one of the simplest and fastest ways to work with databases in .NET. Dapper is a lightweight ORM (Object-Relational Mapper) created by Stack Overflow, designed for performance and minimal abstraction—ideal if you want control over your SQL while avoiding boilerplate code.

How to Connect to SQL Server Using Dapper

1. What You Need

Before getting started, make sure you have:

• A .NET project (e.g., .NET 6 or later)
• SQL Server installed (local or remote)
• NuGet packages: Dapper, Microsoft.Data.SqlClient

You can install them via NuGet:

dotnet add package Dapper
dotnet add package Microsoft.Data.SqlClient

2. Create a Connection String

A typical SQL Server connection string looks like:

string connectionString = "Server=localhost;Database=YourDb;Trusted_Connection=True;";

Or with SQL authentication:

string connectionString = "Server=localhost;Database=YourDb;User Id=yourUser;Password=yourPassword;";

3. Open a Connection

Dapper works on top of ADO.NET, so you use SqlConnection:

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

IDbConnection db = new SqlConnection(connectionString);

4. Execute a Simple Query

Here’s how to fetch data:

using Dapper;

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

Dapper automatically maps columns to your C# class:

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

5. Parameterized Queries (Important for Safety)

Avoid SQL injection by using parameters:

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

6. Insert Data

var rowsAffected = db.Execute(
 "INSERT INTO Users (Name) VALUES (@Name)",
 new { Name = "John" }
);

7. Async Support (Recommended)

Dapper supports async operations:

var users = await db.QueryAsync<User>("SELECT * FROM Users");

Useful Online Resources

Here are solid free learning materials to go deeper:

• Official GitHub repo: https://github.com/DapperLib/Dapper
• Microsoft SQL Client docs: https://learn.microsoft.com/sql/connect/ado-net/
• Dapper tutorial (step-by-step): https://www.learndapper.com/
• SQL Server basics: https://learn.microsoft.com/sql/sql-server/

Why Use Dapper?

• Extremely fast (close to raw SQL performance)
• Minimal learning curve
• Full control over SQL queries
• Lightweight (no heavy ORM overhead like Entity Framework)

Thrifty Tip

If you're experimenting locally, you don’t need to pay for SQL Server—use:

• SQL Server Express (completely free)
• Or a free cloud tier from providers like Azure

Published by Techm Studios Pty Ltd, South Africa