19.4 Methods Reference

Cancel();

Halts a Command that is currently executing. If the Command isn't executing, nothing happens. If the Command is in the middle of fetching results with a DataReader, the DataReader is closed. If the Command is in the middle of performing another time-consuming operation, an attempt is made to stop the operation. However, in this case, the Cancel( ) method must be called from another thread because the main thread will be blocked, waiting for the operation to complete.

IDbDataParameter param = Command.CreateParameter();

Returns a strongly typed provider-specific IDbDataParameter object. This method is primarily useful if you are writing generic code because it doesn't force you to explicitly differentiate your code based on the type of Parameter object. However, this approach can be restrictive because it prevents you from using data types that may be specific to your data source (you must use the closest type from the System.Data.DbType enumeration instead). Also, unlike the Parameter object constructors and the ParameterCollection.Add() method, the CreateParameter( ) method doesn't accept any parameters, which means that you need to specify the name, data type, length, and so on, using separate property set statements.

Example

The following code snippet uses the CreateParameter( ) method to generically create a Parameter object and then configures it accordingly:

IDbDataParameter param = cmd.CreateParameter();
param.Name = "@Description";
param.DbType = DbType.VarWChar;
param.Size = 88;
param.Value = "This is the description";

cmd.Add(param);

int rowsAffected = Command.ExecuteNonQuery();

