asp.net实现调用存储过程并带返回值的方法

本文实例讲述了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

希望本文所述对大家asp.net程序设计有所帮助。