ADO.NET+存储过程+事务的一个奇怪问题，求探讨

最新推荐文章于 2024-11-08 18:30:00 发布

weixin_33754065

最新推荐文章于 2024-11-08 18:30:00 发布

阅读量97

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/cnuusw/archive/2012/04/19/2457052.html

版权

一段程序代码如下：

public int SetUserRoleAndRange(int uid, string roleIDList, string rangeIDList)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@UID", SqlDbType.Int,4),
                    new SqlParameter("@RoleIDList", SqlDbType.NVarChar),
                    new SqlParameter("@RangeIDList", SqlDbType.NVarChar),
                    new SqlParameter("@ReturnValue",SqlDbType.Int,4)};
            parameters[0].Value = uid;
            parameters[1].Value = roleIDList;
            parameters[2].Value = rangeIDList;
            parameters[3].Direction = ParameterDirection.ReturnValue;
            SqlHelper.ExecuteNonQuery("EF_Account_SetUserRoleAndRange", parameters);
            return (int)parameters[3].Value;
        }

其中，uid=1693，RoleIDList='',RangeDIList=''.调试结果如下：

注意，这里我们将SQLHelper类进行了一定的改造，在PrepareCommand方法中，我们将参数中的NULL和“”全部转化为了DBNull.Value，代码如下：

foreach (SqlParameter parm in cmdParms)
                {
                    if ((parm.Value == null) || (string.IsNullOrEmpty(parm.Value.ToString())))
                    {
                        parm.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parm);
                }

下面是我们的存储过程代码：

USE [EasyFast.OA]
GO
/****** Object:  StoredProcedure [dbo].[EF_Account_SetUserRoleAndRange]    Script Date: 2012/4/19 12:35:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[EF_Account_SetUserRoleAndRange]
@UID INT,
@RoleIDList NVARCHAR(MAX),
@RangeIDList NVARCHAR(MAX)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @Index NVARCHAR(MAX)
    SET @SQL = 'DELETE FROM EF_UserRoles WHERE UID = '+CONVERT(NVARCHAR,@UID)+';';
    SET @SQL = @SQL + 'DELETE FROM EF_UserRange WHERE UID = '+CONVERT(NVARCHAR,@UID)+';';

    SET @Index = 1;
    WHILE @Index <= dbo.Get_StrArrayLength(@RoleIDList,',')
    BEGIN
        SET @SQL = @SQL + 'INSERT INTO EF_UserRoles (UID,RID) VALUES ('+CONVERT(NVARCHAR,@UID)+','+dbo.Get_StrArrayStrOfIndex(@RoleIDList,',',@Index)+');';
        SET @Index = @Index + 1;
    END

    SET @Index = 1;
    WHILE @Index <= dbo.Get_StrArrayLength(@RangeIDList,',')
    BEGIN
        SET @SQL = @SQL + 'INSERT INTO EF_UserRange (UID,OfficeID) VALUES ('+CONVERT(NVARCHAR,@UID)+','+dbo.Get_StrArrayStrOfIndex(@RangeIDList,',',@Index)+');';
        SET @Index = @Index + 1;
    END

    BEGIN TRY
        BEGIN TRAN
            EXECUTE sp_executesql @SQL;
        COMMIT TRAN
        return 1;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        RETURN 0
    END CATCH
END