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 a nd 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