Using Dapper | Data Access | Csharp Tutorial

Introduction to Dapper

Dapper is a simple object mapper for .NET and it is virtually as fast as using a raw ADO.NET data reader. It provides a convenient way to execute SQL queries and map the results to C# objects. This tutorial will guide you through the basics of using Dapper in a C# application.

Installing Dapper

To use Dapper in your project, you need to install the Dapper package from NuGet. You can do this using the NuGet Package Manager in Visual Studio or by running the following command in the Package Manager Console:

Install-Package Dapper

Setting Up the Database

For this tutorial, we will use a simple SQLite database. Ensure you have the SQLite package installed by running:

Install-Package System.Data.SQLite

Create a database file named sample.db and use the following SQL script to create a table named Users:

CREATE TABLE Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Age INTEGER NOT NULL);

Connecting to the Database

First, add the necessary using directives:

using System.Data;
using System.Data.SQLite;
using Dapper;

Next, write a method to create and return a connection to the database:

public IDbConnection CreateConnection() {
    return new SQLiteConnection("Data Source=sample.db");
}

Inserting Data

Let's insert a new user into the Users table. Here is an example method to insert data:

public void InsertUser(string name, int age) {
    using (IDbConnection dbConnection = CreateConnection()) {
        string sql = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age)";
        dbConnection.Execute(sql, new { Name = name, Age = age });
    }
}

Querying Data

To query data from the database, you can use the Query method provided by Dapper. Here is an example method to retrieve all users:

public IEnumerable<User> GetAllUsers() {
    using (IDbConnection dbConnection = CreateConnection()) {
        string sql = "SELECT * FROM Users";
        return dbConnection.Query<User>(sql).ToList();
    }
}

Output:

ID: 1, Name: John Doe, Age: 30
ID: 2, Name: Jane Smith, Age: 25

Updating Data

To update existing data, you can use the Execute method. Here is an example method to update a user's age:

public void UpdateUserAge(int id, int newAge) {
    using (IDbConnection dbConnection = CreateConnection()) {
        string sql = "UPDATE Users SET Age = @Age WHERE Id = @Id";
        dbConnection.Execute(sql, new { Id = id, Age = newAge });
    }
}

Deleting Data

To delete data, you can use the Execute method. Here is an example method to delete a user by ID:

public void DeleteUser(int id) {
    using (IDbConnection dbConnection = CreateConnection()) {
        string sql = "DELETE FROM Users WHERE Id = @Id";
        dbConnection.Execute(sql, new { Id = id });
    }
}

Conclusion

In this tutorial, we covered the basics of using Dapper to perform CRUD operations in a C# application. Dapper provides a simple and efficient way to interact with your database, making it a great choice for many applications. With this knowledge, you can further explore Dapper's capabilities and integrate it into your projects.

Dapper Tutorial