| Groups | Blog | Home |
dotnet ado.net : Handling concurrency violations
My mind is not wrapped around what you are trying to do, so I apologize.
In the DataSet, which is, underlying, an XML document, you have the original row, the changed row, etc. You can query the different versions to find out what has changed. That is how .NET determines what to update and insert. As for concurrency, you can query a DataSet for the error rows after you attempt to update. My suggestion is look for Dino Esposito's works on concurrency. A google search using site:msdn.microsoft.com and Dino Esposito and concurrency should yield something. You might have to search a bit, but you should find his work. It is a very good pattern. -- Gregory A. Beamer MVP, MCP: +I, SE, SD, DBA Subscribe to my blog http://gregorybeamer.spaces.live.com/lists/feed.rss or just read it: http://gregorybeamer.spaces.live.com/ ************************************************* | Think outside the box! | ************************************************* [quoted text, click to view] "John" <info@nospam.infovis.co.uk> wrote in message news:uXHlxUujIHA.5820@TK2MSFTNGP04.phx.gbl... > Hi > > I have developed the following logic to handle db concurrency violations. > I just wonder if someone can tell me if it is correct or if I need a > different approach.Would love to know how pros handle it. > > Thanks > > Regards > > > Dim dc As DataColumn > Dim drCache As DataRow > Dim drCurrent As DataRow > > Try > ' Attempt the update > daContacts.Update(ds.Contacts) > > Catch Ex As DBConcurrencyException > > ' First - cache the row > drCache = ds.Contacts.NewRow() > For Each dc In ds.Contacts.Columns > If Not dc.ReadOnly Then > drCache(dc.ColumnName) = Ex.Row(dc.ColumnName) > End If > Next > > ' Refresh from database > daContacts.Fill(ds.Contacts) > > ' Position to the faulted row > drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID")) > > ' Apply User Changes > For Each dc In ds.Contacts.Columns > If Not dc.ReadOnly Then > drCurrent(dc.ColumnName) = drCache(dc.ColumnName) > End If > Next > > ' Save again > daContacts.Update(ds.Contacts) > End Try >
Hi
I have developed the following logic to handle db concurrency violations. I just wonder if someone can tell me if it is correct or if I need a different approach.Would love to know how pros handle it. Thanks Regards Dim dc As DataColumn Dim drCache As DataRow Dim drCurrent As DataRow Try ' Attempt the update daContacts.Update(ds.Contacts) Catch Ex As DBConcurrencyException ' First - cache the row drCache = ds.Contacts.NewRow() For Each dc In ds.Contacts.Columns If Not dc.ReadOnly Then drCache(dc.ColumnName) = Ex.Row(dc.ColumnName) End If Next ' Refresh from database daContacts.Fill(ds.Contacts) ' Position to the faulted row drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID")) ' Apply User Changes For Each dc In ds.Contacts.Columns If Not dc.ReadOnly Then drCurrent(dc.ColumnName) = drCache(dc.ColumnName) End If Next ' Save again daContacts.Update(ds.Contacts) End Try
John,
It looks like your approach to concurrency handling is to get around the concurrency error by reloading the data from the database and then applying your changes. You might as well just turn off concurrency checking, since that is the end result anyway. In other words, it looks like your code is just avoiding concurrency exceptions, not handling concurrency conflicts. Kerry Moorman [quoted text, click to view] "John" wrote:
> Hi > > I have developed the following logic to handle db concurrency violations. I > just wonder if someone can tell me if it is correct or if I need a > different approach.Would love to know how pros handle it. > > Thanks > > Regards > > > Dim dc As DataColumn > Dim drCache As DataRow > Dim drCurrent As DataRow > > Try > ' Attempt the update > daContacts.Update(ds.Contacts) > > Catch Ex As DBConcurrencyException > > ' First - cache the row > drCache = ds.Contacts.NewRow() > For Each dc In ds.Contacts.Columns > If Not dc.ReadOnly Then > drCache(dc.ColumnName) = Ex.Row(dc.ColumnName) > End If > Next > > ' Refresh from database > daContacts.Fill(ds.Contacts) > > ' Position to the faulted row > drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID")) > > ' Apply User Changes > For Each dc In ds.Contacts.Columns > If Not dc.ReadOnly Then > drCurrent(dc.ColumnName) = drCache(dc.ColumnName) > End If > Next > > ' Save again > daContacts.Update(ds.Contacts) > End Try > >
John,
You need to decide how you want to handle concurrency issues in a particular application. For example, how do you want to handle this situation: Mary loads your employee record from the Employees table into the Employee Manager application. She loaded your record because your cell phone number has changed and she is updating that piece of information in your record. While Mary is working on your record, Jim loads your employee record from the Employees table into the Employee Manager application. He loaded your record because you have just gotten a big raise and he is updating that piece of information in your record. He changes your salary and updates your record in the database. Mary has now finished changing your cell phone number and attempts to update your record in the database. Do you want Mary's update to succeed? If it does you have just lost your raise because her update will overwrite your new salary that Jim entered with your old salary as it was when Mary loaded your record. Do you want Mary's update to fail because of a concurrency conflict? When it fails, how do you want to deal with the conflict? Your current code appears to read the updated record and then apply the old values to it. This gets rid of the concurrency conflict but you still end up with your old salary instead of your raise. My choice would be to inform the user that the record has been changed by another user and let the user reload the record and start over. Kerry Moorman [quoted text, click to view] "John" wrote:
> So Kerry how do I handle it? which was my original question. > > Regards > > > "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message > news:31F1C265-0B1C-40DB-8174-3A5F6DA4DD8F@microsoft.com... > > John, > > > > It looks like your approach to concurrency handling is to get around the > > concurrency error by reloading the data from the database and then > > applying > > your changes. > > > > You might as well just turn off concurrency checking, since that is the > > end > > result anyway. > > > > In other words, it looks like your code is just avoiding concurrency > > exceptions, not handling concurrency conflicts. > > > > Kerry Moorman > > > > "John" wrote: > > > >> Hi > >> > >> I have developed the following logic to handle db concurrency violations. > >> I > >> just wonder if someone can tell me if it is correct or if I need a > >> different approach.Would love to know how pros handle it. > >> > >> Thanks > >> > >> Regards > >> > >> > >> Dim dc As DataColumn > >> Dim drCache As DataRow > >> Dim drCurrent As DataRow > >> > >> Try > >> ' Attempt the update > >> daContacts.Update(ds.Contacts) > >> > >> Catch Ex As DBConcurrencyException > >> > >> ' First - cache the row > >> drCache = ds.Contacts.NewRow() > >> For Each dc In ds.Contacts.Columns > >> If Not dc.ReadOnly Then > >> drCache(dc.ColumnName) = Ex.Row(dc.ColumnName) > >> End If > >> Next > >> > >> ' Refresh from database > >> daContacts.Fill(ds.Contacts) > >> > >> ' Position to the faulted row > >> drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID")) > >> > >> ' Apply User Changes > >> For Each dc In ds.Contacts.Columns > >> If Not dc.ReadOnly Then > >> drCurrent(dc.ColumnName) = drCache(dc.ColumnName) > >> End If > >> Next > >> > >> ' Save again > >> daContacts.Update(ds.Contacts) > >> End Try > >> > >> > >> > >
So Kerry how do I handle it? which was my original question.
Regards [quoted text, click to view] "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message news:31F1C265-0B1C-40DB-8174-3A5F6DA4DD8F@microsoft.com... > John, > > It looks like your approach to concurrency handling is to get around the > concurrency error by reloading the data from the database and then > applying > your changes. > > You might as well just turn off concurrency checking, since that is the > end > result anyway. > > In other words, it looks like your code is just avoiding concurrency > exceptions, not handling concurrency conflicts. > > Kerry Moorman > > "John" wrote: > >> Hi >> >> I have developed the following logic to handle db concurrency violations. >> I >> just wonder if someone can tell me if it is correct or if I need a >> different approach.Would love to know how pros handle it. >> >> Thanks >> >> Regards >> >> >> Dim dc As DataColumn >> Dim drCache As DataRow >> Dim drCurrent As DataRow >> >> Try >> ' Attempt the update >> daContacts.Update(ds.Contacts) >> >> Catch Ex As DBConcurrencyException >> >> ' First - cache the row >> drCache = ds.Contacts.NewRow() >> For Each dc In ds.Contacts.Columns >> If Not dc.ReadOnly Then >> drCache(dc.ColumnName) = Ex.Row(dc.ColumnName) >> End If >> Next >> >> ' Refresh from database >> daContacts.Fill(ds.Contacts) >> >> ' Position to the faulted row >> drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID")) >> >> ' Apply User Changes >> For Each dc In ds.Contacts.Columns >> If Not dc.ReadOnly Then >> drCurrent(dc.ColumnName) = drCache(dc.ColumnName) >> End If >> Next >> >> ' Save again >> daContacts.Update(ds.Contacts) >> End Try >> >> >>
Hi Kerry
Many thanks for this insight. Is there an easy way to display both new and old row values to the user to let him/her select? Thanks Regards [quoted text, click to view] "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message news:287D48C5-72D5-4A42-A59F-211AF9599F66@microsoft.com... > John, > > You need to decide how you want to handle concurrency issues in a > particular > application. > > For example, how do you want to handle this situation: > > Mary loads your employee record from the Employees table into the Employee > Manager application. She loaded your record because your cell phone number > has changed and she is updating that piece of information in your record. > > While Mary is working on your record, Jim loads your employee record from > the Employees table into the Employee Manager application. He loaded your > record because you have just gotten a big raise and he is updating that > piece > of information in your record. He changes your salary and updates your > record > in the database. > > Mary has now finished changing your cell phone number and attempts to > update > your record in the database. > > Do you want Mary's update to succeed? If it does you have just lost your > raise because her update will overwrite your new salary that Jim entered > with > your old salary as it was when Mary loaded your record. > > Do you want Mary's update to fail because of a concurrency conflict? When > it > fails, how do you want to deal with the conflict? > > Your current code appears to read the updated record and then apply the > old > values to it. This gets rid of the concurrency conflict but you still end > up > with your old salary instead of your raise. > > My choice would be to inform the user that the record has been changed by > another user and let the user reload the record and start over. > > Kerry Moorman > > "John" wrote: > >> So Kerry how do I handle it? which was my original question. >> >> Regards >> >> >> "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message >> news:31F1C265-0B1C-40DB-8174-3A5F6DA4DD8F@microsoft.com... >> > John, >> > >> > It looks like your approach to concurrency handling is to get around >> > the >> > concurrency error by reloading the data from the database and then >> > applying >> > your changes. >> > >> > You might as well just turn off concurrency checking, since that is the >> > end >> > result anyway. >> > >> > In other words, it looks like your code is just avoiding concurrency >> > exceptions, not handling concurrency conflicts. >> > >> > Kerry Moorman >> > >> > "John" wrote: >> > >> >> Hi >> >> >> >> I have developed the following logic to handle db concurrency >> >> violations. >> >> I >> >> just wonder if someone can tell me if it is correct or if I need a >> >> different approach.Would love to know how pros handle it. >> >> >> >> Thanks >> >> >> >> Regards >> >> >> >> >> >> Dim dc As DataColumn >> >> Dim drCache As DataRow >> >> Dim drCurrent As DataRow >> >> >> >> Try >> >> ' Attempt the update >> >> daContacts.Update(ds.Contacts) >> >> >> >> Catch Ex As DBConcurrencyException >> >> >> >> ' First - cache the row >> >> drCache = ds.Contacts.NewRow() >> >> For Each dc In ds.Contacts.Columns >> >> If Not dc.ReadOnly Then >> >> drCache(dc.ColumnName) = Ex.Row(dc.ColumnName) >> >> End If >> >> Next >> >> >> >> ' Refresh from database >> >> daContacts.Fill(ds.Contacts) >> >> >> >> ' Position to the faulted row >> >> drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID")) >> >> >> >> ' Apply User Changes >> >> For Each dc In ds.Contacts.Columns >> >> If Not dc.ReadOnly Then >> >> drCurrent(dc.ColumnName) = drCache(dc.ColumnName) >> >> End If >> >> Next >> >> >> >> ' Save again >> >> daContacts.Update(ds.Contacts) >> >> End Try >> >> >> >> >> >> >> >> >>
John,
I don't display both new and old values to the user, so I don't have any code examples. However, this book has a section on just that topic. I can highly recommend this book in general: Programming Microsoft ADO.NET 2.0 Core Reference by David Sceppa Kerry Moorman [quoted text, click to view] "John" wrote:
> Hi Kerry > > Many thanks for this insight. Is there an easy way to display both new and > old row values to the user to let him/her select? > > Thanks > > Regards > > "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message > news:287D48C5-72D5-4A42-A59F-211AF9599F66@microsoft.com... > > John, > > > > You need to decide how you want to handle concurrency issues in a > > particular > > application. > > > > For example, how do you want to handle this situation: > > > > Mary loads your employee record from the Employees table into the Employee > > Manager application. She loaded your record because your cell phone number > > has changed and she is updating that piece of information in your record. > > > > While Mary is working on your record, Jim loads your employee record from > > the Employees table into the Employee Manager application. He loaded your > > record because you have just gotten a big raise and he is updating that > > piece > > of information in your record. He changes your salary and updates your > > record > > in the database. > > > > Mary has now finished changing your cell phone number and attempts to > > update > > your record in the database. > > > > Do you want Mary's update to succeed? If it does you have just lost your > > raise because her update will overwrite your new salary that Jim entered > > with > > your old salary as it was when Mary loaded your record. > > > > Do you want Mary's update to fail because of a concurrency conflict? When > > it > > fails, how do you want to deal with the conflict? > > > > Your current code appears to read the updated record and then apply the > > old > > values to it. This gets rid of the concurrency conflict but you still end > > up > > with your old salary instead of your raise. > > > > My choice would be to inform the user that the record has been changed by > > another user and let the user reload the record and start over. > > > > Kerry Moorman > > > > "John" wrote: > > > >> So Kerry how do I handle it? which was my original question. > >> > >> Regards > >> > >> > >> "Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message > >> news:31F1C265-0B1C-40DB-8174-3A5F6DA4DD8F@microsoft.com... > >> > John, > >> > > >> > It looks like your approach to concurrency handling is to get around > >> > the > >> > concurrency error by reloading the data from the database and then > >> > applying > >> > your changes. > >> > > >> > You might as well just turn off concurrency checking, since that is the > >> > end > >> > result anyway. > >> > > >> > In other words, it looks like your code is just avoiding concurrency > >> > exceptions, not handling concurrency conflicts. > >> > > >> > Kerry Moorman > >> > > >> > "John" wrote: > >> > > >> >> Hi > >> >> > >> >> I have developed the following logic to handle db concurrency > >> >> violations. > >> >> I > >> >> just wonder if someone can tell me if it is correct or if I need a > >> >> different approach.Would love to know how pros handle it. > >> >> > >> >> Thanks > >> >> > >> >> Regards > >> >> > >> >> > >> >> Dim dc As DataColumn > >> >> Dim drCache As DataRow > >> >> Dim drCurrent As DataRow > >> >> > >> >> Try > >> >> ' Attempt the update > >> >> daContacts.Update(ds.Contacts) > >> >> > >> >> Catch Ex As DBConcurrencyException > >> >> > >> >> ' First - cache the row > >> >> drCache = ds.Contacts.NewRow() > >> >> For Each dc In ds.Contacts.Columns > >> >> If Not dc.ReadOnly Then > >> >> drCache(dc.ColumnName) = Ex.Row(dc.ColumnName) > >> >> End If > >> >> Next > >> >> > >> >> ' Refresh from database > >> >> daContacts.Fill(ds.Contacts) > >> >> > >> >> ' Position to the faulted row > >> >> drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID")) > >> >> > >> >> ' Apply User Changes > >> >> For Each dc In ds.Contacts.Columns > >> >> If Not dc.ReadOnly Then > >> >> drCurrent(dc.ColumnName) = drCache(dc.ColumnName) > >> >> End If > >> >> Next > >> >> > >> >> ' Save again > >> >> daContacts.Update(ds.Contacts) > >> >> End Try > >> >> > >> >> > >> >> > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |