Parameters in Data-Adapter Commands

A data adapter's commands — those defined in the CommandText property of the SelectCommand,InsertCommand, UpdateCommand, andDeleteCommand objects — often involve parameters. At run time, parameters are used to pass values to the SQL statements or stored procedures represented by the commands.

Parameters are used in two contexts:

• Selection parameters - in production applications, you frequently fetch only a subset of the data in a database. To do so, you use an SQL statement or stored procedure that includes a WHERE clause with a parameter for selection criteria that you get at run time. In addition, when you update or delete records, you use a WHERE clause that pinpoints the record or records to be changed. The values used in the WHERE clause are usually derived at run time.

• Update parameters - when you update an existing record or insert a new one, the values for the columns in the changed or new record are established at run time. In addition, values used during optimistic concurrency checking are established using parameters.

  Note

  For Oracle, when using named parameters in an SQL statement or stored procedure, you must precede the parameter name with a colon (:). However, when referring to a named parameter elsewhere in your code (for example, when calling Add), do not precede the named parameter with a colon (:). The data provider supplies the colon automatically. For more information, see OracleParameter Class.

Selection Parameters

When selecting records to fill a dataset, you often include one or more parameters in the WHERE clause so you can specify at run time which records to fetch. For example, users might search a book database for a specific title keyword that they type into a Web page. To allow that, you might specify an SQL statement like the following as the CommandText property of a SelectCommand. Parameters are indicated either with a placeholder (a question mark) or with a named parameter variable. Parameters for queries involving OleDbCommand and OdbcCommand objects use question marks; queries that use SqlCommand objects use named parameters that begin with an @ symbol, whereas OracleCommand objects use named parameters that begin with a colon (:).

A query that uses placeholders might look like the following:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE ?)

A query that uses SqlCommand named parameters might look like the following:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE @title)

A query that uses OracleCommand named parameters might look like the following:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE :title)

In your application, you prompt the user for a title keyword. You then set the parameter value and run the command.

In Visual Studio you can build SQL statements with parameters using the Query Builder. If you drag elements from Server Explorer, Visual Studio can configure parameters in some cases but not all, and you will need to complete the configuration manually.

Update Parameters

Whether or not an adapter's SelectCommand object contains a parameterized command, the commands for the UpdateCommand, InsertCommand, and DeleteCommand properties always do.

The commands for the UpdateCommand and InsertCommand properties need parameters for every column in the database to be updated. In addition, the UpdateCommand and DeleteCommand statements require a parameterized WHERE clause that identifies the record to be updated, similar to the way the SelectCommand object is often configured.

Imagine an application where users can buy books. As users shop, they maintain a shopping cart, which is implemented as a data table. In the ShoppingCart table, users maintain a record for each book they want to buy, with the book ID and customer ID together acting as the key to the shopping cart record.

When users add a book to their shopping cart, the application might invoke an SQL INSERT statement. In the adapter, the statement's syntax might look like the following:

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (?, ?, ?)

The three question marks represent parameter placeholders that will be filled in at run time with values for the customer ID, book ID, and quantity. If you are using named parameters, the same query might look like this:

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (@bookid, @custid, @quantity)

If the user decides to change something about an item in the shopping cart — for example, by changing the quantity — the application might invoke an SQL UPDATE statement. The syntax of the statement might be as follows:

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Or if you are using named parameters, it might be as follows:

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

In this statement, the parameters in the SET clause are filled in with updated values for the changed record. The parameters in the WHERE clause identify which record to update and are filled in with the original values from the record.

A user might also remove an item from the shopping cart. In that case, the application might invoke an SQL DELETE statement with syntax such as the following, if you are using parameter placeholders:

DELETE FROM ShoppingCart
WHERE (BookId = ? AND CustId = ?)

Or the following if you are using named parameters:

DELETE FROM ShoppingCart
WHERE (BookId = @bookid AND CustId = @custid)

The Parameters Collection and Parameter Objects

To allow you to pass parameter values at run time, each of the four command objects for a data adapter supports a Parameters property. The property contains a collection of individual parameter objects that correspond one-to-one with the placeholders in a statement.

The following table shows the corresponding parameter collection for each data adapter:

By using the parameters collection, you save yourself the trouble of having to manually construct a SQL command as a string with run-time values; in addition, you get the benefit of type checking in your parameters.

If you use the Data Adapter Configuration Wizard to configure the adapter, the parameters collection is set up and configured automatically for all four adapter commands. If you drag elements from Server Explorer onto the form or component, Visual Studio can perform the following configurations:

