When creating a TableAdapter, the initial query or stored procedure is used to define the schema of the TableAdapter's associated DataTable. You execute this initial query or stored procedure by calling the TableAdapter's main Fill method (which fills the TableAdapter's associated DataTable). Any changes made to the TableAdapter's main query are reflected in the schema of the associated data table. For example, removing a column from the main query removes the column from the associated data table. If any additional queries on the TableAdapter use SQL statements returning columns that are not in the main query, then the designer will attempt to synchronize the column changes between the main query and any additional queries. For more information, see How to: Edit TableAdapters.

The update functionality of a TableAdapter is dependent on how much information is available based on the main query provided in the TableAdapter Wizard. For example, TableAdapters that are configured to fetch values from multiple tables (JOINs), scalar values, views, or the results of aggregate functions are not initially created with the ability to send updates back to the underlying database. However, you can configure the INSERT, UPDATE and DELETE commands manually in the Properties window.

Unlike standard data adapters, TableAdapters can contain multiple queries to fill their associated data tables. You can define as many queries for a TableAdapter as your application requires, as long as each query returns data that conforms to the same schema as its associated data table. This enables loading of data that satisfies differing criteria. For example, if your application contains a table of customers, you can create a query that fills the table with every customer whose name begins with a certain letter, and another query that fills the table with all customers located in the same state. To fill a Customers table with customers in a given state you can create a FillByState query that takes a parameter for the state value: SELECT * FROM Customers WHERE State = @State. You execute the query by calling the FillByState method and passing in the parameter value like this: CustomerTableAdapter.FillByState("WA"). For more information, see How to: Create TableAdapter Queries.

In addition to queries that return data of the same schema as the TableAdapter's data table, you can add queries that return scalar (single) values. For example, creating a query that returns a count of customers (SELECT Count(*) From Customers) is valid for a CustomersTableAdapter even though the data returned does not conform to the table's schema.

The TableAdapter adds a property not available on the base DataAdapter class. By default, every time you execute a query to fill a TableAdapter's data table, the data is cleared and only the results of the query are loaded into the table. Set the TableAdapter's ClearBeforeFill property to false if you want to add or merge the data returned from a query to the existing data in a data table. Regardless of whether you clear the data, you need to explicitly send updates back to the database, if desired. So remember to save any changes made to the data in the table before executing another query that fills the table. For more information, see How to: Update Data by Using a TableAdapter.

TableAdapters extend the functionality of standard data adapters by encapsulating a configured DataAdapter. By default, the TableAdapter inherits from Component and cannot be cast to the DataAdapter class. Casting a TableAdapter to a DataAdapter results in an InvalidCastException. To change the base class of a TableAdapter, you can type a class that derives from Component in the Base Class property of the TableAdapter in the Dataset Designer.

The TableAdapter class is not part of the .NET Framework, and as such you cannot look it up in the documentation or the Object Browser. It is created at design time when you use one of the wizards mentioned above. The name assigned to a TableAdapter when you create it is based on the name of the table you are working with. For example, when creating a TableAdapter based on a table in a database named Orders, the TableAdapter would be named OrdersTableAdapter. The class name of the TableAdapter can be changed using the Name property in the Dataset Designer.

The following are the commonly used methods and properties of TableAdapters:

TableAdapters use data commands to read to and write from the database. The TableAdapter's initial Fill (main) query is used as the basis for creating the schema of the associated data table, as well as the InsertCommand, UpdateCommand, and DeleteCommand commands associated with the TableAdapter.Update method. This means that calling a TableAdapter's Update method executes the statements created when the TableAdapter was originally configured, and not one of the additional queries added with the TableAdapter Query Configuration Wizard.

When you use a TableAdapter, it effectively performs the same operations with the commands that you typically would perform. For example, when you call the adapter's Fill method, the adapter executes the data command in its SelectCommand property and uses a data reader (for example, SqlDataReader) to load the result set into the data table. Similarly, when you call the adapter's Update method, it executes the appropriate command (in the UpdateCommand, InsertCommand, and DeleteCommand properties) for each changed record in the data table.

Note:

If there is enough information in the main query, the InsertCommand, UpdateCommand, and DeleteCommand commands are created by default when the TableAdapter is generated. If the TableAdapter's main query is more than a single table SELECT statement, it is possible the designer will not be able to generate the InsertCommand, UpdateCommand, and DeleteCommand. If these commands are not generated, you may receive an error when executing the TableAdapter.Update method.

In addition to the InsertCommand, UpdateCommand, and DeleteCommand, TableAdapters are created with methods that can be executed directly against the database. These methods (TableAdapter.Insert, TableAdapter.Update, and TableAdapter.Delete) can be called directly to manipulate data in the database.

If you do not want to create these direct methods, set the TableAdapter's GenerateDbDirectMethods property to false (in the Properties window). Additional queries added to the TableAdapter are standalone queries — they do not generate these methods.

The TableAdapters support nullable types Nullable(Of T) and T?. For more information on nullable types in Visual Basic, see Nullable Value Types. For more information on nullable types in C#, see Using Nullable Types (C# Programming Guide).

Tasks

Concepts

Other Resources