Sqlserver 和.net中的事务

转载 2012年03月25日 15:37:57

Sqlserver 存储过程中结合事务的代码实例

--本人多采用方式二:

--方式一
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_ProcedureWithTransaction_Demo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP_ProcedureWithTransaction_Demo]
GO
-- =============================================
-- Author: <ChengXiaoming>
-- Create date: <2010-06-11>
-- Description: <Demo:存储过程中使用事务>
-- =============================================
Create PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
As
Begin
SET XACT_ABORT ON
Begin Transaction
Insert Into Lock(LockTypeID) Values('A')--此语句将出错,LockTypeID为Int类型
Update Lock Set LockTypeID = 2 Where LockID = 32
Commit Transaction
SET XACT_ABORT OFF
End
GO

--方式二
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_ProcedureWithTransaction_Demo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP_ProcedureWithTransaction_Demo]
GO
-- =============================================
-- Author: <ChengXiaoming>
-- Create date: <2010-06-11>
-- Description: <Demo:存储过程中使用事务>
-- =============================================
Create PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
As
Begin
Begin Transaction
Insert Into Lock(LockTypeID) Values('A')--此语句将出错,LockTypeID为Int类型
Update Lock Set LockTypeID = 1 Where LockID = 32
Commit Transaction
If(@@ERROR <> 0)
Rollback Transaction
End
GO

--方式三
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_ProcedureWithTransaction_Demo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP_ProcedureWithTransaction_Demo]
GO
-- =============================================
-- Author: <ChengXiaoming>
-- Create date: <2010-06-11>
-- Description: <Demo:存储过程中使用事务>
-- =============================================
Create PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
As
Begin
Begin Try
Begin Transaction
Update Lock Set LockTypeID = 1 Where LockID = 32--此语句将出错,LockTypeID为Int类型
Insert Into Lock(LockTypeID) Values('A')
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End
GO

2,.Net中使用事务处理
SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();

SqlTransaction myTrans = myConnection.BeginTransaction(); //使用New新生成一个事务
SqlCommand myCommand = new SqlCommand();
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Update Address set location='23 rain street' where userid='0001'";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Record is udated.");
}
catch(Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Sorry, Record can not be updated.");
}
finally
{
myConnection.Close();
}


举报

相关文章推荐

SQLSERVER环境下调用包含webservice引用的.net类库

最近做到一个项目,涉及到系统的集成。对于SSO单点登陆已经解决,但

黑马程序员-自学笔记-SQLServer与ADO.Net(六)

SQLServer与ADO.Net(六) ---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流! ----------------...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

sqlserver+asp.net+devextreme从零开始(1)

因为公司的业务原因,经常需要和各类厂家要到其产品。但是之前的产品都没有保存,我自己试着用excel存了一些,但是发现VBA并不是十分好用,而且产品信息库也不便于传播和更新。我们需要的东西,更像是一个公...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)