|
-- The simple stored procedure is as follows:
Bytes ----------------------------------------------------------------------------------------
Create proc p_test
@ Name varchar (20 ),
@ Rowcount int output
As
Begin
Select * From t_customer whereName = @ name
Set @ rowcount = @ rowcount
End
Go
Bytes ----------------------------------------------------------------------------------------
-- The stored procedure is called as follows:
Bytes ----------------------------------------------------------------------------------------
Declare @ I int
Exec p_test 'A', @ I output
Select @ I
-- Result
/*
Name Address Tel
----------------------------------------
A address telphone
(The number of affected rows is 1)
-----------
1
(The number of affected rows is 1)
*/
Bytes ----------------------------------------------------------------------------------------
-- DOTNET part (C #)
-- Webconfig file:
Bytes ----------------------------------------------------------------------------------------
......
</System. Web>
<! -- Database connection string
-->
<Deleetask>
<Add key = "connectstring" value = "Server = (local); User ID = sa; Password =; database = test"/>
</Appsettings>
</Configuration>
Bytes ----------------------------------------------------------------------------------------
-- C # code: (two test controls are used: datagrid1 (used to display the binding result set) and lable (used to display the single value returned by the stored procedure)
Bytes ----------------------------------------------------------------------------------------
// Add database reference
Using system. Data. sqlclient;
......
Private void page_load (Object sender, system. eventargs E)
{
// Place user code here to initialize the page
String dbconnstr;
Dataset mydataset = new dataset ();
System. Data. sqlclient. sqldataadapter dataadapter = new system. Data. sqlclient. sqldataadapter ();
Dbconnstr = system. configuration. configurationsettings. deleettings ["connectstring"];
System. Data. sqlclient. sqlconnection myconnection = new system. Data. sqlclient. sqlconnection (dbconnstr );
If (myconnection. State! = Connectionstate. open)
{
Myconnection. open ();
}
System. Data. sqlclient. sqlcommand mycommand = new system. Data. sqlclient. sqlcommand ("p_test", myconnection );
Mycommand. commandtype = commandtype. storedprocedure;
// Add input query parameters and assign values
Mycommand. Parameters. Add ("@ name", sqldbtype. varchar );
Mycommand. Parameters ["@ name"]. value = "";
// Add output parameters
Mycommand. Parameters. Add ("@ rowcount", sqldbtype. INT );
Mycommand. Parameters ["@ rowcount"]. Direction = parameterdirection. output;
Mycommand. executenonquery ();
Dataadapter. selectcommand = mycommand;
If (mydataset! = NULL)
{
Dataadapter. Fill (mydataset, "table ");
}
Datagrid1.datasource = mydataset;
Datagrid1.databind ();
// Obtain the Stored Procedure output parameters
Label1.text = mycommand. Parameters ["@ rowcount"]. value. tostring ();
If (myconnection. State = connectionstate. open)
{
Myconnection. Close ();
}
} |
