Create parameterized TableAdapter queries

 

A parameterized query returns data that meets the conditions of a WHERE clause within the query. For example, you can parameterize a customer list to display only customers in a certain city by adding WHERE City = @City to the end of the SQL statement that returns a list of customers.

You create parameterized TableAdapter queries in the Dataset Designer. You can also create them in a Windows application with the Parameterize Data Source command on the Data menu. The Parameterize Data Source command creates controls on your form where you can input the parameter values and run the query.

System_CAPS_noteNote

When constructing a parameterized query, use the parameter notation that's specific to the database you're coding against. For example, Access and OleDb data sources use the question mark '?' to denote parameters, so the WHERE clause would look like this: WHERE City = ?.

System_CAPS_noteNote

The dialog boxes and menu commands you see might differ from those described in Help, depending on your active settings or the edition you're using. To change your settings, go to the  Tools menu and select Import and Export Settings. For more information, see Customizing Development Settings in Visual Studio.

To create a parameterized query in the Dataset Designer

To create a parameterized query while designing a data-bound form

  1. Select a control on your form that is already bound to a dataset. For more information, see Bind Windows Forms controls to data in Visual Studio.

  2. On the Data menu, select Add Query.

  3. Complete the Search Criteria Builder dialog box, adding a WHERE clause with the desired parameters to the SQL statement.

To add a query to an existing data-bound form

  1. Open the form in the Windows Forms Designer.

  2. On the Data menu, select Add Query  or Data Smart Tags.

    System_CAPS_noteNote

    If Add Query is not available on the Data menu, select a control on the form that displays the data source you want to add the parameterization to. For example, if the form displays data in a DataGridView control, select it. If the form displays data in individual controls, select any data-bound control.

  3. In the Select data source table area, select the table that you want to add parameterization to.

  4. Type a name in the New query name box if you are creating a new query.

    -or-

    Select a query in the Existing query name box.

  5. In the Query Text box, type a query that takes parameters.

  6. Select OK.

    A control to input the parameter and a Load button are added to the form in a ToolStrip control.

TableAdapter parameters can be assigned null values when you want to query for records that have no current value. For example, consider the following query that has a ShippedDate parameter in its WHERE clause:

SELECT CustomerID, OrderDate, ShippedDate

FROM Orders

WHERE (ShippedDate = @ShippedDate) OR

(ShippedDate IS NULL)

If this were a query on a TableAdapter, you could query for all orders that have not been shipped with the following code:

ordersTableAdapter.FillByShippedDate(northwindDataSet.Orders, null);

To enable a query to accept null values

  1. In the Dataset Designer, select the TableAdapter query that needs to accept null parameter values.

  2. In the Properties window, select Parameters. Then press the ellipsis () button to open the Parameters Collection Editor.

  3. Select the parameter that allows null values and set the AllowDbNull property to true.

Show: