Filling datasets by using TableAdapters

Filling datasets by using TableAdapters

 

A TableAdapter is the component that 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, or to issue global commands not related to any specific table.

System_CAPS_noteNote

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

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

Topic

Description

Creating and configuring TableAdapters

How to use the designers to create and configure TableAdapters.

How to: Create parameterized TableAdapter queries

How to enable users to supply arguments to TableAdapter procedures or queries.

How to: Directly access the database with a TableAdapter

How to use the Dbdirect methods of a TableAdapter.

How to: 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 a TableAdapter.

Walkthrough: Reading XML data into a dataset

How to work with XML.

TableAdapters are designer-generated components that connect to a database, execute queries or stored procedures, and fill their DataTable with the returned data. TableAdapters are also used to send updated data from your application back to the database. You can have 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 generated are not generated as nested classes of the DataSet. They are located in a separate namespace specific to each dataset. For example, if you have a dataset named NorthwindDataSet, the TableAdapters associated with the 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);

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 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.

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 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").

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.

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:

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 representing the number of rows affected by the update. For more information, see How to: Update Data by Using a TableAdapter.

TableAdapter.GetData

Returns a new DataTable filled with data.

TableAdapter.Insert

Creates a new row in the data table. For more information, see How to: Insert New Records into a Database.

TableAdapter.ClearBeforeFill

Determines whether a data table is emptied before you call one of the Fill methods.

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.

System_CAPS_noteNote

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. 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 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 (Visual Basic). For more information on nullable types in C#, see Using Nullable Types (C# Programming Guide).

When using 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, you should always verify that the information is valid. A best practice is to always use parameterized queries or stored procedures when possible.

Show:
© 2016 Microsoft