asp.net調(diào)用存儲過程類

2024-07-10 13:05:47

字體：大中小

供稿：網(wǎng)友

最近在csdn上遇到些朋友在問在asp.net上調(diào)用存儲過程的方法，在這里將我的經(jīng)驗(yàn)總結(jié)一下并整理發(fā)布處理，供大家參考。

基本思路是：先獲得存儲過程的參數(shù)，然后根據(jù)參數(shù)表收集值，然后再調(diào)用存儲過程。但要求在頁面中的控件id必須與存儲過程的參數(shù)保持一致。并有幾種調(diào)用方式。這個也是經(jīng)驗(yàn)的總結(jié)，并未仔細(xì)的推敲，包括很多地方?jīng)]有捕捉error，如果有朋友將起改進(jìn)，麻煩將副本發(fā)給我一個，謝謝。本人mail：[email protected]

public class db:page
{
//數(shù)據(jù)庫連接
public sqlconnection conn = new sqlconnection(system.configuration.configurationsettings.appsettings["conn"]);
//創(chuàng)建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;}}

//連接數(shù)據(jù)庫
public sqlconnection conn()
{
   if(conn.state!=connectionstate.open)
   {
    conn.open();
   }
   return conn;
}

//獲取存儲過程的參數(shù)
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");
   //連接數(shù)據(jù)庫
   conn=this.conn();
   //創(chuàng)建并獲取存儲過程參數(shù)列表
   dataset ds=new dataset();
   ds=this.getstoreprocedureparams(storeprocedurename);

   //存儲過程參數(shù)值
   string tempvalue;
   //以數(shù)組方式傳遞的參數(shù)對象的個數(shù)
   int args=this.paras.length;
   //存儲過程賦值方式標(biāo)記
   bool flag=false;
   //創(chuàng)建sqlcommand對象，并置為存儲過程方式
   cmd=new sqlcommand(storeprocedurename,conn);
   cmd.commandtype=commandtype.storedprocedure;
   //對存儲過程參數(shù)集進(jìn)行遍歷，如果未獲得數(shù)組賦值，則遍歷窗體控件進(jìn)行賦值，如果二者都沒有，則賦值為空
   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;
     }

     //初始化新參數(shù)值，并置賦值方式狀態(tài)
     tempvalue="";
     flag=false;

     //遍歷數(shù)組
     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)
      {
       //創(chuàng)建窗體對象集
       ienumerator ie=this.hf.controls.getenumerator();
       ie.reset();
       //遍歷窗體控件，檢索對應(yīng)參數(shù)的賦值
       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;
}
}

調(diào)用方法：

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