Executes a nonquery command (a command that doesn't return a set of rows). You can use ExecuteNonQuery( ) to modify data with the UPDATE, INSERT, or DELETE statements, in which case it returns the number of affected rows. You can also use the ExecuteNonQuery( ) method with other SQL statements, such as DDL commands that modify the structure of database tables. In this case, the ExecuteNonQuery( ) command returns -1.

Example

The following example executes an UPDATE command and displays the number of affected rows in a console window:

string cQL = "UPDATE Categories SET CategoryName='Beverages'" +
             "WHERE CategoryID=1";

SqlCommand cmd = new SqlCommand(SQL, con);

// Execute the command.
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();

// Display the result of the operation.
Console.WriteLine(rowsAffected.ToString() + " row(s) affected");

IDataReader r = Command.ExecuteReader();
IDataReader r = Command.ExecuteReader(CommandBehavior cb);

Executes a query command, typically a SELECT statement or a stored procedure that uses a SELECT statement. The command returns a strongly typed provider-specific DataReader object (implementing IDataReader), which must be used to iterate through the results.

Parameter

CommandBehavior cb

The CommandBehavior parameter can specify additional options detailing how the provider should handle the request. These options are indicated using a bitwise combination of values from the System.Data.CommandBehavior enumeration, as described in Table 19-4.

Table 19-4. CommandBehavior values

Value	Description
CloseConnection	When the DataReader returned by this method is closed, the underlying Connection object is also closed automatically.
Default	Specifies the default behavior. Using this value is equivalent to calling ExecuteReader( ) without supplying any CommandBehavior values.
KeyInfo	The query returns column and primary key information. When you use this option, the SQL Server provider automatically appends a FOR BROWSE clause to the statement.
SchemaOnly	The query returns column information only.
SequentialAccess	Instead of loading an entire row into memory each time you call the DataReader.Read( ) method, the information is read as a stream (and you can use DataReader.GetBytes( ) to access it). As a side effect, columns must be accessed in the order they are retrieved in the query, and you can't reread a column once you have read past its location. This value is typically used when retrieving large binary values because it lowers the memory footprint and can increase performance. See Chapter 5 for a complete example.
SingleResult	The query doesn't return more than one result set. Depending on the database, this information can theoretically allow the provider to optimize how it accesses the database.
SingleRow	The query returns a single row or multiple result sets that each contain a single row. Depending on the database, this information can theoretically allow the provider to optimize how it accesses the database. For example, when you specify SingleRow with the OLE DB provider, it uses the OLE DB IRow interface, if possible, instead of the IRowset interface.

Example

The following code snippet uses ExecuteReader( ) to retrieve a list of customers:

string SQL = "SELECT * FROM Customers";

SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader r;

con.Open();
r = cmd.ExecuteReader();

// Iterate over the results.
while (r.Read())
{
    lstNames.Items.Add(r["ContactName"]);
}

con.Close();

Note

Some providers don't support the values of the CommandBehavior enumeration. In this case, they may ignore the values or throw a NotSupportedException.

object result = Command.ExecuteScalar();

Executes a SQL command and returns the first value of the first row from the result set. One common use of this method is to return the results of an aggregate SQL function.

Example

Following is an example that uses the ExecuteScalar( ) method with the SQL aggregate function COUNT to determine how many rows match a specific criteria.

string SQL = "SELECT COUNT(*) FROM Orders WHERE " +
             "OrderDate >= '1996-01-01' AND OrderDate < '1997-01-01'";

SqlCommand cmd = new SqlCommand(SQL, con);

con.Open();
int result = (int)cmd.ExecuteScalar();
con.Close();

// Display the result of the operation.
Console.WriteLine(result.ToString() + " rows in 1996");

XmlReader = Command.ExecuteXmlReader();

Executes a query command, typically a SELECT statement or a stored procedure that uses a SELECT statement. The command returns an XmlReader object, which must be used to iterate through the results.

In order for this method to work successfully, your query must return an XML document from the data source. In other words, your query should include the SQL Server FOR XML clause.

The SQL Server 2000 XML extensions are described in Chapter 17, which also has information about the SQLXML provider.

Example

The following example retrieves category records as an XML document in which each row is a separate element and all column values are represented by element attributes. The code iterates through the results and prints out the returned information.

string SQL = "SELECT * FROM Categories FOR XML AUTO";

SqlCommand com = new SqlCommand(SQL, con);

con.Open();
XmlReader reader = com.ExecuteXmlReader();

while (reader.Read())
{
    Console.WriteLine("Element: " + reader.Name);

     // Print all column values.
     if (reader.HasAttributes)
     {
         for (int i = 0; i < reader.AttributeCount; i++)
         {
             reader.MoveToAttribute(i);
             Console.Write("\t");
             Console.Write(reader.Name + ": ");
             Console.WriteLine(reader.Value);
         }
         // Move back to the element node.
         reader.MoveToElement();
    }
}

reader.Close();
con.Close();

Note

As with the DataReader, you should read results as quickly as possible, and you must call XmlReader.Close( ) before attempting to use the Connection for another task.

Command.Prepare();

Calling the Prepare( ) method creates a prepared version of a command in the data source, leading to improved performance if you want to reuse it multiple times with different values. However, some providers will not support this method, and others will not demonstrate any performance increase. Typically, SQL Server Version 6.5 or earlier may demonstrate an improvement, while SQL Server 7 databases perform all the necessary optimization automatically.

If you wish to use the Prepare( ) method, call it only after you have defined the Command and added all its parameters.

Example

The following example uses the Prepare( ) method before invoking a parameterized UPDATE command:

string SQL = "UPDATE Categories SET CategoryName=@CategoryName " +
             "WHERE CategoryID=@CategoryID";

SqlCommand cmd = new SqlCommand(SQL, con);

SqlParameter param;
param = cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
param.Value = "Beverages";

param = cmd.Parameters.Add("@CategoryID", SqlDbType.Int);
param.Value = 1;

// Prepare and execute the command.
con.Open();
cmd.Prepare();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();

Notes

Because Prepare( ) requires an extra trip to the data source (to compile the initial command), it can actually reduce performance. It's recommended that you use this method only if you have tested it and confirmed it achieves a performance increase under your operating conditions.

When using Prepare( ), make sure each Parameter object has the correct value set for its Parameter.Size properties. Otherwise, data may be truncated, and no error will occur to inform you of the problem.

Command.ResetCommandTimeout();

Resets the CommandTimeout property to the default value, which is usually 30 seconds. This method isn't defined by the IDbCommand interface and as such, isn't guaranteed to be supported by all ADO.NET providers.