ASP.net implements methods to invoke stored procedures with return values _ Practical tips

The example in this article describes the method by which the asp.net implementation invokes the stored procedure and returns a value. Share to everyone for your reference, specific as follows:

<summary>///Database Summary description///</summary> public class DataBase {///<summary>///databa SE's summary description///</summary> protected static SqlConnection basesqlconnection = new SqlConnection ();//Connection Object PR otected SqlCommand Basesqlcommand = new SqlCommand ();
    Command object public DataBase () {///TODO: Add constructor logic here//} protected void OpenConnection () {if (basesqlconnection.state = = connectionstate.closed)//connection closes try {Basesqlconnect Ion. ConnectionString = configurationmanager.connectionstrings["Productsunion"].
          ToString ();
          Basesqlcommand.connection = basesqlconnection;
        Basesqlconnection.open (); The 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 (parame ter. 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 ();

 }
    }
}

Joined a combination of classes

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 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
}

Front 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 Contents

ALTER PROCEDURE [dbo].
  [Product_category_insert] @parent_id int, @cn_Name nvarchar (m), @en_Name nvarchar 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-- Computes the @id IF @Parent_Id =0 BEGIN SET @Depth =1--calculation @depth SELECT @Ordering =isnull (MAX (ordering) +1,1) from Produ ct_category--computes the @orderid end ELSE BEGIN SELECT @Depth =depth+1 the from product_category WHERE id= @Parent_Id--compute @Depth, you need to use the SELECT @Ordering =max (ordering) +1 from product_category--to compute the @ordering to calculate the @ordering WHERE id= @Parent _id UPDATE product_category SET ordering=ordering+1 where ordering>= @Ordering--Move backward all node end insert after insertion position NtO 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 ELS

 E BEGIN ROLLBACK TRAN return @ERR end

I hope this article will help you with ASP.net programming.