今天來(lái)曬曬我的通用數(shù)據(jù)訪問(wèn)層。

寫(xiě)了很多年的數(shù)據(jù)庫(kù)項(xiàng)目，數(shù)據(jù)訪問(wèn)嘛，一直是用業(yè)務(wù)實(shí)體+存儲(chǔ)過(guò)程的方式，因此經(jīng)常會(huì)寫(xiě)很多調(diào)用存儲(chǔ)過(guò)程的代碼。這些代碼用Ado.net如何寫(xiě)，我想大家應(yīng)該都知道：創(chuàng)建Connection, 創(chuàng)建Command, 給命令參數(shù)一個(gè)一個(gè)賦值，然后調(diào)用，調(diào)用完成后，如果有輸出參數(shù)，則要讀出來(lái)，如果有結(jié)果集，則要將結(jié)果集轉(zhuǎn)換成自己的實(shí)體列表，這個(gè)過(guò)程也是非常機(jī)械化的?？傊?，調(diào)用任何存儲(chǔ)過(guò)程都需要這樣一堆類似的代碼。

我是個(gè)喜歡最求完美的人，自然不喜歡每個(gè)項(xiàng)目都有這樣一堆機(jī)械代碼的存在，于是經(jīng)過(guò)不斷的重構(gòu)代碼，慢慢的就形成了自己的通用數(shù)據(jù)訪問(wèn)層。

我的通用數(shù)據(jù)訪問(wèn)層具有以下特點(diǎn)：

1. 可用于訪問(wèn)各種類型的數(shù)據(jù)庫(kù)，讓您的應(yīng)用程序從特定的數(shù)據(jù)庫(kù)類型中解藕出來(lái)，從而非常簡(jiǎn)單地就可以實(shí)現(xiàn)對(duì)多種數(shù)據(jù)庫(kù)的支持。
2. 非常方便的調(diào)用存儲(chǔ)過(guò)程、將數(shù)據(jù)庫(kù)的結(jié)果轉(zhuǎn)成實(shí)體類型（或列表）、調(diào)用完成后自動(dòng)“回寫(xiě)”輸出參數(shù)到實(shí)體對(duì)象。 只需要一個(gè)調(diào)用便可實(shí)現(xiàn)這三個(gè)操作步驟。
3. 數(shù)據(jù)訪問(wèn)層可以同時(shí)支持多種數(shù)據(jù)庫(kù)類型的多個(gè)連接。并可以在運(yùn)行時(shí)簡(jiǎn)單的切換。
4. 數(shù)據(jù)訪問(wèn)層可以非常方便地實(shí)現(xiàn)類似“多帳套數(shù)據(jù)庫(kù)”的支持，即根據(jù)不同的客戶端請(qǐng)求來(lái)切換相應(yīng)的數(shù)據(jù)庫(kù)連接。
5. 數(shù)據(jù)訪問(wèn)層同時(shí)提供簡(jiǎn)單或詳細(xì)的API，連接或事務(wù)可以自動(dòng)控制也可以由上層類來(lái)控制。總之就是讓您在享受簡(jiǎn)化的過(guò)程中擁有對(duì)細(xì)節(jié)的充分控制機(jī)會(huì)。
6. 提供一個(gè)輔助(Profiler)工具，讓您可以隨時(shí)了解詳細(xì)的數(shù)據(jù)庫(kù)訪問(wèn)情況：打開(kāi)了多少次連接，每個(gè)連接執(zhí)行了哪些調(diào)用，以及調(diào)用的執(zhí)行時(shí)間，調(diào)用參數(shù)等等。

設(shè)計(jì)目標(biāo)：調(diào)用存儲(chǔ)過(guò)程，不管輸入?yún)?shù)多么復(fù)雜，不管有多少輸出參數(shù)，包含轉(zhuǎn)換一個(gè)結(jié)果集到實(shí)體列表，只需要一行C#代碼。

1. 示范代碼，簡(jiǎn)單地調(diào)用單個(gè)存儲(chǔ)過(guò)程

C#實(shí)體類型，成員與數(shù)據(jù)庫(kù)表對(duì)應(yīng)，這里就不給出表結(jié)構(gòu)截圖了。

/// <summary>  
/// 表示一個(gè)商品對(duì)象的實(shí)體類  
/// </summary>  
public sealed class Product  
{  
    public int ProductID { get; set; }  
    public string ProductName { get; set; }  
    public int CategoryID { get; set; }  
    public string Unit { get; set; }  
    public decimal UnitPrice { get; set; }  
    public int Quantity { get; set; }  
 
    // 僅當(dāng)加載詳細(xì)信息(單個(gè)實(shí)體)時(shí)才加載它。加載列表時(shí)忽略這個(gè)字段。  
    [ItemField(OnlyLoadAll = true)]      
    public string Remark { get; set; }  
} 

存儲(chǔ)過(guò)程-更新商品信息

create procedure [dbo].[UpdateProduct](   
    @ProductName nvarchar(50),   
    @CategoryID int,   
    @Unit nvarchar(10),   
    @UnitPrice money,   
    @Quantity int,   
    @Remark nvarchar(max),   
    @ProductID int 
)   
as 
update Products   
set ProductName = @ProductName,   
    CategoryID = @CategoryID,   
    Unit = @Unit,   
    UnitPrice = @UnitPrice,   
    Quantity = @Quantity,   
    Remark = @Remark   
where ProductID = @ProductID; 

C#調(diào)用代碼

public bool UpdateProduct(Product product)  
{  
    return (FishBLLHelper.CallSpExecuteNonQuery("UpdateProduct", product) > 0);  
} 

存儲(chǔ)過(guò)程-獲取商品列表，支持分頁(yè)

