Fast and Efficient Queries with Dapper

Dapper is a micro ORM that allows you to run SQL queries directly against the database quickly and efficiently. It offers lightweight object mapping for query results, making it a great choice when you need more control over SQL queries or want to optimize performance. In this example, we will see how to perform a simple query and map the results to a C# object using Dapper.

Libraries:

To use the Dapper library, install the NuGet package in your project:

Install-Package Dapper
Install-Package Microsoft.Data.Sqlite

Example Code:

using Dapper;
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;

namespace DapperExample
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new SqliteConnection("Data Source=products.db"))
            {
                connection.Open();

                // Creating the table
                connection.Execute("CREATE TABLE IF NOT EXISTS Products (Id INTEGER PRIMARY KEY, Name TEXT, Price REAL)");

                // Inserting a product
                connection.Execute("INSERT INTO Products (Name, Price) VALUES (@Name, @Price)", new { Name = "Laptop", Price = 1500.99m });

                // Fetching the products
                IEnumerable<Product> products = connection.Query<Product>("SELECT Id, Name, Price FROM Products");

                // Displaying the products
                foreach (var product in products)
                {
                    Console.WriteLine($"Id: {product.Id}, Name: {product.Name}, Price: {product.Price}");
                }
            }
        }
    }
}

Code Explanation:

In this example, we use Dapper to execute SQL queries in a SQLite database. First, we open a connection and create the Products table if it doesn’t already exist. Next, we insert a new product into the table using the Execute method. Then, we fetch all products from the table using the Query method, which maps the results to Product objects. Finally, we display the products in the console.

Conclusion:

Dapper is an excellent tool for those looking for a micro ORM that offers more control over SQL queries without sacrificing performance. It’s easy to use, flexible, and allows for quick data-to-object mapping.

Source code: GitHub