The second training materials (SQL Server Stored Procedures and ADO. NET access stored procedures) of the Network Studio after the summer vacation (II)

Using ADO and NET to call stored procedures can be divided into four methods

1) call a stored procedure without Parameters

2) call a stored procedure with a parameter

3) call a stored procedure with multiple parameters

4) Call the stored procedure with output parameters

(The stored procedure used in this sample code uses the stored procedure created in the previous blog of the author. Blog address: Workshop)

1. The stored procedure without parameters is called by ADO. NET. The stored procedure is the stored procedure created in 1-1 in the previous blog article.

The call code 1-1 is shown as follows:

1 public void ShowData1 () 2 {3 SqlConnection con = new SqlConnection (strConString); 4 // instantiate the SqlCommand object, specify the parameter object as the name of the stored procedure to be accessed. 5 SqlCommand cmd = new SqlCommand ("select_activity", con); 6 // specify the command type as stored procedure 7 cmd. commandType = CommandType. storedProcedure; 8 // create a dataset 9 SqlDataAdapter adapter = new SqlDataAdapter (cmd); 10 able table = new DataTable (); 11 adapter. fill (table); 12 13 DataRow Dr = table. rows [0]; 14 Response. write (Dr [3]. toString (); 15 16}

2. Because the previous blog does not create a stored procedure with only one parameter, the creation process is as follows:

1 -- create a stored procedure with a parameter 2 create proc select_activity33 @ id int4 as5 -- SQL command executed by the Stored Procedure 6 select * from T_Activity where id = @ id; 7 8 -- call the select_activity Stored Procedure 9 exec select_activity3 5

Example 1-2 code for calling a stored procedure with a parameter in ADO. NET is as follows:

1 /// <summary> 2 /// call the stored procedure with a parameter 3 /// </summary> 4 public void ShowData2 () 5 {6 SqlConnection con = new SqlConnection (strConString); 7 SqlCommand cmd = new SqlCommand ("select_activity3", con); 8 // specify the command type as Stored Procedure 9 cmd. commandType = CommandType. storedProcedure; 10 11 // declare the parameter object 12 SqlParameter parameter = new SqlParameter (); 13 // specify the parameter name 14 parameter. parameterName = "@ id"; 15 // the Data Type of the specified parameter 16 parameter. sqlDbType = SqlDbType. int; 17 // assign 18 parameter to the parameter. value = 5; 19 // Add the command object to 20 cmd in the parameter set of the command object. parameters. add (parameter); 21 SqlDataAdapter adapter = new SqlDataAdapter (cmd); 22 // create a DataTable object 23 DataTable table = new DataTable (); 24 adapter. fill (table); 25 26 DataRow Dr = table. rows [0]; 27 Response. write (Dr [3]. toString (); 28 29}

Iii. ADO.. NET calls a stored procedure with multiple parameters. In this example, the 1-3 Code uses two parameters as a demonstration. For more parameters, refer to this example, the stored procedure used is the example 1-2 in the previous blog

1 /// <summary> 2 /// call the parameter array with two parameters 3 /// </summary> 4 public void ShowData3 () 5 {6 SqlConnection con = new SqlConnection (strConString); 7 SqlCommand cmd = new SqlCommand ("select_activity1", con); 8 // specify the command type as Stored Procedure 9 cmd. commandType = CommandType. storedProcedure; 10 11 // declare a multi-dimensional parameter array 12 SqlParameter [] parameter = 13 {14 new SqlParameter ("@ id", SqlDbType. int, 4), 15 new SqlParameter ("@ activityName", SqlDbType. NVarChar, 50) 16}; 17 parameter [0]. value = 5; 18 // assign the parameter 19 parameter [1]. value = ""; 20 // Add the command object to the parameter set of the command object 21 cmd. parameters. addRange (parameter); 22 SqlDataAdapter adapter = new SqlDataAdapter (cmd); 23 // create a DataTable object 24 DataTable table = new DataTable (); 25 adapter. fill (table); 26 27 DataRow Dr = table. rows [0]; 28 Response. write (Dr [3]. toString (); 29}

4. ADO. NET access stored procedures with output parameters. The code 1-4 in this example uses one input parameter and one output parameter as a demonstration. For more parameters, refer to this example, the stored procedure used is an example of the previous blog article 1-4

1 /// <summary> 2 // call the stored procedure with the returned value 3 /// </summary> 4 public void ShowData4 () 5 {6 SqlConnection con = new SqlConnection (strConString); 7 SqlCommand cmd = new SqlCommand ("select_MathResult", con); 8 cmd. commandType = CommandType. storedProcedure; 9 SqlParameter [] parameter = {10 new SqlParameter ("@ activityName", SqlDbType. NVarChar, 50), 11 new SqlParameter ("@ result", SqlDbType. int, 4) 12}; 13 14 parameter [0]. value = ""; 15 // set the output method of the output parameter to 16 parameter [1]. direction = ParameterDirection. output; 17 cmd. parameters. addRange (parameter); 18 SqlDataAdapter adapter = new SqlDataAdapter (cmd); 19 DataTable table = new DataTable (); 20 adapter. fill (table); 21 Response. write (Convert. toInt32 (cmd. parameters [1]. value); 22}

In this way, all examples of accessing SQL Server Stored Procedures by ADO. NET are completed. Because I found that there was too much content to complete in a blog, I could only divide it into several articles. Please keep an eye on my blog. We will update it for you next time, and learn about SQL Server table connection query, multi-Table query, paging, and so on.