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.
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.
Dim CustomersTableAdapter As New NorthwindDataSetTableAdapters.CustomersTableAdapter()To use the TableAdapter to fill a DataTable:
Dim NorthwindDataSet as 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.
Changing the default query will also change the DataTable associated with the TableAdapter.
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
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.
OrdersTableAdapter.Insert("NEW", Nothing, Nothing)
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:
If (CustomerID Is Nothing) Then
Throw New System.ArgumentNullException("CustomerID")
Else
Me.Adapter.UpdateCommand.Parameters(0).Value = CType(CustomerID,String)
End If
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)
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.