DataRelation Insert and ForeignKey

Question

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;

Answer 1

You need to double click the relationship in the dataset designer, and select Cascade Updates. When your real SQL server generated PK values for your Person table are generated, it will automatically set the foreign key values in the address table as well.

You don't need to do any of that RowUpdated event stuff. Its built into the dataset functionality.

Answer 2

I had a similar problem, but my solution was a little different.

@Noel Kennedy: Your solution does not work with SQL Server 2005 CE, because it doesn't support multiple statements and the TableAdapter won't generate the refresh code needed to update the autoincrement columns in the parent table.

NOTE: You still need Cascade Updates in the relationship so the child tables get updated.

I also add a method in my TableAdapter, which is generic enough to just copy/paste in all your parent TableAdapters. The only thing that I change is the identity row type and index (if needed). I also add a query to the TableAdapter called GetIdentity(). You can add it to the TableAdapter in the dataset designer by adding a scalar query with sql="SELECT @@IDENTITY;"

Now the custom function is:

public int InsertAndRefresh(System.Data.DataTable dataTable) { int updated = 0;

    System.Data.DataRow[] updatedRows = dataTable.Select("", "", System.Data.DataViewRowState.Added);

    bool closed = (this.Connection.State == System.Data.ConnectionState.Closed);
    if (closed) 
        this.Connection.Open();

    foreach (System.Data.DataRow row in updatedRows)
    {
        updated+=this.Adapter.Update(new global::System.Data.DataRow[] { row });
        decimal identity = (decimal)this.GetIdentity();
        row[0] = System.Decimal.ToInt64(identity);
        row.AcceptChanges();
    }
    if (closed)
        this.Connection.Close();

    return updated;
}

You want to call this on the parent first. Then do everything as usual (update parent and then children).

Cheers!