asp. net implements methods that call stored procedures with return values

This article illustrates the asp. net implementation of calling stored procedures with return values. Share it for your reference, as follows:

/// <summary>
/// DataBase  Summary description of 
/// </summary>
public class DataBase
{
    /// <summary>
    ///DataBase  Summary description of 
    /// </summary>
    protected static SqlConnection BaseSqlConnection = new SqlConnection();// Connection object 
    protected SqlCommand BaseSqlCommand = new SqlCommand(); // Command object 
    public DataBase()
    {
      //
      // TODO:  Add constructor logic here 
      //
    }
    protected void OpenConnection()
    {
      if (BaseSqlConnection.State == ConnectionState.Closed) // Is the connection 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();
      }
    }
}

Added 1 composite class

public class SqlModel:ISqlModel
{
  #region ISqlModel  Member 
  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];// Add a return value 
        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
}

Foreground call

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);

Stored procedure content

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-- Calculation @Id
  IF @Parent_Id=0
    BEGIN
      SET @Depth=1-- Calculation @Depth
      SELECT @Ordering=ISNULL(MAX(Ordering)+1,1) FROM Product_Category-- Calculation @OrderId
    END
  ELSE
    BEGIN
      SELECT @Depth=Depth+1 FROM Product_Category WHERE Id=@Parent_Id-- Calculation @Depth, Calculation @Ordering Need to be used when 
      SELECT @Ordering=MAX(Ordering)+1 FROM Product_Category-- Calculation @Ordering
        WHERE Id=@Parent_Id
      UPDATE Product_Category SET Ordering=Ordering+1 WHERE Ordering>=@Ordering-- Move all nodes behind the insertion position backward 
    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

I hope this article is helpful to everyone's asp. net programming.