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)ASBEGIN  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    ENDEND