DataRelation Insert and ForeignKey
- by Steve
Guys,
I have a winforms application with two DataGridViews displaying a master-detail relationship from my Person and Address tables. Person table has a PersonID field that is auto-incrementing primary key. Address has a PersonID field that is the FK.
I fill my DataTables with DataAdapter and set Person.PersonID column's AutoIncrement=true and AutoIncrementStep=-1. I can insert records in the Person DataTable from the DataGridView. The PersonID column displays unique negative values for PersonID. I update the database by calling DataAdapter.Update(PersonTable) and the negative PersonIDs are converted to positive unique values automatically by SQL Server.
Here's the rub. The Address DataGridView show the address table which has a DataRelation to Person by PersonID. Inserted Person records have the temporary negative PersonID. I can now insert records into Address via DataGridView and Address.PersonID is set to the negative value from the DataRelation mapping. I call Adapter.Update(AddressTable) and the negative PersonIDs go into the Address table breaking the relationship.
How do you guys handle primary/foreign keys using DataTables and master-detail DataGridViews?
Thanks!
Steve
EDIT:
After more googling, I found that SqlDataAdapter.RowUpdated event gives me what I need. I create a new command to query the last id inserted by using @@IDENTITY. It works pretty well. The DataRelation updates the Address.PersonID field for me so it's required to Update the Person table first then update the Address table. All the new records insert properly with correct ids in place!
Adapter = new SqlDataAdapter(cmd);
Adapter.RowUpdated += (s, e) =>
{
if (e.StatementType != StatementType.Insert) return;
//set the id for the inserted record
SqlCommand c = e.Command.Connection.CreateCommand();
c.CommandText = "select @@IDENTITY id";
e.Row[0] = Convert.ToInt32( c.ExecuteScalar() );
};
Adapter.Fill(this);
SqlCommandBuilder sb = new SqlCommandBuilder(Adapter);
sb.GetDeleteCommand();
sb.GetUpdateCommand();
sb.GetInsertCommand();
this.Columns[0].AutoIncrement = true;
this.Columns[0].AutoIncrementSeed = -1;
this.Columns[0].AutoIncrementStep = -1;