create procedure [dbo].[GetProductByCategoryId](  
    @CategoryID int,  
    @PageIndex int = 0,  
    @PageSize int = 20,  
    @TotalRecords int output  
)  
as 
begin  
     
declare @ResultTable table  
(  
    RowIndex int,  
    ProductID int,  
    ProductName nvarchar(50),  
    CategoryID int,  
    Unit nvarchar(10),  
    UnitPrice money,  
    Quantity int 
);  
     
insert into @ResultTable  
select row_number() over (order by ProductID asc) as RowIndex,  
       p.ProductID, p.ProductName, p.CategoryID, p.Unit, p.UnitPrice, p.Quantity  
from   Products as p  
where CategoryID = @CategoryID;  
       
select  @TotalRecords = count(*) from  @ResultTable;  
     
select *  
from   @ResultTable  
where  RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1));  
     
end; 

C#調(diào)用代碼

public List<Product> GetProductByCategoryId(int categoryId, ref int pageIndex, int pageSize, out int recCount)  
{  
    return FishBLLHelper.CallSpGetDataItemListPaged<Product>("GetProductByCategoryId",  
   ref pageIndex, pageSize, out recCount, categoryId);  
}  

2. 示范代碼，以事務(wù)方式調(diào)用多個(gè)存儲(chǔ)過(guò)程

C#實(shí)體類型，成員與數(shù)據(jù)庫(kù)表對(duì)應(yīng)，這里就不給出表結(jié)構(gòu)截圖了。

public sealed class OrderItem  
{  
    public int OrderID { get; set; }  
    public int? CustomerID { get; set; }  
    public DateTime OrderDate { get; set; }  
    public decimal SumMoney { get; set; }  
    [ItemField(OnlyLoadAll = true)]    // 僅當(dāng)加載詳細(xì)信息時(shí)才加載它。  
    public string Comment { get; set; }  
    public bool Finished { get; set; }  
    public string CustomerName { get; set; }  
 
    [ItemField(IgnoreLoad=true)]    // 不加載這個(gè)成員  
    public List<OrderDetail> Detail;  
}  
 
public sealed class OrderDetail  
{  
    public int OrderID { get; set; }  
    public int ProductID { get; set; }  
    public decimal UnitPrice { get; set; }  
    public int Quantity { get; set; }  
} 

三個(gè)存儲(chǔ)過(guò)程，用于插入主表，子表，刷新總金額

create procedure [dbo].[InsertOrder](  
    @CustomerID int = null,  
    @SumMoney money,  
    @Comment nvarchar(300),  
    @OrderID int output  
)  
as 
begin  
   
insert into Orders( CustomerID, OrderDate, SumMoney, Comment)  
values( @CustomerID, getdate(), @SumMoney, @Comment);  
   
set @OrderID = scope_identity();  
   
end;  
create procedure [dbo].[InsertOrderDetail](  
    @OrderID int,  
    @ProductID int,  
    @Quantity int 
)  
as 
declare @Price money;  
select @Price = (select UnitPrice from Products where ProductID = @ProductID);  
 
insert into [Order Details] (OrderID, ProductID, UnitPrice, Quantity)  
values (@OrderID, @ProductID, @Price, @Quantity);  
 
 
create procedure [dbo].[RefreshOrderSumMoney](  
    @OrderID int 
)  
as 
declare @SumMoney money;  
select @SumMoney = (select sum(UnitPrice * Quantity) from [Order Details] where OrderID = @OrderID);  
 
update Orders set SumMoney = @SumMoney  where OrderID = @OrderID;  
 

說(shuō)明：以上三個(gè)存儲(chǔ)要求先調(diào)用InsertOrder，然后獲取新的OrderID后，才能調(diào)用后面二個(gè)。下面來(lái)看看在C#中該如何調(diào)用這三個(gè)存儲(chǔ)過(guò)程吧。

C#調(diào)用代碼

public int AddOrder(OrderItem order)  
{  
    // 以事務(wù)的方式創(chuàng)建一個(gè)FishDbContext對(duì)象，將使用默認(rèn)的連接字符串  
    using( FishDbContext db = new FishDbContext(true) ) {  
        // 添加記錄到表Orders，同時(shí)獲取新產(chǎn)生ID  
        FishBLLHelper.CallSpExecuteNonQuery(db, "InsertOrder", order);  
          
        // 為訂單明細(xì)設(shè)置OrderId，并添加到表[Order Details]  
        order.Detail.ForEach(x => {  
            x.OrderID = order.OrderID;  
            FishBLLHelper.CallSpExecuteNonQuery(db, "InsertOrderDetail", x);  
        });  
 
        // 刷新訂單總金額。  
        FishBLLHelper.CallSpExecuteNonQuery(db, "RefreshOrderSumMoney", null, order.OrderID);  
 
        // 提交事務(wù)。  
        db.CommitTransaction();  
 
        return order.OrderID;  
    }  
}  

好了，示例就寫(xiě)到這里，方不方便嘛，自己覺(jué)得好用就行。

今天先不談ORM工具，我們只談存儲(chǔ)過(guò)程，下次我會(huì)寫(xiě)個(gè)關(guān)于性能測(cè)試的文章來(lái)專門(mén)談ORM。

原文鏈接：http://www.cnblogs.com/fish-li/archive/2011/03/28/1998104.html

【編輯推薦】

1. DBA應(yīng)用技巧：如何升級(jí)InnoDB Plugin
2. 一句代碼實(shí)現(xiàn)批量數(shù)據(jù)綁定 上
3. 一句代碼實(shí)現(xiàn)批量數(shù)據(jù)綁定 下
4. MySQL日志操作教程:DBA們管理的利器
5. MySQL觸發(fā)器如何正確使用