Use ADO. Net to operate SQL Server Stored Procedures

1. Call the stored procedure without input/output parameters
Create procedure testproc
As
Select pub_id, title_id, price, pubdate
From titles
Where price is not null
Order by pub_id

Use the following program to call this stored procedure. When calling this stored procedure, you must tell the command object that you want to call the stored procedure. <Form ID = "form1" runat = "server">
<Div>
<Asp: DataGrid id = "DG" runat = "server"/>
</Div>
</Form>

Sqlconnection conn;
Protected void page_load (Object sender, eventargs E)
{
Conn = new sqlconnection ("Server = localhost; database = pubs; uid = sa; Pwd = ''");
Sqlcommand comm = new sqlcommand ("testproc", Conn );
Comm. commandtype = commandtype. storedprocedure;
Conn. open ();
Sqldatareader DR = comm. executereader ();
DG. datasource = Dr;
DG. databind ();
Conn. Close ();
}

2. Create procedure sp_checkpass
(@ Chkname varchar (30), @ chkpass varchar (30), @ isvalid varchar (12) Output)
As
If exists (select username from webusers where username = @ chkname and
Userpass = @ chkpass)
Select @ isvalid = 'good'
Else
Select @ isvalid = 'bad'

Sqlconnection conn;
Protected void page_load (Object sender, eventargs E)
{
Conn = new sqlconnection ("Server = localhost; database = pubs; uid = sa; Pwd = ''");
Sqlcommand comm = new sqlcommand ("sp_checkpass", Conn );
Comm. commandtype = commandtype. storedprocedure;
Sqlparameter parm = comm. Parameters. Add ("@ chkname", sqldbtype. varchar, 30 );
Parm. value = "AA ";
Parm = comm. Parameters. Add ("@ chkpass", sqldbtype. varchar, 30 );
Parm. value = "AA ";
Parm = comm. Parameters. Add ("@ isvalid", sqldbtype. varchar, 12 );
Parm. Direction = parameterdirection. output;

Conn. open ();
Sqldatareader DR = comm. executereader ();
Response. Write (Comm. Parameters ["@ isvalid"]. value );
Conn. Close ();
}