.NET轻量级ORM框架Dapper.NET的高级应用实例详解

geL28资讯网——每日最新资讯28at.com

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

示例1：多表关联查询

Dapper允许你轻松执行多表关联查询。在这个示例中，我们将查询两个表，一个是Customers表，另一个是Orders表，并将它们关联起来。geL28资讯网——每日最新资讯28at.com

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中使用事务。geL28资讯网——每日最新资讯28at.com

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类型的映射。geL28资讯网——每日最新资讯28at.com

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支持批量插入数据，这对于大规模数据操作非常有用。在这个示例中，我们将演示如何批量插入多个产品记录。geL28资讯网——每日最新资讯28at.com

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语句，例如存储过程或函数调用。在这个示例中，我们将演示如何执行一个存储过程。geL28资讯网——每日最新资讯28at.com

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语句，而不仅仅是查询。在这个示例中，我们将演示如何执行一个自定义的更新语句。geL28资讯网——每日最新资讯28at.com

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支持异步查询，这对于高并发应用程序非常有用。在这个示例中，我们将演示如何使用异步方法执行查询。geL28资讯网——每日最新资讯28at.com

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特性来指定实体类与数据库中不同表之间的映射关系。在这个示例中，我们将演示如何自定义表名。geL28资讯网——每日最新资讯28at.com

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默认使用@作为参数前缀，但你可以自定义参数前缀。在这个示例中，我们将演示如何自定义参数前缀为$。geL28资讯网——每日最新资讯28at.com

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来执行分页查询。在这个示例中，我们将演示如何执行分页查询。geL28资讯网——每日最新资讯28at.com

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来简化数据访问任务。geL28资讯网——每日最新资讯28at.com

本文链接：http://www.28at.com/showinfo-26-74665-0.html.NET轻量级ORM框架Dapper.NET的高级应用实例详解

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。邮件：2376512515@qq.com

上一篇： 学到了！Figma 原来是这样表示矩形的

下一篇： 文件系统那些事儿，你学会了吗？