DataAdapter Parameters


The DbDataAdapter has four properties that are used to retrieve data from and update data to the data source: the SelectCommand property returns data from the data source; and the InsertCommand , UpdateCommand, and DeleteCommand properties are used to manage changes at the data source. The SelectCommand property must be set before you call the Fill method of the DataAdapter. The InsertCommand, UpdateCommand, or DeleteCommand properties must be set before the Update method of the DataAdapter is called, depending on what changes were made to the data in the DataTable. For example, if rows have been added, the InsertCommand must be set before you call Update. When Update is processing an inserted, updated, or deleted row, the DataAdapter uses the respective Command property to process the action. Current information about the modified row is passed to the Command object through the Parameters collection.

When you update a row at the data source, you call the UPDATE statement, which uses a unique identifier to identify the row in the table be updated. The unique identifier is typically the value of a primary key field. The UPDATE statement uses parameters that contain both the unique identifier and the columns and values to be updated, as shown in the following Transact-SQL statement.

UPDATE Customers SET CompanyName = @CompanyName   
  WHERE CustomerID = @CustomerID  

System_CAPS_ICON_note.jpg Note

The syntax for parameter placeholders depends on the data source. This example shows placeholders for a SQL Server data source. Use question mark (?) placeholders for System.Data.OleDb and System.Data.Odbc parameters.

In this Visual Basic example, the CompanyName field is updated with the value of the @CompanyName parameter for the row where CustomerID equals the value of the @CustomerID```parameter. The parameters retrieve information from the modified row using the [SourceColumn](assetId:///P:System.Data.SqlClient.SqlParameter.SourceColumn?qualifyHint=False&autoUpgrade=True) property of the [SqlParameter](assetId:///T:System.Data.SqlClient.SqlParameter?qualifyHint=False&autoUpgrade=True) object. The following are the parameters for the previous sample UPDATE statement. The code assumes that the variableadapter` represents a valid SqlDataAdapter object.

adapter.Parameters.Add( _  
  "@CompanyName", SqlDbType.NChar, 15, "CompanyName")  
Dim parameter As SqlParameter = _  
  adapter.UpdateCommand.Parameters.Add("@CustomerID", _  
  SqlDbType.NChar, 5, "CustomerID")  
parameter.SourceVersion = DataRowVersion.Original  

The Add method of the Parameters collection takes the name of the parameter, the data type, the size (if applicable to the type), and the name of the SourceColumn from the DataTable. Notice that the SourceVersion of the @CustomerID parameter is set to Original. This guarantees that the existing row in the data source is updated if the value of the identifying column or columns has been changed in the modified DataRow. In that case, the Original row value would match the current value at the data source, and the Current row value would contain the updated value. The SourceVersion for the @CompanyName parameter is not set and uses the default, Current row value.

System_CAPS_ICON_note.jpg Note

For both the Fill operations of the DataAdapter and the Get methods of the DataReader, the .NET Framework type is inferred from the type returned from the .NET Framework data provider. The inferred .NET Framework types and accessor methods for Microsoft SQL Server, OLE DB, and ODBC data types are described in Data Type Mappings in ADO.NET.

The SourceColumn and SourceVersion may be passed as arguments to the Parameter constructor, or set as properties of an existing Parameter. The SourceColumn is the name of the DataColumn from the DataRow where the value of the Parameter will be retrieved. The SourceVersion specifies the DataRow version that the DataAdapter uses to retrieve the value.

The following table shows the DataRowVersion enumeration values available for use with SourceVersion.

DataRowVersion EnumerationDescription
CurrentThe parameter uses the current value of the column. This is the default.
DefaultThe parameter uses the DefaultValue of the column.
OriginalThe parameter uses the original value of the column.
ProposedThe parameter uses a proposed value.

The SqlClient code example in the next section defines a parameter for an UpdateCommand in which the CustomerID column is used as a SourceColumn for two parameters: @CustomerID (SET CustomerID = @CustomerID), and @OldCustomerID (WHERE CustomerID = @OldCustomerID). The @CustomerID parameter is used to update the CustomerID column to the current value in the DataRow. As a result, the CustomerID SourceColumn with a SourceVersion of Current is used. The @OldCustomerID parameter is used to identify the current row in the data source. Because the matching column value is found in the Original version of the row, the same SourceColumn (CustomerID) with a SourceVersion of Original is used.

The following example demonstrates how to create a SqlDataAdapter and set the MissingSchemaAction to AddWithKey in order to retrieve additional schema information from the database. The SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties set and their corresponding SqlParameter objects added to the Parameters collection. The method returns a SqlDataAdapter object.

[!CODE [Classic WebData SqlDataAdapter.SqlDataAdapter Example#1](../CodeSnippet/VS_Snippets_ADO.NET/Classic WebData SqlDataAdapter.SqlDataAdapter Example#1)]

For the OleDbDataAdapter and OdbcDataAdapter objects, you must use question mark (?) placeholders to identify the parameters.

string selectSQL =   
  "SELECT CustomerID, CompanyName FROM Customers " +  
  "WHERE CountryRegion = ? AND City = ?";  
string insertSQL =   
  "INSERT INTO Customers (CustomerID, CompanyName) " +  
  "VALUES (?, ?)";  
string updateSQL =   
  "UPDATE Customers SET CustomerID = ?, CompanyName = ? " +  
  "WHERE CustomerID = ? ";  
string deleteSQL = "DELETE FROM Customers WHERE CustomerID = ?";  

The parameterized query statements define which input and output parameters must be created. To create a parameter, use the Parameters.Add method or the Parameter constructor to specify the column name, data type, and size. For intrinsic data types, such as Integer, you do not have to include the size, or you can specify the default size.

The following code example creates the parameters for a SQL statement and then fills a DataSet.

// Assumes that connection is a valid OleDbConnection object.  
OleDbDataAdapter adapter = new OleDbDataAdapter();  
OleDbCommand selectCMD = new OleDbCommand(selectSQL, connection);  
adapter.SelectCommand = selectCMD;  
// Add parameters and set values.  
  "@CountryRegion", OleDbType.VarChar, 15).Value = "UK";  
  "@City", OleDbType.VarChar, 15).Value = "London";  
DataSet customers = new DataSet();  
adapter.Fill(customers, "Customers");  

// Assumes that connection is a valid OdbcConnection object.  
OdbcDataAdapter adapter = new OdbcDataAdapter();  
OdbcCommand selectCMD = new OdbcCommand(selectSQL, connection);  
adapter.SelectCommand = selectCMD;  
//Add Parameters and set values.  
selectCMD.Parameters.Add("@CountryRegion", OdbcType.VarChar, 15).Value = "UK";  
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London";  
DataSet customers = new DataSet();  
adapter.Fill(customers, "Customers");  

System_CAPS_ICON_note.jpg Note

If a parameter name is not supplied for a parameter, the parameter is given an incremental default name of ParameterN , starting with "Parameter1". We recommend that you avoid the ParameterN naming convention when you supply a parameter name, because the name that you supply might conflict with an existing default parameter name in the ParameterCollection. If the supplied name already exists, an exception is thrown.

DataAdapters and DataReaders
Commands and Parameters
Updating Data Sources with DataAdapters
Modifying Data with Stored Procedures
Data Type Mappings in ADO.NET
ADO.NET Managed Providers and DataSet Developer Center