|
download source code
Automated Form Navigation and Standard CRUD Procedures
Visual Studio 2005 offers fantastic improvements in rapidly constructing Windows and Smart Client applications. The Data Sources panel automates placing controls on a form and binding them through the Binding Source component. While the Binding Navigator component offers great potential, some simple extensions vastly improve its capability to completely automate navigation and CRUD (Create, Read, Update, and Delete) functionality for data access. This article demonstrates how to create a User Control to extend the functionality of the Binding Navigator component.
Introduction
In previous articles I have emphasized the
RAD capabilities of Visual Studio 2005
in quickly creating an n-tiered Windows application with
generated Business and Data Access layers.
This article builds on those concepts to provide a simple architecture of code templates and user
controls that further automate the ability to rapidly develop complete applications.
Much of the increased functionality is gained by extending the Binding Navigator component in a user control to automate the saving of edited data. In the process, the Binding Source control needs to be extended and an interface template needs to be added to the data access logic.
Binding Navigator Component in Visual Studio 2005
To demonstrate this capability of the new Visual Studio 2005 RAD tools, create a new Windows application and add a new Data Source (DataSet) based on the Customer and Order tables in the Northwind database. (For more detailed instructions, see my
previous article).
The Data Sources panel offers the convenience of dragging tables or fields from a Typed Dataset and dropping them on a form to create fully bound and ready to use data access logic. Use the new data source created above to drag the Customer table onto the form. The wizard then creates an instance of the dataset, a Binding Source component linked to the table in the dataset, and a Binding Navigator component linked to the binding source. If the dataset is local to the project, the wizard also instantiates a table adapter and adds a call to the Fill method. (But if you are using an Object Data Source, you have to add this code manually.)
The Binding Navigator places a tool strip docked to the top of the form that allows user navigation through the records. Buttons are provided to navigate to the first, previous, next and last records as well as any relative position by typing an index number.
Buttons are also added to add records, delete records and save edits. The save button (diskette icon) is added specifically by the Data Sources wizard and is not part of the standard navigation bar. It is this save button that has instigated this article.
When the wizard adds the save button to the navigation strip, the proper code will be generated if the dataset is local to the project (as apposed to an Object Data Source.) The following method is added for the Customer table:
Private Sub CustomersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingNavigatorSaveItem.Click
Me.Validate()
Me.CustomersBindingSource.EndEdit()
Me.CustomersTableAdapter.Update(Me.NorthwindDataSet.Customers)
End Sub
The Validate command closes out the editing of a control; the EndEdit method of the binding source writes any edited data in the controls back to the record in the dataset; and then the Update command of the table adapter sends updated records back to the database.
This new control offers significant enhancement and generically handles most of the navigation and CRUD needs of a data bound form. But there are several deficiencies that need to be overcome before it is a complete tool handling all the needs of a data bound form. Here are a few of the problems that need to be overcome:
When using the Object Data Sources, the wizard does not attempt to generate the code to fill and update the data table. This code must be added manually. The user must remember to click the Save button when data is edited. There is no IsDataDirty flag set when data has changed and no warning when the user changes records without saving. There is no warning or confirmation when deleting a record. The navigation strip also needs a means for looking up a specific record.
Implementing a Generic Table Update Interface
To address the first problem, we need to create generic code to Fill and Update the data table. In
part 3 of my previous article, I showed the code to wrap the data access logic in the business layer. Since then, I have revised the structure slightly to make use of the data table subclass to make the methods more consistent. To enforce this consistency, we need to create an interface that each table class should implement in the wrapper.
In Solution Explorer, right click on the project and click Add, New Item. (In an n-tiered application, this should be done in the Business layer, but for demonstration purposes, put it in the Windows project for now and move it later.) Choose a Class Item and name it _Interface (so it sorts to the top). This will be a utilities type of class where general methods are kept that can be accessed through out the program. -
Create an Interface called ITableUpdate which requires a Fill method and an Update method.
Public Class _Interface
''' <summary>
''' Interface for Data table in a dataset to standardize fill and update methods
''' </summary>
Public Interface ITableUpdate
Sub Fill()
Sub Update()
End Interface
End Class
In the Partial Class behind the DataSet (right click on the dataset and click View Code), create the following template code for each updateable table in the DataSet. In this example, I am implementing the Customer class for the NorthwindDataSet.
Partial Class NorthwindDataSet
Shared taCustomer As New NorthwindDataSetTableAdapters.CustomersTableAdapter
Partial Class CustomersDataTable
Implements _Interface.ITableUpdate
Public Sub Fill() Implements _Interface.ITableUpdate.Fill
taCustomer.Fill(Me)
End Sub
Public Sub Update() Implements _Interface.ITableUpdate.Update
taCustomer.Update(Me)
End Sub
End Class
End Class
By implementing the interface, a generic UI call can work for any table. Now the Save method can call the update using generic code instead of calling an update method specific for the Customer table. Then the save routine can be written once and reused rather than written specifically for each table.
Extending the Binding Navigator Control
One way to fix the problems of the navigation toolbar and extend the functionality is to create a user control based on the Binding Navigator control and then add the properties and methods that are needed.
Right click on the Windows project and Add, New Item. From the list of items, select the User Control and give it a name of "exBindingNavigator.vb". (If you want to use this enhancement in other applications, the user controls and custom controls should be in a separate project that can be added to each solution. For this demonstration, leave it in the Windows project.) VS will create a blank control with an empty container. Click and drag the bottom right corner until it is the size of the Binding Navigator tool bar. Switch back to the Customer form (or any form where the Data Sources wizard has created the navigation control) and copy the navigation tool bar. Switch back to the user control and paste the navigation bar into the container. In the properties box, rename the Navigator from CustomerBindingNavigator to something more generic like GenericBindingNavigator. Click on the save button (diskette icon) and in the properties panel, change the name to a more general name of BindingNavigatorSaveItem. -
Right click on the form and select View Code to open the code page. Add a Sub New constructor. In the constructor, set the Dock property to the top of the form.
Public Class exBindingNavigator
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
Me.Dock = DockStyle.Top
End Sub
End Class
-
First, we need a property to track the reference to the binding source for the embeded navigator control. One of the tasks when the binding source is defined is to get a reference to the underlying table. We could include the code to get the reference to the table here, but sometimes, the data source has not been defined before the binding source is defined, or the data source may change. Therefore, an event handler is added to handle the Data Source Changed event and an method is called to set the table reference. To fill the table automatically, a reference to the containing form is derived and the Form Load event is subscribed to.
Private WithEvents _BindingSource As BindingSource
Public Property BindingSource() As BindingSource
Get
Return _BindingSource
End Get
Set(ByVal value As BindingSource)
GenericBindingNavigator.BindingSource = value
_BindingSource = value
If Not _BindingSource Is Nothing Then
'subscribe to the events in case not yet set
AddHandler _BindingSource.DataSourceChanged, _
AddressOf bs_DataSourceChanged
'get a reference to the table now
bs_DataSourceChanged(New Object, New EventArgs)
End If
End Set
End Property
-
Add the following method to handle the data source changed event above.
Private Sub bs_DataSourceChanged(ByVal sender As Object, ByVal e As EventArgs)
If Not _BindingSource Is Nothing Then
_DataTable = GetTableFromBindingSource(GenericBindingNavigator.BindingSource)
If Not _DataTable Is Nothing Then
'if child BS, get reference to parent BS
Dim testBS As BindingSource = _
TryCast(GenericBindingNavigator.BindingSource.DataSource, BindingSource)
If Not testBS Is Nothing Then
ParentBindingSource = testBS 'call the getter to capture event
End If
End If
End If
End Sub
Another method is needed to get the table reference from a Binding Source as used above. The Binding Source has two properties that determine which table to bind to: the Data Source and the Data Member. Most of the time, The Data Source is set to an instance of a DataSet and the Data Member is the name of the table in the DataSet. But in situations with parent-child relationships, the Data Source can be another Binding Source and the Data Member is the name of the relationship. Therefore, we need a little calculation to deduce the table.
-
Add a method called GetTableFromBindingSource which passes a Binding Source as a parameter and returns a reference to a Data Table.
Public Function GetTableFromBindingSource(ByVal bs As BindingSource)
'get a reference to the dataset
Dim ds As DataSet, dt As DataTable
'try to cast the data source as a binding source
Dim bsTest As BindingSource = bs
Do While Not TryCast(bsTest.DataSource, BindingSource) Is Nothing
'if cast was successful, walk up the chain until dataset is reached
bsTest = CType(bsTest.DataSource, BindingSource)
Loop
'since it is no longer a binding source, it must be a dataset
If TryCast(bsTest.DataSource, DataSet) Is Nothing Then
'Cast as dataset did not work
Throw New ApplicationException("Invalid Binding Source ")
End If
ds = CType(bsTest.DataSource, DataSet)
'check to see if the Data Member is the name of a table in the dataset
If ds.Tables(bs.DataMember) Is Nothing Then
'it must be a relationship instead of a table
Dim rel As System.Data.DataRelation = ds.Relations(bs.DataMember)
If Not rel Is Nothing Then
dt = rel.ChildTable
Else
Throw New ApplicationException("Invalid Data Member")
End If
Else
dt = ds.Tables(bs.DataMember)
End If
If TryCast(dt, ITableUpdate) Is Nothing Then
Throw New ApplicationException("Table " & dt.TableName & _
" does not implement ITableUpdate interface")
End If
Return dt
End Function
To get the table, we first need a reference to the dataset. If the Data Source casts correctly as a Binding Source, then we must walk back up the chain until we get to the original dataset. Once we have the dataset, we can see if the Data Member is a table in the dataset. If it is not, then it must be a relation. Looking up the relation in the set of relations in the dataset, we can get the table reference from the Child table property of the relation.
Once we get a reference to the table, we can use polymorphism to cast it as the interface we created earlier. If it does not cast, it means that the data table did not implement the interface correctly and an exception should be thrown. If it does cast, then the generic update routine can be called to save the data.
-
Add the following method into the exBindingNavigator class to cover the click event of the save button. (You can double click on the save button to generate the code stub.)
Private Sub SaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Me.Validate()
_BindingSource.EndEdit()
'cast table as ITableUpdate to get the Update method
CType(_DataTable, _Interface.ITableUpdate).Update()
IsDataDirty = False
End Sub
-
By adding another method to handle the Form Load event, you could have the table automatically fill itself when the form opens. This is not always needed since the tables are many times filled as needed, using logic. Therefore, this function should be selectable by adding another property, allowing the developer to choose.
Private _AutoFillFlag As Boolean = True
Public Property AutoFillFlag() As Boolean
Get
Return _AutoFillFlag
End Get
Set(ByVal value As Boolean)
_AutoFillFlag = value
End Set
End Property
Private Sub Form_Load(ByVal sender As Object, ByVal e As EventArgs)
If _AutoFillFlag Then
'cast table as ITableUpdate to get the Fill method
CType(_DataTable, _Interface.ITableUpdate).Fill()
End If
End Sub
-
Lastly, by subscribing to the load event of the parent form, all this should happen. When the load event
of the user control fires, we can get a reference to the parent form.
Private Sub exBindingNavigator_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'get the reference to the hosting form
Dim frm As Object = CType(Me, ContainerControl).ParentForm
While TryCast(frm, System.Windows.Forms.Form) Is Nothing
'if not a form, walk up chain
If Not TryCast(frm, System.ComponentModel.Container) Is Nothing Then
frm = CType(frm, ContainerControl).Parent
Else
frm = CType(frm, Control).Parent
End If
End While
_Form = CType(frm, System.Windows.Forms.Form)
'add the handler for the Form Load to fill the table
AddHandler _Form.Load, AddressOf Form_Load
End Sub
Now, by adding this control to your form and setting the Binding Source property, the save button will be functional with what ever dataset you are using and with the property set, the table will automatically fill (as long as the table implements the interface). This solves the first problem.
Implementing AutoSave Functionality
The second problem is a bit more complex. Microsoft has left a significant deficiency in the Binding Source component by not adding a flag to record when data has been edited. In a grid, there is an IsRowDirty flag that can be interrogated during the RowLeave event, but there is no corresponding flag for the bound details format controls. (I have reported this as a bug, and Microsoft has said this will be fixed in a future version.) So, in our control, we need to add a property that can be set when the data has been edited.
-
Right click on the screen and select View Code to switch to the code behind page. Add the following code to create the Is Dirty property needed for the control.
Private _IsDataDirty As Boolean = False
Public Property IsDataDirty() As Boolean
Get
Return _IsDataDirty
End Get
Set(ByVal value As Boolean)
_IsDataDirty = value
If BindingNavigatorSaveItem.Enabled <> _IsDataDirty Then
BindingNavigatorSaveItem.Enabled = _IsDataDirty
End If
End Set
End Property
-
To set this flag, we need to intercept the BindingComplete event of the Binding Source. This is actually an expensive event as it fires for each bound control every time the record is changed. But it also fires when data is updated from the control back to the dataset, so it does give us the information we need. The following method handles the Binding Complete event and sets the property if data has been edited.
Private Sub BindingComplete(ByVal sender As Object, ByVal e As BindingCompleteEventArgs)
'is this a return from the control to the dataset?
If e.BindingCompleteContext = BindingCompleteContext.DataSourceUpdate Then
'was it successful and not for a read only control?
If e.BindingCompleteState = BindingCompleteState.Success And _
Not e.Binding.Control.BindingContext.IsReadOnly Then
IsDataDirty = True
End If
End If
End Sub
-
Instead of subscribing to the event here with the Handles clause, we need to wait and assign the handler after the Binding Source has been defined to avoid an error in Visual Basic. Add this snippet to the handler section of the Binding Source set method.
AddHandler _BindingSource.BindingComplete, AddressOf BindingComplete
AddHandler _BindingSource.PositionChanged, AddressOf bs_PositionChanged
-
The second line adds a handler for when the user moves to a new record so the previous record can be saved. There are several events that can be used to tell when the record changes: CurrentChanged, CurrentItemChanged, and PositionChanged. The latter is the best choice. The problem is that these events are past tense--they fire after the record has already changed. So there is no way to keep the record from changing. The best we can do is save the data in the previous record.
-
The following method handles the PositionChanged event for the binding source and checks for flag, calling the save method if needed.
Private Sub bs_PositionChanged(ByVal sender As Object, ByVal e As EventArgs)
If (_IsDataDirty And Not _DataTable Is Nothing) Then
SaveItem_Click(New Object(), New EventArgs())
End If
End Sub
Prompting the User for Saves
The third problem is that the user may want to be warned and given the option of changing data or rolling back the edits. Of course this should be an option, so first we need another property called AutoSave that if false, prompts the user before making the save.
-
Add this code to the Property region of the code page.
Private _AutoSaveFlag As Boolean
Public Property AutoSaveFlag() As Boolean
Get
Return _AutoSaveFlag
End Get
Set(ByVal value As Boolean)
_AutoSaveFlag = value
End Set
End Property
-
The above handler for the Position Changed event can be modified to check the flag and prompt the user if needed.
Private Sub bs_PositionChanged(ByVal sender As Object, ByVal e As EventArgs) _
Handles _BindingSource.PositionChanged
If (_IsDataDirty And Not _DataTable Is Nothing) Then
Dim msg As String = "Do you want to save edits to the previous record?"
If _AutoSaveFlag Or MessageBox.Show(msg, "Confirm Save", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
SaveItem_Click(New Object(), New EventArgs())
Else
_DataTable.RejectChanges()
MessageBox.Show("All unsaved edits have been rolled back.")
_IsDataDirty=False
End If
End If
End Sub
-
While we are prompting the user, the Delete record routine needs a prompt to confirm deletes. First, we need to turn off the built-in method. In the designer view of the user control, select the Binding Navigator tool strip. In the properties panel, find the property for DeleteItem (in the Items section) , drop down the list and choose (none).
-
We need another method to delete the records only after prompting. Double click on the Delete icon (red X) in the toolbar to create a code stub and add the following:
Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click
Dim msg As String = "Are you sure you want to delete the current record? "
If _AutoSaveFlag Or MessageBox.Show(msg, "Confirm Delete", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
'Delete the current record
_BindingSource.RemoveCurrent()
CType(_DataTable, Win._Interface.ITableUpdate).Update()
End If
End Sub
Adding a Lookup List
The last problem to solve is more of a convenience than a problem. I find that most data entry forms need a means of looking up a specific record. Normally, this takes the form of a combo box with the ID as the value and a unique text as the display member. In the Selected Value Changed event, the position of the Binding Source can be changed to the selected record.
Adding a combo box to the navigation bar is a little more difficult. The Tool Strip Combo Box cannot be bound and is really only a drop down list box and has no value member. To overcome this deficiency, we will use a hash table to store the value member.
Follow these steps to add the combo box to the navigation bar:
In design view on the user control, the last icon on the bar is a drop down list showing the items that can be added. First select a Label and a label will be added to the bar with the name ToolStripLabel1. Change the text of the label to "Lookup:". (You should make a property of this value and allow the developer to change it at design time.) Drop the list down again and add a Combo Box control to add a new item called ToolStripComboBox1. -
Add another property to the user control so the developer can choose which field to use as the display member.
Private _DisplayMember As String
Public Property DisplayMember() As String
Get
Return _DisplayMember
End Get
Set(ByVal value As String)
_DisplayMember = value
End Set
End Property
-
This property can be defaulted to the first text type field in the bound table by adding the following snippet to the bs_DataSourceChanged method:
If Not _DataTable Is Nothing Then
'find the first text column
For Each col As DataColumn In _DataTable.Columns
If col.GetType().Equals(Type.GetType("System.String")) Then
_DisplayMember = col.ColumnName
Exit For
End If
Next
End If
-
Define the hash table and add a new method to our control called BuildLookupList that will populate the combo box with the proper values.
Private ht As New System.Collections.Hashtable
Public Sub BuildLookupList()
If Not _DisplayMember Is Nothing Then
'fill both lookup box and hash table with values
ToolStripComboBox1.Items.Clear()
ht.Clear()
If (BindingSource.List.Count > 0) Then
'get the primary key as value member (assumes single field key)
Dim _valueMember As String = _DataTable.PrimaryKey(0).ColumnName
'temp change the sort of the binding source
Dim tempSort As String = _BindingSource.Sort
BindingSource.Sort = _DisplayMember + " ASC"
'step through the records in the binding source as filtered
For Each drv As DataRowView In _BindingSource
If Not drv(_DisplayMember) Is Nothing Then
ToolStripComboBox1.Items.Add(drv(_DisplayMember))
Try
ht.Add(drv(_DisplayMember), drv(_valueMember))
Catch 'ignore dups
End Try
End If
Next
'restore sort field
BindingSource.Sort = tempSort
End If
End If
End Sub
-
This method needs to be called when the list changes. Add a handler to the handler section of the BindingSource method and another method to handle the event and call the above method.
AddHandler _BindingSource.ListChanged, AddressOf bs_ListChanged
Private Sub bs_ListChanged(ByVal sender As Object, _
ByVal e As System.ComponentModel.ListChangedEventArgs)
BuildLookupList()
End Sub
-
If the binding source is based on a relationship, the list will change every time the parent position changes.
So, we need to add a new property that tracks the parent binding source and a new method to call.
Note: it would be tempting to let the List Changed handler above do double duty, but notice that
the event args are different.
Private WithEvents _ParentBindingSource As BindingSource
Public Property ParentBindingSource() As BindingSource
Get
Return _ParentBindingSource
End Get
Set(ByVal value As BindingSource)
_ParentBindingSource = value
If Not _ParentBindingSource Is Nothing Then
AddHandler _BindingSource.PositionChanged, _
AddressOf parentBS_PositionChanged
End If
End Set
End Property
Private Sub parentBS_PositionChanged(ByVal sender As Object, _
ByVal e As EventArgs)
BuildLookupList()
End Sub
-
Add another event handler for the parent position changed event
Private Sub parentBS_PositionChanged(ByVal sender As Object, ByVal e As EventArgs)
BuildLookupList()
End Sub
-
The Parent Binding Source property can be set by Developers and we can capture it when assigning the data table property by adding the following snippet to the bs_DataSourceChanged method.
'if child BS, get reference to parent BS
Dim testBS As BindingSource = TryCast(_BindingSource.DataSource, BindingSource)
If Not testBS Is Nothing Then
ParentBindingSource = testBS 'call the getter to subcribe to events
End If
-
If you run the form now, you will see that the list is built, but nothing happens when the user makes a selection. We need to capture the event when the user makes a selection from the combo box list. Select the Combo Box control (this is sometimes hard--select the label and press tab) and in the Properties panel, switch to the events section (click the lightening bolt icon at the top) and double click on the SelectedIndexChanged event to generate a code stub. Add the following code:
Private Sub ToolStripComboBox1_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles ToolStripComboBox1.SelectedIndexChanged
If (ToolStripComboBox1.Focused And ToolStripComboBox1.Text <> "") Then
'get the primary key field
Dim _valueMember As String = _DataTable.PrimaryKey(0).ColumnName
'set the position by finding the primary key in the binding source
_BindingSource.Position = BindingSource.Find(_valueMember, _
ht(ToolStripComboBox1.Text))
End If
End Sub
-
One last thing to take care of--after selecting a record, if the position is changed using the next or previous buttons, the entry in the combo box will be out of synch with the record showing. The following method will synchronize the list with the record.
'make sure the same record shows in the combo box as is in the record
Private Sub syncLookupCombobox()
If (ToolStripComboBox1.Items.Count > 0 And _BindingSource.Position >= 0) Then
'get the display string for the current record in the binding source
Dim lookup As String = CType(_BindingSource.Current, _
DataRowView).Row(_DisplayMember).ToString()
If (lookup.Length > 0) Then
ToolStripComboBox1.SelectedIndex = _
ToolStripComboBox1.FindStringExact(lookup)
End If
End If
End Sub
-
This method needs to be called from the Position Changed event handler.
-
Now, when you compile and run the form, everything should be functional with no code written behind the form.
This user control can be further extended to accomplish other CRUD type events for the form such as disabling the controls until the user chooses to edit data. To implement the new control, delete the Binding Navigator put on by the wizard, open the Toolbox panel and in the Win section will by the user control. Drag the user control to the form and fill in the Binding Source property.
We have built-in the capability of having multiple navigators on the form with Parent-Child relationship. To demonstrate this:
First drag a Panel control to the right side of the form. Open the Data Sources panel and expand the Customer table and select the Orders child relationship table. Drag and drop this into the panel. The wizard will add another binding source and a grid. Size the panel and grid as necessary. Leave a little space in the panel above the grid. Drag another copy of the exBindingNavigator user control to the panel. It will dock to the top. Set the Binding Source property of the navigator to the new OrdersBindingSource. You can now run the form and the navigation should work as before.
Conclusion
The Binding Navigator component can be easily extended by making it into a user control and adding properties and methods. Adding this user control to a form will implement auto filling of a data table, implement the update functionality (with user prompting), and allow specific record lookup. By putting the user control into a control library project, it can be added to any solution and speed development of data access applications.
Implementing the Interface in the Data Access logic quickly creates stubs for the needed methods for each table in the dataset, but some code does need to be written. But, this is very structured code and can easily be generated using CodeDom or a 3rd party code generation system.
There are some problems with this methodology--mostly problems that Microsoft needs to fix in the IDE. After adding the user control to a form, if any changes are made to the user control, the form will not automatically reload in the IDE. You have to close and reopen the form to view it in design mode. Sometimes, you even have to rebuild the project to get the form to view correctly again. So make sure your user control is completely finished before using on forms.
You can download the code for article here. The VS2005 solution contains both VB and C# examples using the Northwind database.
About the Author
David Catherman - CMI Solutions
Email: DCatherman (at) CMiSolutions (dot) com
David Catherman has 20+ years designing and developing database applications with specific concentration for the last 4-5 years on Microsoft .NET and SQL Server. He is currently Application Architect and Senior Developer at CMI Solutions using Visual Studio 2005 and SQL Server 2005. He has several MCPs in .NET and is pursuing MCSD.
|
