LINQ To SQL Samples - Optimistic Concurrency |
This sample demonstrates how to retrieve the changes that lead to an optimistic concurrency exception.
Public Sub LinqToSqlOptimistic01()
Console.WriteLine("YOU: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
Dim product = db.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
Console.WriteLine()
Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
' Open a second connection to the database to simulate another user
' who is going to make changes to the Products table
Dim otherUser_db = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1) With {.Log = db.Log}
Dim otherUser_product = otherUser_db.Products.First(Function(p) p.ProductID = 1)
otherUser_product.UnitPrice = 999.99D
otherUser_product.UnitsOnOrder = 10
otherUser_db.SubmitChanges()
Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
Console.WriteLine("YOU (continued): ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
product.UnitPrice = 777.77D
Dim conflictOccurred = False
Try
db.SubmitChanges(ConflictMode.ContinueOnConflict)
Catch c As ChangeConflictException
Console.WriteLine("* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *")
For Each aConflict In db.ChangeConflicts
Dim prod = CType(aConflict.Object, Product)
Console.WriteLine("The conflicting product has ProductID {0}", prod.ProductID)
Console.WriteLine()
Console.WriteLine("Conflicting members:")
Console.WriteLine()
For Each memConflict In aConflict.MemberConflicts
Dim name = memConflict.Member.Name
Dim yourUpdate = memConflict.CurrentValue.ToString()
Dim original = memConflict.OriginalValue.ToString()
Dim theirUpdate = memConflict.DatabaseValue.ToString()
If (memConflict.IsModified) Then
Console.WriteLine("'{0}' was updated from {1} to {2} while you updated it to {3}", _
name, original, theirUpdate, yourUpdate)
Else
Console.WriteLine("'{0}' was updated from {1} to {2}, you did not change it.", _
name, original, theirUpdate)
End If
Console.WriteLine()
Next
conflictOccurred = True
Next
Console.WriteLine()
If (Not conflictOccurred) Then
Console.WriteLine("* * * COMMIT SUCCESSFUL * * *")
Console.WriteLine("Changes to Product 1 saved.")
End If
Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ")
ResetProducts() ' clean up
End Try
End Sub
Result:
YOU: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
YOU (continued): ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *
The conflicting product has ProductID 1
Conflicting members:
'UnitPrice' was updated from 778.7700 to 999.9900 while you updated it to 777.77
'UnitsOnOrder' was updated from 0 to 10, you did not change it.
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
This sample demonstrates how to automatically resolve concurrency conflicts. The 'overwrite current values' option writes the new database values to the client objects.
Public Sub LinqToSqlOptimistic02()
Dim otherUser_db = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
db.Log = Nothing
Dim product = db.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("You retrieve the product 1, it costs {0}", product.UnitPrice)
Console.WriteLine("There are {0} units in stock, {1} units on order", product.UnitsInStock, product.UnitsOnOrder)
Console.WriteLine()
Console.WriteLine("Another user changes the price to 22.22 and UnitsInStock to 22")
Dim otherUser_product = otherUser_db.Products.First(Function(p) p.ProductID = 1)
otherUser_product.UnitPrice = 22.22D
otherUser_product.UnitsInStock = 22
otherUser_db.SubmitChanges()
Console.WriteLine("You set the price of product 1 to 1.01 and UnitsOnOrder to 11")
product.UnitPrice = 1.01D
product.UnitsOnOrder = 11
Try
Console.WriteLine("You submit")
Console.WriteLine()
db.SubmitChanges()
Catch c As ChangeConflictException
WriteConflictDetails(db.ChangeConflicts) ' write changed objects / members to console
Console.WriteLine()
Console.WriteLine("Resolve by overwriting current values")
db.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues)
db.SubmitChanges()
End Try
Console.WriteLine()
Dim dbResult = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
Dim result = dbResult.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("Now product 1 has price={0}, UnitsInStock={1}, UnitsOnOrder={2}", _
result.UnitPrice, result.UnitsInStock, result.UnitsOnOrder)
Console.WriteLine()
ResetProducts() ' clean up
End Sub
Result:
You retrieve the product 1, it costs 18.0000
There are 39 units in stock, 0 units on order
Another user changes the price to 22.22 and UnitsInStock to 22
You set the price of product 1 to 1.01 and UnitsOnOrder to 11
You submit
Optimistic Concurrency Conflict in product 1
'UnitPrice' was updated from 18.0000 to 22.2200 while you updated it to 1.01
'UnitsInStock' was updated from 39 to 22, you did not change it.
Resolve by overwriting current values
Now product 1 has price=22.2200, UnitsInStock=22, UnitsOnOrder=0
This sample demonstrates how to automatically resolve concurrency conflicts. The 'keep current values' option changes everything to the values of this client.
Public Sub LinqToSqlOptimistic03()
Dim otherUser_db = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
db.Log = Nothing
Dim Prod = db.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("You retrieve the product 1, it costs {0}", Prod.UnitPrice)
Console.WriteLine("There are {0} units in stock, {1} units on order", Prod.UnitsInStock, Prod.UnitsOnOrder)
Console.WriteLine()
Console.WriteLine("Another user changes the price to 22.22 and UnitsInStock to 22")
Dim otherUser_product = otherUser_db.Products.First(Function(p) p.ProductID = 1)
otherUser_product.UnitPrice = 22.22D
otherUser_product.UnitsInStock = 22
otherUser_db.SubmitChanges()
Console.WriteLine("You set the price of product 1 to 1.01 and UnitsOnOrder to 11")
Prod.UnitPrice = 1.01D
Prod.UnitsOnOrder = 11
Try
Console.WriteLine("You submit")
Console.WriteLine()
db.SubmitChanges()
Catch c As ChangeConflictException
WriteConflictDetails(db.ChangeConflicts) ' write changed objects / members to console
Console.WriteLine()
Console.WriteLine("Resolve by keeping current values")
db.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues)
db.SubmitChanges()
End Try
Console.WriteLine()
Dim dbResult = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
Dim result = dbResult.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("Now product 1 has price={0}, UnitsInStock={1}, UnitsOnOrder={2}", _
result.UnitPrice, result.UnitsInStock, result.UnitsOnOrder)
Console.WriteLine()
ResetProducts() ' clean up
End Sub
Result:
You retrieve the product 1, it costs 18.0000
There are 39 units in stock, 0 units on order
Another user changes the price to 22.22 and UnitsInStock to 22
You set the price of product 1 to 1.01 and UnitsOnOrder to 11
You submit
Optimistic Concurrency Conflict in product 1
'UnitPrice' was updated from 18.0000 to 22.2200 while you updated it to 1.01
'UnitsInStock' was updated from 39 to 22, you did not change it.
Resolve by keeping current values
Now product 1 has price=1.0100, UnitsInStock=39, UnitsOnOrder=11
This sample demonstrates how to automatically resolve concurrency conflicts. The 'keep changes' option keeps all changes from the current user and merges changes from other users if the corresponding field was not changed by the current user.
Public Sub LinqToSqlOptimistic04()
Dim otherUser_db = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
db.Log = Nothing
Dim prod = db.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("You retrieve the product 1, it costs {0}", prod.UnitPrice)
Console.WriteLine("There are {0} units in stock, {1} units on order", prod.UnitsInStock, prod.UnitsOnOrder)
Console.WriteLine()
Console.WriteLine("Another user changes the price to 22.22 and UnitsInStock to 22")
Dim otherUser_product = otherUser_db.Products.First(Function(p) p.ProductID = 1)
otherUser_product.UnitPrice = 22.22D
otherUser_product.UnitsInStock = 22
otherUser_db.SubmitChanges()
Console.WriteLine("You set the price of product 1 to 1.01 and UnitsOnOrder to 11")
prod.UnitPrice = 1.01D
prod.UnitsOnOrder = 11D
Try
Console.WriteLine("You submit")
Console.WriteLine()
db.SubmitChanges()
Catch c As ChangeConflictException
WriteConflictDetails(db.ChangeConflicts) 'write changed objects / members to console
Console.WriteLine()
Console.WriteLine("Resolve by keeping changes")
db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges)
db.SubmitChanges()
End Try
Console.WriteLine()
Dim dbResult = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
Dim result = dbResult.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("Now product 1 has price={0}, UnitsInStock={1}, UnitsOnOrder={2}", _
result.UnitPrice, result.UnitsInStock, result.UnitsOnOrder)
Console.WriteLine()
ResetProducts() ' clean up
End Sub
Result:
You retrieve the product 1, it costs 18.0000
There are 39 units in stock, 0 units on order
Another user changes the price to 22.22 and UnitsInStock to 22
You set the price of product 1 to 1.01 and UnitsOnOrder to 11
You submit
Optimistic Concurrency Conflict in product 1
'UnitPrice' was updated from 18.0000 to 22.2200 while you updated it to 1.01
'UnitsInStock' was updated from 39 to 22, you did not change it.
Resolve by keeping changes
Now product 1 has price=1.0100, UnitsInStock=22, UnitsOnOrder=11
Demonstrates using MemberConflict.Resolve to write a custom resolve rule.
Public Sub LinqToSqlOptimistic05()
Dim otherUser_db = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
db.Log = Nothing
Dim prod = db.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("You retrieve the product 1, it costs {0}", prod.UnitPrice)
Console.WriteLine("There are {0} units in stock, {1} units on order", prod.UnitsInStock, prod.UnitsOnOrder)
Console.WriteLine()
Console.WriteLine("Another user changes the price to 22.22 and UnitsOnOrder to 2")
Dim otherUser_product = otherUser_db.Products.First(Function(p) p.ProductID = 1)
otherUser_product.UnitPrice = 22.22D
otherUser_product.UnitsOnOrder = 2
otherUser_db.SubmitChanges()
Console.WriteLine("You set the price of product 1 to 1.01 and UnitsOnOrder to 11")
prod.UnitPrice = 1.01D
prod.UnitsOnOrder = 11
Dim needsSubmit = True
While needsSubmit
Try
Console.WriteLine("You submit")
Console.WriteLine()
needsSubmit = False
db.SubmitChanges()
Catch c As ChangeConflictException
needsSubmit = True
WriteConflictDetails(db.ChangeConflicts) ' write changed objects / members to console
Console.WriteLine()
Console.WriteLine("Resolve by higher price / order")
For Each conflict In db.ChangeConflicts
conflict.Resolve(RefreshMode.KeepChanges)
For Each memConflict In conflict.MemberConflicts
If (memConflict.Member.Name = "UnitPrice") Then
'always use the highest price
Dim theirPrice = CDec(memConflict.DatabaseValue)
Dim yourPrice = CDec(memConflict.CurrentValue)
memConflict.Resolve(Math.Max(theirPrice, yourPrice))
ElseIf (memConflict.Member.Name = "UnitsOnOrder") Then
'always use higher order
Dim theirOrder = CShort(memConflict.DatabaseValue)
Dim yourOrder = CShort(memConflict.CurrentValue)
memConflict.Resolve(Math.Max(theirOrder, yourOrder))
End If
Next
Next
End Try
End While
Dim dbResult = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
Dim result = dbResult.Products.First(Function(p) p.ProductID = 1)
Console.WriteLine("Now product 1 has price={0}, UnitsOnOrder={1}", _
result.UnitPrice, result.UnitsOnOrder)
Console.WriteLine()
ResetProducts() 'clean up
End Sub
Result:
You retrieve the product 1, it costs 18.0000
There are 39 units in stock, 0 units on order
Another user changes the price to 22.22 and UnitsOnOrder to 2
You set the price of product 1 to 1.01 and UnitsOnOrder to 11
You submit
Optimistic Concurrency Conflict in product 1
'UnitPrice' was updated from 18.0000 to 22.2200 while you updated it to 1.01
'UnitsOnOrder' was updated from 0 to 2 while you updated it to 11
Resolve by higher price / order
You submit
Now product 1 has price=22.2200, UnitsOnOrder=11
Submit(FailOnFirstConflict) throws an Optimistic Concurrency Exception when the first conflict is detected. Only one exception is handled at a time, you have to submit for each conflict.
Public Sub LinqToSqlOptimistic06()
db.Log = Nothing
Dim otherUser_db = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
'you load 3 products
Dim prod() = db.Products.OrderBy(Function(p) p.ProductID).Take(3).ToArray()
For i = 0 To 2
Console.WriteLine("You retrieve the product {0}, it costs {1}", i + 1, prod(i).UnitPrice)
Next
'other user changes these products
Dim otherUserProd() = otherUser_db.Products.OrderBy(Function(p) p.ProductID).Take(3).ToArray()
For i = 0 To 2
Dim otherPrice = (i + 1) * 111.11D
Console.WriteLine("Other user changes the price of product {0} to {1}", i + 1, otherPrice)
otherUserProd(i).UnitPrice = otherPrice
Next
otherUser_db.SubmitChanges()
Console.WriteLine("Other user submitted changes")
'you change your loaded products
For i = 0 To 2
Dim yourPrice = (i + 1) * 1.01D
Console.WriteLine("You set the price of product {0} to {1}", i + 1, yourPrice)
prod(i).UnitPrice = yourPrice
Next
' submit
Dim needsSubmit = True
While needsSubmit
Try
Console.WriteLine("======= You submit with FailOnFirstConflict =======")
needsSubmit = False
db.SubmitChanges(ConflictMode.FailOnFirstConflict)
Catch c As ChangeConflictException
For Each conflict In db.ChangeConflicts
DescribeConflict(conflict) 'write changes to console
Console.WriteLine("Resolve conflict with KeepCurrentValues")
conflict.Resolve(RefreshMode.KeepCurrentValues)
Next
needsSubmit = True
End Try
End While
Dim dbResult = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
For i = 0 To 2
'Creating a temporary since this will be used in a lambda
Dim tmp = i
Dim result = dbResult.Products.First(Function(p) p.ProductID = tmp + 1)
Console.WriteLine("Now the product {0} has price {1}", i + 1, result.UnitPrice)
Next
ResetProducts() 'clean up
End Sub
c
Result:
You retrieve the product 1, it costs 18.0000
You retrieve the product 2, it costs 19.0000
You retrieve the product 3, it costs 10.0000
Other user changes the price of product 1 to 111.11
Other user changes the price of product 2 to 222.22
Other user changes the price of product 3 to 333.33
Other user submitted changes
You set the price of product 1 to 1.01
You set the price of product 2 to 2.02
You set the price of product 3 to 3.03
======= You submit with FailOnFirstConflict =======
Optimistic Concurrency Conflict in product 1
'UnitPrice' was updated from 18.0000 to 111.1100 while you updated it to 1.01
Resolve conflict with KeepCurrentValues
======= You submit with FailOnFirstConflict =======
Optimistic Concurrency Conflict in product 2
'UnitPrice' was updated from 19.0000 to 222.2200 while you updated it to 2.02
Resolve conflict with KeepCurrentValues
======= You submit with FailOnFirstConflict =======
Optimistic Concurrency Conflict in product 3
'UnitPrice' was updated from 10.0000 to 333.3300 while you updated it to 3.03
Resolve conflict with KeepCurrentValues
======= You submit with FailOnFirstConflict =======
Now the product 1 has price 1.0100
Now the product 2 has price 2.0200
Now the product 3 has price 3.0300
Submit(ContinueOnConflict) collects all concurrency conflicts and throws an exception when the last conflict is detected.\r\nAll conflicts are handled in one catch statement. It is still possible that another user updated the same objects before this update, so it is possible that another Optimistic Concurrency Exception is thrown which would need to be handled again.
Public Sub LinqToSqlOptimistic07()
db.Log = Nothing
Dim otherUser_db = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
' you load 3 products
Dim prod() = db.Products.OrderBy(Function(p) p.ProductID).Take(3).ToArray()
For i = 0 To 2
Console.WriteLine("You retrieve the product {0}, it costs {1}", i + 1, prod(i).UnitPrice)
Next
' other user changes these products
Dim otherUserProd() = otherUser_db.Products.OrderBy(Function(p) p.ProductID).Take(3).ToArray()
For i = 0 To 2
Dim otherPrice = (i + 1) * 111.11D
Console.WriteLine("Other user changes the price of product {0} to {1}", i + 1, otherPrice)
otherUserProd(i).UnitPrice = otherPrice
Next
otherUser_db.SubmitChanges()
Console.WriteLine("Other user submitted changes")
' you change your loaded products
For i = 0 To 2
Dim yourPrice = (i + 1) * 1.01D
Console.WriteLine("You set the price of product {0} to {1}", i + 1, yourPrice)
prod(i).UnitPrice = yourPrice
Next
' submit
Dim needsSubmit = True
While needsSubmit
Try
Console.WriteLine("======= You submit with ContinueOnConflict =======")
needsSubmit = False
db.SubmitChanges(ConflictMode.ContinueOnConflict)
Catch c As ChangeConflictException
For Each conflict In db.ChangeConflicts
DescribeConflict(conflict) ' write changes to console
Console.WriteLine("Resolve conflict with KeepCurrentValues")
conflict.Resolve(RefreshMode.KeepCurrentValues)
Next
needsSubmit = True
End Try
End While
Dim dbResult = New NorthwindDataContext(My.Settings.NORTHWINDConnectionString1)
For i = 0 To 2
Dim tmp = i
Dim result = dbResult.Products.First(Function(p) p.ProductID = tmp + 1)
Console.WriteLine("Now the product {0} has price {1}", i + 1, result.UnitPrice)
Next
ResetProducts() 'clean up
End Sub
Result:
You retrieve the product 1, it costs 18.0000
You retrieve the product 2, it costs 19.0000
You retrieve the product 3, it costs 10.0000
Other user changes the price of product 1 to 111.11
Other user changes the price of product 2 to 222.22
Other user changes the price of product 3 to 333.33
Other user submitted changes
You set the price of product 1 to 1.01
You set the price of product 2 to 2.02
You set the price of product 3 to 3.03
======= You submit with ContinueOnConflict =======
Optimistic Concurrency Conflict in product 1
'UnitPrice' was updated from 18.0000 to 111.1100 while you updated it to 1.01
Resolve conflict with KeepCurrentValues
Optimistic Concurrency Conflict in product 2
'UnitPrice' was updated from 19.0000 to 222.2200 while you updated it to 2.02
Resolve conflict with KeepCurrentValues
Optimistic Concurrency Conflict in product 3
'UnitPrice' was updated from 10.0000 to 333.3300 while you updated it to 3.03
Resolve conflict with KeepCurrentValues
======= You submit with ContinueOnConflict =======
Now the product 1 has price 1.0100
Now the product 2 has price 2.0200
Now the product 3 has price 3.0300
