Data Access with Dapper.NET

Introduction

Efficient data access is essential in software development, regardless of the project's size. Proper handling of database interactions is crucial to maintaining performance and scalability. Dapper.NET is a lightweight Object-Relational Mapping (ORM) framework that simplifies data access in .NET applications. In this article post, we'll provide practical examples to help you understand Dapper.NET and how it can streamline your data access code.

Introducing Dapper.NET

Dapper.NET, created by the Stack Overflow team, aims to offer speedy and efficient data access for .NET applications. Unlike certain ORM frameworks, Dapper.NET values straightforwardness and optimal performance. Before we delve into examples, let's take a closer look at some of its essential features.

Key Features of Dapper.NET

Speed: Dapper.NET's emphasis on performance sets it apart from numerous other ORM frameworks, making it significantly faster.
Lightweight: The design of the framework is minimalist, which helps to reduce complexity and overhead.
Raw SQL Support: With Dapper.NET, you can create and run unprocessed SQL queries.
Object Mapping: The tool maps query results to .NET objects automatically, minimizing the need for manual data manipulation.
Dynamic Parameters: Dapper.NET enables the use of parameterized queries to enhance security and efficiency.
Multiple Result Sets: The system can effortlessly manage requests that produce multiple sets of results.
Stored Procedure Support: Stored procedures are compatible with Dapper.NET.

Getting Started with Dapper.NET: Examples

To better understand how Dapper.NET can make data access easier, let's explore some practical examples.

Example 1. Basic Query

Suppose we have a simple database table named Products with columns ProductId, ProductName, and Price. We want to retrieve all products from the database.

Install Dapper: Install the Dapper package via NuGet.
Create Connection: Establish a connection to your database. For this example, we'll use SQL Server.

Write Query and Retrieve Data:

using System;
using System.Data.SqlClient;
using Dapper;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";
        
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            var products = connection.Query<Product>("SELECT * FROM Products");

            foreach (var product in products)
            {
                Console.WriteLine($"{product.ProductId}: {product.ProductName} - ${product.Price}");
            }
        }
    }
}

class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
}

Example 2. Parameterized Query

Let's retrieve products within a certain price range using a parameterized query.

using System;
using System.Data.SqlClient;
using Dapper;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";
        
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            decimal minPrice = 20.0M;
            decimal maxPrice = 50.0M;

            var products = connection.Query<Product>(
                "SELECT * FROM Products WHERE Price BETWEEN @MinPrice AND @MaxPrice",
                new { MinPrice = minPrice, MaxPrice = maxPrice });

            foreach (var product in products)
            {
                Console.WriteLine($"{product.ProductId}: {product.ProductName} - ${product.Price}");
            }
        }
    }
}

Example 3. Working with Stored Procedures

Dapper.NET shines when it comes to interacting with stored procedures. Suppose we have a stored procedure named GetProductsByCategory that retrieves products based on a given category.

using System;
using System.Data;
using System.Data.SqlClient;
using Dapper;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";
        
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string category = "Electronics";

            var products = connection.Query<Product>(
                "GetProductsByCategory", // Stored procedure name
                new { Category = category },
                commandType: CommandType.StoredProcedure);

            foreach (var product in products)
            {
                Console.WriteLine($"{product.ProductId}: {product.ProductName} - ${product.Price}");
            }
        }
    }
}

class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
}

Conclusion

As a developer, you can benefit from Dapper.NET, which is a lightweight and efficient solution for data access. It provides a performance-oriented approach and supports raw SQL queries and stored procedures, making it an excellent tool in your .NET development toolbox.

In this article, we will explore practical examples of using Dapper.NET, including basic queries, parameterized queries, and working with stored procedures. By embracing Dapper.NET's simplicity and performance, you can optimize your data access code, resulting in more efficient and maintainable applications.

Regardless of the size of your project, Dapper.NET's ability to streamline data access will have a positive impact on your development workflow. Try it out and see for yourself how Dapper.NET simplifies the journey from database to application.