TableAdapters in Visual Studio 2005

 

Jay Hickerson
Microsoft Corporation

September 2005

Summary: Visual Studio 2005 expands typed data access with TableAdapter, a new object that greatly simplifies interacting with data objects on the client machine and sending updates to a database. (19 printed pages)

Contents

Introduction
Overview
Generating TableAdapters
The TableAdapter Code
Creating TableAdapter Instances
DataTable Columns
Null Support
Database Direct Methods
Fill and GetData Methods
Multiple Query Support
The Queries TableAdapter
More on Scalar Queries
Updating the Database
DataObjectAttribute
Connection Strings
Customizability and Extensibility
A Quick Reference of TableAdapter Properties
Conclusion

Introduction

In Visual Studio 2003, developers had a number of data-access components. However, using these components together was often tedious and didn't promote good object reuse. In Visual Studio 2005, we have expanded typed data access with a new object called a TableAdapter. With TableAdapters, the experience of interacting with data objects on the client machine and sending updates to a database is greatly simplified. TableAdapters encapsulate the objects necessary to communicate with a database and provide methods to access the data in a type-safe way.

Overview

TableAdapter objects are not found in the .NET Framework. Unlike typed datasets, which inherit from the System.Data.DataSet class, TableAdapters are entirely generated by Visual Studio using the data model you create with the Data Source Configuration Wizard, or in the Dataset Designer.

TableAdapters abstract the database type away from application code. For example, let's say you have developed your application using an Oracle database. Later you decide to port that database to Microsoft SQL Server. In Visual Studio 2002 and Visual Studio 2003, you would have had to change the object types you were using to access the database. OracleDataAdapters might have become SqlDataAdapters and OracleConnection objects would have become SqlConnection objects. Any commands you had created would also have to be changed. With TableAdapters, the "heavy lifting" is now handled for you. Changing your connection string and regenerating the dataset will regenerate the TableAdapter with the same name and interface. Internally the TableAdapter code will be regenerated to use the appropriate SQL objects instead of Oracle objects.

The abstraction is created in two parts. The first part is the common language runtime (CLR) to database type conversion that happens inside the TableAdapter. Since the .NET programming languages do not natively contain data types for database access, a mapping is created between the type of the database column and a CLR type. By creating this mapping, a TableAdapter can expose methods and properties associated with columns in the database that can be accessed directly by your code. For example, an Oracle database might contain a column of type varchar2. The TableAdapter will map this type to string in the properties and methods exposed. If you later decide to use a database created with Microsoft SQL Server instead, the same field in the SQL Server database will be of type nvarchar but the TableAdapter interface will still use type string for the column.

The second part of the abstraction is encapsulation of the database objects found in earlier versions of Visual Studio. Encapsulated within the TableAdapter are a DataAdapter, a connection object, and an array of command objects. The command objects are exposed publicly through method calls—for each command object, there is a public method on the TableAdapter. These command objects are exposed as TableAdapter queries in the dataset schema.

One caveat to all of this is that you may have to modify your SQL statements to match the syntax of the new database. The most common place you will encounter this is with parameter names. In the example above, any parameters used in the original Oracle statements will have to be changed from :ParamName to @ParamName so that they will be recognized by SQL Server.

Generating TableAdapters

The code for a TableAdapter is generated after the TableAdapter is added to a dataset. A TableAdapter can be added to a dataset in several ways:

  • Through the Data Source Configuration Wizard
  • By dragging a database object from Server Explorer (Database Explorer in Express and Standard editions of Visual Studio 2005) onto the Dataset Designer
  • By dragging from the Toolbox onto the Dataset Designer
  • By right-clicking on the Dataset Designer and selecting Add New TableAdapter from the context menu

Using the Data Source Configuration Wizard or dragging from Server Explorer will create a TableAdapter that is configured with defaults. A typical table from a database will include a select statement that looks something like SELECT CustomerID, ContactName, ContactName, Address, City, Region, PostalCode, Phone FROM Customers. Dragging a TableAdapter from the Toolbox or adding one through the context menu on the dataset designer will create a new, unconfigured TableAdapter and launch the TableAdapter Configuration Wizard. The following steps show a basic walkthrough of the TableAdapter Configuration Wizard:

