CMT DEMO(容器管理事务演示)
我们用执行订单来来演示容器管理事务,我们在执行订单的时候,需要涉及到三个表,他们是Orders,Products,和Order Details 表,我们每在一个订单里订购一个产品,我们就要在产品表里减去一个产品,不能出现,订单表里添加了产品而库存没有减少库存量这种情况,也不能出现Orders表没有插入成功而Order Details却插入成功这种情况,因为这些情况都不符合实际的业务逻辑或者会产生无效数据,前者不符合错实际情况,后者呢因为订单明细表找不到对应的订单表,所以数据就成了无效数据,所以我感觉用执行订单来演示事务处理是很适合的。
先来看一下这条业务逻辑的事件流
1、打开化一个数据库连接对象并开启一个数据库事务;
2、执行插入订单操作;
3、执行插入订单明细操作;
4、执行更新库存操作;
6、如果没有遇到错误就提交事务,如果遇到错误就回滚事务;
我用T-SQL写一个程序来示例
DECLARE @ProductID int --添加到订单的产品ID
DECLARE @Quantity int --添加到订单的产品的数量
SELECT @ProductID = 11 --指定要添加到订单的产品ID
SELECT @Quantity = 5 --指定要添加到订单产品的数量,这里设置为5,如果你测试事务回滚的话可以把它设置成-5,你会发现什么也没有插入
SELECT ProductID,UnitsOnOrder FROM Products WHERE ProductID = @ProductID --注意,select最好不要嵌入到事务处理里面
BEGIN TRAN --开始一个事务
INSERT INTO [Orders] (CustomerID, OrderDate ) VALUES ('ALFKI', GETDATE()) --插入一个订单
IF @@error <> 0 GOTO Err
SET @newOrderID = @@IDENTITY --获取新订单ID
--添加一个订单明细
INSERT INTO [Order Details](OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (@NewOrderid,@ProductID,14.0000,@Quantity,0.0)
IF @@error <> 0 GOTO Err
UPDATE Products SET UnitsOnOrder = UnitsOnOrder + @Quantity WHERE ProductID=@ProductID --更新产品表里的库存数量
IF @@error <> 0 GOTO Err
COMMIT TRAN --提交事务
SELECT ProductID,UnitsOnOrder FROM Products WHERE ProductID = @ProductID
SELECT Orderid,CustomerID,OrderDate FROM [Orders] WHERE OrderID = @newOrderID
SELECT * FROM [Order Details] WHERE OrderID = @newOrderID
RETURN
Err:
ROLLBACK
然后我们创建几个存储过程
@CustomerID nchar(5),
@OrderID int out
AS
BEGIN TRAN
INSERT INTO [Orders] (CustomerID, OrderDate ) VALUES (@CustomerID, GETDATE()) --插入一个订单
SELECT @OrderID=SCOPE_IDENTITY()
COMMIT TRAN
GO
这里用了一个技巧来保证返回的订单ID的正确性,解决了并发问题
下面这段话出自《CSDN开发高手》2004年第4期《彻底解决MS SQL SERVER 2000中最大流水号的生成问题》一文。
SCOPE_IDENTITY()和@@IDENTITY变量都是用来取得当前session中最后的IDENTITY值,关键的不同是SCOPE_IDENTITY()函数有作用范围,即SCOPE_IDENTITY()仅仅在当前代码范围内有效,相当于高级编程语言中的局部变量。而@@IDENTITY 的作用范围比SCOPE_IDENTITY()广。@IDENTITY 的作用范围比SCOPE_IDENTITY()广一些。说明它们作用范围不同的一个简单的例子是如果Orders表有触发器,而恰恰触发器中也有insert语句,那么@@IDENTITY变量就会被触发器中的insert语句改写,而SCOPE_IDENTITY()则不会。
也许读者担心如果有并发用户同时Insert 数据到TradeInfo会不会影响SCOPE_IDENTITY()的值?答案是否定的。不用担心这个问题。由于SCOPE_IDENTITY()有SCOPE,你总能得到正确的值。关键是记得在INSERT之后立即保存SCOPE_IDENTITY()函数的值,否则当前代码内的另外一个Insert有可能会影响SCOPE_IDENTITY()的返回结果。除了SCOPE_IDENTITY()外,SQL Server 2000中还引进了另外一个非常有用的函数IDENT_CURRENT(),用来取得某个表的最后的Identity值。
下面我们来实现CMT(容器管理事务),按照刚才的T-SQL演示,我们先准备三个存储过程
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
以上存储过程都是在northwind库里建的哦,存储过程建立好后,就可以用我写的代码生成器(CMPCodePro.hta)来生成相应的元数据和业务实体类。我简单贴一个例子吧。
{
private Int32 _OrderID;
private String _CustomerID;
public Int32 OrderID
{
get { return _OrderID; }
set { _OrderID = value; }
}
public String CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
限于篇幅,其他两个元数据和业务实体代码我就不贴了,具体可以查看后面下载的源码。然后就是我们写业务逻辑了,如下。
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
其实实现CMP支持事务是很简单的,我为SqlPersistenceContainer类加了重载的构造函数, public SqlPersistenceContainer( ContainerMapping initCurrentMap, SqlConnection conn, SqlTransaction tran )然后,在这个构造函数里可以传入一个数据库连接对象和一个事务对象,然后设置私有成员isExteriorConn为true,然后在BuildCommandFromMapping里判断isExteriorConn来确定是使用外来的数据库连接还是自己创建一个数据库连接。
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
如果是使用外部连接的话,在执行CRUD操作后不用关闭数据库连接了。
{
SqlCommand selectCommand = null;
try
{
CommandMapping cmdMap = currentMap.SelectCommand;
selectCommand = BuildCommandFromMapping(cmdMap);
//省略若干代码
selectCommand.Dispose();
}
catch (Exception dbException)
{
throw new Exception("Persistance (Select) Failed for PersistableObject", dbException);
}
finally
{
if(!this.isExteriorConn)
selectCommand.Connection.Dispose();
}
}
改进:其实现在做的还不够好,因为现在传给容器的数据库连接和事务是特定于SQLSERVER的,其实应该在StdPersistenceContainer抽象类里定义一个重载的构造函数,传入抽象的数据库连接对象和事务对象。
提示:要运行下载的代码,需要配置app.config里的数据库连接和元数据放置的目录。另外下载的代码里还有个customer类,Test类里还有个TestConcurrent()方法,app.config里还有个CustomersMap的容器映射小节,这都是我为了做乐观并发测试用的,暂时还没有做完试验,不用管他们,不影响调试测试事务处理的例子。
示例程序在windows2003+sqlserver2000+.net 2.0beta +vc#2005 beta里运行通过。
要运行测试程序,先在SQLSERVER的查询分析器里选择northwind,并执行wawa.sql脚本,然后CMPCodePro文件夹是我改进的代码生成器,全部由wawacodepro的VBS代码改成了js代码。运行的时候直接打开TestConcurrencyAndTransaction.sln解决方案文件就行了。
需要安装好vc#2005哦
源码下载地址如下:
https://files.cnblogs.com/wawacrm/TestConcurrencyAndTransaction.rar