How to: Configure 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.
Note
In the previous version of Visual Studio, data adapters were used for communicating between an application and a database. While data adapters are still a main component of .NET Framework Data Providers (ADO.NET), TableAdapters are designer-generated components that simplify the process of moving data between your application and a database. For more information on working with TableAdapters, see TableAdapter Overview.
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.
Note
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.
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.
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.