Configuring Parameters for Data Adapters
Under most circumstances, a data adapter's Command objects (SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand) reference SQL statements or stored procedures that require parameters. If you have used the Data Adapter Configuration Wizard to create the adapter, then the parameters for these commands are configured for you automatically. However, you might want to configure parameters manually or make small changes to an existing parameters collection.
Tip To configure parameters after making changes to the SQL statement for a command, you can choose the Regenerate parameters collection for this command option in the Query Builder.
To configure parameters for a data adapter
- Create a data adapter. For details, see Creating Data Adapters.
- In the form or component designer, select the data adapter and then open the Properties window.
- Expand the command object for which you want to configure parameters (for example, the UpdateCommand) and then in the Parameters property, click the ellipsis button () to open the Parameter Collection Editor window.
- To create a new parameter object, click Add.
- Place the new parameter at the correct index location in the collection for that parameter by clicking the arrows under Sort to move it.
- To set parameter properties, select the parameter in the Members list, and then use the property grid on the right-hand side. The following table lists properties that you typically set.
Property Description SourceColumn The name of a column in a dataset table from which the parameter value will be read. This property is used with parameters that fill values in an UPDATE, INSERT, or DELETE statement (or equivalent stored procedures). SourceVersion If the parameter value is being derived via the SourceColumn property, SourceVersion specifies which version of a dataset record should be used as the parameter value source:
- Original - the value of the column as it was read from the database. You use this option when you need the value of a column before it has changed. An example is when a primary key is changing but you need the old key to find the record in the database.
- Current - the value of the column after a record has been updated. This is usually the value to be written to the database.
- Default - the value to which a column is set if no value is explicitly supplied.
For details about different versions of records in the dataset, see Dataset Updates in Visual Studio .NET.
Value An explicit value to set for the parameter. This property is often set at run time rather than statically at design time.
The Value property takes precedence over the SourceColumn property if both are set.
NamedParameter An Boolean value indicating whether the parameter corresponds to a variable (typically with the format @parametername) or to a placeholder (typically a question mark). If you are working with a SqlConnection object and are using named parameters, set this to true. DBType, Precision, Scale, Size Information about the native data type (in the data store) of the parameter value. The parameter value is converted into or out of the type you specify. Direction A specification for whether the parameter value is being passed to the command or back:
- Input - the value is being passed to the command. This is the default.
- Output - the value is being passed back from a stored procedure. (Not applicable for SQL statements.)
- InputOutput - the value is passed to a stored procedure and passed back, usually with a modified value.
- ReturnValue - the value is an explicit return value. By default, the returned value is the first item (index value zero) in the parameters collection.
ParameterName A name by which you can refer to the parameter in the collection, instead of referencing it by its index value. Although not required, a parameter name is recommended for ease of maintenance.
- Click OK to close the Parameter Collection Editor window.
- Repeat Steps 3 through 7 to configure parameters for other command objects.