Fill datasets by using TableAdapters

A TableAdapter component fills a dataset with data from the database, based on one or more queries or stored procedures that you specify. TableAdapters can also perform adds, updates, and deletes on the database to persist changes that you make to the dataset. You can also issue global commands that are unrelated to any specific table.

For detailed information about TableAdapter operations, you can skip directly to one of these topics:

TableAdapters overview

TableAdapters are designer-generated components that connect to a database, run queries or stored procedures, and fill their DataTable with the returned data. TableAdapters also send updated data from your application back to the database. You can run as many queries as you want on a TableAdapter as long as they return data that conforms to the schema of the table with which the TableAdapter is associated. The following diagram shows how TableAdapters interact with databases and other objects in memory:

While TableAdapters are designed with the Dataset Designer, the TableAdapter classes are not generated as nested classes of DataSet. They are located in separate namespaces that are specific to each dataset. For example, if you have a dataset named NorthwindDataSet, the TableAdapters that are associated with DataTables in the NorthwindDataSet would be in the NorthwindDataSetTableAdapters namespace. To access a particular TableAdapter programmatically, you must declare a new instance of the TableAdapter. For example:

NorthwindDataSet northwindDataSet = new NorthwindDataSet();

NorthwindDataSetTableAdapters.CustomersTableAdapter customersTableAdapter = 
    new NorthwindDataSetTableAdapters.CustomersTableAdapter();

customersTableAdapter.Fill(northwindDataSet.Customers);

Dim northwindDataSet As New NorthwindDataSet()
Dim customersTableAdapter As New NorthwindDataSetTableAdapters.CustomersTableAdapter()

customersTableAdapter.Fill(northwindDataSet.Customers)

Associated DataTable schema

When you create a TableAdapter, you use the initial query or stored procedure to define the schema of the TableAdapter's associated DataTable. You run this initial query or stored procedure by calling the TableAdapter's Fill method (which fills the TableAdapter's associated DataTable). Any changes that are 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 also removes the column from the associated data table. If any additional queries on the TableAdapter use SQL statements that return columns that are not in the main query, the designer attempts to synchronize the column changes between the main query and the additional queries. For more information, see How to: Edit TableAdapters.

TableAdapter update commands

The update functionality of a TableAdapter is dependent on how much information is available in the main query 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.

TableAdapter queries

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 capability enable a TableAdapter to load different results based on differing criteria.

For example, if your application contains a table with customer names, you can create a query that fills the table with every customer name that begins with a certain letter, and another that fills the table with all customers that are 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 as follows: SELECT * FROM Customers WHERE State = @State. You run the query by calling the FillByState method and passing in the parameter value like this: CustomerTableAdapter.FillByState("WA").

In addition to adding 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, a query that returns a count of customers (SELECT Count(*) From Customers) is valid for a CustomersTableAdapter, even though the data that's returned doesn't conform to the table's schema.

ClearBeforeFill property

By default, every time you run a query to fill a TableAdapter's data table, the existing 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 that's 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 you want to persist them. So remember to save any changes to the data in the table before running another query that fills the table. For more information, see Update data by using a TableAdapter.

TableAdapter inheritance

TableAdapters extend the functionality of standard data adapters by encapsulating a configured DataAdapter class?qualifyHint=False&autoUpgrade=True. By default, the TableAdapter inherits from the Component class and can't be cast to the DataAdapter class. Casting a TableAdapter to the DataAdapter class results in a InvalidCastException error?qualifyHint=False&autoUpgrade=True. 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.

TableAdapter methods and properties

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

Following are the commonly used methods and properties of TableAdapters:

TableAdapter update method

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 that are associated with the TableAdapter.Update method. Calling a TableAdapter's Update method runs the statements that were created when the TableAdapter was originally configured, not one of the additional queries that was 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 runs 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 runs the appropriate command (in the UpdateCommand, InsertCommand, and DeleteCommand properties) for each changed record in the data table.

TableAdapter GenerateDbDirectMethods

In addition to InsertCommand, UpdateCommand, and DeleteCommand, TableAdapters are created with methods that can be run directly against the database. These methods (TableAdapter.Insert, TableAdapter.Update, and TableAdapter.Delete) can be called directly to manipulate data in the database. This means you can call these individual methods from your code instead of calling TableAdapter.Update to handle the inserts, updates, and deletes that are pending for the associated data table.

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

TableAdapter support for nullable types

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

Security

When you use data commands with a CommandType property set to CommandType, carefully check information that is sent from a client before passing it to your database. Malicious users might try to send (inject) modified or additional SQL statements in an effort to gain unauthorized access or damage the database. Before you transfer user input to a database, always verify that the information is valid. A best practice is to always use parameterized queries or stored procedures when possible.

See Also

Dataset tools in Visual Studio