none
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. RRS feed

  • Question

  • I'm migrating a MySql database to Sql server 2005.

    I've used ODBC to transfer most of the tables across using "select * into xxx from openquery" samples within Sql Server management , but the MySql LONGTEXT columns in several tables cause this to fail on tables as that data type isnt supported by sql server.

    So, I migrated all non failing columns to create the tables, then added nText columns to replace the LONGTEXT.

    Next Ive written a small utility to read in the string columns from mysql then write them out again to sql server database.

    The function that updates the column is found below.
    This works great if all rows can be matched in the target database table, but when I deleted one of the rows in the target then I received the exception "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
    How do I handle this error please?

        /// <summary>
        /// Updates the column in the target table with the values held in the source dataset
        /// </summary>
        /// <param name="dataset">source dataset containing 1 table with the copied values</param>
        /// <param name="tableName">name of table in target database</param>
        /// <param name="columnName">column to be copied over (source == target)</param>
        /// <param name="keyFields">Key fields required to find the correct record in the table</param>
        /// <returns>Number of records affected</returns>
        public int UpdateFrom(DataSet dataset, string tableName, string columnName, params string[] keyFields)
        {
          if ((keyFields.Length == 0)||(dataset.Tables.Count == 0))
            return 0;

          // force each row to appear as if it's changed (grrr till i find correct method of doing this)
          // otherwise it appears unchanged and wont update
          foreach (DataRow row in dataset.Tables[0].Rows)
            row[columnName] = row[columnName];

          const string sqlFormat = "UPDATE {0} SET {1} = @{1} WHERE ({2})";

          // Add key fields to the where clause
          string whereClause = string.Empty;
          foreach (string keyField in keyFields)
            whereClause += string.Format("{0} = @{0} AND ", keyField);
          whereClause = whereClause.Substring(0, whereClause.Length - 5);
         
          // Format the sql string eg "UPDATE emaillog SET Information = @Information
          // WHERE (recid = @recid AND fk_sites_recid = @fk_sites_recid)"
          string sql = string.Format(sqlFormat, tableName, columnName, whereClause);

          using (IDbConnection connection = GetIDbConnection())
          {
            using (IDbCommand command = GetIDbCommand(sql, connection))
            {
              IDbDataParameter param = command.CreateParameter();
              param.ParameterName = string.Format("@{0}", columnName);
              param.SourceColumn = columnName;
              command.Parameters.Add(param);

              foreach(string keyField in keyFields)
              {
                param = command.CreateParameter();
                param.ParameterName = string.Format("@{0}", keyField);
                param.SourceColumn = keyField;
                command.Parameters.Add(param);
              }
             
              IDbDataAdapter adapter = GetDataAdapter();
              adapter.UpdateCommand = command;
              return adapter.Update(dataset);
            }
          }
        }


    Tuesday, August 3, 2010 11:53 AM

Answers

  • As you know, ADO.NET works in disconnected environment. Means one you fetch data from Database, it is possible that someone else (different user) can modify original data source. When you apply your changes back to the DB using an update command, and provider finds this difference in row, you will get concurrency exception. ADO.NET provides different ways to handle this situation.

    In general, there are three common ways to manage concurrency in a database:

    Pessimistic concurrency control: A row is unavailable to users from the time the record is fetched until it is updated in the database.

    Optimistic concurrency control: A row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.

    "Last in wins": A row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.

    Please continue reading here : http://msdn.microsoft.com/en-us/library/cs6hb8k4(VS.80).aspx

    http://msdn.microsoft.com/en-us/magazine/cc163924.aspx

     

    • Marked as answer by Czeshirecat2 Monday, August 16, 2010 6:55 AM
    Tuesday, August 3, 2010 12:36 PM
  • Hi Czeshirecat2,

     

    When you get a Concurrency Violation it indicates that the data in the database and data in the datatable are not synchronized. For example, the row in the datatable that you're updating no longer exists in the database, or the row’s value in the database has already been changed by another user which conficts with the original value of the row in datatable. You mentioned in your original post that “when I deleted one of the rows in the target then I received the exception "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."” , so I think that the reason why you got this exception is probably that the row in the datatable you are going to update no longer exists in the database. Please consider the necessity to delete that row in the target database, or if you delete it from the database, then do not try to update that row or delete it from the data table too.

     

    Mike_999 has provide you two very good links which talk about handling the Concurrency Violation. And you can also refer to the following thread to get some information about Concurrency violation handling in data adapter.

    http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/bb17dad8-72f2-4dc3-8472-e9f710ef814b

     

     

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Czeshirecat2 Monday, August 16, 2010 6:55 AM
    Wednesday, August 4, 2010 6:21 AM
    Moderator

All replies

  • As you know, ADO.NET works in disconnected environment. Means one you fetch data from Database, it is possible that someone else (different user) can modify original data source. When you apply your changes back to the DB using an update command, and provider finds this difference in row, you will get concurrency exception. ADO.NET provides different ways to handle this situation.

    In general, there are three common ways to manage concurrency in a database:

    Pessimistic concurrency control: A row is unavailable to users from the time the record is fetched until it is updated in the database.

    Optimistic concurrency control: A row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.

    "Last in wins": A row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.

    Please continue reading here : http://msdn.microsoft.com/en-us/library/cs6hb8k4(VS.80).aspx

    http://msdn.microsoft.com/en-us/magazine/cc163924.aspx

     

    • Marked as answer by Czeshirecat2 Monday, August 16, 2010 6:55 AM
    Tuesday, August 3, 2010 12:36 PM
  • Hi Czeshirecat2,

     

    When you get a Concurrency Violation it indicates that the data in the database and data in the datatable are not synchronized. For example, the row in the datatable that you're updating no longer exists in the database, or the row’s value in the database has already been changed by another user which conficts with the original value of the row in datatable. You mentioned in your original post that “when I deleted one of the rows in the target then I received the exception "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."” , so I think that the reason why you got this exception is probably that the row in the datatable you are going to update no longer exists in the database. Please consider the necessity to delete that row in the target database, or if you delete it from the database, then do not try to update that row or delete it from the data table too.

     

    Mike_999 has provide you two very good links which talk about handling the Concurrency Violation. And you can also refer to the following thread to get some information about Concurrency violation handling in data adapter.

    http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/bb17dad8-72f2-4dc3-8472-e9f710ef814b

     

     

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Czeshirecat2 Monday, August 16, 2010 6:55 AM
    Wednesday, August 4, 2010 6:21 AM
    Moderator
  • Hi Czeshirecat2, 

    I'm writing to follow up the post. Does the above suggestion work?

    Please feel free to let me know if you need any help.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 16, 2010 2:32 AM
    Moderator
  • Hi and thank you both for answering.

    I did read the article, and understand why it failed.

    I was probably going to argue that kicking a full batch of records because of a single failure might be over-enthusiastic until I thought about what circumstances I might want to use an update query on many records like this.

    If I were running an update for a single record using key fields to find it, my execute would return the number of records affected so i'd be able to handle it if a zero was returned. A batch couldn't.

     

    Monday, August 16, 2010 6:51 AM