Forem: mtyide

[Boost]

mtyide — Tue, 05 May 2026 12:22:06 +0000

mtyide

May 5

Connecting to SQL Server Using Dapper

#dapper #sqlserver #netcore #vscode

Comments

2 min read

Connecting to SQL Server Using Dapper

mtyide — Tue, 05 May 2026 09:43:10 +0000

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

Dapper vs. Entity Framework Core: An In-Depth Comparison

mtyide — Mon, 04 May 2026 05:57:12 +0000

Dapper vs. Entity Framework

When building data-driven applications in .NET, two of the most popular data access technologies are Dapper and Entity Framework Core (EF Core). While both serve the same fundamental purpose—interacting with databases—they take very different approaches. Choosing between them depends heavily on your performance needs, development style, and project complexity.

Let’s break down how they compare and where each one shines.

What Are They?

Dapper:

Dapper is a micro-ORM (Object-Relational Mapper) created by Stack Overflow. It focuses on speed and simplicity, acting as a lightweight wrapper over raw SQL queries.

You write SQL manually
Minimal abstraction
Extremely fast performance

Entity Framework Core (EF Core):

EF Core is a full-featured ORM developed by Microsoft. It allows developers to interact with databases using C# objects instead of SQL.

Uses LINQ queries:
Supports change tracking, migrations, and relationships
Abstracts much of the database layer

Performance Comparison:
Dapper: Built for Speed
Dapper is widely known for being one of the fastest data access tools in .NET.

Why?

Minimal overhead
No change tracking
Executes raw SQL directly

Best for:

High-performance APIs
Real-time systems
Applications where every millisecond matters

EF Core: Slightly Slower, But Smarter

EF Core introduces overhead due to:

Change tracking
Query translation (LINQ → SQL)
Object materialization

That said, EF Core has improved significantly in recent versions and is fast enough for most applications.

Best for:

Complex business logic
Rapid development
Maintainability over raw speed

Developer Experience
EF Core: Productivity Powerhouse
EF Core excels in developer productivity.

Benefits:

Strong typing with LINQ
Built-in migrations
Automatic relationship handling
Change tracking

You can write queries like:

var users = context.Users.Where(u => u.IsActive).ToList();
Instead of raw SQL.

Dapper: Full Control, More Responsibility
With Dapper, you write SQL directly:

var users = connection.Query<User>("SELECT * FROM Users WHERE IsActive = 1");

Pros:

Total control over queries
No hidden SQL generation

Cons:

More boilerplate
Higher chance of human error
Harder to maintain large systems

Flexibility & Control
Dapper: Maximum Control

You control every query
Easy to optimize performance
Works well with stored procedures

Tradeoff: You must manage relationships and mapping manually.

EF Core: Convention Over Configuration

Handles joins, relationships, and navigation properties
Abstracts SQL complexity

Tradeoff: Less transparency into generated SQL (though logging helps)

Use Case Scenarios
When to Choose Dapper
Choose Dapper if:

You need maximum performance
Your queries are complex and highly optimized
You prefer full SQL control
You're building microservices or APIs

Example:

Financial trading systems
High-throughput APIs
Reporting systems

When to Choose EF Core
Choose EF Core if:

You want rapid development
Your app has complex relationships
You need maintainability and scalability
You prefer working with C# over SQL

Example:

Enterprise applications
CRUD-heavy systems
SaaS platforms

Can You Use Both?

Yes—and many teams do.

A hybrid approach is common:

Use EF Core for most operations
Use Dapper for performance-critical queries

This gives you:

Productivity + Performance
Clean architecture + optimization where needed

Key Benefits Summary

Dapper Advantages

Blazing fast
Full SQL control
Minimal overhead
Great for performance-critical apps

EF Core Advantages

High productivity
Rich feature set
Easier maintenance
Built-in migrations and tracking

Final Verdict

There’s no universal “better” choice—it depends on your priorities:

If performance is king, go with Dapper
If developer productivity and maintainability matter most, choose EF Core
If you want the best of both worlds, combine them strategically

Final Perspective

At Techm Studios, the philosophy isn’t about choosing one framework over another—it’s about choosing the right tool for the job. Every system has unique requirements, and the goal is always to align technology decisions with business needs, scalability goals, and performance expectations.

Whether that means leveraging the raw speed of Dapper, the productivity of EF Core, or a hybrid of both, the focus remains on delivering fast, secure, and reliable database interactions without compromising maintainability.