The Using Database Connections” section briefly touched on the idea of issuing commands against a database, A command is in its simplest form, a string of text containing SQLstatements that is to be issued to the database, A command could also be a stored procedure, or the name of a table that will return all columns and all rows-from that table (in other words, a SELECT∗-style clause).
A command can be constructed by passing the SQL clause as a parameter to the constructor of the Command class, as shown in this example:
The <provider>Command classes have a property called CommandType,which is used to define whether the command is a SQL clause, a call to a stored procedure, or a full table statement (which simply selects all columns and rows from a given table), The following table summarizes the CommandType enumeration.
When executing a stored procedure, it might be necessary to pass parameters to that procedure.
The previous example sets the @CustomerIDparameterdirectly,although there are other ways of setting the parameter value, which you look at later in this chapter. Note that in ,NET 2.0, the AddWithValue ( ) method was added to the command parameters collection – and the Add ( name, value) member was attributed as Obsolete, If you have used this original method of constructing parameters for calling a stored procedure, you will receive compiler warnings when you recompile your code, We suggest altering your code now because Microsoft will most likely remove the older method in a subsequent release of .NET.
The TableDirect command type is valid only in the OleDb provider; other providers will throw an exception if you attempt to use this command type with them.
After you have defined the command, you need to execute it. A number of ways exist to issue the statement, depending on what you expect to be returned (if anything) from that command, The <provider>Conunand classes provide the following execute methods:
¤ ExecuteNonQuery () – Executes the command but does not return any output
¤ ExecuteReader () – Executes the command and returns a typed IDataReader
¤ ExecuteScalar () – Executes the command and returns a single value
In addition to these methods, the SqlCommand class exposes the following method:
¤ ExecuteXmlReader () – Executes the command and returns an XmlReader object, which can be used to traverse the XML fragment returned from the database
As with the other chapters, you can download the sample code from the csharp Web siteat www.csharpaidcom.
This method iscommonly used for UPDATE, INSERT, or DELETE statements, where the only returned value is the number of records affected.This method can, however, return resultsifyou calla stored procedure that has output parameters:
ExecuteNonQuery () returns the number of rows affected by the command as an int.
This method executes the command and returns a typed data reader object,depending on the provider in use, The object returned can be used to iterate through the record(s) returned, as shown in the following code:
Figure 26-2 shows the output of this code.
The <provider>DataReader objects are discussed later in this chapter.
On many occasions, it is necessary to return a single result from a SQLstatement, such as the count of records in a given table, or the current date/time on the server, The ExecuteScalar method can be used in such situations:
The method returns an object, which you can cast to the appropriate type if required. If the SQL you are calling returns only one column, it is preferable to use ExecuteScalar over any other method of retrieving that column, That also applies to stored procedures that return a single value.
ExecuteXmlReader() (SqlClient Provider Only)
As its name implies, this method executes the command and returns an XmlReader object to the caller, SQL Server permits a SQL SELECT statement to be extended with a FOR XML clause. This clause can take one of three options:
¤ FOR XMLAUTO- Builds a tree based on the tables in the FROM clause
¤ FOR XML RAW- Maps result set rows to elements, with columns mapped to attributes
¤ FOR XML EXPLICIT – Requires that you specify the shape of the XML tree to be returned
Professional SQL Server 2000 XML (csharp Press, ISB. 1-861005-46-6) includes a complete description of these options. For this example, use AUTO:
Note that you have to import the System. Xml namespace in order to output the returned XML, This namespace and further XML capabilities of .NET Framework ” Here, you include the FOR XMLAUTOclause in the SQL statement, then call the ExecuteXmlReader () method. Figure 26-3 shows the output of this code.
In the SQL clause, you specified FROMCustomers, so an element of type Customers is shown in the output, To this are added attributes, one for each column selected from the database, This builds up an XML fragment for each row selected from the database.
Calling Stored Procedures
Calling a stored procedure with a command object is just a matter of defining the name of the stored procedure, adding a definition for each parameter of the procedure, and then executing the command with one of the methods presented in the previous section.
To make the examples in this section more useful, a set of stored procedures has been defined that can be used to insert, update, and delete records from the Region table in the Northwind sample database, Despite its small size, this is a good candidate to choose for the example because it can be used to define examples for each of the types of stored procedures you will commonly write.
Calling a Stored Procedure That Returns Nothing
The simplest example of calling a stored procedure is one that returns nothing to the caller, Two such procedures are defined in the following two subsections: one for updating a preexisting Region record and one for deleting a given Region record.
Updating a Region record is fairly trivial because there is only one column that can be modified (assuming primary keys cannot be updated), You can type these examples directly into the SQL Server Query Analyzer.
An update command on a more real-world table might need to reselect and return the updated record in its entirety, This stored procedure takes two input parameters (@RegionID and @RegionDesdription), and issues an UPDATEstatement against the database.
To run this stored procedure from within .NET code, you need to define a SQLcommand and execute it:
‘Thiscode creates a new SqlCommand object named aCommand, and defines it as a stored procedure, You then add each parameter in turn using the Add With Value method, Thisconstructs a parameter and also sets its value – you can also manually construct SqlParameter instances and add these to the Parameters collection if appropriate.
The stored procedure takes two parameters: the unique primary key of the Region record being updated and the new description to be given to this record. After the command has been created, it can be executed by issuing the following command:
Because the procedure returns nothing, Execu teNonQuery () will suffice, Command parameters can be set directly using the AddWi thValue method, or by constructing SqlParameter instances, Note that the parameter collection is indexable by position or parameter name.
The next stored procedure required is one that can be used to delete a Region record from the database:
CREATE PROCEOURE RegionOelete (@RegionIO INTEGER) AS
SET NOCOUNT OFF
WHERE FROM Region
WHERE RegionID = @RegionID
‘This procedure requires only the primary key value of the record, The code uses a SqlCommand object to call this stored procedure as follows:
‘This command accepts only a single parameter, as shown in the following code, which will execute the Region Delete stored procedure; here, you see an example of setting the parameter by name, If you have many similar calls to make to the same stored procedure, then constructing SqlParameter instances and setting the values as in the following may lead to better performance than re-constructing the entire SqlCommand for each call.
cmd.Parameters[“@RegionIO”j .value= 999;
Calling a Stored Procedure That Returns Output Parameters
Both of the previous examples execute stored procedures that return nothing, If a stored procedure includes output parameters, these need to be defined within the .NET client so that they can be filled when the procedure returns, The following example shows how to insert a record into the database and return the primary key of that record to the caller.
The Region table consists of only a primary key (RegionID) and description field (RegionDescription), To insert a record, this numeric primary key must be generatt41, and then a new row needs to be inserted into the database, The primary key generation in this example has been simplified by creating one within the stored procedure, The method used is exceedingly crude, which is why there is a section on key generation later in this chapter. For now, this primitive example suffices:
The insert procedure creates a new Region record, Because the primary key value is generated by the database itself, this value is returned as an output parameter from the procedure (@RegionID), This is sufficient for this simple example, but for a more complex table (especially one with default values), it is more common not to use output parameters, and instead select the entire inserted row and return this to the caller, The .NET classes can cope with either scenario.
Here, the definition of the parameters is much more complex, The second parameter, @RegionID, is defined to include its parameter direction, which in this example is Output, In addition to this flag, on the last line of the code, the UpdateRowSource enumeration is used to indicate that data will be returned from this stored procedure via output parameters, This flag is mainly used when issuing stored procedure calls from a DataTable.
Calling this stored procedure is similar to the previous examples, except in this instance the output parameter is read after executing the procedure:
After executing the command, the value of the @RegionID parameter is read and cast to an integer, A shorthand version of the preceding is the ExecuteScalar () method, which will return (as an object) the first value returned from the stored procedure.
You might be wondering what to do if the stored procedure you call returns output parameters and a set of rows, In this instance, define the parameters as appropriate, and rather than calling ExecuteNonQuery (), call one of the other methods (such as ExecuteReader () that will permit you to traverse any record(s) returned.