asp.net call stored procedure class

Asp.net| Stored Procedures

Recently met some friends in csdn to ask the method of the stored procedure on the ASP.net, here will my experience summed up and the release of processing for your reference.

The basic idea is to get the parameters of the stored procedure, then collect the values based on the parameter table, and then call the stored procedure. However, the control ID required in the page must be consistent with the parameters of the stored procedure. And there are several ways to invoke it. This is also the summary of experience, not careful scrutiny, including many places do not catch error, if a friend will be improved, please send me a copy, thank you. I mail:huangguolinc@163.com

public class Db:page
{
Database connection
Public SqlConnection conn = new SqlConnection (system.configuration.configurationsettings.appsettings["Conn"]);
Creating SqlCommand objects
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;}}

Connecting to a database
Public SqlConnection Conn ()
{
IF (Conn. State!=connectionstate.open)
{
Conn. Open ();
}
Return conn;
}

Get parameters for stored procedures
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 ");
Connecting to a database
Conn=this. Conn ();
Create and get a list of stored procedure parameters
DataSet ds=new DataSet ();
Ds=this. Getstoreprocedureparams (Storeprocedurename);

Stored Procedure parameter values
String Tempvalue;
The number of parameter objects passed as an array
int Args=this. Paras.length;
Stored procedure Assignment method tag
BOOL Flag=false;
Create the SqlCommand object and place it in the stored procedure mode
Cmd=new SqlCommand (Storeprocedurename,conn);
Cmd.commandtype=commandtype.storedprocedure;
Iterate over the stored procedure parameter set, and if no array assignment is obtained, iterate through the form control to assign the value, if neither, the assignment is null
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
}

Initializes a new parameter value and assigns the state of the assignment
Tempvalue= "";
Flag=false;

Traversing an array
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)
{
Create a Form object set
IEnumerator Ie=this. Hf. Controls.getenumerator ();
Ie. Reset ();
Iterate through the form control to retrieve the assignment of the corresponding parameter
while (ie. MoveNext ())
{
Control ctl= 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 to SDR;
}
}

Call Method:

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