CRUD Operations in .NET Core with Dapper: Fast and Efficient Data Management

Database operations are one of the core aspects of modern software development. When working with .NET Core for database interactions, using a lightweight and fast micro ORM like Dapper offers significant advantages, especially in high-performance projects. In this post, we will guide you step by step through performing basic CRUD (Create, Read, Update, Delete) operations using Dapper in .NET Core.

Dapper allows you to interact with the database with minimal overhead, making queries faster and more efficient compared to larger ORMs. If you need to quickly add, update, delete, and query data, Dapper is an excellent choice.

In the following sections, you'll learn how to perform CRUD operations in .NET Core and Dapper using an example of a User table. These operations include adding, reading, updating, and deleting data seamlessly.

1. Project Setup

Once you've created your project, add the Dapper and SQL Client packages via NuGet:

dotnet add package Dapper
dotnet add package Microsoft.Data.SqlClient

2. Database Connection

You need to create a connection class that will handle database connections.

using System.Data.SqlClient;

public class DatabaseService
{
    private readonly string _connectionString = "YourConnectionString";

    public SqlConnection GetConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

3. Model Class (User)

Now, let's create the User model class. This class will represent rows in the database table.

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

4. CRUD Operations

We'll define the necessary methods for the CRUD operations in the UserService class.

Create (Insert Data)

To insert a new user, we create the InsertUser method:

using Dapper;
using System.Threading.Tasks;

public class UserService
{
    private readonly DatabaseService _databaseService;

    public UserService(DatabaseService databaseService)
    {
        _databaseService = databaseService;
    }

    public async Task<int> InsertUserAsync(User user)
    {
        using (var connection = _databaseService.GetConnection())
        {
            await connection.OpenAsync();
            var query = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age); SELECT CAST(SCOPE_IDENTITY() AS INT);";
            var userId = await connection.QuerySingleAsync<int>(query, new { user.Name, user.Age });
            return userId;
        }
    }
}

Here, we are inserting a new user and returning the Id of the newly created user. SCOPE_IDENTITY() is used to retrieve the Id of the last inserted row.

Read (Get Data)

To read all users, we define the GetUsers method:

public async Task<IEnumerable<User>> GetUsersAsync()
{
    using (var connection = _databaseService.GetConnection())
    {
        await connection.OpenAsync();
        var query = "SELECT * FROM Users";
        return await connection.QueryAsync<User>(query);
    }
}

This method fetches all users from the Users table.

Update (Update Data)

To update a user's information, we create the UpdateUser method:

public async Task<bool> UpdateUserAsync(User user)
{
    using (var connection = _databaseService.GetConnection())
    {
        await connection.OpenAsync();
        var query = "UPDATE Users SET Name = @Name, Age = @Age WHERE Id = @Id";
        var rowsAffected = await connection.ExecuteAsync(query, new { user.Name, user.Age, user.Id });
        return rowsAffected > 0;
    }
}

This method updates the user with the given Id. If the update is successful, it returns true.

Delete (Delete Data)

To delete a user, we define the DeleteUser method:

public async Task<bool> DeleteUserAsync(int userId)
{
    using (var connection = _databaseService.GetConnection())
    {
        await connection.OpenAsync();
        var query = "DELETE FROM Users WHERE Id = @Id";
        var rowsAffected = await connection.ExecuteAsync(query, new { Id = userId });
        return rowsAffected > 0;
    }
}

This method deletes the user with the given Id and returns true if the deletion is successful.

5. Example Usage

Here’s an example of how to use these CRUD methods in your application:

public class Program
{
    public static async Task Main(string[] args)
    {
        var databaseService = new DatabaseService();
        var userService = new UserService(databaseService);

        // Create
        var newUser = new User { Name = "John Doe", Age = 30 };
        var userId = await userService.InsertUserAsync(newUser);
        Console.WriteLine($"User created with Id: {userId}");

        // Read
        var users = await userService.GetUsersAsync();
        Console.WriteLine("Users:");
        foreach (var user in users)
        {
            Console.WriteLine($"Id: {user.Id}, Name: {user.Name}, Age: {user.Age}");
        }

        // Update
        var existingUser = new User { Id = userId, Name = "John Updated", Age = 35 };
        var updateSuccess = await userService.UpdateUserAsync(existingUser);
        Console.WriteLine(updateSuccess ? "User updated successfully." : "User update failed.");

        // Delete
        var deleteSuccess = await userService.DeleteUserAsync(userId);
        Console.WriteLine(deleteSuccess ? "User deleted successfully." : "User deletion failed.");
    }
}

Conclusion

In this example, we demonstrated how to perform CRUD operations using Dapper in a .NET Core application. Dapper's simplicity and speed make it an excellent choice for database operations, especially when performance is critical. As you can see, managing database connections and executing SQL queries is easy and efficient with Dapper.

If you have any questions or need further clarification, feel free to ask!