Call stored procedure-zt

Http://www.oread.cn/user1/cdbookcn/archives/2006/20061122234428.html21.3.2 call Stored Procedure

Calling a stored procedure with a command object means defining the name of the stored procedure, adding parameter definitions to each parameter of the procedure, and then executing the command using the method given in the previous section.

To make the examples in this section more convincing, the following defines a set of stored procedures that can be used to insert, update, and delete records of the Region table in the Northwind sample database, although small, however, it can be used to write examples for each common stored procedure.

1. Call a stored procedure without return values

The simplest example of calling a stored procedure is not to return any value to the caller. The following two stored procedures are defined: one for updating the existing Region record and the other for deleting the specified Region record.

(1) Record Update

Updating a Region record is simple because (assuming that the primary key code cannot be updated) only one column can be updated. Directly type these examples in the SQL Server Query analyzer, or run the StoredProcs. SQL file in the downloaded code in this chapter to include all the stored procedures in this section:

Create procedure RegionUpdate (@ RegionID INTEGER,

@ RegionDescription NCHAR (50)

SET NOCOUNT OFF

UPDATE Region

SET RegionDescription = @ RegionDescription

WHERE RegionID = @ RegionID

GO

To execute the update command for tables in the real world, you must repeat and return updated records. This stored procedure has two input parameters (@ RegionID and @ RegionDescription) to execute the UPDATE Statement on the database.

To run this stored procedure in. NET code, you need to define an SQL command and execute it:

SqlCommand aCommand = new SqlCommand ("RegionUpdate", conn );

 

ACommand. CommandType = CommandType. StoredProcedure;

ACommand. Parameters. Add (new SqlParameter ("@ RegionID ",

SqlDbType. Int,

0,

"RegionID "));

ACommand. Parameters. Add (new SqlParameter ("@ RegionDescription ",

SqlDbType. NChar,

50,

"RegionDescription "));

ACommand. UpdatedRowSource = UpdateRowSource. None;

This Code creates a new SqlCommand object aCommand and defines it as a stored procedure. Then, add each parameter in sequence, and set the result of the stored procedure to a value in the UpdateRowSource enumeration.

The stored procedure has two parameters: the unique primary key code of the Region record to be updated, and a new description of the record. After creating a command, run the following command:

ACommand. Parameters [0]. Value = 999;

ACommand. Parameters [1]. Value = "South Western England ";

ACommand. ExecuteNonQuery ();

These commands set the values of each parameter and then execute the stored procedure. This process does not return values, so it is sufficient to use ExecuteNonQuery. Command parameters can be set in sequence as before, or by name.

(2) Deletion of records

The next stored procedure can be used to delete a Region record from the database:

Create procedure RegionDelete (@ RegionID INTEGER)

SET NOCOUNT OFF

Delete from Region

WHERE RegionID = @ RegionID

GO

In this process, only the key value of the record is required. The following code uses the SqlCommand object to call this stored procedure:

SqlCommand aCommand = new SqlCommand ("RegionDelete", conn );

ACommand. CommandType = CommandType. StoredProcedure;

ACommand. Parameters. Add (new SqlParameter ("@ RegionID", SqlDbType. Int, 0,

"RegionID "));

ACommand. UpdatedRowSource = UpdateRowSource. None;

This command only receives one parameter, as shown in the following code. It executes the RegionDelete stored procedure. This is an example of setting parameters by name:

ACommand. Parameters ["@ RegionID"]. Value = 999;

ACommand. ExecuteNonQuery ();

2. Call the stored procedure of the returned output parameter

None of the preceding two examples of stored procedures return values. If stored procedures contain output parameters, they need to be defined in the. NET client program to fill in the output parameters when the process returns. The following example shows how to insert a record in the database and return the primary key code of the record to the caller.

Insert record

The Region table only consists of a RegionID and a RegionDescription field. To insert a record, you need to generate the key code for this number and then insert the new row into the database. In this example, a primary key code is created during the storage process to simplify the generation of the primary key code. The method used has not been processed. This is the reason why the key generation is described in a section later in this chapter. The following example is sufficient:

Create procedure RegionInsert (@ RegionDescription NCHAR (50 ),

@ RegionID integer output)

SET NOCOUNT OFF

SELECT @ RegionID = MAX (RegionID) + 1

FROM Region

Insert into Region (RegionID, RegionDescription)

VALUES (@ RegionID, @ RegionDescription)

GO

During the insert process, a new Region record is created. When the database itself generates the primary key code value, this value is returned as the output parameter (@ RegionID) from the process ). This is enough for this simple example. However, for complicated tables (especially tables with default values), the output parameter is usually not used, but the entire inserted row is selected, return this row to the caller .. NET class can handle these two cases.

SqlCommand aCommand = new SqlCommand ("RegionInsert", conn );

ACommand. CommandType = CommandType. StoredProcedure;

ACommand. Parameters. Add (new SqlParameter ("@ RegionDescription ",

SqlDbType. NChar,

50,

"RegionDescription "));

ACommand. Parameters. Add (new SqlParameter ("@ RegionID ",

SqlDbType. Int,

0,

ParameterDirection. Output,

False,

0,

0,

"RegionID ",

DataRowVersion. Default,

Null ));

ACommand. UpdatedRowSource = UpdateRowSource. OutputParameters;

Parameter definitions are complex. The second parameter @ RegionID is defined to include its parameter orientation. In this example, Output is used. In addition to this flag, this example uses the UpdateRowSource enumeration in the last row to display the data returned from this stored procedure through the output parameters. This flag is used when calling a stored procedure from a able (see the content later in this chapter.

Calling this stored procedure is similar to the previous example, but in this instance, you need to read the output parameters after the execution:

ACommand. Parameters ["@ RegionDescription"]. Value = "South West ";

ACommand. ExecuteNonQuery ();

Int newRegionID = (int) aCommand. Parameters ["@ RegionID"]. Value;

After executing the command, read the value of the @ RegionID parameter and convert its data type to an integer.

What if the called stored procedure returns output parameters and a set of record rows? In this instance, appropriate parameters should be defined rather than ExecuteNonQuery (). Another method (such as ExecuteReader () should be called to traverse all returned records.