Edit

Fill datasets by using TableAdapters in .NET Framework applications

  • Article

Note

Datasets and related classes are legacy .NET Framework technologies from the early 2000s that enable applications to work with data in memory while the applications are disconnected from the database. They are especially useful for applications that enable users to modify data and persist the changes back to the database. Although datasets have proven to be a very successful technology, we recommend that new .NET applications use Entity Framework Core. Entity Framework provides a more natural way to work with tabular data as object models, and it has a simpler programming interface.

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.

Note

TableAdapters are generated by Visual Studio designers. If you are creating datasets programmatically, then use DataAdapter, which is a .NET class.

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

Topic Description
Create and configure TableAdapters How to use the designers to create and configure TableAdapters
Create parameterized TableAdapter queries How to enable users to supply arguments to TableAdapter procedures or queries
Directly access the database with a TableAdapter How to use the Dbdirect methods of TableAdapters
Turn off constraints while filling a dataset How to work with foreign-key constraints when updating data
How to extend the functionality of a TableAdapter How to add custom code to TableAdapters
Read XML data into a dataset How to work with XML

TableAdapter 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:

Data flow in a client application

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);

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.

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 (using a JOIN), 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

TableAdapter with multiple 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 enables 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. 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 an InvalidCastException error. To change the base class of a TableAdapter, you can specify 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 a .NET type. 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:

Member Description
TableAdapter.Fill Populates the TableAdapter's associated data table with the results of the TableAdapter's SELECT command.
TableAdapter.Update Sends changes back to the database and returns an integer that represents the number of rows affected by the update. For more information, see Update data by using a TableAdapter.
TableAdapter.GetData Returns a new DataTable that's filled with data.
TableAdapter.Insert Creates a new row in the data table. For more information, see Insert new records into a database.
TableAdapter.ClearBeforeFill Determines whether a data table is emptied before you call one of the Fill methods.

TableAdapter update method

TableAdapters use data commands to read to and write from the database. Use the TableAdapter's initial Fill (main) query 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 you added with the TableAdapter Query Configuration Wizard.

When you use a TableAdapter, it effectively performs the same operations with the commands that you would typically 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.

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's possible the designer won't be able to generate InsertCommand, UpdateCommand, and DeleteCommand. If these commands aren't generated, you might receive an error when running the TableAdapter.Update method.

TableAdapter GenerateDbDirectMethods

In addition to InsertCommand, UpdateCommand, and DeleteCommand, TableAdapters are created with methods that you can run directly against the database. You can call these methods (TableAdapter.Insert, TableAdapter.Update, and TableAdapter.Delete) 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 Use nullable types.

TableAdapterManager reference

By default, a TableAdapterManager class generates when you create a dataset that contains related tables. To prevent the class from being generated, change the value of the Hierarchical Update property of the dataset to false. When you drag a table that has a relation onto the design surface of a Windows Form or WPF page, Visual Studio declares a member variable of the class. If you don't use databinding, you have to manually declare the variable.

The TableAdapterManager class is not a .NET type. Therefore, you cannot look it up in the documentation. It's created at design time as part of the dataset creation process.

The following are the frequently used methods and properties of the TableAdapterManager class:

Member Description
UpdateAll method Saves all data from all data tables.
BackUpDataSetBeforeUpdate property Determines whether to create a backup copy of the dataset before executing the TableAdapterManager.UpdateAll method.Boolean.
tableName TableAdapter property Represents a TableAdapter. The generated TableAdapterManager contains a property for each TableAdapter it manages. For example, a dataset with a Customers and Orders table generates with a TableAdapterManager that contains CustomersTableAdapter and OrdersTableAdapter properties.
UpdateOrder property Controls the order of the individual insert, update, and delete commands. Set this to one of the values in the TableAdapterManager.UpdateOrderOption enumeration.

By default, the UpdateOrder is set to InsertUpdateDelete. This means that inserts, then updates, and then deletes are performed for all tables in the dataset.

Security

When you use data commands with a CommandType property set to Text, 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.

Related content