ASP. NET calls a stored procedure and receives the returned values of the stored procedure

 

Assume the table structure

Create Table Table_1

(

Tid int identity (1, 1 ),

Number int

)

1. Call a stored procedure without parameters or returned values

Assume that the stored procedure is as follows:

Create PROC proc_1

AS

Insert Table_1 (number) Values (100)

GO

 

Call method:

Public static void exec_proc_1 ()

{

SqlCommand cmd = new SqlCommand ("proc_1", con); // proc_1 indicates the name of the stored procedure and con indicates the established connection.

Cmd. CommandType = CommandType. StoredProcedure; // you can specify the execution type as stored procedure.

Cmd. ExecuteNonQuery ();

}

 

2. Call a stored procedure with parameters and returned values

Assume that the stored procedure is as follows:

Create PROC proc_2

@ Result int output, // parameters to be returned

@ Number int,

@ Tid int

AS

Update Table_1 Set number = number + @ number Where tid = @ tid

Select @ result = number From Table_1

GO

 

Call method:

Public static int exec_proc_2 (int number, int tid)

{

SqlCommand cmd = new SqlCommand ("proc_2", con); // same as above

Cmd. CommandType = CommandType. StoredProcedure; // same as above

Cmd. Parameters. Add (new SqlParameter ("@ result", SqlDbType. Int); // Add a parameter named @ result. The data type is SqlDbType. Int.

Cmd. Parameters ["@ result"]. Direction = ParameterDirection. Output; // set the @ result parameter to the receiving Output parameter.

Cmd. Parameters. AddWithValue ("@ number", number );

Cmd. Parameters. AddWithValue ("@ tid", tid );

Cmd. ExecuteNonQuery ();

Int result = (int) cmd. Parameters ["@ result"]. Value; // convert the output Object data to the int type.

Return result;

}

 

3. Call and return the stored procedure of a table and receive

Assume that the stored procedure is as follows:

Create PROC proc_3

AS

Select * From Table_1 -- query a table

GO

 

Call method:

Public static DataTable exec_proc_3 ()

{

SqlCommand cmd = new SqlCommand ("proc_3", con); // same as above

Cmd. CommandType = CommandType. StoredProcedure; // same as above

SqlDataAdapter da = new SqlDataAdapter (cmd); // use SqlDataAdapter to execute

DataTable table = new DataTable (); // create a table

Da. Fill (table); // Fill the table

Return table;

}

 

From: Xiaofeng's blog