Search code examples
c#databasedatatableauto-incrementdataadapter

Database and DataSet are not synced regarding auto incrementing ID

I built a Database (Microsoft SqlServerCe.4.0) using Visual Studio and one table containing two fields:

  • id: int, primary key, not null, unique, no default value, identity
  • nom, we don't really care about this one

Then I built a DataSet containing this table as a DataTable and I have a DataAdapter like this :

        marque_adapter = factory.CreateDataAdapter();

        command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM " + DB_TABLE_MARQUE + ";";
        marque_adapter.SelectCommand = command;

        command = connection.CreateCommand();
        command.CommandText = "UPDATE " + DB_TABLE_MARQUE + " SET nom = @nom WHERE id = @id;";
        CreateAndAddParameterFromSource(command, "id", "id");
        CreateAndAddParameterFromSource(command, "nom", "nom");
        marque_adapter.UpdateCommand = command;

        command = connection.CreateCommand();
        command.CommandText = "DELETE " + DB_TABLE_MARQUE + " WHERE id = @id;";
        CreateAndAddParameterFromSource(command, "id", "id");
        marque_adapter.DeleteCommand = command;

        command = connection.CreateCommand();
        command.CommandText = "INSERT INTO " + DB_TABLE_MARQUE + " (nom) VALUES (@nom);";
        CreateAndAddParameterFromSource(command, "nom", "nom");
        marque_adapter.InsertCommand = command;
        //...
        data = new DataSet();
        marque_adapter.Fill(data, DB_TABLE_MARQUE);

The problem arises when I try to insert a new row. I do :

  • table.NewRow()
  • set the "nom" field
  • table.Rows.Add(newRow)
  • adapter.Update(dataSet, tableName)

If I don't do anything else, I have issues later when I try to get the ID of this row (I guess it will set it somewhere between the four instructions above). I was expecting the DataTable to take care of generating one, but ...

So I tried remindind the DataTable to take care of the auto incrementing :

idColumn.Unique = true;
idColumn.AutoIncrement = true;

Now it works the first time, but when I run the program a second time, it starts counting from one again and I'm told that the ID should be unique. If I delete the database (the copy of the sdf file made by Visual), or if I delete the rows manually using Visual, it runs well the first time, and I get the same error after.

The problem really is when I try to save my DataSet, particularly when adding new rows (selecting, updating, deleting is fine).

Obviously I didn't get how to manage primary keys when the DataTable and the database are involved (the datatable alone is ok). Particularly to sync the two ...

What did I miss ? I am quite sure I have misunderstood something.

Solution

  • According to MSDN,

    Bydefault, AcceptChanges is called implicitly after an update, and the original values in the row, which may have been AutoIncrement values assigned by ADO.NET, are lost.

    So you need to create a strategy to merge the AutoIncremented value Either via ADO or getting back the incremented Id from Sql as output parameter and then merge the Identity column value as indicated in this MSDN Article.