DEV Community

Cover image for Connecting to SQL Server Using Dapper
mtyide
mtyide

Posted on

Connecting to SQL Server Using Dapper

#dapper #sqlserver #netcore #vscode

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
Enter fullscreen mode Exit fullscreen mode

2. Create a Connection String

A typical SQL Server connection string looks like:

string connectionString = "Server=localhost;Database=YourDb;Trusted_Connection=True;";
Enter fullscreen mode Exit fullscreen mode

Or with SQL authentication:

string connectionString = "Server=localhost;Database=YourDb;User Id=yourUser;Password=yourPassword;";
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

4. Execute a Simple Query

Here’s how to fetch data:

using Dapper;

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

Dapper automatically maps columns to your C# class:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}
Enter fullscreen mode Exit fullscreen mode

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 }
);
Enter fullscreen mode Exit fullscreen mode

6. Insert Data

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

7. Async Support (Recommended)

Dapper supports async operations:

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

Useful Online Resources

Here are solid free learning materials to go deeper:

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

Top comments (0)