Creating and configuring TableAdapters
TableAdapters provide communication between your application and a database. They connect to the database, execute queries or stored procedures, and either return a new data table or fill an existing DataTable with the returned data. TableAdapters can also send updated data from your application back to the database.
TableAdapters are created for you when you perform one of the following actions:
Running the Data Source Configuration Wizard, selecting either the Database or Web Service data-source type.
Dragging database objects from Server Explorer onto the Dataset Designer.
You can create a new TableAdapter and configure it with a data source by dragging a Table Adapter from the Toolbox to an empty region in the Dataset Designer surface.
For an introduction to TableAdapters, see Filling datasets by using TableAdapters.
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the Visual Studio IDE.
Run the wizard to create or edit TableAdapters and their associated DataTables. You can configure existing TableAdapters by right-clicking on it in the Dataset Designer.
If you drag a new Table Adapter from the Toolbox when the Dataset Designer is in focus, the wizard will prompt you to specify which data source the adapter should connect to, and what kind of commands the adapter should use to communicate with the database, SQL statements or stored procedures. You won't see this if you are configuring an adapter that is already associated with a data source.
The Create methods to send updates directly to the database option is equivalent to setting the GenerateDBDirectMethods property to true. The option is unavailable when the original SQL statement does not provide enough information or the query is not an updateable query. This situation can occur, for example, in JOIN queries and queries that return a single value (scalar).
If you choose to create a new stored procedure, you can specify to create it in the underlying database depending on the the security settings and permissions for the specific database. If you do not have permission to create new stored procedures in the database, this option cannot be completed.
You can also choose existing stored procedures to execute for the SELECT, INSERT, UPDATE, and DELETE commands of the TableAdapter. The stored procedure assigned to the Update command, for example, is executed when the TableAdapter.Update() method is called.
Map parameters from the selected stored procedure to the corresponding columns in the data table. For example, if your stored procedure accepts a parameter named @CompanyName that it passes to the CompanyName column in the table, set the Source Column of the @CompanyName parameter to CompanyName.
The stored procedure assigned to the SELECT command is executed by calling the method of the TableAdapter that you name in the next step of the wizard. The default is Fill, so the typical code to execute the SELECT procedure is TableAdapter.Fill(tableName). Substitute Fill with the name you assign if you change it from the default of Fill, and replace "TableAdapter" with the actual name of the TableAdapter (for example, CustomersTableAdapter).
The Advanced Options in the wizard enable you to generate INSERT, UPDATE, and DELETE statements based on the SELECT statement defined on the Generate SQL statements page, use optimistic concurrency, and specify whether to refresh the data table after INSERT and UPDATE statements are executed.
Sometimes you might want to change the schema of the adapter's table. To do this, you modify the adapter's primary Fill method. TableAdapters are created with a main Fill method that defines the schema of the associated data table. The main Fill method is based on the query or stored procedure you entered when you originally configured the TableAdapter; it is the first (topmost) method under the data table on the Creating and Editing Typed Datasets.
Any changes that you make to the TableAdapter's main Fill method are reflected in the schema of the associated data table. For example, removing a column from the query in the main Fill method also removes the column from the associated data table. Additionally, removing the column from the main Fill method removes the column from any additional queries for that TableAdapter.
You can use the TableAdapter Query Configuration Wizard to create and edit additional queries for the TableAdapter. These additional queries must conform to the table schema, unless they return a scalar value. The additional queries have a name that you specify (for example, CustomersTableAdapter.FillByCity(NorthwindDataSet.Customers, "Seattle").)
To start the TableAdapter Query Configuration wizard with a new query
Open your dataset in the Dataset Designer.
If you are creating a new query, drag a Query object from the DataSet tab of the Toolbox onto a DataTable, or select Add Query from the TableAdapter's shortcut menu. You can also drag a Query object onto an empty area of the Dataset Designer, which creates a TableAdapter without an associated DataTable. These queries are limited to returning single values (scalar), or executing UPDATE, INSERT, or DELETE commands against the database.
On the Choose Your Data Connection page select or create the connection the query will use.
This page only appears when the designer cannot determine the proper connection to use, or when no connections are available.
On the Choose a Command Type page, select from the following methods of fetching data from the database:
Use SQL statements allows you to type a SQL statement to select the data from your database.
Create new stored procedure — Select this option to have the wizard create a new stored procedure (in the database) based on the specified SELECT statement.
Use existing stored procedures — Select this option to execute an existing stored procedure when running the query.
To start the TableAdapter Query Configuration wizard on an existing query
If you are editing an existing TableAdapter query, right-click the query, and choose Configure from the shortcut menu.
Right-clicking the main query of a TableAdapter reconfigures the TableAdapter and DataTable schema, whereas right-clicking an additional query on a TableAdapter only configures the selected query. The TableAdapter Configuration Wizard reconfigures the TableAdapter definition; the TableAdapter Query Configuration Wizard reconfigures only the selected query.
To add a global query to a TableAdapter
Global queries are SQL queries that return either a single (scalar) value or no value. Typically, global functions perform database operations such as inserts, updates, deletes, and the aggregating of information, such as returning a count of customers in a table or the total charges for all items in a particular order.
You add global queries by dragging a Query from the DataSet tab of the Toolbox onto an empty area of the Dataset Designer.
Provide a query that performs the desired task, for example, SELECT COUNT(*) AS CustomerCount FROM Customers.
Dragging a query directly onto the Dataset Designer creates a method that will only return a scalar (single) value. While the query or stored procedure you select may return more than a single value, the method created by the wizard will only return a single value. For example, the query might return the first column of the first row of the returned data.