DataRelation Insert and ForeignKey

Question

I have a winforms application with two DataGridViews displaying master-detail relationships from my Person and Address tables. The Person table has a PersonID field that automatically increments the primary key. The address has a PersonID field, which is FK.

I populate my DataTables DataAdapter and set the Person.PersonID column AutoIncrement = true and AutoIncrementStep = -1. I can insert records into a DataTable Person from a DataGridView. The PersonID column displays unique negative values ​​for PersonID. I update the database by calling DataAdapter.Update (PersonTable), and negative personal identifiers are automatically converted to positive unique SQL Server values.

Here rub. The DataGridView address shows an address table that has a DataRelation for Person by PersonID. Insertion Records have a temporary negative PersonID. Now I can insert records into the address through the DataGridView and Address.PersonID sets a negative value from the DataRelation mapping. I call Adapter.Update (AddressTable), and negative personal identifiers go into the Address table, breaking the connection.

How do you guys handle primary / foreign keys with DataTables and master-detail DataGridViews?

Thanks! Steve

EDIT:

, , SqlDataAdapter.RowUpdated , . @@IDENTITY. . DataRelation Address.PersonID , Person, Address. !

            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

Cascade Updates. SQL- PK Person, .

- RowUpdated. .

Answer 2

, .

@Noel Kennedy: SQL Server 2005 CE, , TableAdapter , .

. , .

TableAdapter, , / TableAdapters. , , ( ). TableAdapter GetIdentity(). TableAdapter , sql = "SELECT @@IDENTITY;"

:

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;
}

. ( , ).

!