Using Dapper for Data Access

Introduction to Dapper

Dapper is a simple object mapper for .NET that provides fast and efficient data access. It extends the IDbConnection interface and provides methods to execute SQL queries and map results to strongly typed objects.

Prerequisites

Before you begin, ensure you have the following:

Visual Studio installed on your machine.
A basic understanding of .NET and SQL.
An existing .NET application to which you want to add Dapper.

Installing Dapper

To use Dapper, you need to install the Dapper NuGet package.

// Install the Dapper package
// PM> Install-Package Dapper

Setting Up the Database

First, set up a database and create a table to work with.

// SQL script to create a database and table
CREATE DATABASE SampleDb;
GO
USE SampleDb;
GO
CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

Creating a Data Access Layer with Dapper

Next, create a data access layer in your .NET application using Dapper.

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

// Create a UserRepository class
using System.Data.SqlClient;
using Dapper;

public class UserRepository
{
    private readonly string _connectionString;

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

    public async Task<IEnumerable<User>> GetAllUsersAsync()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var sql = "SELECT * FROM Users";
            var users = await connection.QueryAsync<User>(sql);
            return users;
        }
    }

    public async Task<User> GetUserByIdAsync(int id)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var sql = "SELECT * FROM Users WHERE Id = @Id";
            var user = await connection.QueryFirstOrDefaultAsync<User>(sql, new { Id = id });
            return user;
        }
    }

    public async Task AddUserAsync(User user)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
            await connection.ExecuteAsync(sql, user);
        }
    }

    public async Task UpdateUserAsync(User user)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var sql = "UPDATE Users SET Name = @Name, Email = @Email WHERE Id = @Id";
            await connection.ExecuteAsync(sql, user);
        }
    }

    public async Task DeleteUserAsync(int id)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var sql = "DELETE FROM Users WHERE Id = @Id";
            await connection.ExecuteAsync(sql, new { Id = id });
        }
    }
}

Using the UserRepository

Let's see how to use the UserRepository in your application.

// Example usage of UserRepository
using System;
using System.Threading.Tasks;

public class Program
{
    public static async Task Main(string[] args)
    {
        var connectionString = "YourConnectionStringHere";
        var userRepository = new UserRepository(connectionString);

        // Add a new user
        var newUser = new User { Name = "John Doe", Email = "john.doe@example.com" };
        await userRepository.AddUserAsync(newUser);
        Console.WriteLine("User added.");

        // Get all users
        var users = await userRepository.GetAllUsersAsync();
        foreach (var user in users)
        {
            Console.WriteLine($"{user.Id} - {user.Name} - {user.Email}");
        }

        // Get a user by ID
        var userById = await userRepository.GetUserByIdAsync(1);
        Console.WriteLine($"{userById.Id} - {userById.Name} - {userById.Email}");

        // Update a user
        userById.Name = "Jane Doe";
        await userRepository.UpdateUserAsync(userById);
        Console.WriteLine("User updated.");

        // Delete a user
        await userRepository.DeleteUserAsync(1);
        Console.WriteLine("User deleted.");
    }
}

Conclusion

In this tutorial, we covered how to use Dapper for data access in a .NET application. We discussed how to install Dapper, set up a database, and create a data access layer. We also demonstrated how to use Dapper to perform CRUD operations. Dapper provides a simple and efficient way to interact with your database, making it a great choice for .NET developers.