Solving the Problems
You really need the concept of read-only fields and to group the remaining fields into separate SQL
UPDATES. To do this you need a CommandBuilder-like class that takes a data definition as input and produces a collection of SQL
UPDATE command objects to update the data. In the SQL
UPDATE command collection you would have one command for the address and a second for the credit info, presuming the current user has the rights to update both. The code then goes through the collection and populates each command with parameters containing old and new values for each field that your user wants to update. If all the old and new values for a specific command match there is clearly no change in that part of the data and the command doesn't need to execute. Once the code has loaded all SQL
UPDATE commands the code can execute those commands. In this case you do not want to wrap these updates as part of a single transaction. If all SQL
UPDATE commands execute successfully there was no conflict and the user can go on with their job. If a conflict occurs it is limited to a specific subset of the data and your application can update the other parts of the data without any delay. Because of the group wise nature of the updates, you cannot use a SQL row version. Instead you need to include the complete list of fields being updated together with the primary key in the SQL
WHERE clause of each command.
An Example
The
Main() method code below demonstrates a simple example of how an update could work. In this example I use the
ExtendedProperties column to store the field grouping information. Columns with the same group name are bundled together into a single SQL
UPDATE command.
''' <summary>
''' Test application to check database updates.
''' </summary>
''' <remarks></remarks>
Sub Main()
Dim dta As New pubsDataSetTableAdapters.titlesTableAdapter
Dim table As pubsDataSet.titlesDataTable
Dim cb As New CommandBuilder
' Load the data
table = dta.GetData()
' Configure the table
cb.ConfigureDataTable(table)
' Make a change to the data
table.Item(0).price *= 1.1
'table.Item(0).title = "New title"
' Update the database
cb.UpdateTable(table)
Console.WriteLine( _
"Press any key to terminate the application.")
Console.ReadKey()
End Sub
The main program uses a typed table adapter to load the titles table from the Pubs database. All remaining functions are part of the CommandBuilder class created in the main function.
In the
ConfigureDataTable() function shown below, each column receives an
UpdateGroup extended property. You'll use this during the update to determine which fields are grouped together. This information isn't determined at update time because you might use the same table for multiple purposes in different business objects with different field groupings. Additionally some fields might be read-only-something that my sample code does not take into account.
''' <summary>
''' Configure the columns into update groups.
''' </summary>
''' <param name="table">The table with columns.</param>
''' <remarks></remarks>
Public Sub ConfigureDataTable( _
ByVal table As pubsDataSet.titlesDataTable)
' Basic data about the book
table.title_idColumn.ExtendedProperties("UpdateGroup") = "Book"
table.titleColumn.ExtendedProperties("UpdateGroup") = "Book"
table.typeColumn.ExtendedProperties("UpdateGroup") = "Book"
table.notesColumn.ExtendedProperties("UpdateGroup") = "Book"
table.pubdateColumn.ExtendedProperties("UpdateGroup") = "Book"
' Financial data about the book
table.pub_idColumn.ExtendedProperties("UpdateGroup") = _
"Financial"
table.priceColumn.ExtendedProperties("UpdateGroup") = _
"Financial"
table.advanceColumn.ExtendedProperties("UpdateGroup") = _
"Financial"
table.royaltyColumn.ExtendedProperties("UpdateGroup") = _
"Financial"
' Sales information about the book
table.ytd_salesColumn.ExtendedProperties("UpdateGroup") = _
"Sales"
End Sub
The
UpdateTable() function (see
Listing 1) first retrieves a collection of SQL
UPDATE commands. Note that for this example I have skipped the SQL
INSERT and SQL
DELETE commands as you handle these in the same way as normal.
The following code shows how the
GetUpdateCommands() function loops through all the field groups and creates a different SQL
UPDATE command for each one. It bundles all separate commands together into a collection and returns the collection.
''' <summary>
''' Build a collection of update commands for the table.
''' </summary>
''' <param name="table">
''' The table that needs to be updated.</param>
''' <returns>
''' A collection of SQLCommands for the update.</returns>
''' <remarks></remarks>
Private Function GetUpdateCommands(ByVal table As DataTable) _
As List(Of SqlClient.SqlCommand)
Dim groups As IDictionary(Of String, List(Of DataColumn))
Dim cmds As List(Of SqlClient.SqlCommand)
cmds = New List(Of SqlClient.SqlCommand)
Console.WriteLine("Building update commands.")
Console.WriteLine()
' Split all columns into groups based upon the
' UpdateGroup extended property.
groups = SplitColumnIntoGroups(table)
For Each group As List(Of DataColumn) In groups.Values
Dim cmd As SqlCommand
cmd = CreateUpdateCommand(table, group)
cmds.Add(cmd)
Console.WriteLine("Update command {0}:", cmds.Count)
Console.WriteLine(cmd.CommandText)
Console.WriteLine()
Next
Return cmds
End Function
The
SplitColumnIntoGroups() function (see
Listing 2) takes all the columns in a table and splits them into separate update groups. This would be a good point to exclude read-only columns and possibly the primary key columns as these are typically not updatable.
In
Listing 3, the
CreateUpdateCommand() function creates a single SQL
UPDATE command for each group of fields. The SQL
WHERE clause consists of the primary key of the row as well as the fields that need to be updated. The
CreateUpdateCommand() function compares each field both against the old value as well as its new value in order to avoid seeing the same change by two users as a conflict where it really doesn't matter that a field is overwritten with the same value.
While my technique isn't a complete solution for all update concurrency issues, I believe it is a step in the right direction. It is a work in progress and I feel that others will discover the best way to handle the separate cases. In the mean time, I hope that my solution helps you create applications that are friendlier and less technology-oriented in their nature.
Previous Page
