Export (0) Print
Expand All

Using Parameters with a DataAdapter

The DataAdapter 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. The InsertCommand, UpdateCommand, and DeleteCommand properties are used to manage changes at the data source. The SelectCommand property must be set before calling 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 DataSet. For example, if rows have been added, the InsertCommand must be set before calling 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.

For example, when updating 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 commonly 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 SQL statement.

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

In this 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 property of the Parameter object. Following are the parameters for the preceding sample UPDATE statement.

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

The Add method of the Parameters collection takes the name of the parameter, the DataAdapter specific 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 ensures 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 will use the default, Current row value.

The following example shows sample SQL statements to be used as the CommandText for the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter. For the OleDbDataAdapter and OdbcDataAdapter objects, you must use question mark (?) placeholders to identify the parameters. For the SqlDataAdapter object, you must use named parameters.

SqlClient

Dim selectSQL As String = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City"
Dim insertSQL As String = "INSERT INTO Customers (CustomerID, CompanyName) " & _
                          "VALUES (@CustomerID, @CompanyName)"

Dim updateSQL As String = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & _
                          "WHERE CustomerID = @OldCustomerID"

Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = @CustomerID"
[C#]
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City";
string insertSQL = "INSERT INTO Customers (CustomerID, CompanyName) " +
                   "VALUES (@CustomerID, @CompanyName)";

string updateSQL = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
                   "WHERE CustomerID = @OldCustomerID";

string deleteSQL = "DELETE FROM Customers WHERE CustomerID = @CustomerID";

OleDb or Odbc

Dim selectSQL As String = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?"
Dim insertSQL AS String = "INSERT INTO Customers (CustomerID, CompanyName) VALUES (?, ?)"

Dim updateSQL AS String = "UPDATE Customers SET CustomerID = ?, CompanyName = ? WHERE CustomerID = ?"

Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = ?"
[C#]
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? 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 will need to 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 need to include the size, or you can specify the default size.

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

SqlClient

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As SqlDataAdapter = New SqlDataAdapter

Dim selectCMD AS SqlCommand = New SqlCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD

' Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London"

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlDataAdapter custDA = new SqlDataAdapter();         

SqlCommand selectCMD = new SqlCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;

// Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London";

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

OleDb

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
                                                       "Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter 

Dim selectCMD AS OleDbCommand = New OleDbCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD

' Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OleDbType.VarChar, 15).Value = "London"

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
                                                "Integrated Security=SSPI;Initial Catalog=northwind;");
OleDbDataAdapter custDA = new OleDbDataAdapter();

OleDbCommand selectCMD = new OleDbCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;

// Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OleDbType.VarChar, 15).Value = "London";

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

Odbc

Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _
                                                     "Trusted_Connection=yes;Database=northwind")
Dim custDA As OdbcDataAdapter = New OdbcDataAdapter

Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD

' Add Parameters and set values.
selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +
                                              "Trusted_Connection=yes;Database=northwind;");
OdbcDataAdapter custDA = new OdbcDataAdapter();

OdbcCommand selectCMD = new OdbcCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;

//Add Parameters and set values.
selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London";

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
Note   If a parameter name is not supplied for a parameter, the parameter is given an incremental default name of ParameterN, starting with "Parameter1". It is recommended that you avoid the naming convention of "ParameterN" when you supply a parameter name, because the name you supply may conflict with an existing default parameter name in the ParameterCollection. If the supplied name already exists, an exception will be thrown.

Parameter.DbType

The type of a parameter is specific to the .NET Framework data provider. Specifying the type converts the value of the Parameter to the .NET Framework data provider type before passing the value to the data source. If the type is not specified, ADO.NET will infer the .NET Framework data provider type of the Parameter from the .NET Framework type of the Value of the Parameter object.

You may also specify the type of a Parameter in a generic fashion by setting the DbType property of the Parameter object to a particular System.Data.DbType. Additionally, ADO.NET will infer the .NET Framework data provider type of a Parameter from the DbType of the Parameter object.

The .NET Framework data provider type of a Parameter object is inferred from the .NET Framework type of the Value of the Parameter object, or from the DbType of the Parameter object. The following table shows the inferred Parameter type based on the object passed as the Parameter value or the specified DbType.

