none
DataContext.SubmitChanges and concurrency issue. RRS feed

  • Question

  • Hi

    When I call method DataContext.SubmitChanges all changes are saved in database.

    1.

    How is constructed SQL update command? It uses only PK in where part all it uses all columns in where part (optimistic concurrency)?

    2.

    How I could add notification to that user that row that we want update has been already updated by another user and because we have old version of this row we cannot do updated?

    Regards


    kicaj
    Thursday, January 27, 2011 5:43 PM

Answers

  • Add a column of type timestamp on each of your tables. The value will automatically be populated by the database on creation and change. If an change occurs with an old timestamp and error is throw, which you can catch.
    • Marked as answer by kicaj Monday, January 31, 2011 5:15 PM
    Monday, January 31, 2011 2:54 PM

All replies

  • Ok I figured out that I can use overloaded version of SubmitChanges with parameters

    ConflictMode .ContinueOnConflict and ConflictMode .FailOnFirstConflict.

     

    I tested this and I have very weird behavior for ContinueOnConflict.

     

    I have only two rows in my table in database.

     

    I select this rows using LINQ to SQL and I change both these rows in C#.

    Before I call SubmitChanges( ConflictMode .ContinueOnConflict) I change first row in database.

     

    When I call SubmitChanges( ConflictMode .ContinueOnConflict) I get exception ChangeConflictException with message: "1 of 2 updates failed." and this is ok.

    But I checked my database by Management Studio I do not see any changes. Why? It looks like a bug in LINQ.

     

    I have additional question: how can I check with row has been updated successfully and which row has not been updated successfully? Because this message “1 of 2…” is very general so user cannot figure out what has been really changed and what not.

     

    I use SQL Server Express 2008 R2.

     

     


    kicaj
    Thursday, January 27, 2011 8:31 PM
  • Nobody knows answer?


    kicaj
    Sunday, January 30, 2011 12:13 PM
  • Hello kicaj,

     

    Thanks for your question.

    According to your description, I think there are two common reasons:

    (1) You are calling AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter. AcceptChanges will commit all changes made since the data was loaded or since AcceptChanges was last called. This means that if you had a row that was marked as Modified, it will now be Unchanged. When you call Update, it will not recognize that the row has changes that need to be submitted to the database. The same applies for inserted and deleted rows as well. For more information, see the AcceptChanges documentation: http://msdn2.microsoft.com/en-us/library/system.data.dataset.acceptchanges.aspx.

    This applies to all database backends and application types (ASP.NET, Windows Forms, etc).

    (2) The other common problem is that in Visual Studio, there is an option to copy your database file to the output folder of your project. Usually this is done with an Access MDB file, or a SQL Server MDF file, when you add the database file to your project.

     

    More information, See this:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/316380ba-ceaf-493a-a54c-1f978b6a1322

     

    I hope it can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 31, 2011 3:31 AM
    Moderator
  • I do not use DataSet, I use LINQ to SQL - dbml file.

    I think you have not read my replay that starts from "Ok I figured out that I can use overloaded..."


    kicaj
    Monday, January 31, 2011 10:01 AM
  • Add a column of type timestamp on each of your tables. The value will automatically be populated by the database on creation and change. If an change occurs with an old timestamp and error is throw, which you can catch.
    • Marked as answer by kicaj Monday, January 31, 2011 5:15 PM
    Monday, January 31, 2011 2:54 PM
  • Additional question:

    How can I check how exactly look SQL updates that are executed during calling SubmitChanges?

    Is there any monitor of updates in SQL Server?


    kicaj
    Monday, January 31, 2011 5:15 PM
  • Nobody knows answer described in third post (this about  ConflictMode .ContinueOnConflict, "1 of 2 updates failed." )?

     


    kicaj
    Wednesday, February 2, 2011 3:09 PM
  • Nobody knows answer described in third post (this about  ConflictMode .ContinueOnConflict, "1 of 2 updates failed." )?
    kicaj
    Wednesday, February 2, 2011 3:10 PM
  • never is too late!

    Use this code to see the conflict details.

    try
    {
                Context.SubmitChanges();
    }catch (Exception e)
    {
                var c = Context.ChangeConflicts;

    }

    Then look into the c variable to watch the conflicts details.

    Friday, June 29, 2012 7:25 PM