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 by the author's previous blog, blog address: http://yisuowushinian.blog.51cto.com/4241271/1016524, please refer)

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:

 
 

  
  
/// <Summary>
  
  
/// Call a stored procedure without Parameters
  
  
/// It is of little significance to call a stored procedure without parameters. Generally, queries are conditional queries.
  
  
/// </Summary>
  
  
Publicvoid ShowData1 ()
  
  
{
  
  
SqlConnection con = new SqlConnection (strConString );
  
  
// Instantiate the SqlCommand object. The parameter object is specified as the name of the stored procedure to be accessed.
  
  
SqlCommand cmd = new SqlCommand ("select_activity", con );
  
  
// Specify the command type as stored procedure
  
  
Cmd. CommandType = CommandType. StoredProcedure;
  
  
// Create a dataset
  
  
SqlDataAdapter adapter = new SqlDataAdapter (cmd );
  
  
DataTable table = new DataTable ();
  
  
Adapter. Fill (table );
  
  
DataRow Dr = table. Rows [0];
  
  
Response. Write (Dr [3]. ToString ());
  
  
}
 
 

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

 
 

  
  
-- Create a stored procedure with a parameter
  
  
Create proc select_activity3
  
  
@ Id int
  
  
As
  
  
-- The SQL command executed by the Stored Procedure
  
  
Select * from T_Activity where id = @ id;
  
  
-- Call the select_activity Stored Procedure
  
  
Exec select_activity3 5
 
 

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

 
 

  
  
/// <Summary>
  
  
/// Call the stored procedure with a parameter
  
  
/// </Summary>
  
  
Publicvoid ShowData2 ()
  
  
{
  
  
SqlConnection con = new SqlConnection (strConString );
  
  
SqlCommand cmd = new SqlCommand ("select_activity3", con );
  
  
// Specify the command type as stored procedure
  
  
Cmd. CommandType = CommandType. StoredProcedure;
  
  
// Declare a parameter object
  
  
SqlParameter parameter = new SqlParameter ();
  
  
// Specify the parameter name
  
  
Parameter. ParameterName = "@ id ";
  
  
// Data type of the specified parameter
  
  
Parameter. SqlDbType = SqlDbType. Int;
  
  
// Assign a value to the parameter
  
  
Parameter. Value = 5;
  
  
// Add the command object to the parameter set of the command object
  
  
Cmd. Parameters. Add (parameter );
  
  
SqlDataAdapter adapter = new SqlDataAdapter (cmd );
  
  
// Create a able object
  
  
DataTable table = new DataTable ();
  
  
Adapter. Fill (table );
  
  
DataRow Dr = table. Rows [0];
  
  
Response. Write (Dr [3]. ToString ());
  
  
}
 
 

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

 
 

  
  
/// <Summary>
  
  
/// Call an array of parameters with two parameters
  
  
/// </Summary>
  
  
Publicvoid ShowData3 ()
  
  
{
  
  
SqlConnection con = new SqlConnection (strConString );
  
  
SqlCommand cmd = new SqlCommand ("select_activity1", con );
  
  
// Specify the command type as stored procedure
  
  
Cmd. CommandType = CommandType. StoredProcedure;
  
  
// Declare a multi-dimensional parameter Array
  
  
SqlParameter [] parameter =
  
  
{
  
  
New SqlParameter ("@ id", SqlDbType. Int, 4 ),
  
  
New SqlParameter ("@ activityName", SqlDbType. NVarChar, 50)
  
  
};
  
  
Parameter [0]. Value = 5;
  
  
// Assign a value to the parameter
  
  
Parameter [1]. Value = "";
  
  
// Add the command object to the parameter set of the command object
  
  
Cmd. Parameters. AddRange (parameter );
  
  
SqlDataAdapter adapter = new SqlDataAdapter (cmd );
  
  
// Create a able object
  
  
DataTable table = new DataTable ();
  
  
Adapter. Fill (table );
  
  
DataRow Dr = table. Rows [0];
  
  
Response. Write (Dr [3]. ToString ());
  
  
}
 
 

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

 
 

  
  
/// <Summary>
  
  
/// Call a stored procedure with a returned value
  
  
/// </Summary>
  
  
Publicvoid ShowData4 ()
  
  
{
  
  
SqlConnection con = new SqlConnection (strConString );
  
  
SqlCommand cmd = new SqlCommand ("select_MathResult", con );
  
  
Cmd. CommandType = CommandType. StoredProcedure;
  
  
SqlParameter [] parameter = {
  
  
New SqlParameter ("@ activityName", SqlDbType. NVarChar, 50 ),
  
  
New SqlParameter ("@ result", SqlDbType. Int, 4)
  
  
};
  
  
Parameter [0]. Value = "";
  
  
// Set the output method direction of the output parameter
  
  
Parameter [1]. Direction = ParameterDirection. Output;
  
  
Cmd. Parameters. AddRange (parameter );
  
  
SqlDataAdapter adapter = new SqlDataAdapter (cmd );
  
  
DataTable table = new DataTable ();
  
  
Adapter. Fill (table );
  
  
Response. Write (Convert. ToInt32 (cmd. Parameters [1]. Value ));
  
  
}
 
 

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.

Original works can be reprinted. During reprinting, you must mark the original source, author information, and this statement in hyperlink form. Otherwise, legal liability will be held.

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/1FG55393-0.png "/>

This article is from the blog of "Hadar column", please be sure to keep this source http://yisuowushinian.blog.51cto.com/4241271/1016527