In the TableAdapter Configuration Wizard, you are prompted to use an existing connection to a database or create a new one:

ms364060.tableadapters_01(en-US,VS.80).gif

Once you have created a connection, you will be asked whether you would like to save the connection string in the application configuration file or have it generated in the code. (This option will not be shown in C++ or smart-device projects because they don't support strongly typed settings files. Instead the connection string will always be generated in the code.):

ms364060.tableadapters_02(en-US,VS.80).gif

You are then prompted to choose the command type for the TableAdapter. The options are to use a SQL statement, create a new stored procedure, or use an existing stored procedure:

ms364060.tableadapters_03(en-US,VS.80).gif

After choosing which command type to use, you are prompted with the appropriate pages to configure the command type you chose. For the sake of brevity, we won't follow all the paths through the wizard, but if you choose to use a SQL statement, you are presented with the following page:

ms364060.tableadapters_04(en-US,VS.80).gif

Clicking the Advanced Options button on the SQL Statement page provides options that control how complimentary SQL Statements are generated. You can choose whether to have corresponding Insert, Update, and Delete statements that write to the database generated for your select statement (Generate Insert, Update and Delete statements), whether to have update and delete statements fail if another user has modified the record (Use optimistic concurrency), and whether to refresh a DataTable after an Insert or Update statement by appending a select statement to the end of the command (Refresh the data table):

ms364060.tableadapters_05(en-US,VS.80).gif

After configuring the SQL command, you are prompted to generate methods for Fill and GetData and given the option to generate DBDirect methods that can be used to insert, update, and delete records directly in the database without using a DataTable (more on these below):

ms364060.tableadapters_06(en-US,VS.80).gif

The last page of the wizard presents a summary of what will be generated in the TableAdapter code. This page will also display any errors encountered in the TableAdapter creation process:

ms364060.tableadapters_07(en-US,VS.80).gif

The TableAdapter Code

The code for your entire dataset, including the TableAdapters, is stored in your project directory with the following file naming convention:

<Dataset Name>.Designer.<Language Extension>

For example, if you have a dataset named NorthwindDataSet in your Visual Basic project, the code file will be named NorthwindDataSet.Designer.vb.

In addition to the designer code file, there is also a .xsc file which stores user interfaces (UI) preferences you have selected in the Data Sources Window and a .xss file which stores designer information about your objects (such as location and size). There may also be a .vb file where you can place your own code. The .vb file is generated the first time you select the View Code command on the Dataset Designer context menu. This file is used to store dataset validation code and other partial class methods and properties associated with your dataset and DataTables.

In Solution Explorer, the files will be nested under your DataSet.xsd file. You can see the files by expanding the DataSet.xsd node. Depending on the profile you use, these files may be hidden. If you do not see the files, on the Solution Explorer toolbar, click Show All Files, and then expand the DataSet.xsd node. Double-clicking any of the files will open them in the appropriate editor.

The code in the designer file is regenerated whenever a change is made to the corresponding dataset. Any modifications you make to this file will be overwritten the next time the file is generated. If you wish to modify or extend a TableAdapter, it is best to do so with a partial class in a separate code file that you add to your project. An example of extending a TableAdapter with a partial class is discussed below in Customizability and Extensibility.

Once you have opened the designer code file, you will find your TableAdapters placed under a separate namespace. For example, if your dataset is named NorthwindDataSet, then the TableAdapters will be placed in the NorthwindDataSetTableAdapters namespace. This separates TableAdapters from the other objects in the dataset. The dataset itself is placed in the project's root namespace and contains nested classes for each DataTable and row type associated with the dataset.

Creating TableAdapter Instances

In Windows Forms projects, TableAdapter instances can be generated on a form by dragging the corresponding DataTable or any of its columns from the Data Sources Window onto the form. They can also be generated onto the form by dragging a typed TableAdapter object from the Toolbox.

If you are writing an application that does not have a graphical user interface or you prefer not to use the form designer, TableAdapters can also be instantiated from code.

Visual Basic

  Dim CustomersTableAdapter As New    NorthwindDataSetTableAdapters.CustomersTableAdapter()

C#

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

To use the TableAdapter to fill a DataTable:

Visual Basic

  Dim NorthwindDataSet as new NorthwindDataSet()
CustomersTableAdapter.Fill(NorthwindDataSet.Customers)

C#

  NorthwindDataSet northwindDataSet = new NorthwindDataSet();
customersTableAdapter.Fill(northwindDataSet.Customers);

Code similar to this is generated in the Form_Load event handler when you drag objects from the Data Sources Window onto the form.

DataTable Columns

When you create a TableAdapter in the designer, a corresponding DataTable is also created. This DataTable matches the schema of the default query. The default query is created by you in the TableAdapter Configuration Wizard, or is automatically created by Visual Studio through the Data Source Configuration Wizard, or by dragging an object from Server Explorer onto the Dataset Designer. When viewing the TableAdapter in the designer, the default query is always the topmost query and the icon next to the query has a checkmark in the upper-left corner:

ms364060.tableadapters_08(en-US,VS.80).gif

Changing the default query will also change the DataTable associated with the TableAdapter.

Null Support

TableAdapters use the new generic type, Nullable, to provide null support on type-safe parameters. Below is the generated function signature for the Insert function of a simplified OrdersTableAdapter:

Visual Basic

  Public Overloads Overridable Function Insert(ByVal CustomerID As String, _
    ByVal EmployeeID As System.Nullable(Of Integer), ByVal OrderDate As _
    System.Nullable(Of Date)) As Integer

C#

  public virtual int Insert(string CustomerID,
    System.Nullable<int> EmployeeID,
    System.Nullable<System.DateTime> OrderDate)

The EmployeeID and OrderDate columns are both type Nullable because the corresponding columns in the Northwind database allow nulls. You can call the Insert function passing Nothing (null in C#) in the place of type-safe parameters instead of setting a separate property or using another bulky mechanism to indicate that the fields are null.

Visual Basic

  OrdersTableAdapter.Insert("NEW", Nothing, Nothing)

C#

  ordersTableAdapter.Insert("NEW", null, null);

In addition to using Nullable on strongly typed columns, the generated TableAdapter code also checks for the use of null on columns of type strings that do not support null entries in the database. The following generated code is an example of how this check is performed:

Visual Basic

  If (CustomerID Is Nothing) Then
    Throw New System.ArgumentNullException("CustomerID")
Else
    Me.Adapter.UpdateCommand.Parameters(0).Value = CType(CustomerID,String)
End If

C#

  if ((CustomerID == null)) {
    throw new System.ArgumentNullException("CustomerID");
}
else {
    this.Adapter.InsertCommand.Parameters[0].Value = ((string)(CustomerID));
}

As you can see from the code above, if null is passed for a non-nullable string parameter, an ArgumentNull exception is thrown.

You can change the handling of nulls in the TableAdapter by modifying the AllowDBNull attribute on individual columns in the associated DataTable. When this property is set to true, parameters are of type Nullable. When it is set to false, parameters are typed according to the database field they represent.

The default setting for AllowDBNull is determined by the origin of the DataTable. For tables in the database, AllowDBNull is set based on whether each column in the database supports null values. For Transact-SQL statements AllowDBNull will be set to false on all columns. For stored procedures, AllowDBNull defaults to true for all columns.

One item to note is that queries will still need special handling for null values in the table. For example, if you want to be able to select null items from the Region field of the Customers table, you will need to write a query that looks like this:

SELECT * FROM Customers WHERE Region=@Region OR (Region IS NULL AND @Region IS NULL)

Database Direct Methods

In addition to the DataTable updating methods available on TableAdapters, you also have the option of generating methods that write to the database directly, without the need to modify a DataTable and send it to the database. This option is controlled through the GenerateDBDirectMethods property on the TableAdapter object in the Dataset Designer. The DBDirect methods generated are Insert, Delete, and an overload of Update that takes one type-safe parameter for each field in the DataTable.

Fill and GetData Methods

When you create a new TableAdapter, you are given two methods for retrieving data from a database into a DataTable. The Fill method takes an existing DataTable as a parameter and fills it. The GetData method returns a new DataTable that has been filled.

Fill is a convenient way to populate a DataTable that already exists. For example, if you are using a DataSet instance in your application, then you can populate the DataTable members in your dataset by passing them to fill.

When you call the Fill method, the value of the ClearBeforeFill property on the TableAdapter is checked. When the property is set to true, the Clear method on the DataTable is called before the DataTable is filled. When the property is set to false, the Clear method is not called. In the latter case, rows in the DataTable are merged with rows in the database. The default value of ClearBeforeFill is true.

GetData is useful when you don't already have a DataTable instance. For example, you might want to implement search functionality on a table in your database. You can add a method to the TableAdapter that returns a new DataTable instance containing only the items that meet your search criteria.

Multiple Query Support

Each TableAdapter can have multiple queries associated with it. Within the TableAdapter these queries are stored as an array of command objects and accessed through type-safe method calls on the TableAdapter.

By grouping queries with the same schema together, common operations can be encapsulated in one TableAdapter. For example, if you commonly filter a table on several different criteria you can add queries for each of those criteria. You might have the following two queries:

SELECT * FROM Customers WHERE CustomerID=@CustomerID

SELECT * FROM Customers WHERE Region=@Region

Each query accessed by calling a method on the TableAdapter takes appropriate parameters and fills a DataTable. The function signatures for the Fill and GetData methods created for each of these queries are shown below.

Visual Basic

  Public Overloads Overridable Function FillByCustomerID( _
    ByVal dataTable As NorthwindDataSet.CustomersDataTable, _
    ByVal CustomerID As String) As Integer
Public Overloads Overridable Function GetDataByCustomerID( _
    ByVal CustomerID As String) As NorthwindDataSet.CustomersDataTable

C#

  public virtual int FillByCustomerID(
    NorthwindDataSet.CustomersDataTable dataTable, string CustomerID)
public virtual NorthwindDataSet.CustomersDataTable GetDataByCustomerID(
    string CustomerID)

Visual Basic

  Public Overloads Overridable Function FillByRegion( _
    ByVal dataTable As NorthwindDataSet.CustomersDataTable, _
    ByVal _Region As String) As Integer
Public Overloads Overridable Function GetDataByRegion( _
    ByVal_Region As String) As NorthwindDataSet.CustomersDataTable

C#

  public virtual int FillByRegion(
    NorthwindDataSet.CustomersDataTable dataTable, string Region)
public virtual NorthwindDataSet.CustomersDataTable GetDataByRegion(
    string Region)

The schema of the DataTable associated with a TableAdapter is determined by the schema of the default query. However, you are not limited to creating queries that match the schema of the TableAdapter. For example, you might add a scalar query to get a count of all customers. By choosing the "SELECT which returns a single value" option in the TableAdapter Query Configuration Wizard, and then entering SELECT COUNT(*) FROM Customers for your SQL statement, a new query will be added to your TableAdapter. This query will use the connection object associated with the TableAdapter but return a scalar value instead of table data.

The Queries TableAdapter

In addition to TableAdapters with associated DataTables, there is a special TableAdapter that contains the global queries in your dataset that return single values. The default name of this TableAdapter is QueriesTableAdapter but, like any other TableAdapter, you can rename it. Queries contained in this TableAdapter are accessed in the same way that queries in other TableAdapters are accessed, with one difference: Instead of creating Fill and GetData methods, only one method is generated. This method will have an appropriate return value to match the return value of the query.

Visual Basic

  Dim myQueries As New NorthwindDataSetTableAdapters.QueriesTableAdapter()
customerCount = myQueries.CustomerCount()

C#

  NorthwindDataSetTableAdapters.QueriesTableAdapter myQueries;
customerCount = myQueries.CustomerCount().value;

TableAdapters are also smart about handling queries with output parameters. For example, if you have a stored procedure called Output that takes one output parameter, the associated TableAdapter method parameters will be passed by reference:

Visual Basic

  Public Overloads Overridable Function Output( _
    ByRef p1 As System.Nullable(Of Integer))

C#

  public virtual int Output(ref System.Nullable<int> p1)

When the method is called, p1 is passed to the stored procedure as a command parameter, the stored procedure is executed, and the value of the modified command parameter is placed back into p1. In code you can use this parameter in the same way you would use any other reference parameter.

More on Scalar Queries

At times, you may want to retrieve a specific value from a row in a table as though it were a scalar value. You can do this with TableAdapters by creating a scalar query that returns only one column. The return value of the generated function will be an object that you can cast to the correct type. For example, if you would like the phone number of a particular customer, you can add a scalar query to your TableAdapter that looks like this:

SELECT Phone FROM Customers WHERE CustomerID=@CustomerID

The generated function will return an object (that you can cast to string) containing the phone number from the first row of the table:

Visual Basic

  Dim phone As String
Dim customerTableAdapter As
    New NorthwindDataSetTableAdapters.CustomersTableAdapter()

phone = CType(customerTableAdapter.CustomerPhone("BOLID"), String)

C#

  string phone;
NorthwindDataSetTableAdapters.CustomersTableAdapter customersTableAdapter = 
    new WindowsApplication2.NorthwindDataSetTableAdapters.CustomersTableAdapter();

phone = (string)customersTableAdapter.CustomerPhone("BOLID");

In this case there will be only one row returned since CustomerID is a unique field. NOTE: This behavior isn't specific to TableAdapters. The underlying DataAdapter provides this functionality. However, TableAdapters are designed to take advantage of this behavior and generate functions that return a single value.

Similarly, you can add Insert, Update, and Delete queries that do not return any data, but act on the same table in the database that your select queries do.

Updating the Database

The most common way to update a database is by sending the changes contained within one or more DataTable objects to the database. TableAdapters provide several overloads of the Update method to facilitate this. Each of these overloads forward the passed-in parameter to the underlying DataAdapter Update method. Below is an example of calling update by passing a DataTable from a typed dataset:

Visual Basic

  customersTableAdapter.Update(northwindDataSet.Customers)

C#

  customersTableAdapter.Update(northwindDataSet.Customers);

DataObjectAttribute

If you use a TableAdapter as an object data source in a Web project, you will see that the ObjectDataSource Wizard detects that the GetData method is available for select operations, and that the Update, Insert, and Delete methods are available for update, insert, and delete operations, respectively. The wizard is able to discover these methods on the TableAdapter because they each have DataObjectAttribute applied to them in the TableAdapter code. For each data method on the TableAdapter, the attribute is applied with the methodType property set to the appropriate value (Fill, Select, Insert, Update, or Delete).

Connection Strings

Visual Studio 2005 introduces typed settings that can be accessed programmatically. TableAdapters take advantage of this feature to offer connection strings that are stored in the app.config file for your application. By using a connection string stored as a typed setting you can change the connection string in the app.config file and all TableAdapters in your application will connect to the database using the new connection string.

Using typed settings has the added benefit of providing defaults if the setting cannot be found in the app.config file. Instead of throwing an exception, the generated settings class returns the value that was set at compile time. For TableAdapters, the connection string you used to develop your application will be used when no other value for the connection string is found.

Customizability and Extensibility

TableAdapters are customizable and extensible in a number of ways through the Dataset Designer and with your own code. Below are some of the common techniques and properties used to customize TableAdapters.

Changing the Inheritance

There is not a base TableAdapter object in the .NET Framework. Instead, TableAdapters inherit from System.ComponentModel.Component when they are created. However, you can change TableAdapters to inherit from a base class of your choosing by setting the BaseClass property in the Dataset Designer.

It is important to note that one of the classes in the inheritance chain must inherit from System.ComponentModel.Component so that the TableAdapter can be dragged onto a Windows Form.

Modifying the Accessibility of the TableAdapter and its Connection

The default accessibility of TableAdapters is public. You can restrict access to your TableAdapters from outside components by changing the Modifier property for the TableAdapter in the Dataset Designer.

Similarly, you can share the connection that a TableAdapter uses by changing the ConnectionModifier property of the TableAdapter in the Dataset Designer. By default, the connection modifier is set to Friend. It is a good practice to leave this modifier set to the default to prevent unknown objects from using your connection (and possibly your credentials) to access the database.

Extending the TableAdapter with Partial Classes

Each TableAdapter class is declared partial in the generated code. Partial classes provide a way to extend a given class by adding methods to the class in multiple code files. For TableAdapters, it's important that you write your partial classes in a separate file from the generated code so that Visual Studio does not overwrite your classes when the TableAdapter code is regenerated. (TableAdapter code is regenerated any time changes are made to a dataset in the Dataset Designer.)

To add a partial class to your project, in Solution Explorer, right-click the project node and select Add->Class. You can then add your code to the new class file. The following example shows how you would add an overload to the database direct Delete method that takes a row instead of individual parameters for each field:

Visual Basic

  Namespace NorthwindDataSetTableAdapters
   Partial Public Class CustomersTableAdapter
      Public Overloads Function Delete( _
        ByVal row as NorthwindDataSet.CustomersRow) As Integer
         Return Me.Delete(row.CustomerID, row.CompanyName, row.ContactName, _
              row.Address, row.City, row._Region, row.PostalCode, row.Phone)
      End Function
   End Class
End Namespace

C#

  namespace WindowsApplication3.NorthwindDataSetTableAdapters
{
    public partial class CustomersTableAdapter
    {
        public int Delete(NorthwindDataSet.CustomersRow row)
        {
            return this.Delete(row.CustomerID, row.CompanyName,
                row.ContactName, row.ContactTitle, row.Address, row.City,
                row.Region, row.PostalCode, row.Country, row.Phone,
                row.Fax);
        }
    }
}

There are two important things to note here. The first is that the class is declared within the TableAdapter namespace. The second is that the class is marked partial. By doing these two things you are telling the compiler that you are extending the existing CustomersTableAdapter class as opposed to creating a new class with the same name in your root namespace.

A Quick Reference of TableAdapter Properties

Code Generation
Base ClassThe class the TableAdapter will inherit from. It is important that TableAdapters inherit from System.Component.ComponentModel so that they can be dragged onto the Form Designer.
ConnectionModifierThe external visibility of the connection object the TableAdapter uses.
ModifierThe external visibility of the TableAdapter.
NameThe name of the TableAdapter.
Data
ConnectionThe information used to generate the connection object for the database connection. This is where the connection string is set.
GenerateDBDirectMethodsA Boolean property indicating whether methods should be generated that allow commands to be sent to the database directly, without using DataTables.
InsertCommand, SelectCommand, UpdateCommand, DeleteCommandSettings for the underlying System.Data.DbCommand objects used to get data and send changes back to the database.
CommandTextThe text of the command to run on the database. Depending on the command type, this text may be a SQL Statement, a Stored Procedure Name, or a Function name.
CommandTypeThe type of command the command text represents. This property can be Text, Stored Procedure, or TableDirect.
ParametersA collection of parameters that should be used when calling the command.

Conclusion

TableAdapters greatly simplify access to database providers and provide a type-safe way to execute database commands. With partial classes and inheritance they can be extended to accomplish almost any task specific to your requirements. In this article you have seen a broad overview of the features of TableAdapters and how they interact with other elements in your project. With these features you can leverage TableAdapter functionality to rapidly create database access objects that are highly reusable.

© Microsoft Corporation. All rights reserved.