none
Concurrency exception thrown on simple save delet save in tableAdapterManager

    Question

  • I get concurrency issues using the tableAdapterManager before I have writen any code.  Can someone help with what I am missing.

    Here are the steps to reproduce problem.

    1) Create a new Windows Forms application

    2) create a new SQL Express local database

    3) add a primare key (int, auto increment), and a varchar.

    4) use the wizard in data sources to create a DataSet

    5) drag the varchar field into your form.  (This creates a DataBindingSource, DataTableAdapter, and AdapterManager)

    6) Run the code.   press + (new) twice.  Press save. press delete. then press save again.

    This will create a concurrancy issue everytime.

    Can anyone help me with methods to fix this.  (My actually program is quite a bit more complex but this is the issue I'm having in it.)

    -Jon


    Jon Nicponski
    Thursday, November 11, 2010 8:35 PM

Answers

  • Hi jonnic,

     

    I am able to reproduce the problem now. The reason for this concurrancy issue is:

     

    When Visual Studio generates the insert commands on a SQL-Server TableAdapter it looks to see whether the table’s primary keys are auto-generated (identity columns) and if so, Visual Studio will write an additional statement to retrieve the key using the SCOPE_IDENTITY functionality of SQL Server. When in the DataSet designer, if you look at the insert statement in the properties window for the SQL TableAdapter you will see two statements separated by a semi-colon:

     

    INSERT INTO [dbo].[Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);

    SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE (ProductID = SCOPE_IDENTITY())

     

    SQL Server supports batch statements through ADO.NET commands so this will populate the primary key back in the DataRow inside the DataSet as each row is inserted into the database."

    However, unlike Microsoft SQL Server, SQL Server Compact Edition does not process statements in batches. SQL Server Compact Edition processes the statements one at a time and executes each statement individually. So unlike SQL Server, we can't issue T-SQL statements where we can do an insert into a table with identity and fetch back the newly created identity value through the same statement.

     

    When you create a local database (SQL Server Compact Edition), press the add button(+) twice and click the save button, two new records will be saved to the database, but the newly created identity value will not be populated back to the DataSet, and you will find that the new primary key values in DataSet are -1, -2.  After that, when you click the delete button, it will try to delete the record whose primary key is -2, however, there is no record in the database whose primary key is -2, so the concurrancy exception occurs. 

     

    You need to write a bit of code yourself to fetch back the newly created identity value into the DataSet. You can refer to the following article which contains the step by step method and complete code about how to do this(focus on the “Adding Code to Handle Inserts to SQL CE Databases” part).

    http://blogs.msdn.com/b/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

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

    Friday, November 12, 2010 6:37 AM
    Moderator
  • Hi Jon,

    On the DataSet designer, set the primare key column's ReadOnly property to false and try the following code:

    public partial class Form1 : Form
    {
      public Form1()
      {
        InitializeComponent();
      }
    
      private void table_1BindingNavigatorSaveItem_Click(object sender, EventArgs e)
      {
        this.Validate();
        this.table_1BindingSource.EndEdit();
        this.tableAdapterManager.UpdateAll(this.database1DataSet);
      }
    
      private void Form1_Load(object sender, EventArgs e)
      {
        // TODO: This line of code loads data into the 'database1DataSet.Table_1' table. You can move, or remove it, as needed.
        this.table_1TableAdapter.Fill(this.database1DataSet.Table_1);
        this.table_1TableAdapter.HookUpEvents();      
      }
    }
    
    public class SQLCEIDHelper
    {
      public static void SetPrimaryKey(SqlCeTransaction trans, SqlCeRowUpdatedEventArgs e)
      {
        // If this is an INSERT operation...
        if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
        {
          System.Data.DataColumn[] pk = e.Row.Table.PrimaryKey;
          // and a primary key PK column exists...
          if (pk != null && pk.Length == 1)
          {
            //Set up the post-update query to fetch new @@Identity
            SqlCeCommand cmdGetIdentity = new SqlCeCommand("SELECT @@IDENTITY", (SqlCeConnection)trans.Connection, trans);
    
            //Execute the command and set the result identity value to the PK
            e.Row[pk[0]] = Convert.ToInt32(cmdGetIdentity.ExecuteScalar());
            e.Row.AcceptChanges();
          }
        }
      }
    }
    
    namespace Database1DataSetTableAdapters 
    {
      public partial class Table_1TableAdapter
      {
        public void HookUpEvents()
        {
          this._adapter.RowUpdated += new SqlCeRowUpdatedEventHandler(_adapter_RowUpdated);
        }
        
        private void _adapter_RowUpdated(object sender, SqlCeRowUpdatedEventArgs e)
        {
          SQLCEIDHelper.SetPrimaryKey(this.Transaction, e);
        }
      }
    }
    
    

     

    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, November 15, 2010 4:34 AM
    Moderator

All replies

  • Hi Jon,

    I made a project with the steps you provide in VS2010, unfortunately, I am not able to reproduce the problem. After run the program several times, the database table looks somthing like the following picture. But there is no concurrancy issue occured. Did you forget something ?


    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.
    Friday, November 12, 2010 2:30 AM
    Moderator
  • First, Alex, thankyou so much for looking into this.

    I think that I may not have been specific enough about creating the data base.

    In my database I chose an ID with type = int, AllowNulls=No, Unique=Yes, PrimaryKey = Yes, and Identity = True;

    I ran through it again twice and am still having the same Concurrancy bug occur. Can you validate you have the same attributes chosen?

     


    Jon Nicponski
    Friday, November 12, 2010 4:57 AM
  • Hi jonnic,

     

    I am able to reproduce the problem now. The reason for this concurrancy issue is:

     

    When Visual Studio generates the insert commands on a SQL-Server TableAdapter it looks to see whether the table’s primary keys are auto-generated (identity columns) and if so, Visual Studio will write an additional statement to retrieve the key using the SCOPE_IDENTITY functionality of SQL Server. When in the DataSet designer, if you look at the insert statement in the properties window for the SQL TableAdapter you will see two statements separated by a semi-colon:

     

    INSERT INTO [dbo].[Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);

    SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE (ProductID = SCOPE_IDENTITY())

     

    SQL Server supports batch statements through ADO.NET commands so this will populate the primary key back in the DataRow inside the DataSet as each row is inserted into the database."

    However, unlike Microsoft SQL Server, SQL Server Compact Edition does not process statements in batches. SQL Server Compact Edition processes the statements one at a time and executes each statement individually. So unlike SQL Server, we can't issue T-SQL statements where we can do an insert into a table with identity and fetch back the newly created identity value through the same statement.

     

    When you create a local database (SQL Server Compact Edition), press the add button(+) twice and click the save button, two new records will be saved to the database, but the newly created identity value will not be populated back to the DataSet, and you will find that the new primary key values in DataSet are -1, -2.  After that, when you click the delete button, it will try to delete the record whose primary key is -2, however, there is no record in the database whose primary key is -2, so the concurrancy exception occurs. 

     

    You need to write a bit of code yourself to fetch back the newly created identity value into the DataSet. You can refer to the following article which contains the step by step method and complete code about how to do this(focus on the “Adding Code to Handle Inserts to SQL CE Databases” part).

    http://blogs.msdn.com/b/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

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

    Friday, November 12, 2010 6:37 AM
    Moderator
  • Alex, Thanks again for looking this up.

    I read through both articles and all of the replys.  In them, there is a lot of C# code thrown out but none of it actually works to solve the problem.

    I was able to see that the code needs to be placed in the RowChangEvent.  I think the code should looks like the following:

     

     

    public partial class tstDataSet
    {
      partial class tstDataTable
      {
       
    public override void EndInit()
        {
          base.EndInit();
          this.tstRowChanging += TestRowChangeEvent;
        }
       
    public void TestRowChangeEvent(object sender, tstRowChangeEvent e)
        {
          System.Data.
    DataColumn[] pk = e.Row.Table.PrimaryKey;
          if ((pk != null) && pk.Length == 1)
          {
            SqlCeCommand cmdGetIdentity = new SqlCeCommand("SELECT @@IDENTITY", ???????? );
            e.Row[pk[0]] = (
    int)(cmdGetIdentity.ExecuteScalar());
          }
        }
      }
    }

    The issue I am having now is HOW do I get the Connection, Transaction or even the connection string so that I can make my SqlCeCommand work.

    In all of the sample code (and aparantly in VB) the connection and the transaction are in scope, but for the RowChangeEvent in the data table I'm not sure how to access the information I need.

    Can you point me in the right direction?

     

    Thanks again.

     

    -Jon

     


    Jon Nicponski
    Saturday, November 13, 2010 4:06 AM
  • Hi Jon,

    On the DataSet designer, set the primare key column's ReadOnly property to false and try the following code:

    public partial class Form1 : Form
    {
      public Form1()
      {
        InitializeComponent();
      }
    
      private void table_1BindingNavigatorSaveItem_Click(object sender, EventArgs e)
      {
        this.Validate();
        this.table_1BindingSource.EndEdit();
        this.tableAdapterManager.UpdateAll(this.database1DataSet);
      }
    
      private void Form1_Load(object sender, EventArgs e)
      {
        // TODO: This line of code loads data into the 'database1DataSet.Table_1' table. You can move, or remove it, as needed.
        this.table_1TableAdapter.Fill(this.database1DataSet.Table_1);
        this.table_1TableAdapter.HookUpEvents();      
      }
    }
    
    public class SQLCEIDHelper
    {
      public static void SetPrimaryKey(SqlCeTransaction trans, SqlCeRowUpdatedEventArgs e)
      {
        // If this is an INSERT operation...
        if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
        {
          System.Data.DataColumn[] pk = e.Row.Table.PrimaryKey;
          // and a primary key PK column exists...
          if (pk != null && pk.Length == 1)
          {
            //Set up the post-update query to fetch new @@Identity
            SqlCeCommand cmdGetIdentity = new SqlCeCommand("SELECT @@IDENTITY", (SqlCeConnection)trans.Connection, trans);
    
            //Execute the command and set the result identity value to the PK
            e.Row[pk[0]] = Convert.ToInt32(cmdGetIdentity.ExecuteScalar());
            e.Row.AcceptChanges();
          }
        }
      }
    }
    
    namespace Database1DataSetTableAdapters 
    {
      public partial class Table_1TableAdapter
      {
        public void HookUpEvents()
        {
          this._adapter.RowUpdated += new SqlCeRowUpdatedEventHandler(_adapter_RowUpdated);
        }
        
        private void _adapter_RowUpdated(object sender, SqlCeRowUpdatedEventArgs e)
        {
          SQLCEIDHelper.SetPrimaryKey(this.Transaction, e);
        }
      }
    }
    
    

     

    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, November 15, 2010 4:34 AM
    Moderator
  • Alex,

    You are THE MAN!!! :-)

    This completely solved my problem. Thank you for the lesson in working with the table adapter.

    Thanks again!

    -Jon


    Jon Nicponski
    Tuesday, November 16, 2010 2:15 AM
  • Hi Jon,

    I am glad to hear that it solved your problem. :)

    Have a nice day !

    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.
    Tuesday, November 16, 2010 2:49 AM
    Moderator