.NET Framework typeSystem.Data.DbTypeSqlDbTypeOleDbTypeOdbcTypeOracleType
boolBooleanBitBooleanBitByte
byteByteTinyIntUnsignedTinyIntTinyIntByte
byte[]BinaryVarBinary. This implicit conversion will fail if the byte array is greater than the maximum size of a VarBinary, which is 8000 bytes. For byte arrays larger than 8000 bytes, explicitly set the SqlDbType.VarBinaryBinaryRaw
char   Inferring a SqlDbType from char is not supported.CharCharByte
DateTimeDateTimeDateTimeDBTimeStampDateTimeDateTime
DecimalDecimalDecimalDecimalNumericNumber
doubleDoubleFloatDoubleDoubleDouble
floatSingleRealSingleRealFloat
GuidGuidUniqueIdentifierGuidUniqueIdentifierRaw
Int16Int16SmallIntSmallIntSmallIntInt16
Int32Int32IntIntIntInt32
Int64Int64BitIntBigIntBigIntNumber
objectObjectVariantVariantInferring an OdbcType from Object is not supported.Blob
stringStringNVarChar. This implicit conversion will fail if the string is greater than the maximum size of an NVarChar, which is 4000 characters. For strings greater than 4000 characters, explicitly set the SqlDbType.VarWCharNVarCharNVarChar
TimeSpanTimeInferring a SqlDbType from TimeSpan is not supported.DBTimeTimeDateTime
UInt16UInt16Inferring a SqlDbType from UInt16 is not supported.UnsignedSmallIntIntUInt16
UInt32UInt32Inferring a SqlDbType from UInt32 is not supported.UnsignedIntBigIntUInt32
UInt64UInt64Inferring a SqlDbType from UInt64 is not supported.UnsignedBigIntNumericNumber
   AnsiStringVarCharVarCharVarCharVarChar
   AnsiStringFixedLengthCharCharCharChar
CurrencyMoneyCurrencyInferring an OdbcType from Currency is not supported.Number
   DateInferring a SqlType from Date is not supported.DBDateDateDateTime
   SByteInferring a SqlType from SByte is not supported.TinyIntInferring an OdbcType from SByte is not supported.SByte
   StringFixedLengthNCharWCharNCharNChar
   TimeInferring a SqlType from Time is not supported.DBTimeTimeDateTime
   VarNumericInferring a SqlDbType from VarNumeric is not supported.VarNumericInferring an OdbcType from VarNumeric is not supported.Number
Note   The .NET Framework data providers that ship with the .NET Framework version 1.0 do not verify the Precision and Scale of Decimal parameter values, which can result in truncated data being inserted at the data source. If you are using the .NET Framework version 1.0, validate the Precision and Scale of your Decimal values before setting the parameter value.
For the .NET Framework version 1.1 and later, an exception is thrown when a Decimal parameter value is set with an invalid Precision. Scale values that exceed the Decimal parameter scale are still truncated.

Parameter.Direction

The following table shows the values you can use with the ParameterDirection enumeration to set the Direction of the Parameter.

Member nameDescription
InputThe parameter is an input parameter. This is the default.
InputOutputThe parameter is capable of both input and output.
OutputThe parameter is an output parameter.
ReturnValueThe parameter represents a return value.

The following code example shows how to set the Direction of the Parameter.

myParm.Direction = ParameterDirection.Output

Parameter.SourceColumn, Parameter.SourceVersion

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 which DataRow version the DataAdapter uses to retrieve the value.

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

Member nameDescription
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 following code example defines an UPDATE statement 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.

SqlClient

custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")

custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")

Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _
                               SqlDbType.NChar, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");

SqlParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;

OleDb

custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5,"CustomerID")

custDA.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName")

Dim myParm As OleDbParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _
                                 OleDbType.Char, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID");

custDA.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName");

OleDbParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", OleDbType.Char, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;

Odbc

custDA.UpdateCommand.Parameters.Add("@CustomerID", OdbcType.Char, 5, "CustomerID")

custDA.UpdateCommand.Parameters.Add("@CompanyName", OdbcType.VarChar, 40, "CompanyName")

Dim myParm As OdbcParameter = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", _
                              OdbcType.Char, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", OdbcType.Char, 5, "CustomerID");

custDA.UpdateCommand.Parameters.Add("@CompanyName", OdbcType.VarChar, 40, "CompanyName");

OdbcParameter myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", OdbcType.Char, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;

UpdatedRowSource

You can control how the values returned from the data source are mapped back to the DataSet using the UpdatedRowSource property of the Command object. By setting the UpdatedRowSource property to one of the UpdateRowSource enumeration values, you can control whether parameters returned by the DataAdapter command will be ignored or applied to the changed row in the DataSet. You can also specify whether the first returned row (if it exists) is applied to the changed row in the DataSet.

The following table describes the different values of the UpdateRowSource enumeration and how they affect the behavior of a command used with a DataAdapter.

UpdateRowSourceDescription
BothBoth the output parameters and the first row of a returned resultset may be mapped to the changed row in the DataSet.
FirstReturnedRecordOnly the data in the first row of a returned resultset may be mapped to the changed row in the DataSet.
NoneAny output parameters or rows of a returned resultset are ignored.
OutputParametersOnly output parameters may be mapped to the changed row in the DataSet.

See Also

Using .NET Framework Data Providers to Access Data | Using Stored Procedures with a Command | DataRowVersion Enumeration | OleDbDataAdapter Class | OdbcDataAdapter Class | ParameterDirection Enumeration | SqlDataAdapter Class

Show:
© 2015 Microsoft