TableAdapters and Object Binding - Bridging the gap

TableAdapters and Object Binding - Bridging the gap

Rate This
  • Comments 7

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 

 

Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post
  • Hello VB Team

    Thankx for the nice Article! As soon as the Beta 2 is out, I will even go through it. At the moment I do have the folloing comment:

    This issue (nullable) I don't get. Maybe you could write a paragraph and further descirbe the implication for this:

    > We weren’t able to get the nullable columns done on the typed DataTable, although the TableAdapters are fully null aware.

    Thankx, Cheers Harry
  • I'm saving this article archives instantly have quality information thanks

  • hi,

    For Each employee As Employee In employees

               Select Case employee.ObjectState

    ==>           Case ObjectState.Added

                       _employeesTableAdapter.Insert(...

    when did this object acquired the state -> "Added" prior to getting this far here.....?

    please elaborate.

  • You have to put code either in your constructor or somewhere else to set the added attribute to true then in your set you can do this:

    If _InventoryService <> value Then

                   If Me.ObjectState <> ObjectState.Added Then

                       Me.DataStateChanged(ObjectState.Modified, "InventoryService")

                       _InventoryService = value

                   End If

               End If

  • All works up until I attempt the code ...

    Case ObjectState.Added

                       _employeesTableAdapter.Insert(employee.LastName, employee.FirstName, employee.HireDate)

    at the very end.

    As I inspect the table adapter, no Insert method exists, even though I specified create insert, update and delete methods when I configured???

  • I realized that the transactionscope didn't work as the tableadapter will update changes back to database even if i commented the .complete method. Please help.

Page 1 of 1 (7 items)