.NET輕量級ORM框架Dapper.NET的高級應(yīng)用實例詳解
作者：架構(gòu)師老盧
在這篇文章中，我將為您提供Dapper的高級應(yīng)用功能示例，每個示例都有源代碼和注釋。這些示例將涵蓋Dapper的一些高級功能，以幫助你更好地理解如何在實際應(yīng)用中使用它。
Dapper是一個輕量級的ORM（對象關(guān)系映射）庫，用于.NET應(yīng)用程序與數(shù)據(jù)庫之間的數(shù)據(jù)訪問。它允許你使用SQL查詢來執(zhí)行數(shù)據(jù)庫操作，而不需要復(fù)雜的映射配置。在這篇文章中，我將為您提供Dapper的高級應(yīng)用功能示例，每個示例都有源代碼和注釋。這些示例將涵蓋Dapper的一些高級功能，以幫助你更好地理解如何在實際應(yīng)用中使用它。
示例1：多表關(guān)聯(lián)查詢
Dapper允許你輕松執(zhí)行多表關(guān)聯(lián)查詢。在這個示例中，我們將查詢兩個表，一個是Customers表，另一個是Orders表，并將它們關(guān)聯(lián)起來。
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：事務(wù)處理
Dapper允許你使用事務(wù)來確保一組操作要么全部成功，要么全部失敗。在這個示例中，我們將演示如何在Dapper中使用事務(wù)。
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允許你自定義數(shù)據(jù)類型到.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支持批量插入數(shù)據(jù)，這對于大規(guī)模數(shù)據(jù)操作非常有用。在這個示例中，我們將演示如何批量插入多個產(chǎn)品記錄。
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通常用于執(zhí)行SQL查詢，但你也可以執(zhí)行自定義的SQL語句，例如存儲過程或函數(shù)調(diào)用。在這個示例中，我們將演示如何執(zhí)行一個存儲過程。
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語句執(zhí)行
你可以使用Dapper的Execute方法來執(zhí)行自定義的SQL語句，而不僅僅是查詢。在這個示例中，我們將演示如何執(zhí)行一個自定義的更新語句。
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支持異步查詢，這對于高并發(fā)應(yīng)用程序非常有用。在這個示例中，我們將演示如何使用異步方法執(zhí)行查詢。
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特性來指定實體類與數(shù)據(jù)庫中不同表之間的映射關(guān)系。在這個示例中，我們將演示如何自定義表名。
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：自定義參數(shù)前綴
Dapper默認使用@作為參數(shù)前綴，但你可以自定義參數(shù)前綴。在這個示例中，我們將演示如何自定義參數(shù)前綴為$。
using Dapper;
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
SqlMapperExtensions.Configure("$$$"); // 設(shè)置參數(shù)前綴為 $$$
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來執(zhí)行分頁查詢。在這個示例中，我們將演示如何執(zhí)行分頁查詢。
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的一些高級功能，包括多表關(guān)聯(lián)查詢、事務(wù)處理、自定義類型映射、批量插入、自定義SQL語句、異步查詢、自定義表名、自定義參數(shù)前綴和查詢分頁。通過這些示例，你可以更好地了解如何在實際應(yīng)用中充分利用Dapper來簡化數(shù)據(jù)訪問任務(wù)。
責(zé)任編輯：姜華
來源：
今日頭條
