| -Simple stored procedures are as follows:
----------------------------------------------------------------------------------------
CREATE PROC P_test
@Name VARCHAR (20),
@Rowcount INT OUTPUT
As
BEGIN
SELECT * from T_customer WHERE name= @Name
SET @Rowcount =@ @ROWCOUNT
End
Go
----------------------------------------------------------------------------------------
--stored procedure calls are as follows:
----------------------------------------------------------------------------------------
DECLARE @i INT
EXEC p_test ' A ', @i OUTPUT
SELECT @i
--The result
/*
Name Address Tel
---------- ---------- --------------------
A Address Telphone (The number of rows affected is 1 rows)
-----------
1
(The number of rows affected is 1 rows)
*/
----------------------------------------------------------------------------------------
--dotnet section (C #)
--webconfig file:
----------------------------------------------------------------------------------------
......
</system.web>
<!--database connection string
-->
<appSettings>
<add key= "connectstring" value= "server=" (local); User Id=sa; password=;d atabase=test "/>
</appSettings>
</configuration>
----------------------------------------------------------------------------------------
--c# code: (used with two test controls, DATAGRID1 (for displaying the binding result set), lable (for displaying stored procedure return single values)
----------------------------------------------------------------------------------------
Add a Database Reference
Using System.Data.SqlClient;
......
private void Page_Load (object sender, System.EventArgs e)
{
Place user code here to initialize page
String Dbconnstr;
DataSet mydataset=new DataSet ();
System.Data.SqlClient.SqlDataAdapter dataadapter=new System.Data.SqlClient.SqlDataAdapter ();
dbconnstr=system.configuration.configurationsettings.appsettings["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, assign values
MYCOMMAND.PARAMETERS.ADD ("@Name", SqlDbType.VarChar);
mycommand.parameters["@Name"]. Value = "A"; 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 ();
Get stored procedure output parameters
label1.text=mycommand.parameters["@Rowcount"]. Value.tostring (); if (myConnection.State = = ConnectionState.Open)
{
Myconnection.close ();
} } |
