Introduction to Dapper

In the previous article, I discussed how to integrate SQLite with a .NET console application using C# and VSCode, available at this link. Now, I’ll show you how simple it is to use Dapper for data access in your .NET applications. By the end of this tutorial, you’ll understand how to leverage Dapper to perform efficient and clean database operations with SQLite in your .NET console applications.

What is Dapper?

Dapper is an object–relational mapping (ORM) and a popular NuGet library that enhances ADO.NET connections by adding extension methods to your DbConnection instance. It provides a simple and efficient API for executing SQL commands, with support for both synchronous and asynchronous data access. Dapper allows for both buffered and non-buffered queries, making it a flexible choice for various data access scenarios.

Key Features of Dapper

1. Performance: Dapper is renowned for its performance. It executes SQL commands and maps the resulting data to objects very quickly, often outperforming traditional ORMs. The performance metrics from Dapper show minimal overhead in executing SELECT statements and mapping data to objects, making it an excellent choice for high-performance applications.
2. Ease of Use: Parameters are typically passed as anonymous classes. This makes it easy to name parameters and allows you to copy and paste SQL snippets directly into your database platform’s query analyzer. This ease of use simplifies the development process and reduces the likelihood of errors.
3. Simplicity: With Dapper, you can continue to write raw SQL queries, giving you full control over your SQL. This simplicity ensures that your code remains clean and maintainable, with minimal boilerplate code.
4. Flexibility: Dapper works seamlessly with any relational database and supports complex queries, multi-mapping, and batch operations. This flexibility makes it suitable for a wide range of applications, from small projects to large enterprise systems.

Why Use Dapper?

Using Dapper in your .NET applications provides several important benefits:

• High Performance: Dapper’s efficient mapping and execution ensure that your data access layer performs optimally.
• Reduced Boilerplate: Dapper minimizes the amount of code you need to write for common database operations, making your codebase cleaner and easier to maintain.
• Type Safety: Dapper’s type-safe mappings reduce runtime errors and improve the reliability of your application.
• Full Control Over SQL: Unlike some ORMs that abstract away SQL, Dapper allows you to write and execute raw SQL queries, giving you complete control over your database operations.

Overall, Dapper combines the performance benefits of direct ADO.NET usage with the convenience of an ORM, making it an excellent choice for efficient and maintainable data access in .NET applications.

Example: Using Dapper with C# in VSCode

Disclaimer: This example is purely for educational purposes. There are better ways to write code and applications that can optimize this example. Use this as a starting point for learning, but always strive to follow best practices and improve your implementation.

Installing Necessary Package

Add the necessary packages for SQLite and Dapper.:

dotnet add package Microsoft.Data.Sqlite
dotnet add package Dapper

Update the Program.cs

Replace the contents of Program.cs with the following code. This code uses Dapper to interact with the SQLite database.

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

class Program
{
  private static void Main(string[] args)
  {
    const string databaseFile = "crm.db";
    var connectionString = $"Data Source={databaseFile}";

    using (var connection = new SQLiteConnection(connectionString))
    {
      connection.Open();
      CreateTable(connection);
      InsertUsers(connection);
      DisplayAllUsers(connection);
      DeleteUserByName(connection, "Otto");
      DisplayAllUsers(connection);
    }

    File.Delete(databaseFile);
    Console.WriteLine("Database file deleted!");
 }

  private static void DeleteUserByName(IDbConnection connection, string name)
  {
    var sql = "DELETE FROM user WHERE name = @Name";
    connection.Execute(sql, new { Name = name });
    Console.WriteLine($"User with name '{name}' deleted.");
  }

  private static void DisplayAllUsers(IDbConnection connection)
  {
    var sql = "SELECT id, name, age FROM user";
    var users = connection.Query<User>(sql);

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

  private static void InsertUsers(IDbConnection connection)
  {
    var sql = @"
      INSERT INTO user (name, age)
      VALUES (@Name, @Age)";
    var users = new[]
    {
      new User { Name = "Otto", Age = 30 },
      new User { Name = "Tim", Age = 25 },
      new User { Name = "Steve", Age = 28 },
      new User { Name = "Robert", Age = 35 }
    };

    connection.Execute(sql, users);
    Console.WriteLine("Users inserted.");
 }

  private static void CreateTable(IDbConnection connection)
  {
    var sql = @"
      CREATE TABLE IF NOT EXISTS user (
      id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      age INTEGER NOT NULL)";
    connection.Execute(sql);
    Console.WriteLine("Table created.");
  }
}

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

Explanation

1. Dependencies: We are using Microsoft.Data.Sqlite for SQLite and Dapper for data access.
2. Database Connection: We establish a connection to the SQLite database using SQLiteConnection.
3. Create Table: The CreateTable method creates the user table if it doesn’t already exist.
4. Insert Users: The InsertUsers method inserts multiple user records into the database using parameterized queries.
5. Display All Users: The DisplayAllUsers method retrieves and displays all users from the database.
6. Delete User: The DeleteUserByName method deletes a user with a specific name.

Run the Application

Compile and run your application to see the results.

dotnet run

You should see the table creation, insertion of users, display of all users, deletion of a user, and the final display of users. The database file will be deleted at the end.

Conclusion

Integrating Dapper into your .NET console application is straightforward. As demonstrated, updating the previous SQLite code to use Dapper involved minimal changes, primarily replacing direct SQLite command execution with Dapper’s extension methods. This simplicity allows you to quickly get up and running with Dapper, leveraging its performance and ease of use.

Overview of Features and Benefits

• High Performance: Dapper’s lightweight nature ensures fast query execution and efficient object mapping, making it ideal for performance-critical applications.
• Reduced Boilerplate: Dapper minimizes the amount of code required for database operations, resulting in cleaner and more maintainable code.
• Type Safety: Dapper’s type-safe mappings reduce runtime errors, improving the reliability of your application.
• Full Control Over SQL: By allowing you to write raw SQL queries, Dapper provides full control over your database operations, offering flexibility and precision.
• Flexibility: It supports complex queries, multi-mapping, and batch operations, making it suitable for a wide range of applications.

Challenges of Dapper

Despite its many benefits, Dapper does have some challenges:

1. Manual SQL Query Writing: Dapper requires you to write raw SQL queries. This flexibility means you must handle query construction and parameterization manually, which can expose you to SQL injection risks. To avoid this, always use parameterized queries, as shown in the examples.
2. Limited High-Level Abstractions: Unlike full-fledged ORMs like Entity Framework, Dapper provides a lower-level API. You need to write more code for common CRUD operations and lack certain high-level abstractions like automatic change tracking and migrations. However, this trade-off gives you more control and potentially better performance.
3. No Built-in LINQ Support: Dapper does not support LINQ, a powerful feature for querying datasets and data repositories. If LINQ is essential for your project, you might need to consider combining Dapper with other libraries or using it in scenarios where raw SQL queries are more appropriate.

Conclusion

Dapper is an excellent choice for developers who prefer the control and performance of raw SQL queries while still benefiting from the convenience of an ORM-like library. It strikes a balance between simplicity, performance, and flexibility, making it a valuable tool for many .NET applications.

If you think your friends or network would find this article useful, please consider sharing it with them. Your support is greatly appreciated.