存储过程

来源：互联网发布：从你的全世界路过知乎编辑：程序博客网时间：2024/04/27 05:31

今天研究了一下如何创建带事务的存储过程和如何在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