TableAdapters and Object Binding - Bridging the gap
I’ve been working on getting an article out on leveraging the data design time features, and the new object binding features we’ve added in Whidbey. We’ve been busy getting Beta 2 done, so I haven’t finished it yet, but here’s a rough draft of the full article.
TableAdapters and Object Binding
In Whidbey we’ve done a lot of work to enable design time support for object based data binding. This leaves the question of how to load and save custom objects. How do developers get the rich functionality of DataSet but using their own object model? In this article I’ll describe some common infrastructure for building a set of base classes to enable round tripping your objects to/from the database.
Loading and Saving Objects
The first question is usually how to load my objects. Should I use DataReaders or can I leverage the new TableAdapter features? I still need to get actual perf numbers, but a key issue to consider is the productivity of the development team, not just the raw performance of the application. While you can certainly use DataReaders to load your objects, you’ll incur extra responsibilities for managing the state of the DataReader and the untyped nature of the parameters and the results of the DataReader. By using the TableAdapters you can dramatically increase your development and stabilization phase productivity by leveraging compile time verification for column changes or additions.
Hydrating objects
One of the key features we’ve added to TableAdapters is something we refer to as DBDirect methods. By default TableAdapters generate Get methods as well as Fill methods. The key difference is the Fill method will fill an existing DataSet or DataTable. The Get methods will return a new DataTable for your particular query. We can then use the Get method to iterate through the DataTable and create custom objects. Yes, we’re creating a DataTable then tossing it out, but let’s look at the code we can easily write.
Using the Data Sources Window add a new Database Data Source to the Northwind database.
To simplify this sample choose the Employees table but only choose EmployeeID, FirstName, LastName and HireDate.
Click finish to create the NorthwindDataSet which includes the DataTables and TableAdapters
Finding employees by name
We’ll want to add an additional query to the EmployeesTableAdapter to find employees by their last name. For the purposes of this article we’re going to simplify things a little bit and remove the concurrency checks and the refresh options. To change this behavior, select the TableAdapter in the DataSet designer and choose Configure. Choose the Advanced Options button and uncheck the checkboxes for Use optimistic concurrency and Refresh the data table. Click finish to close the wizard.
The next thing we’ll do is add our additional query. On the EmployeeTableAdapter select the Add Query task on the context menu.
To leverage sprocs, choose the Create new stored procedure as the command type and then choose SELECT which returns rows.
Enter the following query:
SELECT EmployeeID, LastName, FirstName, HireDate FROM dbo.Employees
WHERE LastName LIKE @lastName + '%'
Click next and name the stored procedure Employees#GetByLastName
On the choose the methods to generate step, name the Fill method name: FillByLastName and the Get method GetByLastName then click finish.
Close the DataSet designer.
Employee Entity
Next we’ll create an Employee class that represents our Employee Object. It will be a simple class that just caries the payload for a specific employee. Later on we’ll add some change tracking/IsDirty and validation logic.
''' <summary>
''' Represents an employee of the company
''' </summary>
Public Class Employee
#Region " Backing Fields "
Private _employeeId As Integer
Private _lastName As String
Private _firstName As String
Private _hireDate As Nullable(Of Date)
#End Region
Public Property EmployeeId() As Integer
Get
Return _employeeId
End Get
Set(ByVal value As Integer)
_employeeId = value
End Set
End Property
Public Property LastName() As String
Get
Return _lastName
End Get
Set(ByVal value As String)
_lastName = value
End Set
End Property
Public Property FirstName() As String
Get
Return _firstName
End Get
Set(ByVal value As String)
_firstName = value
End Set
End Property
Public ReadOnly Property FullName() As String
Get
Return _firstName & " " & _lastName
End Get
End Property
Public Property HireDate() As Nullable(Of Date)
Get
Return _hireDate
End Get
Set(ByVal value As Nullable(Of Date))
_hireDate = value
End Set
End Property
End Class
Employee Factory
We now need a factory class to load and create a collection of employees. However, we’ll need to add some functionality to our collection of employees so we’ll create an EmployeeList collection. For now, it will be pretty simple, but later on we’ll add some functionality for GetChanges.
Public Class EmployeeList
Inherits System.ComponentModel.BindingList(Of Employee)
End Class
To create the Factory we’ll use the following code:
Public Class EmployeeFactory
' sinlge instance of the TableAdapter to be used by the Factory
Private Shared _employeesTableAdapter As NorthwindDataSetTableAdapters.EmployeesTableAdapter
Shared Sub New()
_employeesTableAdapter = New NorthwindDataSetTableAdapters.EmployeesTableAdapter()
End Sub
''' <summary>
''' Gets all employees for a given last name
''' </summary>
Public Shared Function GetEmployeesByLastName(ByVal lastName As String) As EmployeeList
Dim employeeList As New EmployeeList()
Dim employeesDataTable As NorthwindDataSet.EmployeesDataTable
employeesDataTable = _employeesTableAdapter.GetByLastName(lastName)
Return ConvertRowsToList(employeesDataTable)
End Function
''' <summary>
''' Gets all employees
''' </summary>
Public Shared Function GetAllEmployees() As EmployeeList
Dim employeesDataTable As NorthwindDataSet.EmployeesDataTable
employeesDataTable = _employeesTableAdapter.GetData()
Return ConvertRowsToList(employeesDataTable)
End Function
''' <summary>
''' Internal method for converting rows to employee entities
''' </summary>
Private Shared Function ConvertRowsToList(ByVal employeesDataTable As NorthwindDataSet.EmployeesDataTable) As EmployeeList
Dim employeeList As New EmployeeList()
For Each employeeRow As NorthwindDataSet.EmployeesRow In employeesDataTable
Dim newEmployee As New Employee()
With newEmployee
' use the typed row as the accessor for each column
.EmployeeId = employeeRow.EmployeeID
.FirstName = employeeRow.FirstName
.LastName = employeeRow.LastName
' Becuase HireDate
If employeeRow.IsHireDateNull Then
.HireDate = Nothing
Else
.HireDate = employeeRow.HireDate
End If
End With
employeeList.Add(newEmployee)
Next
Return employeeList
End Function
End Class
In the above code you’ll see that I used the typed EmployeeRow to get the specific columns. In addition I was able to leverage the typed parameters on the TableAdapter for the FillByLastName. This is just an example of where the developer gets some productivity gains. If the column names change in the DataSet, or the parameters change on the TableAdapters, VS will generate compile time errors. This means you don’t have to run your app just to find out its broken. You’ll also notice that I had to check if the HireDate was null. This is the one hole we still have in our designtime null support. We weren’t able to get the nullable columns done on the typed DataTable, although the TableAdapters are fully null aware.
Loading a form
To test out our progress, let’s do a little Drag Once DataBinding. To keep the productivity going, I’m going to leverage the object binding features of the Data Sources Window. Be sure to build your project to make sure all the types are compiled.
Using the Data Sources Window choose the object data source type and select your employee class. Notice that any XML comments you put on the class are displayed below the list.
If your employee object is in the same assembly as the dataset and the form you’ll notice the NorthwindDataSet is also displayed in the Data Sources Window, however we’re going to use the employee object we just created. From the Data Sources Window drag this employee object to your form. This will create an EmployeeDataGridView bound to a BindingSource named EmployeeBindingSource. If you look at the .designer file you’ll see EmployeeBindingSource.DataSource property is set to GetType(Employee) This tells the BindingSource to emit the “shape” of the employee object to controls bound to the BindingSource. This is why the DataGridView shows the columns for the employee object.
The next step is to actually get some data. Double click on the form to get the form load event. Add the following code:
Me.EmployeeBindingSource.DataSource = EmployeeFactory.GetAllEmployees()
To make it more interesting, add a textbox and a button to the form and add the following code to the button.click event:
Me.EmployeeBindingSource.DataSource = EmployeeFactory.GetEmployeesByLastName(Me.TextBox1.Text.Trim)
We now have the basic model established. We can load entity objects with a factory pattern using the data design time features, and we have full control over our entity objects. The next step is to save the objects. This brings up some interesting challenges that the DataSet solves, but I’ll demonstrate how to get the basic functionality with your own objects.
Batch of Individual Updates
One of the first things to consider is whether you plan to support batch updates. By batch I mean the user can change several rows of data before committing the changes back to the database. This may involve listing several phone numbers for a contact, or the creation of a new order with several line items. Once you go beyond a single row things get a little more complicated. We need to track whether the end user added, deleted or modified the given row. So which is better, direct to the database – AKA MS Access Style? Or batch? Well, as most technical decisions, it depends. Certainly when you’re working on an isolated app with a single user database, the direct to database style works well and definitely reduces the complexity. However, when working in any shared environment, particularly mission critical systems where multiple people are reading and writing to the database at a given time it’s important to only save “completed” information. For instance let’s say we’re creating an order for a customer that just called in. The customer wants to get some pricing and check inventory on some items. They don’t want to give their personal information until they know the company has the items at the price they want to pay. The sales rep starts to create a new order. They enter a bunch of line items that the customer was asking about. At this point the sales rep doesn’t have enough information to fill out the Order Header record so we can’t save it back to the database. And should we? So far this is just a set of scratch notes. If the customer hangs up, it’s cumbersome to have to clean up the database. There are other strategies such as scratch tables, and good old post-it notes. However, if the customer decides to place the order the sales rep shouldn’t have to copy that information into an order. It really comes down to whether your database contains information that the company will act upon, or is it a place to store state and transient data? In general developers tend write information back to the database because they find it easier to read/write to the database rather then maintain their own in-memory cache. For websites this may actually be required in order to manage a true stateless environment. However, even in web scenarios its best to use an isolated database for maintaining your state. As the end user completes their operation, the data is then moved to the main database that runs the business.
In order to minimize the activity with the database and get the greatest scalability, its best to leverage the power of the desktop and manage updates in batches.
Self aware objects, or entities and factories
There are two distinct patterns the have surfaced. One is the self aware model. In this case the employee class would have save and load methods. The problem with this model is each employee object has to maintain a lot more common functionality, such as the data access code, or a web service to pass its self back to. This also means it’s harder to batch operations such as a single call to a web service, database, or to incorporate a transaction.
The other model is the entity model where each object maintains a minimal set of information to represent its self. It’s also nice to incorporate basic validation logic so the end user receives immediate feedback on the validity of their data. In order to load or save these objects a factory pattern is used. A call is made to a factory and it returns a collection of entity objects. In order to save the objects, the collection is passed back to the factory and the factory saves the changes.
What changed?
In batch operations we need to have each entity track its state is. At first glance you might thing an IsDirty Boolean property. But we really need to track Inserted, Modified and Deleted. This can be described with a simple enum that you can add to your project.
<Flags()> _
Public Enum ObjectState
Unchanged
Added
Deleted
Modified
End Enum
Since this functionality is common across all our entity objects, we’ll create a base class.
To simply things a bit, we’ll also add an import:
Imports System.ComponentModel
Public MustInherit Class BizObjBase
Implements INotifyPropertyChanged
On our base entity class we simple add a property for ObjectState. Now one could argue that this property should be ReadOnly and there’s definitely some good logic around that. However if we look at the debates we’ve seen over the DataRow.DataRowState property that exists within ADO.net, it’s raised the question of how tight we should make our object models. So, for this example, we’ll just leave it read/write.
Since we’ll likely use these classes for object based data binding, and we would most likely never expose this ObjectState property on a grid, we’ll mark it with the browsable attribute. This just means this property won’t show up on the property grid, be displayed in the Data Sources Window or show up on a DataGridView automatically.
Private _objectState As ObjectState
<Browsable(False)> _
Public Property ObjectState() As ObjectState
Get
Return _objectState
End Get
Set(ByVal value As ObjectState)
_objectState = value
End Set
End Property
We’ll also add an IsDirty property for simplification:
<Browsable(False)> _
Public ReadOnly Property IsDirty() As Boolean
Get
Return Me.ObjectState <> ObjectState.Unchanged
End Get
End Property
When a property changes, we’ll want to let data bound controls know that something has changed. This code is actually added automatically for us when we added the implements keyword on the base class
Public Event PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged
In order to set the ObjectState we’ll add a few methods to make it easy for entity objects set the ObjectState
Protected Sub DataStateChanged(ByVal propertyName As String)
DataStateChanged(ObjectState.Modified)
End Sub
Protected Sub DataStateChanged()
DataStateChanged(CType(Nothing, String))
End Sub
Protected Sub DataStateChanged(ByVal dataState As ObjectState)
DataStateChanged(dataState, Nothing)
End Sub
Protected Sub DataStateChanged(ByVal dataState As ObjectState, ByVal propertyName As String)
' If the PropertyName was passed in, raise a PropertyChangedEvent
' Not all properties are required to use this event as the BindingSource
' will trigger a change notification if the updated value was pushed through
' the databinding infrastructure. This is typically only required if the value
' of a property was changed through code, or through a control that
' wasn't databound
If Not String.IsNullOrEmpty(propertyName) Then
RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
End If
' Only change the ObjectState if we're unchanged
If Me.ObjectState = ObjectState.Unchanged And _
dataState = ObjectState.Modified Then
Me.ObjectState = dataState
Else
If dataState = ObjectState.Deleted Then
Me.ObjectState = ObjectState.Deleted
End If
End If
End Sub
Now all we need to do is wire this up. When these objects are created we need to determine whether the object is new, or simply being hydrated from the database and is actually unchanged.
Back in our employee class, we’ll add some code to each property setter. But first we’ll need to add our base class to our employee class.
Public Class Employee
Inherits BizObjBase
For each property add the if block below.
Public Property LastName() As String
Get
Return _lastName
End Get
Set(ByVal value As String)
If _lastName <> value Then
Me.DataStateChanged(ObjectState.Modified, "LastName")
_lastName = value
End If
End Set
End Property
Where did my deleted objects go?
This is where it gets a little interesting. If you delete an object, how does it get removed from the database? For self aware objects you could simply have the object tell the database to delete its self. However, for entity objects, we need to keep something around to tell the factory to delete the row in the database when the collection is sent back to the factory.
To manage this we’ll create a BizObjListBase class and add a deletedRows collection. Because we want to create typed lists for all our entities, we’ll leverage generics and the BindingList(Of T). Because our list assumes some basic functionality in the BizObjBase class, we’ll limit the types that inherit BizObjListBase to those that use BizObjBase.
Imports System.ComponentModel
Public MustInherit Class BizObjListBase(Of T As BizObjBase)
Inherits BindingList(Of T)
Private _deletedRows As BindingList(Of T)
Protected ReadOnly Property DeletedRows() As BindingList(Of T)
Get
Return _deletedRows
End Get
End Property
Public Sub New()
MyBase.New()
Initialize()
End Sub
Protected Sub Initialize()
Me.AllowNew = True
_deletedRows = New BindingList(Of T)
End Sub
End Class
Because we’ve inherited from BindingList(Of T) we can leverage some collection handling and override the RemoveItem method
Protected Overrides Sub RemoveItem(ByVal index As Integer)
' Mark the item as deleted
Me.Item(index).ObjectState = ObjectState.Deleted
' Move the deleted row to the deleted rows collection
DeletedRows.Add(Me.Item(index))
' Remove the item from the main list
MyBase.RemoveItem(index)
End Sub
Now that we have a deleted rows and the ability to delete and track a deleted item we need a way to get the deleted rows back out. To follow the DataSet model we’ll add some GetChagnes methods.
Public Function GetChanges() As BindingList(Of T)
' We need to return all items that have been changed,
' including the delted rows
Dim changedItems As New BindingList(Of T)
For Each item As T In Me.Items
' return any item that has been changed
If item.ObjectState <> ObjectState.Unchanged Then
changedItems.Add(item)
End If
Next
' Get the deleted items
For Each item As T In DeletedRows
changedItems.Add(item)
Next
' Return all the changed items (deleted, modified, added)
Return changedItems
End Function
Public Function GetChanges(ByVal objectState As ObjectState) As BindingList(Of T)
Dim changedItems As New BindingList(Of T)
If objectState = objectState.Deleted Then
For Each item As T In DeletedRows
changedItems.Add(item)
Next
Else
For Each item As T In Me.Items
If item.ObjectState = objectState Then
changedItems.Add(item)
End If
Next
End If
Return changedItems
End Function
And of course we’ll add an AcceptChanges and Clear method.
Public Sub AcceptChanges()
For Each item As T In Me.Items
item.ObjectState = ObjectState.Unchanged
Next
DeletedRows.Clear()
End Sub
Public Overloads Sub Clear()
MyBase.ClearItems()
DeletedRows.Clear()
End Sub
Now that we have a new base class, we can switch our EmployeeList to utilize our new BizObjListBase class.
Public Class EmployeeList
Inherits BizObjListBase(Of Employee)
At this point we have a base class that supports deletes and entity objects that can track their state. The next step is to implement the Factory.SaveEmployees method.
Saving objects to the database
In order to save the employee objects we’ll add a SaveEmployees method to our EmployeeFactory. We’ll use the DBDirect methods on the EmployeeTableAdapter and wrap the whole thing within a transaction. In order to leverage the TransactionScope object you’ll need to add a reference to the System.Transactions.dll
Public Shared Sub SaveEmployees(ByVal employees As EmployeeList)
Dim txScope As New System.Transactions.TransactionScope
Using txScope
' First process the deleted rows
For Each employee As Employee In employees.GetChanges(ObjectState.Deleted)
_employeesTableAdapter.Delete(employee.EmployeeId)
Next
' now process any of the updated and inserted rows:
For Each employee As Employee In employees
Select Case employee.ObjectState
Case ObjectState.Added
_employeesTableAdapter.Insert(employee.LastName, employee.FirstName, employee.HireDate)
Case ObjectState.Modified
_employeesTableAdapter.Update(employee.LastName, employee.FirstName, employee.HireDate, employee.EmployeeId)
Case Else
' nothing else to do
End Select
Next
txScope.Complete()
' if all the updates happened within the transaction,
' update all the RowState values to UnModified
employees.AcceptChanges()
End Using
End Sub
Notice that we leverage the ObjectState of our entity base class to determine which method to call on the EmployeeTableAdapter.
Testing the save
To test out the save method we’ll utilize our Form1 test harness. Notice that Visual Studio creates a save button on the EmployeeBindingNavigator. It’s disabled by default as VS knows it didn’t generate any save logic. Select the SaveToolStripButton and toggle the enabled to true. Then add the following code to the click event:
Me.Validate()
Me.EmployeeBindingSource.EndEdit()
EmployeeFactory.SaveEmployees(CType(Me.EmployeeBindingSource.List, EmployeeList))
Wrapping Up
Using the new data design time features of Visual Studio 2005 developers can assemble rich object models without having to revert hand writing all the data access code.
Steve Lasker
Program Manager
Visual Studio 2005
