Introduction to DataCommand Objects in Visual Studio

Article
10/07/2008

ADO.NET offers you two basic ways to work with data: using a dataset or working directly against a database. In the dataset model, you create an in-memory store of the records you want to work with, load the store using a data adapter, manipulate the data, and then, optionally, use the data adapter to write changes back to the database. For an overview of this, see Introduction to Data Access with ADO.NET.

Alternatively, you can work directly against the database. In this model, you configure a data command object with an SQL statement or the name of a stored procedure. You then execute the data command. If the command returns a result set, you use a data reader object to fetch the data.

Under some circumstances, you cannot use a dataset. For example, if you want to create database elements such as tables, you must use data commands.

Note For information about choosing between datasets and data commands, see Recommendations for Data Access Strategies.

About Data Commands

A data command contains a reference to an SQL statement or stored procedure that you can execute directly. A data command is an instance of the OleDbCommand, SqlCommand, OdbcCommand, or OracleCommand class; as with other such classes, the OleDbCommand class can be used with any OLE DB provider, the SqlCommand class is optimized for use with SQL Server 7.0 or later, the OdbcCommand class is for use with ODBC data sources, and the OracleCommand class is for use with Oracle databases.

Note For Oracle, the OracleCommand object (unlike the OleDbCommand, SqlCommand and OdbcCommand objects) does not support a CommandTimeout property. Setting a command timeout has no effect, and the value returned is always zero.

Security Note When using data commands with a CommandType property set to Text, carefully check information that is sent from a client before passing it to your database. Malicious users might try to send (inject) modified or additional SQL statements in an effort to gain unauthorized access or damage the database. Before you transfer user input to a database, you should always verify that the information is valid. A best practice is to always use parameterized queries or stored procedures when possible.

By using data commands, you can do the following in your application:

Execute Select commands that return a result you can read directly, rather than loading it into the dataset. To read the results, you use a data reader (OleDbDataReader, SqlDataReader, OdbcDataReader, or OracleDataReader object), which works like a read-only, forward only cursor and that you can bind controls to. This is a useful strategy for reducing memory usage and loading read-only data very quickly.
Execute database definition (DDL) commands to create, edit, and remove tables, stored procedures, and other database structures. (You must have permissions to perform these actions, of course.)
Execute commands to get database catalog information.
Execute dynamic SQL commands to update, insert, or delete records — rather than updating dataset tables and then copying changes to the database.
Execute commands that return a scalar value (that is, a single value), such as the results of a credit-card authentication lookup or a calculated value.
Execute commands that return data from a SQL Server database (version 7.0 or later) in XML format. A typical use is to execute a query and get back data in XML format, apply an XSLT transform to it (to convert the data to HTML), and then send the results to a browser.

How Data Commands Work

A data command's properties contain all the information necessary to execute a command against a database. This includes:

**A connection **The command references a connection that it uses to communicate with the database.
The name or text of a command The command includes the actual text of an SQL statement or the name of a stored procedure to execute.
**Parameters **A command might require you to pass parameters values along with it (input parameters). The command might also return values in the form of a return value or output parameter values. Each command has a collection of parameters that you can set or read individually to pass or receive values.

You execute a data command using a method appropriate to the results you expect to get back. For example, if you expect a result set, you call the command's ExecuteReader method, which returns records to a data reader. If you are performing an Update, Insert, or Delete command, you call the command's ExecuteNonQuery method, which returns a value indicating the number of rows affected.

Multiple Result Sets

A typical use of data commands is to return a single result set. However, data commands can execute procedures that return multiple results sets. This can happen in different ways. One is that the command references a stored procedure that returns multiple result sets. Alternatively, the command can contain two (or more) statements or stored procedure names. In that case, the statements or procedures are run sequentially and return multiple result sets with a single call.

Returning multiple result sets allows you to optimize your use of a single open connection. Typical uses include:

Running several queries, each of which returns a separate result set.
Running an Update or Insert statement followed by a Select statement that returns a refreshed version of the changed record or records, including values set by the database (such as auto-increment values).

If you specify multiple statements or procedures for a command, they must all be of the same type. For example, you can run successive SQL statements or successive stored procedures. However, you cannot mix stored procedure calls and SQL statements in the same command. For more information, see Retrieving Data Using the DataReader.

Note For Oracle, the .NET Framework Data Provider for Oracle does not support batched SQL statements. However, it does allow you to use multiple REF CURSOR output parameters to fill a dataset, each in its own data table. You must define the parameters, mark them as output parameters, and indicate that they are REF CURSOR data types. Note that you will be unable to use the Update method when the OracleDataAdapter object is filled from REF CURSOR parameters to a stored procedure, because Oracle does not provide the information necessary to determine what the table name and column names are when the SQL statement is executed.

Executing the Data Command

After configuring the data command's properties, you can execute the command. Data commands support four execute methods; the one you call depends on the statement or procedure being executed and the results you expect. You can execute and return:

Result set. In this case, you are executing an SQL statement or stored procedure that returns one or more records. You can get the result set one record at a time using a data reader. For more information, see Executing a Data Command that Returns a Result Set.
Count of affected records. This situation occurs when you are executing a statement or stored procedure that updates the database or that changes the database structure (for example, by adding a table or stored procedure). For more information, see Executing Updates or Database Commands Using a Data Command.
Single (scalar) value. This type of execution is for stored procedures or SQL statements that perform a lookup, calculate an aggregate value, or otherwise resolve to a single value. For more information, see Executing a Data Command that Returns a Single Value.
Data in XML format. This capability is supported by SQL Server version 7.0 or later. For more information, see Obtaining Data as XML from SQL Server.

How Data Adapters Use Data Commands

If you have already worked with datasets and data adapters, much of what you read about data commands might be familiar. In fact, a data adapter uses data commands to read and write to and from the database. Data adapters can contain up to four data command objects, one each in its SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties.

When you use a data adapter, it effectively performs the same operations with the commands that you might. For example, when you call the adapter's Fill method, the adapter executes the data command in its SelectCommand property and uses a data reader to fill the result set into the dataset table you specify. Similarly, when you call the adapter's Update method, it executes the appropriate command (in the UpdateCommand, InsertCommand, and DeleteCommand properties) for each changed record in the dataset table. The data adapter also makes sure that the appropriate parameters are passed with commands.

An important point is that data adapter is designed to use the commands to interact specifically with a dataset. By using data commands yourself, however, you can perform the same functions that a data adapter does, but you have more control over how and when the commands are executed, and especially, over what happens with the results of the commands.

For more information about data adapters and how they use data commands, see Introduction to Data Adapters.