今天研究了一下如何创建带事务的存储过程和如何在C#里面调用,总结一下下:
一、在存储过程中处理事务:
1、存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[spInsertUser]
@id char(512),
@name char(512),
@age char(512),
@wid char(512),
@wname char(512),
@wyear char(512)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrTimeFLag AS INT
DECLARE @ErrFlag AS INT
SET @ErrTimeFLag = 0
SET @ErrFlag = 0
begin tran
--insert user infomation
INSERT INTO [user] (id, [name], age) VALUES (@id, @name, @age)
SET @ErrTimeFLag = @@ERROR
IF @ErrTimeFLag <> 0
SET @ErrFlag = @ErrTimeFLag
--insert work infomation
INSERT INTO [work] (wid, wname, wyear) VALUES (@wid ,@wname,@wyear)
SET @ErrTimeFLag = @@ERROR
If @ErrTimeFLag <> 0
SET @ErrFlag = @ErrTimeFLag
IF @ErrFlag <> 0
BEGIN
ROLLBACK
RETURN -99
END
ELSE
BEGIN
COMMIT
RETURN 0
END
END
2、C#中的调用:
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringtest"].ConnectionString;
SqlCommand comm = new SqlCommand();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "spInsertUser";
comm.Connection = objConn;
objConn.Open();
comm.Parameters.Add("@id", SqlDbType.Char, 100);
comm.Parameters.Add("@name", SqlDbType.Char, 100);
comm.Parameters.Add("@age", SqlDbType.Char, 100);
comm.Parameters.Add("@wid", SqlDbType.Char, 100);
comm.Parameters.Add("@wname", SqlDbType.Char, 100);
comm.Parameters.Add("@wyear", SqlDbType.Char, 100);
comm.Parameters["@id"].Value = Session["Account"].ToString();
comm.Parameters["@name"].Value = Session["UserName"].ToString();
comm.Parameters["@age"].Value = Session["UserName"].ToString();
comm.Parameters["@wid"].Value = Session["Account"].ToString();
comm.Parameters["@wname"].Value = Session["UserName"].ToString();
comm.Parameters["@wyear"].Value = Session["UserName"].ToString();
comm.ExecuteReader();
二、在C#中处理事务:
1,存储过程中的处理
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spInsertUser]
@id int,
@name char(512),
@age char(512),
@wid int,
@wname char(512),
@wyear char(512)
AS
BEGIN
SET NOCOUNT ON;
update [user] set id=@id,[name]=@name,age=@age
INSERT INTO [work] (wid, wname, wyear) VALUES (@wid ,@wname,@wyear)
END
2,C#中的处理
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringtest"].ConnectionString;
SqlCommand comm = new SqlCommand();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "spInsertUser";
comm.Connection = objConn;
SqlTransaction objTrans;
objConn.Open();
objTrans = objConn.BeginTransaction();
comm.Transaction = objTrans;
comm.CommandTimeout = 10;
try
{
//INPUT PARAMETERS
comm.Parameters.Add("@id", SqlDbType.Int, 100);
comm.Parameters.Add("@name", SqlDbType.Char, 100);
comm.Parameters.Add("@age", SqlDbType.Char, 100);
comm.Parameters.Add("@wid", SqlDbType.Int, 100);
comm.Parameters.Add("@wname", SqlDbType.Char, 100);
comm.Parameters.Add("@wyear", SqlDbType.Char, 100);
comm.Parameters["@id"].Value = 1;
comm.Parameters["@name"].Value = Session["UserName"].ToString();
comm.Parameters["@age"].Value = Session["UserName"].ToString();
comm.Parameters["@wid"].Value = 1;
comm.Parameters["@wname"].Value = Session["UserName"].ToString();
comm.Parameters["@wyear"].Value = Session["UserName"].ToString();
comm.ExecuteScalar();
objTrans.Commit();
}
catch (Exception ex)
{
objTrans.Rollback();
}
finally
{
comm = null;
objConn.Close();
}
三、测试如果sqltimeout了 就要rollback 但是如果只是单纯锁表的话 会报非timeout的错误 所以要另想办法:
1、在sql中使用另外一个事务锁定表(不执行commit tran),然后在执行以上的C#,这样就可以出现sqltimeout的现象了
begin tran
select * from [user] with (TABLOCKX)
commit tran