asp.net调用存储过程并带返回值

/// <summary>
    /// DataBase 的摘要说明
    /// </summary>
    public class DataBase
    {
        /// <summary>
        ///DataBase 的摘要说明
        /// </summary>
        protected static SqlConnection BaseSqlConnection = new SqlConnection();//连接对象
        protected SqlCommand BaseSqlCommand = new SqlCommand();  //命令对象

        public DataBase()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        protected void OpenConnection()
        {
            if (BaseSqlConnection.State == ConnectionState.Closed)  //连接是否关闭
                try
                {
                    BaseSqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["productsunion"].ToString();
                    BaseSqlCommand.Connection = BaseSqlConnection;
                    BaseSqlConnection.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
        }

        public void CloseConnection()
        {
            if (BaseSqlConnection.State == ConnectionState.Open)
            {
                BaseSqlConnection.Close();
                BaseSqlConnection.Dispose();
                BaseSqlCommand.Dispose();
            }
        }
        public bool Proc_Return_Int(string proc_name, params SqlParameter[] cmdParms)
        {
            try
            {
                OpenConnection();
                if (cmdParms != null)
                {
                    foreach (SqlParameter parameter in cmdParms)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        BaseSqlCommand.Parameters.Add(parameter);
                    }
                    BaseSqlCommand.CommandType = CommandType.StoredProcedure;
                    BaseSqlCommand.CommandText = proc_name;
                    BaseSqlCommand.ExecuteNonQuery();

                    if (BaseSqlCommand.Parameters["Return"].Value.ToString()== "0")
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }

            }
            catch
            {
                return false;
            }
            finally
            {
                BaseSqlCommand.Parameters.Clear();
                CloseConnection();
            }
        }

加入了一个组合类

    public class SqlModel:ISqlModel
    {

        #region ISqlModel 成员

        public bool Proc_Return_Int(string proc_name, string[,] sArray)
        {
            try
            {
                if (sArray.GetLength(0) >= 1)
                {
                    DataBase db = new DataBase();
                    SqlParameter[] sqlpar = new SqlParameter[sArray.GetLength(0)+1];//加入返回值
                    for (int i = 0; i < sArray.GetLength(0); i++)
                    {
                        sqlpar[i] = new SqlParameter(sArray[i,0], sArray[i,1]);
                    }
                    sqlpar[sArray.GetLength(0)] = new SqlParameter("Return", SqlDbType.Int);
                    sqlpar[sArray.GetLength(0)].Direction = ParameterDirection.ReturnValue;

                    if (db.Proc_Return_Int(proc_name, sqlpar))
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        #endregion
    }

前台调用

        string[,] sArray = new string[3,2];
        sArray[0,0]="@parent_id";
        sArray[1,0]="@cn_name";
        sArray[2,0]="@en_name";
        sArray[0,1]="5";
        sArray[1,1]="aaaab";
        sArray[2,1]="cccccc";
        Factory.SqlModel sm = new Factory.SqlModel();
        sm.Proc_Return_Int("Product_Category_Insert", sArray);

存储过程内容

ALTER PROCEDURE [dbo].[Product_Category_Insert]
    @parent_id int,
    @cn_Name nvarchar(50),
    @en_Name nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ERR int
    SET @ERR=0
    BEGIN TRAN
    IF @parent_id<0 OR ISNULL(@cn_Name,'')=''
        BEGIN
            SET @ERR=1
            GOTO theEnd
        END
        
    IF(NOT EXISTS(SELECT Id FROM Product_Category WHERE Id=@parent_id))
        BEGIN
            SET @ERR=2
            GOTO theEnd
        END  
    DECLARE @Id int,@Depth int,@ordering int

    SELECT @Id=ISNULL(MAX(Id)+1,1) FROM Product_Category--计算@Id

    IF @Parent_Id=0
        BEGIN
            SET @Depth=1--计算@Depth
            SELECT @Ordering=ISNULL(MAX(Ordering)+1,1) FROM Product_Category--计算@OrderId
        END
    ELSE
        BEGIN
            SELECT @Depth=Depth+1  FROM Product_Category WHERE Id=@Parent_Id--计算@Depth,计算@Ordering时需要用到
            SELECT @Ordering=MAX(Ordering)+1 FROM Product_Category--计算@Ordering
                WHERE Id=@Parent_Id 
            UPDATE Product_Category SET Ordering=Ordering+1 WHERE Ordering>=@Ordering--向后移动插入位置后面的所有节点
        END

    INSERT INTO Product_Category(Id,Parent_Id,cn_Name,en_name,Depth,Ordering) VALUES (@Id,@Parent_Id,@cn_Name,@en_name,@Depth,@Ordering)

    IF @@ERROR<>0
        SET @ERR=-1
    theEnd:
    IF @ERR=0
        BEGIN
        COMMIT TRAN
        RETURN 0
        END
    ELSE
        BEGIN
        ROLLBACK TRAN
        RETURN @ERR
        END
END