.NET轻量级ORM框架Dapper.NET高级应用示例

Dapper是一个轻量级的ORM（对象关系映射）库，用于.NET应用程序与数据库之间的数据访问。它允许你使用SQL查询来执行数据库操作，而不需要复杂的映射配置。在这篇文章中，我将为您提供Dapper的高级应用功能示例，每个示例都有源代码和注释。这些示例将涵盖Dapper的一些高级功能，以帮助你更好地理解如何在实际应用中使用它。

示例1：多表关联查询

Dapper允许你轻松执行多表关联查询。在这个示例中，我们将查询两个表，一个是Customers表，另一个是Orders表，并将它们关联起来。

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

public class Customer
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public decimal TotalAmount { get; set; }
}

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);

        string query = "SELECT c.CustomerId, c.CustomerName, o.OrderId, o.TotalAmount " +
                       "FROM Customers c " +
                       "JOIN Orders o ON c.CustomerId = o.CustomerId";

        var result = dbConnection.Query<Customer, Order, Customer>(
            query,
            (customer, order) =>
            {
                customer.Orders = order;
                return customer;
            },
            splitOn: "OrderId"
        );

        foreach (var customer in result)
        {
            Console.WriteLine($"Customer ID: {customer.CustomerId}, Name: {customer.CustomerName}");
            Console.WriteLine($"Order ID: {customer.Orders.OrderId}, Total Amount: {customer.Orders.TotalAmount}");
            Console.WriteLine();
        }
    }
}

示例2：事务处理

Dapper允许你使用事务来确保一组操作要么全部成功，要么全部失败。在这个示例中，我们将演示如何在Dapper中使用事务。

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

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);
        dbConnection.Open();

        using var transaction = dbConnection.BeginTransaction();

        try
        {
            string insertQuery = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)";
            string updateQuery = "UPDATE Customers SET CustomerName = @CustomerName WHERE CustomerId = @CustomerId";

            var product = new { Name = "ProductX", Price = 19.99 };
            var customer = new { CustomerName = "NewName", CustomerId = 1 };

            dbConnection.Execute(insertQuery, product, transaction: transaction);
            dbConnection.Execute(updateQuery, customer, transaction: transaction);

            // Commit the transaction if all operations are successful
            transaction.Commit();
            Console.WriteLine("Transaction committed.");
        }
        catch (Exception ex)
        {
            // Rollback the transaction if any operation fails
            transaction.Rollback();
            Console.WriteLine("Transaction rolled back. Error: " + ex.Message);
        }
    }
}

示例3：自定义类型映射

Dapper允许你自定义数据类型到.NET类型的映射。在这个示例中，我们将使用TypeHandler来自定义Point类型的映射。

using Dapper;
using System;
using System.Data;
using System.Data.SqlClient;
using Npgsql;
using NpgsqlTypes;

public class Point
{
    public double X { get; set; }
    public double Y { get; set; }
}

public class PointTypeHandler : SqlMapper.TypeHandler<Point>
{
    public override void SetValue(IDbDataParameter parameter, Point value)
    {
        parameter.Value = $"({value.X},{value.Y})";
        parameter.DbType = DbType.String;
    }

    public override Point Parse(object value)
    {
        if (value is string strValue)
        {
            var parts = strValue.Trim('(', ')').Split(',');
            if (parts.Length == 2 && double.TryParse(parts[0], out double x) && double.TryParse(parts[1], out double y))
            {
                return new Point { X = x, Y = y };
            }
        }
        return null;
    }
}

class Program
{
    static void Main()
    {
        SqlMapper.AddTypeHandler(new PointTypeHandler());

        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new NpgsqlConnection(connectionString);

        string query = "SELECT PointColumn FROM MyTable WHERE Id = @Id";
        var result = dbConnection.Query<Point>(query, new { Id = 1 }).FirstOrDefault();

        if (result != null)
        {
            Console.WriteLine($"X: {result.X}, Y: {result.Y}");
        }
        else
        {
            Console.WriteLine("Point not found.");
        }
    }
}

示例4：批量插入

Dapper支持批量插入数据，这对于大规模数据操作非常有用。在这个示例中，我们将演示如何批量插入多个产品记录。

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