• If you drag a table or some columns onto the designer, Visual Studio generates a SelectCommand object (specifically, an SQL SELECT statement) with no parameters, and parameterized UpdateCommand, InsertCommand, and DeleteCommand objects. If you want the SelectCommand object statement to have parameters, you must configure them manually.

• If you drag a stored procedure onto the designer, Visual Studio generates a SelectCommand object, with parameters as required by the stored procedure. However, if you need them, you must configure the UpdateCommand, InsertCommand, and DeleteCommand objects yourself, along with their parameters.

Generally speaking, if you want to create parameterized queries for the adapter, you should use the Data Adapter Configuration Wizard. However, should you need to, you can configure parameters manually using the Properties window.

Structure of the Parameters Collection

The items in a command's parameters collection correspond one-to-one to parameters required for the corresponding command object. If the command object is an SQL statement, the items in the collection correspond to the placeholders (question marks) in the statement. The following UPDATE statement requires a collection of five parameter items:

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Here is the same statement with named parameters:

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

If the command object references a stored procedure, the number of parameter items in the collection is determined by the procedure itself. The parameters might not correspond exactly to the placeholders in an SQL statement.

In stored procedures, parameters can also be named. In that case, the position of a parameter in the collection is not important. Instead, each parameter item in the collection has a ParameterName property that is used to match it to the corresponding parameter in the stored procedure.

If you are configuring the parameters collection manually, you must understand exactly which parameters the stored procedure requires. Many stored procedures return a value; if so, the value is passed back to your application in the parameters collection, so you must allow for that. In addition, some stored procedures include multiple SQL statements, and you must be sure that the parameters collection reflects all the values passed to all statements in the procedure.

If parameters are not named (as in stored procedures), the items in the collection map positionally to the parameters required by the command. If the command is a stored procedure and it returns a value, the first item in the collection (item zero) is reserved for this return value.

You can therefore reference individual parameter objects by index position in the collection. However, parameter objects also support a ParameterName property that provides a way to reference parameters independently of their order. For example, the following two statements might be equivalent (assuming that the second parameter in the collection is named Title_Keyword):

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters(1).Value = titleKeyword
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword

// Encloses the keyword in SQL wildcard characters.
string titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters[1].Value = titleKeyword;
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;

Using a parameter name is generally a much better programming practice than referencing parameters by index value, because it reduces the need for maintenance if the number of parameters changes and frees you from having to remember whether a stored procedure returns a value. There is slight additional overhead in referencing a parameter by name rather than index value, but this can be offset by ease of programming and by application maintainability.

Establishing Parameter Values

There are two ways that you can establish the value of a parameter:

• By setting the parameter's Value property explicitly.

• By mapping parameters to columns in a dataset table, so that the values can be extracted from data rows when needed.

You set the parameter value explicitly when you are filling a dataset or calling a command — that is, for selection parameters. For example, in the example above of searching for books, the application might have a text box where users enter a title keyword. You would then explicitly set the value of the parameter to the text of the text box before calling the adapter's Fill method. Code to do this might look like the following, which establishes the contents of a text box as a parameter before filling a dataset.

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword
OleDbDataAdapter1.Fill(dsAuthors1)

// Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;
this.OleDbDataAdapter1.Fill(dsAuthors1);

Mapped parameter values are used during updates. When you call an adapter's Update method, the method walks through the records in a dataset table, individually making the appropriate update (update, insert, delete) for each record. In that case, the parameter values are already available as columns in the dataset records. For example, when the update process gets to a new record in the dataset table — a record for which it must call an INSERT statement in the database — the values for the INSERT statement's VALUE clause can be read directly out of the record.

These are typical scenarios, but not the only ones. Stored procedures sometimes return data using out parameters or via the procedure's return value. If so, the returned values should be mapped to columns in a dataset table.

It is possible to set update parameters explicitly as well. The adapter supports a RowUpdating event that is called each time a row is being updated. You can create a handler for this event and set parameter values there. This gives you very precise control over parameter values, and allows you to perform processes such as creating parameter values dynamically before they are written to a database record.

See Also

Concepts

Populating a DataSet from a DataAdapter (ADO.NET)

What's New in Data

Creating Data Applications by Using Visual Studio

Other Resources

DataAdapters and DataReaders (ADO.NET)

Creating Data Adapters

Data Walkthroughs

ADO.NET