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(
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
{
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(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
{
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(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(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(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(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(query, new { Offset = (pageNumber - 1) * pageSize, PageSize = pageSize });
foreach (var product in result)
{
Console.WriteLine($"Name: {product.Name}, Price: {product.Price}");
}
}
} 这些示例演示了Dapper的一些高级功能，包括多表关联查询、事务处理、自定义类型映射、批量插入、自定义SQL语句、异步查询、自定义表名、自定义参数前缀和查询分页。通过这些示例，你可以更好地了解如何在实际应用中充分利用Dapper来简化数据访问任务。
