ConstraintException on DataTable.Rows.Add with AutoIncrement Column

Question

Hi,

I've got some DataTables with an ID column with AutoIncrement enabled both on the MySQL server side and the ADO.NET schema side (loaded from an XML schema file). I'd like not to have to care about these IDs when inserting rows because their sole use is to have a primary key for the table – there are no foreign key references. However, the DataTable will already contain rows when the new rows are to be added, i. e. rows with IDs starting from 1 already reside in the DataTable.

The default AutoIncrementSeed value for the ID column is -1 and the AutoIncrementStep value is 1 (just left the defaults provided by Visual Studio). Everything seems to be quite fine if I only want to insert two new rows at once, because when created with dataTable.NewRow() they get the IDs -1 and 0. But if I want to add a third row, it gets the ID 1 assigned by NewRow(). Subsequently a ConstraintException is thrown when I want to insert the new row with dataTable.Rows.Add(...) because there already is a row with ID = 1.

I suppose disabling the AutoIncrement stuff for the ADO.NET schema is no solution because then I would still have to make sure myself that the IDs are unique before I add the rows with .Rows.Add()

What would be an easy/elegant solution of this problem? Right now I can't imagine that this is a very uncommon task.

Thank you!

Answer 1

Can you try something akin to the example I have provided below. You can verify whether the unique column value already exists using Select on the table before adding an another row:

using (SqlConnection connection = new SqlConnection(sqlConnectionStr))
        {
            connection.Open();
            // Do work here.
            DataTable userTable = new DataTable("usertable");

            using (SqlCommand sqlCmd = new SqlCommand("select * from [user]"))
            {
                sqlCmd.Connection = connection;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCmd);
                dataAdapter.Fill(userTable);
            }

            int newUserId = 3;
            DataRow[] selectedRows = userTable.Select(  string.Format( "id={0}", newUserId));
            bool userIdAlreadyTaken = selectedRows.Length > 0 ;
            if( !userIdAlreadyTaken )
            {
                // Add new user
                DataRow newRow = userTable.Rows.Add(new object[] { 3, "John" });
            }
        }