晒晒我的通用数据访问层

今天来晒晒我的通用数据访问层。

写了很多年的数据库项目，数据访问嘛，一直是用业务实体+存储过程的方式，因此经常会写很多调用存储过程的代码。这些代码用Ado.net如何写，我想大家应该都知道：创建Connection, 创建Command, 给命令参数一个一个赋值，然后调用，调用完成后，如果有输出参数，则要读出来，如果有结果集，则要将结果集转换成自己的实体列表，这个过程也是非常机械化的。总之，调用任何存储过程都需要这样一堆类似的代码。

我是个喜欢最求完美的人，自然不喜欢每个项目都有这样一堆机械代码的存在，于是经过不断的重构代码，慢慢的就形成了自己的通用数据访问层。

我的通用数据访问层具有以下特点：

1. 可用于访问各种类型的数据库，让您的应用程序从特定的数据库类型中解藕出来，从而非常简单地就可以实现对多种数据库的支持。
2. 非常方便的调用存储过程、将数据库的结果转成实体类型（或列表）、调用完成后自动“回写”输出参数到实体对象。 只需要一个调用便可实现这三个操作步骤。
3. 数据访问层可以同时支持多种数据库类型的多个连接。并可以在运行时简单的切换。
4. 数据访问层可以非常方便地实现类似“多帐套数据库”的支持，即根据不同的客户端请求来切换相应的数据库连接。
5. 数据访问层同时提供简单或详细的API，连接或事务可以自动控制也可以由上层类来控制。总之就是让您在享受简化的过程中拥有对细节的充分控制机会。
6. 提供一个辅助(Profiler)工具，让您可以随时了解详细的数据库访问情况：打开了多少次连接，每个连接执行了哪些调用，以及调用的执行时间，调用参数等等。

设计目标：调用存储过程，不管输入参数多么复杂，不管有多少输出参数，包含转换一个结果集到实体列表，只需要一行C#代码。

1. 示范代码，简单地调用单个存储过程

C#实体类型，成员与数据库表对应，这里就不给出表结构截图了。

/// <summary>  
/// 表示一个商品对象的实体类  
/// </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; }  
 
    // 仅当加载详细信息(单个实体)时才加载它。加载列表时忽略这个字段。  
    [ItemField(OnlyLoadAll = true)]      
    public string Remark { get; set; }  
} 

存储过程-更新商品信息

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#调用代码

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

存储过程-获取商品列表，支持分页

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#调用代码

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. 示范代码，以事务方式调用多个存储过程

C#实体类型，成员与数据库表对应，这里就不给出表结构截图了。

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)]    // 仅当加载详细信息时才加载它。  
    public string Comment { get; set; }  
    public bool Finished { get; set; }  
    public string CustomerName { get; set; }  
 
    [ItemField(IgnoreLoad=true)]    // 不加载这个成员  
    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; }  
} 

三个存储过程，用于插入主表，子表，刷新总金额

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;  
 

说明：以上三个存储要求先调用InsertOrder，然后获取新的OrderID后，才能调用后面二个。下面来看看在C#中该如何调用这三个存储过程吧。

C#调用代码

public int AddOrder(OrderItem order)  
{  
    // 以事务的方式创建一个FishDbContext对象，将使用默认的连接字符串  
    using( FishDbContext db = new FishDbContext(true) ) {  
        // 添加记录到表Orders，同时获取新产生ID  
        FishBLLHelper.CallSpExecuteNonQuery(db, "InsertOrder", order);  
          
        // 为订单明细设置OrderId，并添加到表[Order Details]  
        order.Detail.ForEach(x => {  
            x.OrderID = order.OrderID;  
            FishBLLHelper.CallSpExecuteNonQuery(db, "InsertOrderDetail", x);  
        });  
 
        // 刷新订单总金额。  
        FishBLLHelper.CallSpExecuteNonQuery(db, "RefreshOrderSumMoney", null, order.OrderID);  
 
        // 提交事务。  
        db.CommitTransaction();  
 
        return order.OrderID;  
    }  
}  

好了，示例就写到这里，方不方便嘛，自己觉得好用就行。

今天先不谈ORM工具，我们只谈存储过程，下次我会写个关于性能测试的文章来专门谈ORM。

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

【编辑推荐】

1. DBA应用技巧：如何升级InnoDB Plugin
2. 一句代码实现批量数据绑定 上
3. 一句代码实现批量数据绑定 下
4. MySQL日志操作教程:DBA们管理的利器
5. MySQL触发器如何正确使用