A number of posts have asked how to extend TableAdapters to support additional functionality. It may be related to supporting transactions across multiple updates, or how do I set the UserId as a parameter on an update statement that isn’t represented in the DataTable?
For Transactions, we don’t expose a Transaction Property by default because this was tedious task in 1.x of the framework. With 2.0 we now have a great solution with System.Transactions. If you search this blog you’ll see several references to using the new TransactionScope object to very easily wrap several TableAdapters in a single transaction that will automatically enlist DTC when needed, or simply leverage SQL 2005 transactions when working with a single database.
However, assuming you’re leveraging an existing codebase you’ve written in 1.x, or you have another request for the TableAdapters, you can simply take advantage of partial classes to add just about any functionality you’d like.
In order to extend the TableAdapters you may need a little help knowing where\/how to extend them. In order to decouple the TableAdpaters from the DataSet they fill, we decided not to use nested classes for each TableAdapter. The next challenge was how do we find a unique namespace for each TableAdapter when you may very likely have several DataSets\/TableAdapter combinations with objects of the same name. For instance, one strategy is to create several smaller DataSets for each unit of work. This means you might wind up with an OrderDataSet that includes Customers, Orders, OrderItems, Products, etc.. You then might have another DataSet for contacts which includes Customers, CompanyContacts, People, etc. In this case you’d wind up with two CustomerTableAdapters. Without nested classes we need a model that would scale without simply concatenating a number to the end of each class. A goal of the Whidbey data features were Smart Defaults. The solution we came up with is to leverage namespaces.
To find your TableAdapters use the following pattern: [DataSetName] + TableAdapter. For the NorthwindDataSet, the namespace would be NorthwindDataSetTableAdapters.
The next question is how\/where to place this code? If you double click on a DataTable we’ll automatically create the partial class for the DataTable and DataSet in the [DataSetName].designer.vb\/cs file. Unfortunately, we just didn’t have the time to do the same thing for TableAdapters. While you could add the TableAdapter code to the [DataSetName].vb\/cs file, you may want to create a separate file just for your TableAdapters.
Using the Northwind database example, we’ll extend the Orders table to include a UserId parameter.
In Solution Explorer select Add Item and choose a Class File. Name the class file, NorthwindDataSet.TableAdapters.vb or cs.
Replace the code with something similar to the following:<\/p>\n
Namespace NorthwindDataSetTableAdapters In the above example I’m setting an additional parameter on the Update and Insert commands that do not map to a column in the orders data table. This parameter is used by my stored procedure for some change tracking. <\/p>\n
Partial Class OrdersTableAdapter
Public Overloads Function Update(ByVal ordersTable As NorthwindDataSet.OrdersDataTable, ByVal userId As Guid) As Integer
Me.m_adapter.UpdateCommand.Parameters(“UserId”).Value = userId
Me.m_adapter.InsertCommand.Parameters(“UserId”).Value = userId
Return Me.Update(ordersTable)
End Function
End Class
End Namespace<\/font><\/p>\n