locked
table update - best practice approach RRS feed

  • Question

  • Hi there, 

    Is there a best practice approach as to how to handle queries from databases, display the data on a form and then later issue an update to the database and ensure that you are not overlaying a change made by another client. 

    Because of some certain complexities in my application, (an maybe some limited expertise with data adapters and such) I am using old style SQL calls straight from the code in some cases. 

    So, for those cases where I am usinging Old style SQL Calls for reads and then subsiquent updates, does anyone have any best practice examples for how to ensure data integrity?

    I'd appreciate your input. Thanks! - Eric -  
    Do unto others as you would have them do unto you!
    Friday, July 11, 2008 10:09 PM

Answers

  • Hi Eric,

    As you, I personaly prefer to use direct sql commands to update information into the database, however I want to explain both scenarios:

    If you see the table adapter's UpdateCommand.CommandText, you can see after setting the values, it adds a WHERE field1=@originalField1 AND field2=@originalField2, and so on for each column. This enables to handle any concurrency conflict, so that if another user changed the same record, the UPDATE sentence will not write anything, because it can't found a matching record to the original getted.

    In the other hand if you don't want to use table adapters, you can add TimeStamp column on each table, so in your UPDATE or DELETE commands you always refer to the timestamp value you get when queried for the data from the server. SQL Server handles automatically the TimeStamp value when any change to the record were made.

    The important here in both cases, is how do you want to solve the concurrency conflict when it comes? More than a "best practice" of handling it, depends on your bussiness logic, even in a same application each table concurrency resolution should be different, depending the latency and importance of the table.

    Hope it helps, regards
    Work smarter...no harder!
    Friday, July 11, 2008 11:06 PM

All replies

  • Hi Eric,

    As you, I personaly prefer to use direct sql commands to update information into the database, however I want to explain both scenarios:

    If you see the table adapter's UpdateCommand.CommandText, you can see after setting the values, it adds a WHERE field1=@originalField1 AND field2=@originalField2, and so on for each column. This enables to handle any concurrency conflict, so that if another user changed the same record, the UPDATE sentence will not write anything, because it can't found a matching record to the original getted.

    In the other hand if you don't want to use table adapters, you can add TimeStamp column on each table, so in your UPDATE or DELETE commands you always refer to the timestamp value you get when queried for the data from the server. SQL Server handles automatically the TimeStamp value when any change to the record were made.

    The important here in both cases, is how do you want to solve the concurrency conflict when it comes? More than a "best practice" of handling it, depends on your bussiness logic, even in a same application each table concurrency resolution should be different, depending the latency and importance of the table.

    Hope it helps, regards
    Work smarter...no harder!
    Friday, July 11, 2008 11:06 PM
  • Please check this thread for reference:
    http://forums.msdn.microsoft.com/en-US/vbide/thread/6e44c506-974f-49de-a694-88178f0c97d2
    Show four methods to making simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save).
    Friday, July 18, 2008 1:21 PM