Post
Ask Question

Simplify Your Data Access with Dapper.NET

Introduction

Efficient and high-performance data access is a crucial aspect of modern software development. Finding the right balance between simplicity and complexity is key. Dapper.NET is a lightweight but robust solution for data access in .NET applications that excels in striking this balance. In this blog post, we will take a practical approach to explore Dapper.NET, featuring hands-on examples that demonstrate its capabilities, including working with stored procedures.

Embracing Dapper.NET's Power

Dapper.NET is a micro-ORM framework developed by the Stack Overflow team. It is designed to achieve high-performance data access while minimizing complexity. Its lightning-fast performance, support for raw SQL, and simplicity make it an excellent choice for developers looking for an efficient and effective data access solution.

Key Dapper.NET Features

  • Speed: Dapper.NET prioritizes performance, outpacing many ORM frameworks.
  • Lightweight: The framework's minimalist design reduces overhead and complexity.
  • Raw SQL Support: Dapper.NET allows you to write and execute raw SQL queries.
  • Object Mapping: Automagically maps query results to .NET objects, minimizing manual data manipulation.
  • Dynamic Parameters: Supports parameterized queries for security and efficiency.
  • Stored Procedure Support: Seamlessly interacts with stored procedures.

Dapper.NET's Potential

Let's explore the power of Dapper.NET in simplifying data access with real-world examples.

Example 1. Basic Query

Imagine we have a basic database table named `Products` with columns `ProductId`, `ProductName`, and `Price`. Our goal: Retrieve all products from the database.

  1. Install Dapper: Begin by installing the Dapper package via NuGet.
  2. Create Connection: Establish a connection to your database (SQL Server in this example).

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 take it a step further and retrieve products within a specific 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

Dapper.NET is a powerful tool that helps developers access data in a lightweight and efficient manner. Its performance-oriented approach, combined with its ability to support raw SQL queries and stored procedures, makes it an essential tool for any .NET developer. 

In this blog post, we have explored practical examples of using Dapper.NET, starting from executing basic queries to working with parameterized queries and stored procedures. By embracing Dapper.NET's simplicity and performance, you can optimize your data access code, resulting in more efficient and maintainable applications. 

Whether you are building a small application or a large-scale system, Dapper.NET can streamline data access, which 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.


Similar Articles