Use stored procedures in ASP. net2.0

The following are two stored procedures in SQL: Create procedure DBO. oa_selectalluser

As

Select * From userinfo

Go

Create procedure DBO. oa_selectbyid

@ ID int

As

Select * From userinfo where id = @ ID

Go

One is a stored procedure with parameters and the other is a stored procedure without parameters. The following describes how to use these two stored procedures in vs2005.

(1). stored procedures without parameters:

Protected void page_load (Object sender, eventargs E)

...{

If (! Page. ispostback)

...{

// Usage of stored procedures without Parameters

Sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["oaconnectionstring"]. tostring ());

Sqldataadapter da = new sqldataadapter ();

Dataset DS = new dataset ();

Da. selectcommand = new sqlcommand ();

Da. selectcommand. Connection = conn;

Da. selectcommand. commandtext = "oa_selectalluser ";

Da. selectcommand. commandtype = commandtype. storedprocedure;

Da. Fill (DS );

Gridview1.datasource = Ds;

Gridview1.databind ();

}

A gridview control is added to the page to bind the result of executing the stored procedure.

(2) stored procedures with parameters:

Protected void btn_search_click (Object sender, eventargs E)

...{

// How to use stored procedures with Parameters

Sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["oaconnectionstring"]. tostring ());

Sqldataadapter da = new sqldataadapter ();

Dataset DS = new dataset ();

Da. selectcommand = new sqlcommand ();

Da. selectcommand. Connection = conn;

Da. selectcommand. commandtext = "oa_selectbyid ";

Da. selectcommand. commandtype = commandtype. storedprocedure;

Sqlparameter Param = new sqlparameter ("@ ID", sqldbtype. INT );

Param. Direction = parameterdirection. input;

Param. value = convert. toint32 (txt_value.text );

Da. selectcommand. Parameters. Add (PARAM );

Da. Fill (DS );

Gridview1.datasource = Ds;

Gridview1.databind ();

}

Similarly, a gridview control is added to the page to bind the result of executing the stored procedure. In addition, a Textbox Control and a button are added to the page, the above execution stored procedure is placed in The onclick event of the button. the Textbox Control is used to receive stored procedure parameters.