The rest of this chapter focuses on building a conflict resolution screen that allows the user to resolve a conflict by selecting the current user value, the original database value, the current database value, or a typed-in value. To drive this conflict resolution screen, the application will display only the customer names and will allow the user to update the list. If a DbConcurrency exception occurs, the conflict resolution screen will be displayed.
Creating the Project
First we create the project for demonstrating conflict resolution when a DbConcurrency exception occurs after an update of the database server from disconnected data.
Create a Microsoft Windows application using the appropriate programming language.
Add a MenuStrip control to the form. Add the following menu items.
MenuItem List
&File &Sync With Database E&xit &Concurrency &Resolve Concurrency Errors
Add a status bar with a single status label called status.
Add a new database file to the application by right-clicking the project, choosing Add, choosing New Item, and then choosing SQL Database. Name the database Customer.mdf, and then click the Add button. This launches the Data Source Configuration Wizard. You haven't created any tables yet, so click Finished to add the empty customer DataSet to your project.
Add the tables as shown earlier in Figure 5-1 or, at a minimum, add a table called TblCustomer that has a column named Id that is a uniqueidentifier primary key and a column named Name that is a varchar(50) data type. Neither column should allow a null value. (You can edit the tables by right-clicking the database file and clicking Open.)
Open the DataSources window by choosing Data and then choosing Show Data Sources. The CustomerDataSet class is visible in this window, but it needs to be updated to show tables that you have added. Right-click the CustomerDataSet and choose Configure DataSet With Wizard, which will launch the Data Source Configuration Wizard. Select all the tables you have created in the database, as shown in Figure 5-6.
In the Data Sources window, drag and drop TblCustomer onto the form. This adds TblCustomerDataGridView to the form. Set the TblCustomerDataGridView object's Dock property to Fill. Set the AutoSizeMode of the Name column to Fill. Also note that Visual Studio automatically added CustomerDataSet, TblCustomerBindingSource, TblCustomer-TableAdapter, and TblCustomerBindingNavigator to the designer tray (Figure 5-7).
Figure 5-6: Adding tables to the CustomerDataSet using the Data Source Configuration Wizard
Figure 5-7: Adding TblCustomer to the form adds the necessary objects to the designer tray automatically.
Extending the Typed DataSet (CustomerDataSet) Class
We must extend this CustomerDataSet class so that there is one place to automatically generate new GUIDs for our primary key columns. Then we can forget about generating new GUIDs in the application.
Right-click the CustomerDataSet.xsd file and choose View Code. This adds a new file called CustomerDataSetExtension.vb or CustomerDataSetExtension.cs under the CustomerDataSet.xsd node.
Add the following code to this file.
Visual Basic
Imports System.Data Partial Public Class CustomerDataSet Private createDefaultGuidForNewRows As Boolean = False Public Sub CreateDefaultGuids() If (createDefaultGuidForNewRows) Then Exit Sub createDefaultGuidForNewRows = True For Each dt As DataTable In Me.Tables If (Not dt.Columns("Id") Is Nothing) Then AddHandler dt.TableNewRow, AddressOf TableNewRow End If Next End Sub Private Sub TableNewRow(ByVal sender As Object, _ ByVal e As DataTableNewRowEventArgs) If (TypeOf e.Row("Id") Is DBNull) Then e.Row("Id") = Guid.NewGuid() End If End Sub End Class
C#
using System; using System.Data; namespace ConflictResolution { public partial class CustomerDataSet { private bool createDefaultGuids = false; public void CreateDefaultGuids() { if (createDefaultGuids) return; createDefaultGuids = true; foreach (DataTable dt in this.Tables) { if (dt.Columns["Id"] != null) dt.TableNewRow += new DataTableNewRowEventHandler(TableNewRow); } } private void TableNewRow(object sender, DataTableNewRowEventArgs e) { if (e.Row["Id"] is DBNull) e.Row["Id"] = Guid.NewGuid(); } } }
This code needs to be called once from the application, so add the following code into the form object's Load method. You can then run the application and add new customers to the table.
Visual Basic
CustomerDataSet.CreateDefaultGuids()
C#
customerDataSet.CreateDefaultGuids();
Extending the TableAdapter (TblCustomerTableAdapter) Class to Expose the ContinueUpdateOnError Property
The DataAdapter object contains a property called ContinueUpdateOnError. If this property is set to true, multi-row updates continue when concurrency errors occur. The TableAdapter objects expose only the minimally required properties, and this application requires the ContinueUpdateOnError property of the underlying TableAdapter object's DataAdapter to be set to true. This means the TblCustomerTableAdapter must be extended.
In the file called CustomerDataSetExtension.vb or CustomerDataSetExtension.cs that you just added when you extended the CustomerDataSet class, add the following code to expose the ContinueUpdateOnError property. (Unfortunately, this code cannot easily be added in a single place for all TableAdapter objects. If you have additional TableAdapter objects, add code to extend each of them in a similar fashion.)
Visual Basic
Namespace CustomerDataSetTableAdapters Partial Public Class TblCustomerTableAdapter Public Property ContinueUpdateOnError() As Boolean Get Return Adapter.ContinueUpdateOnError End Get Set(ByVal value As Boolean) Adapter.ContinueUpdateOnError = Value End Set End Property End Class End Namespace
C#
namespace ConflictResolution.CustomerDataSetTableAdapters { public partial class TblCustomerTableAdapter { public bool ContinueUpdateOnError { get { return Adapter.ContinueUpdateOnError; } set { Adapter.ContinueUpdateOnError = value; } } } }
Add the following code to the form's Load method to set the ContinueUpdateOnError property of each TableAdapter object to true.
Synchronizing the Disconnected DataSet with the Database Server
The application can now be run, but without any synchronization of the disconnected CustomerDataSet with the database server. The following step adds synchronization to your code.
Place the following code into the Synchronize With Database menu item. This code attempts to send all changes back to the database server. If it is successful, the customer table is refilled from the database server to capture any changes that other users have made.
Visual Basic
Private Sub syncwithDatabaseToolStripMenuItem_Click(_ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles syncwithDatabaseToolStripMenuItem.Click TblCustomerTableAdapter.Update(CustomerDataSet.TblCustomer) If (CustomerDataSet.HasErrors) Then status.Text = "Partial synchronization with concurrency errors." Else 'get current database data tblCustomerTableAdapter.Fill(CustomerDataSet.TblCustomer) status.Text = "Database synchronized." End If TblCustomerDataGridView.Refresh() End Sub
C#
private void syncwithDatabaseToolStripMenuItem_Click( object sender, EventArgs e) { tblCustomerTableAdapter.Update(customerDataSet.TblCustomer); if (customerDataSet.HasErrors) { status.Text = "Partial synchronization with concurrency errors."; } else { //get current database data this.tblCustomerTableAdapter.Fill(customerDataSet.TblCustomer); status.Text = "Database synchronized."; } tblCustomerDataGridView.Refresh(); }
Creating the Conflict Resolution Screen
The primary purpose of the conflict resolution screen is to allow you to select the final data values when a concurrency error occurs. It displays your current values, the original database values, and the current database values. The screen is somewhat generic, and you will probably want to tweak it to suit your needs. For reference, the form that you create is shown in Figure 5-8.
Figure 5-8: The completed conflict resolution screen
Add a new form to your project called frmConflict.vb or frmConflict.cs.
Add a Panel control to the form, and set its Dock property to Bottom.
Add two Button controls to the Panel control. Assign the Name properties, btnAccept and btnCancel, and set their Text property to "Accept Final Value" and "Cancel". Set the Anchor property on both buttons to Top.
Add a SplitContainer control to the form. Set its Dock property to Fill, and set its Orientation property to Horizontal.
Add a TabControl to the top of the SplitContainer. Create three tab pages, with the Name property set to tabCurrent, tabOriginal, and tabCurrentDb. Set the Text property to Your Current Value, Original Database Value, and Current Database Value.
Add a TabControl to the bottom of the SplitContainer. Create a single tab page, with the Name property set to tabFinal. Set the Text property to Final Value.
The conflict resolution screen is created by passing two DataRow objects into a new constructor. The first DataRow object contains your current data values in the Current DataRowVersion and the original database data values in the Original DataRowVersion.
The second DataRow object initially contains the current database data value in the Original and Current DataRowVersion. The Current DataRowVersion will contain the final result of your conflict resolution. These DataRow objects are stored in properties. Here is the code to accomplish this.
Visual Basic
Public Sub New(ByVal currentDataRow As DataRow, _ ByVal currentDatabaseDataRow As DataRow) MyBase.New() InitializeComponent() Me.CurrentDataRow = currentDataRow FinalDatabaseDataRow = currentDatabaseDataRow End Sub Public Property FinalDatabaseDataRow() As DataRow Get Return m_finalDatabaseDataRow End Get Set(ByVal value As DataRow) m_finalDatabaseDataRow = value End Set End Property Private m_finalDatabaseDataRow As DataRow = Nothing Public Property CurrentDataRow() As DataRow Get Return m_currentDataRow End Get Set(ByVal value As DataRow) m_currentDataRow = value End Set End Property Private m_currentDataRow As DataRow = Nothing
C#
public frmConflict(DataRow currentDataRow, DataRow currentDatabaseDataRow) { InitializeComponent(); CurrentDataRow = currentDataRow; FinalDatabaseDataRow = currentDatabaseDataRow; } public DataRow FinalDatabaseDataRow { get { return m_finalDatabaseDataRow; } set { m_finalDatabaseDataRow = value; } } private DataRow m_finalDatabaseDataRow = null; public DataRow CurrentDataRow { get { return m_currentDataRow; } set { m_currentDataRow = value; } } private DataRow m_currentDataRow = null;
Create the PopulateTab and CopyToFinal methods. For each column in the DataRow object, the PopulateTab populates a tab page with a Label that shows the column name, a TextBox that shows the value of the column based on the DataRowVersion, and a Button that lets you copy the selected value to the final value by executing the CopyToFinal method. Here is the code for these two methods.
Visual Basic
Public Sub PopulateTab(ByVal tab As TabPage, ByVal dataRow As DataRow, _ ByVal dataRowVersion As DataRowVersion, ByVal m_ReadOnly As Boolean) Const verticalSpacing As Integer = 30 Const labelWidth As Integer = 50 Const horizontalSpacing As Integer = 10 Const buttonWidth As Integer = 100 Const buttonHeight As Integer = 20 For col As Integer = 0 To dataRow.ItemArray.Length - 1 Dim val As Object = dataRow(col, dataRowVersion) Dim label As New Label() tab.Controls.Add(label) label.Text = dataRow.Table.Columns(col).ColumnName label.Top = (col + 1) * verticalSpacing label.Left = horizontalSpacing label.Width = labelWidth label.Visible = True Dim textBox As New TextBox() tab.Controls.Add(textBox) textBox.Text = val.ToString() textBox.Top = (col + 1) * verticalSpacing textBox.Left = (horizontalSpacing * 2) + labelWidth textBox.Width = tab.Width textBox.Left _ - buttonWidth - (horizontalSpacing * 2) textBox.Name = tab.Name + label.Text textBox.ReadOnly = m_ReadOnly textBox.Visible = True textBox.Anchor = AnchorStyles.Left _ Or AnchorStyles.Top Or AnchorStyles.Right If (tab.Name = "tabFinal") Then Continue For Dim btn As New Button() tab.Controls.Add(btn) btn.Text = "Copy to Final" btn.Left = textBox.Left + textBox.Width _ + horizontalSpacing btn.Top = (col + 1) * verticalSpacing btn.Height = buttonHeight btn.Visible = True btn.Anchor = AnchorStyles.Top OR AnchorStyles.Right AddHandler btn.Click, AddressOf CopyToFinal Dim propertyBag As New ArrayList() propertyBag.Add(dataRow.Table.Columns(col)) propertyBag.Add(textBox) btn.Tag = propertyBag Next End Sub Private Sub CopyToFinal(ByVal sender As Object, ByVal e As EventArgs) Dim btn As Button = CType(sender, Button) Dim propertyBag As ArrayList = CType(btn.Tag, ArrayList) Dim dc As DataColumn = CType(propertyBag(0), DataColumn) Dim textBox As TextBox = CType(propertyBag(1), TextBox) tabFinal.Controls(tabFinal.Name + dc.ColumnName).Text = textBox.Text End Sub
Add code to collect the final data values from the TextBox objects in tabFinal and place the values into the FinalDatabaseRow, which will be retrieved by the calling form. This code will be placed into the Click event handler of btnAccept as follows.
Visual Basic
Private Sub btnAccept_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnAccept.Click for each dc as DataColumn in FinalDatabaseDataRow.Table.Columns FinalDatabaseDataRow(dc) = _ tabFinal.Controls(tabFinal.Name + dc.ColumnName).Text next End Sub
C#
private void btnAccept_Click(object sender, EventArgs e) { foreach (DataColumn dc in FinalDatabaseDataRow.Table.Columns) { FinalDatabaseDataRow[dc] = tabFinal.Controls[tabFinal.Name + dc.ColumnName].Text; } }
The final step is to configure the buttons. Set the DialogResult of btnAccept to OK, and set the DialogResult of btnCancel to Cancel. On the form's property screen, set the Accept-Button property to btnAccept and set the CancelButton property to btnCancel.
Calling the Conflict Resolution Screen
The conflict resolution screen deals with conflicts only on a DataRow-by-DataRow basis. This means you must loop through your concurrency errors, displaying the conflict resolution screen for each conflict.
In the Resolve Concurrency Errors menu item, add the following code to retrieve the latest data from the database and call the conflict resolution screen for each conflict that has occurred since the last time you synchronized with the database.
Visual Basic
Private Sub resolveConcurrencyErrorsToolStripMenuItem_Click(_ ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles resolveConcurrencyErrorsToolStripMenuItem.Click if (customerDataSet.TblCustomer.HasErrors) then 'get data refresh with most current Dim refreshCustomer as new CustomerDataSet() tblCustomerTableAdapter.Fill(refreshCustomer.TblCustomer) 'loop through the errors for each dr as DataRow in customerDataSet.TblCustomer.GetErrors() Dim currentDb as DataRow = _ refreshCustomer.TblCustomer.Rows.Find(dr("Id")) using conflict as new frmConflict(dr, currentDb) if (conflict.ShowDialog(Me) = DialogResult.OK) then dr.ClearErrors() tblCustomerTableAdapter.Update(conflict.FinalDatabaseDataRow) customerDataSet.TblCustomer.LoadDataRow( _ conflict.FinalDatabaseDataRow.ItemArray, _ LoadOption.OverwriteChanges) status.Text = "Single row updated." else status.Text = "Single row update cancelled." end if end using next tblCustomerDataGridView.Refresh() end if End Sub
C#
private void resolveConcurrencyErrorsToolStripMenuItem_Click( object sender, EventArgs e) { if (customerDataSet.TblCustomer.HasErrors) { //get data refresh with most current CustomerDataSet refreshCustomer = new CustomerDataSet(); this.tblCustomerTableAdapter.Fill(refreshCustomer.TblCustomer); //loop through the errors foreach (DataRow dr in customerDataSet.TblCustomer.GetErrors()) { DataRow currentDb = refreshCustomer.TblCustomer.Rows.Find(dr["Id"]); using (frmConflict conflict = new frmConflict(dr, currentDb)) { if (conflict.ShowDialog(this) == DialogResult.OK) { dr.ClearErrors(); tblCustomerTableAdapter.Update(conflict.FinalDatabaseDataRow); customerDataSet.TblCustomer.LoadDataRow( conflict.FinalDatabaseDataRow.ItemArray, LoadOption.OverwriteChanges); status.Text = "Single row updated."; } else { status.Text = "Single row update cancelled."; } } } tblCustomerDataGridView.Refresh(); } }
Correcting Concurrency Errors with the Conflict Resolution Screen
When it's time to test the conflict resolution screen, you must have two instances of the application running. When each instance starts, it loads a copy of the data from the database. If changes are made in both instances, the first instance that synchronizes with the database will succeed and the second instance will fail for each DataRow object that was also updated by the first instance.
Follow these steps to create and resolve concurrency errors.
Start two instances of the application. (Navigate to the ConflictResolution.exe file and double-click it twice, or right-click the project in the Solution Explorer window and choose Debug | Start New Instance twice.) You will see the contents of the TblCustomer table.
The first time the application is executed there is no data, so add data to both instances, as shown in Figure 5-9.
Synchronize with the database. When the first instance is synchronized, the only noticeable change is that the status bar displays a message indicating that the data is synchronized. When the second instance is synchronized, the same message is displayed, but the grid displays the data that was input into the first instance. Notice that both instances can synchronize with the new data because there are no conflicts. If the first instance is synchronized again, it will contain the data from the second instance, as shown in Figure 5-10.
Make changes to the same name in both instances. For example, you can change the name 234 to Joe in one instance and to Mary in the other instance. Synchronize the instance with Joe as the name, and the synchronization will succeed. Next, synchronize the instance with Mary as the name; a concurrency error occurs because the data in the database was changed between the time this instance was previously synchronized and this attempt to synchronize. The grid displays the error, as shown in Figure 5-11.
Finally, test the conflict resolution screen. Click the Resolve Concurrency Errors menu item to launch the conflict resolution screen, as shown in Figure 5-12. The tab pages are populated with data. Move between the tab pages to see the current disconnected data values, the original database values, and the current database values. Note that the final data values are displayed at the bottom, and notice the button beside each TextBox that allows you to copy the value in the TextBox into the final data value.
Figure 5-9: Different data has been entered into each instance of the application.
Figure 5-10: The two instances are synchronized with the database.
Figure 5-11: The concurrency error is displayed in the grid because the underlying data changed since we read the data.
Figure 5-12: The conflict resolution screen allows you to resolve a concurrency error by selecting the current disconnected data values, the original database values, or the current database values.
Building a Better Conflict Resolution Screen
The conflict resolution screen was designed to let you generically resolve conflicts from concurrency errors. Most of the code can be reused in your application, but you might want to make some changes to make the screen more appealing to your users and to further encapsulate your code. Here are some changes to consider.
Pass two DataSet objects to the conflict resolution form instead of two DataRow objects. The first DataSet object contains all of your data, along with the concurrency errors. The second DataSet object contains the data that is currently in the database. You can program the conflict resolution screen to loop through the errors so that users don't have to examine each error individually.
Hide surrogate keys. Surrogate keys have no relationship with business data, so there is no need to show them. If you use GUID-Keys, you can simply hide all columns that are the GUID data type.
Provide Copy All buttons. On each tab page, add a Copy All button that copies all of the data on the tab page to the final data values.
