asp.net调用存储过程类

2024-07-10 13:05:47

字体：大中小

来源：转载

供稿：网友

最近在csdn上遇到些朋友在问在asp.net上调用存储过程的方法，在这里将我的经验总结一下并整理发布处理，供大家参考。

基本思路是：先获得存储过程的参数，然后根据参数表收集值，然后再调用存储过程。但要求在页面中的控件id必须与存储过程的参数保持一致。并有几种调用方式。这个也是经验的总结，并未仔细的推敲，包括很多地方没有捕捉error，如果有朋友将起改进，麻烦将副本发给我一个，谢谢。本人mail：[email protected]

public class db:page
{
//数据库连接
public sqlconnection conn = new sqlconnection(system.configuration.configurationsettings.appsettings["conn"]);
//创建sqlcommand对象
private sqlcommand cmd;

public sqldatareader returnsdr;
public string sqlquerystring="";

public string sqs
{
   set
   {
    sqlquerystring=value;
   }
   get
   {
    return sqlquerystring;
   }
}
public sqldatareader sdr
{
   set
   {
    returnsdr=value;
   }
   get
   {
    return returnsdr;
   }
}

public string[] paras={};
public string[] values={};
public string valuetype="ds";

public string valuetype
{
   set
   {
    valuetype=value;
   }
   get
   {
    return valuetype;
   }
}
public string[] paras
{
   set
   {
    paras=value;
   }
   get
   {
    return paras;
   }
}
public string[] values
{
   set
   {
    values=value;
   }
   get
   {
    return values;
   }
}

public system.web.ui.htmlcontrols.htmlform hf;
public system.web.ui.htmlcontrols.htmlform hf{set{hf=value;}get{return hf;}}
public bool hfenable=true;
public bool hfenable{set {hfenable=value;}get{return hfenable;}}
public int info;
public int info{set{info=value;}get{return info;}}

//连接数据库
public sqlconnection conn()
{
   if(conn.state!=connectionstate.open)
   {
    conn.open();
   }
   return conn;
}

//获取存储过程的参数
protected dataset getstoreprocedureparams(string storeprocedurename)
{
conn=this.conn();

int storeprocedureid=-1;
dataset ds=new dataset();

   sqlcommand sc=new sqlcommand("select id from dbo.sysobjects where name = '"+storeprocedurename+"'",conn);
   sqldatareader sdr=sc.executereader();
   while(sdr.read())
   {
    storeprocedureid=sdr.getint32(0);
   }
   sdr.close();

   sqldataadapter sda=new sqldataadapter("select dbo.syscolumns.name, dbo.systypes.name as type, dbo.syscolumns.length,dbo.syscolumns.isoutparam from dbo.syscolumns inner join dbo.systypes on dbo.syscolumns.xtype = dbo.systypes.xtype where dbo.syscolumns.id ='"+storeprocedureid+"'",conn);
   sda.fill(ds,"dbo.syscolumns");
   //sda.fill(ds,"dbo.systypes");

return ds;
}

public sqlcommand callstoreprocedure(string storeprocedurename)
{
   //server.transfer("../main/1.aspx");
   //连接数据库
   conn=this.conn();
   //创建并获取存储过程参数列表
   dataset ds=new dataset();
   ds=this.getstoreprocedureparams(storeprocedurename);

   //存储过程参数值
   string tempvalue;
   //以数组方式传递的参数对象的个数
   int args=this.paras.length;
   //存储过程赋值方式标记
   bool flag=false;
   //创建sqlcommand对象，并置为存储过程方式
   cmd=new sqlcommand(storeprocedurename,conn);
   cmd.commandtype=commandtype.storedprocedure;
   //对存储过程参数集进行遍历，如果未获得数组赋值，则遍历窗体控件进行赋值，如果二者都没有，则赋值为空
   foreach(datatable dt in ds.tables)
   {
    foreach(datarow dr in dt.rows)
    {
     switch(dr["type"].tostring())
     {
      case "varchar":
       cmd.parameters.add(new sqlparameter(dr["name"].tostring(),sqldbtype.varchar));
       break;
      default:
       cmd.parameters.add(new sqlparameter(dr["name"].tostring(),sqldbtype.varchar));
       break;
     }

     //初始化新参数值，并置赋值方式状态
     tempvalue="";
     flag=false;

     //遍历数组
     for(int itemindex=0;itemindex<args;itemindex++)
     {
      if(this.paras[itemindex]==dr["name"].tostring())
      {
       tempvalue=this.values[itemindex];
       flag=true;
       break;
      }
     }

     if(this.hfenable)
     {
      if(!flag)
      {
       //创建窗体对象集
       ienumerator ie=this.hf.controls.getenumerator();
       ie.reset();
       //遍历窗体控件，检索对应参数的赋值
       while(ie.movenext())
       {
        control ctl=(control)ie.current;
        if("@"+ctl.id==dr["name"].tostring())
        {

         switch(ctl.gettype().tostring())
         {
          case "system.web.ui.webcontrols.textbox":
           tempvalue=((textbox)ctl).text;
           break;
          case "system.web.ui.webcontrols.dropdownlist":
           tempvalue=((dropdownlist)ctl).selectedvalue;
           break;
          default:
           tempvalue="";
           break;
         }
         break;
        }
        if(ctl.gettype().tostring()=="system.web.ui.webcontrols.panel")
        {
         control ctlchild=ctl.findcontrol(dr["name"].tostring().substring(1));
         if(ctlchild!=null)
         {
          switch(ctlchild.gettype().tostring())
          {
           case "system.web.ui.webcontrols.textbox":
            tempvalue=((textbox)ctlchild).text;
            break;
           case "system.web.ui.webcontrols.dropdownlist":
            tempvalue=((dropdownlist)ctlchild).selectedvalue;
            break;
           default:
            tempvalue="";
            break;
          }
          //server.transfer("../main/1.aspx");
         }
        }
       }
      }
     }

     cmd.parameters[dr["name"].tostring()].value=tempvalue;
    }
   }
   return cmd;
}
public void nonexecute(string storeprocedurename)
{
   sqlcommand cmd = this.callstoreprocedure(storeprocedurename);

cmd.executenonquery();
}
public dataset dsexecute(string storeprocedurename)
{
dataset ds=new dataset();

sqlcommand cmd = this.callstoreprocedure(storeprocedurename);

   sqldataadapter sda = new sqldataadapter(cmd);
   sda.fill(ds,"result");
   return ds;
}
public sqldatareader sdrexecute(string storeprocedurename)
{
   sqlcommand cmd = this.callstoreprocedure(storeprocedurename);

sqldatareader sdr = cmd.executereader();

return sdr;
}
}

调用方法：

db.mapping dm=new db.mapping();
dm.hf=frm;
dm.hfenable=true;
string[] paras={"@saleperformcreater","@saleperformcreatetime"};
string[] values={((int)session["userid"]).tostring(),ldate.tostring()};
dm.paras=paras;
dm.values=values;

dm.nonexecute("saleperformnew");