Introduction to CSharp and Dapper

Why did the C# developer break up with Entity Framework and start dating Dapper?
Because they wanted a relationship with less baggage and more speed!

Dapper is a lightweight ORM (Object-Relational Mapper) for .NET. Unlike Entity Framework, Dapper is a micro ORM, meaning it provides the basic functionality needed for data access without the overhead of a full-fledged ORM. This makes it an excellent choice for developers who want the speed and control of raw ADO.NET while still enjoying the convenience of mapping objects to database rows.

In this article, we will explore what Dapper is, why you might choose it over other ORMs, and how to get started with it in a C# application.

Why Use Dapper?

Before diving into the code, it’s worth discussing why you might choose Dapper over other ORMs like Entity Framework or NHibernate. Here are a few reasons:

1. Performance: Dapper is known for its speed. It’s extremely fast because it works as an extension to IDbConnection and does not abstract away all database interactions, allowing for fine-tuned performance optimization.
2. Simplicity: Dapper is easy to set up and use. It requires minimal configuration and allows you to write SQL queries directly in your C# code, giving you full control over the database interactions.
3. Flexibility: Dapper is flexible and can be used with any database supported by ADO.NET. It does not impose any specific patterns on your application architecture, which means you can use it in any type of .NET application, be it ASP.NET, Console, or a Windows Forms application.
4. Minimal Overhead: Since Dapper is a micro ORM, it introduces minimal overhead to your application. It doesn’t track changes, generate SQL automatically, or manage your database schema, which keeps the library lean and fast.

Getting Started with Dapper in C

To start using Dapper in a C# application, you need to follow these basic steps:

1. Install Dapper: The easiest way to add Dapper to your project is via NuGet. You can do this through the Package Manager Console with the following command:

   Install-Package Dapper

2. Set Up Your Database Connection: Dapper works with any database that has an ADO.NET provider. In this example, we will use SQL Server. Make sure you have the connection string ready.
3. Write Some Code: Now, let’s see how to use Dapper to execute SQL queries and map the results to C# objects.

Example: Using Dapper with SQL Server

Let’s create a simple example where we will connect to a SQL Server database, retrieve some data, and display it in the console.

Step 1: Define Your Model

First, define a simple model class that represents the data you want to work with. For example, let’s assume we have a table Users with columns Id, Name, and Email.

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

Step 2: Create a Repository

Next, create a repository class that will handle the database operations. This class will use Dapper to query the database and return the results as a list of User objects.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;

public class UserRepository
{
    private readonly string _connectionString;

    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IEnumerable<User> GetAllUsers()
    {
        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            string sqlQuery = "SELECT * FROM Users";
            return db.Query<User>(sqlQuery);
        }
    }
}

Step 3: Use the Repository in Your Application

Finally, use the UserRepository in your application to fetch and display the data.

class Program
{
    static void Main(string[] args)
    {
        string connectionString = "YourConnectionStringHere";
        UserRepository userRepository = new UserRepository(connectionString);

        IEnumerable<User> users = userRepository.GetAllUsers();

        foreach (var user in users)
        {
            Console.WriteLine($"ID: {user.Id}, Name: {user.Name}, Email: {user.Email}");
        }
    }
}

More Advanced Dapper Features

Dapper offers several other features that can help streamline your database interactions:

• Parameterized Queries: Dapper automatically maps parameters to prevent SQL injection attacks. You can use anonymous types to pass parameters to your queries.

  string sqlQuery = "SELECT * FROM Users WHERE Name = @Name";
  var users = db.Query<User>(sqlQuery, new { Name = "John Doe" });

• Stored Procedures: Dapper can easily execute stored procedures and map the results to objects.

  var users = db.Query<User>("spGetAllUsers", commandType: CommandType.StoredProcedure);

• Multi-Mapping: Dapper can map multiple result sets to different objects, allowing you to work with more complex data models.

  var sql = "SELECT * FROM Users; SELECT * FROM Orders;";
  using (var multi = db.QueryMultiple(sql))
  {
      var users = multi.Read<User>().ToList();
      var orders = multi.Read<Order>().ToList();
  }

• Bulk Operations: Dapper can be combined with other libraries for bulk operations, like Dapper.Contrib or SqlBulkCopy, to perform large-scale data manipulation efficiently.

Dapper is a powerful yet simple tool for data access in .NET applications. It provides the speed and flexibility of raw ADO.NET while offering the convenience of mapping query results to objects. Whether you are building a small application or a large-scale system, Dapper can help you manage your database interactions efficiently.

By understanding and utilizing Dapper’s features, you can create robust and performant data access layers that meet the specific needs of your application. Whether you prefer to write raw SQL or use stored procedures, Dapper gives you the tools to get the job done with minimal overhead and maximum control.