asp.net调用存储过程类

2022-07-15 80酷酷网 80kuku.com

　　asp.net|存储过程

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

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

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