ADO .net 2.0: PrimaryKey not updated after calling DataAdapter.Update()

Question

Hi,

I came across a problem while updating a typed DataTable that has a primary key column. At some point in my code I fill DataTables (some of them have primary and foreign key columns) and then I insert the data of all DataTables in one transaction using DataAdapters and Update(). Because the typed DataTables do not allow the PrimaryKey table to be empty I insert some integer values in there. After calling Update() I expected the PK columns to be updated with the database PKs.

    public void UpdateMethod(DbTransaction transaction)
{
 DbDataAdapter dataAdapter = mDbProviderFactory.CreateDataAdapter();
 using (DbCommand insertCommand = CreateCommand())
 {
  insertCommand.Connection = mDbConnection;
  insertCommand.Transaction = transaction;
  dataAdapter.InsertCommand = insertCommand;
  dataAdapter.Update(dataTable);
 }

 // not sure if i need to do this:
 dataTable.AcceptChanges();

 // I would expect that databaseId is now the Id used in the database,
 // but it is the original Id which I set while creating the row entry
 databaseId = (int)dataTable.Rows[0]["Id"];
}

private DbCommand CreateCommand()
{
 // Make command object.
 DbCommand cmd = mDbProviderFactory.CreateCommand();

 // add command input parameters
 DbParameter parameter1 = mDbProviderFactory.CreateParameter();
 parameter1.ParameterName = mDatabaseParameterPrefix + "someColumn";
 parameter1.SourceColumn = "someColumn";
 parameter1.Size = 255;
 parameter1.DbType = DbType.String;

 // Output parameter
 DbParameter idParameter = mDbProviderFactory.CreateParameter();
 idParameter.ParameterName = mDatabaseParameterPrefix + "ID";
 idParameter.SourceColumn = "ID";
 idParameter.Direction = ParameterDirection.InputOutput;
 idParameter.DbType = DbType.Int32;

 // setup sql command
 cmd.Parameters.Add(parameter1);
 cmd.Parameters.Add(idParameter)
 cmd.CommandText = @"INSERT INTO [SomeTable] ([someColumn], ...) VALUES(@someColumn, ... ) SELECT CAST(SCOPE_IDENTITY() AS int) AS 'ID'";
 cmd.UpdatedRowSource = UpdateRowSource.Both;

 return cmd;
}

Thanks for any hints!

Answer 1

When you have a primary key set when you fill the datatable, it won't overwrite rows that already exist (however, if it comes across a matching primary key it may or may not update non-key elements depending on what your loadoption is set to)

You would have to clear your datatable first before you fill it I believe.

Answer 2

Solved it. The problem was the SQL statement: Instead of

INSERT INTO [SomeTable] ([someColumn], ...) VALUES(@someColumn, ... ) SELECT CAST(SCOPE_IDENTITY() AS int) AS 'ID'

It must be:

INSERT INTO [SomeTable] ([someColumn], ...) VALUES(@someColumn, ... ) SELECT @ID = SCOPE_IDENTITY()

Otherwise the value of the primary key is not assigned to the output parameter.