C#执行含事务的存储过程

存储过程如下:

USE[TestDB]
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: <>
--
Create date: <>
--
Description: <存储过程中使用事务>
--
=============================================
CREATE PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test SET num = 1 WHERE TypeID = 1;
UPDATE dbo.test SET num = 4 WHERE TypeID = 2; --此语句将出错,Phone为Int类型
UPDATE dbo.test SET num = 'c' WHERE TypeID = 3;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN -1;
END CATCH
RETURN 1;
END

 c#执行此存储过程

/// <summary>
/// 执行存储过程,无参数,有返回值。存储过程执行成功时返回1,否则返回0。
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <returns>0:出错;1:成功</returns>
public int SpWithReturnValueExecuteNonQuery(string spName)
{
int result = 0;
SqlConnection cnn
= GetConnection();
SqlTransaction trans
= null;
SqlCommand cmd
= new SqlCommand(spName, cnn);

cmd.Parameters.Add(
"@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

cmd.CommandTimeout
= _commandTimeout;
cmd.CommandType
= CommandType.StoredProcedure;

try
{
cmd.ExecuteNonQuery();
result
= (int)cmd.Parameters["@ReturnValue"].Value;
}
catch (SqlException)
{
return result;
}
finally
{
cnn.Close();
}
return result;
}
posted @ 2011-09-09 11:31  ChineseCheng  阅读(764)  评论(1编辑  收藏  举报