public class Product
{
    public string Name { get; set; }
    public decimal Price { get; set; }
}

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);
        dbConnection.Open();

        var products = new List<Product>
        {
            new Product { Name = "ProductA", Price = 10.99m },
            new Product { Name = "ProductB", Price = 15.99m },
            new Product { Name = "ProductC", Price = 20.99m }
        };

        string insertQuery = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)";
        int rowsAffected = dbConnection.Execute(insertQuery, products);

        Console.WriteLine($"{rowsAffected} rows inserted.");
    }
}

示例5：自定义SQL语句

虽然Dapper通常用于执行SQL查询，但你也可以执行自定义的SQL语句，例如存储过程或函数调用。在这个示例中，我们将演示如何执行一个存储过程。

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

class Program
{
    static void Main()
   

 {
        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);

        string storedProcedure = "MyStoredProcedure";
        var parameters = new DynamicParameters();
        parameters.Add("Param1", 123);
        parameters.Add("Param2", "TestValue", DbType.String, ParameterDirection.Input, 50);

        var result = dbConnection.Query<int>(storedProcedure, parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();

        Console.WriteLine($"Stored procedure result: {result}");
    }
}

示例6：自定义SQL语句执行

你可以使用Dapper的Execute方法来执行自定义的SQL语句，而不仅仅是查询。在这个示例中，我们将演示如何执行一个自定义的更新语句。

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

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);

        string updateStatement = "UPDATE Customers SET CustomerName = @NewName WHERE CustomerId = @CustomerId";
        var parameters = new { NewName = "NewName", CustomerId = 1 };

        int rowsAffected = dbConnection.Execute(updateStatement, parameters);

        Console.WriteLine($"{rowsAffected} rows updated.");
    }
}

示例7：异步查询

Dapper支持异步查询，这对于高并发应用程序非常有用。在这个示例中，我们将演示如何使用异步方法执行查询。

using Dapper;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

class Program
{
    static async Task Main()
    {
        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);

        string query = "SELECT * FROM Products";
        var products = await dbConnection.QueryAsync<Product>(query);

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

示例8：自定义表名

你可以使用Dapper的Table特性来指定实体类与数据库中不同表之间的映射关系。在这个示例中，我们将演示如何自定义表名。

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

[Table("MyCustomTableName")]
public class CustomTable
{
    public int Id { get; set; }
    public string Name { get; set; }
}

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);

        string query = "SELECT * FROM MyCustomTableName";
        var result = dbConnection.Query<CustomTable>(query);

        foreach (var item in result)
        {
            Console.WriteLine($"Id: {item.Id}, Name: {item.Name}");
        }
    }
}

示例9：自定义参数前缀

Dapper默认使用@作为参数前缀，但你可以自定义参数前缀。在这个示例中，我们将演示如何自定义参数前缀为$。

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

class Program
{
    static void Main()
    {
        SqlMapperExtensions.Configure("$$$"); // 设置参数前缀为 $$$

        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);

        string query = "SELECT * FROM Products WHERE Name = $$$productName";
        var result = dbConnection.Query<Product>(query, new { productName = "ProductA" });

        foreach (var product in result)
        {
            Console.WriteLine($"Name: {product.Name}, Price: {product.Price}");
        }
    }
}

示例10：查询分页

Dapper使分页查询变得容易，你可以使用LIMIT和OFFSET来执行分页查询。在这个示例中，我们将演示如何执行分页查询。

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

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";

        using IDbConnection dbConnection = new SqlConnection(connectionString);

        int pageSize = 10;
        int pageNumber = 2;

        string query = "SELECT * FROM Products ORDER BY ProductId OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
        var result = dbConnection.Query<Product>(query, new { Offset = (pageNumber - 1) * pageSize, PageSize = pageSize });

        foreach (var product in result)
        {
            Console.WriteLine($"Name: {product.Name}, Price: {product.Price}");
        }
    }
}

这些示例演示了Dapper的一些高级功能，包括多表关联查询、事务处理、自定义类型映射、批量插入、自定义SQL语句、异步查询、自定义表名、自定义参数前缀和查询分页。通过这些示例，你可以更好地了解如何在实际应用中充分利用Dapper来简化数据访问任务。