What is Dapper?

Dapper is a high-performance, lightweight Object-Relational Mapper (ORM) for .NET applications. It is developed by Stack Overflow and is known for its speed and simplicity in executing SQL queries directly against a database while mapping the results to .NET objects.

Unlike Entity Framework (EF Core), which provides automatic change tracking and complex data handling, Dapper focuses on raw SQL execution and fast data retrieval.

---

Key Features of Dapper

• ✔ Fast Performance: Dapper is almost as fast as using raw ADO.NET because it minimizes overhead.
• ✔ Lightweight & Simple: It’s a single-file library that works with existing ADO.NET connections.
• ✔ Supports Parameterized Queries: Helps prevent SQL injection.
• ✔ Automatic Object Mapping: Maps query results to .NET objects with minimal configuration.
• ✔ Supports Multiple Databases: Works with SQL Server, MySQL, PostgreSQL, SQLite, Oracle, etc.
• ✔ Supports Stored Procedures: Easily executes stored procedures and maps results to objects.

---

Installing Dapper

To use Dapper in a .NET Core project, install the Dapper NuGet package:

dotnet add package Dapper

or

Install-Package Dapper

---

How to Use Dapper

1. Establishing a Database Connection

Dapper uses ADO.NET’s SqlConnection to interact with the database.

using System.Data.SqlClient; using Dapper; string connectionString = "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;"; using (var connection = new SqlConnection(connectionString)) { connection.Open(); Console.WriteLine("Connected to Database!"); }

---

2. Fetching Data Using Dapper

Dapper makes query execution simple and fast.

Example: Fetching a Single Record

var sql = "SELECT * FROM Users WHERE UserId = @Id"; using (var connection = new SqlConnection(connectionString)) { var user = connection.QueryFirstOrDefault<User>(sql, new { Id = 1 }); Console.WriteLine($"User: {user.Name}, Email: {user.Email}"); }

✔ Uses QueryFirstOrDefault<T>() to fetch a single record and map it to a User object.

---

3. Fetching Multiple Records

var sql = "SELECT * FROM Users"; using (var connection = new SqlConnection(connectionString)) { var users = connection.Query<User>(sql).ToList(); foreach (var user in users) { Console.WriteLine($"User: {user.Name}, Email: {user.Email}"); } }

✔ Uses Query<T>() to fetch multiple records efficiently.

---

4. Inserting Data Using Dapper

var sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)"; using (var connection = new SqlConnection(connectionString)) { int rowsAffected = connection.Execute(sql, new { Name = "John Doe", Email = "john@example.com" }); Console.WriteLine($"Inserted {rowsAffected} record(s)"); }

✔ Uses Execute() for INSERT, UPDATE, and DELETE operations.

---

5. Updating Data Using Dapper

var sql = "UPDATE Users SET Email = @Email WHERE UserId = @Id"; using (var connection = new SqlConnection(connectionString)) { int rowsAffected = connection.Execute(sql, new { Id = 1, Email = "newemail@example.com" }); Console.WriteLine($"Updated {rowsAffected} record(s)"); }

---

6. Deleting Data Using Dapper

var sql = "DELETE FROM Users WHERE UserId = @Id"; using (var connection = new SqlConnection(connectionString)) { int rowsAffected = connection.Execute(sql, new { Id = 1 }); Console.WriteLine($"Deleted {rowsAffected} record(s)"); }

---

7. Calling a Stored Procedure with Dapper

Dapper also supports executing stored procedures easily.

var sql = "GetUserById"; // Name of the stored procedure using (var connection = new SqlConnection(connectionString)) { var user = connection.QueryFirstOrDefault<User>(sql, new { Id = 1 }, commandType: CommandType.StoredProcedure); Console.WriteLine($"User: {user.Name}, Email: {user.Email}"); }

✔ Uses CommandType.StoredProcedure to execute a stored procedure.

---

When to Use Dapper Instead of Entity Framework (EF Core)?

• ✔ Use Dapper for high-performance APIs and raw SQL execution.
• ✔ Use EF Core when you need automatic tracking and complex relationships.

---

Conclusion

Dapper is a lightweight, high-performance ORM for .NET applications that simplifies querying and mapping SQL data to C# objects. It is perfect for microservices, APIs, and applications that require raw SQL execution with minimal overhead.

Key Takeaways:

• ✔ Faster than EF Core for raw SQL queries.
• ✔ Simple and easy to use.
• ✔ Supports stored procedures and parameterized queries.
• ✔ Best for performance-critical applications.