C # Call the stored procedure simple and complete example

- Simple stored procedures are as follows: ------------------------------------------- -------------------------------------------- Create Proc P_Test @ Name VARCHAR (20), @ Rowcount Int outputasbegin select * from t_customer where name = @@ rowcountendgo ------------------------- -------------------------------------------------- -------------- store procedure calls are as follows: ------------------------------ -------------------------------------------------- -------- Declare @i intexec p_test 'a', @ i outputselect @ i - Result / * Name Address Tel ------------------ -------------------- A Address Telphone

(The number of rows affects is 1 line)

----------- 1

(The number of rows affects is 1 line) * / -------------------------------------- -------------------------------------------------- - DOTNET section (C #) - WebConfig file: -------------------------------------- -------------------------------------------------- ... ------------------------------------ -------------------------------------------------- ---- C # code: (with two test controls, DataGrid1 (used to display binding result collection), lable (for displaying stored procedures return single value) ------------- -------------------------------------------------- ------------------------- // Add database reference using system.data.sqlclient; ... private void page_load (Object Sender, System.EventArgs e) {// Put user code to initialize the page here String DBConnStr; DataSet MyDataSet = new DataSet (); System.Data.SqlClient.SqlDataAdapter DataAdapter = new System.Data.SqlClient.SqlDataAdapter (); DBConnStr = System.configuration.configurationSettings.AppSettings ["ConnectString"]; s ystem.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 an input query parameter, given a value of 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.database (); // Get stored procedure output parameter label1.text = mycommand.Parameters [@ rowcount "] .Value.toString ();

IF (MyConnection.State == ConnectionState.Open) {myconnection.close ();}

} ------------------------------------- -------------------------------------- Run the above code (return to record collection and storage Procedure return value)