DataSets, DataAdapters, DataTables and Relations

[balabaster]

I have my SQL database with the following structure:

Table1(
PrimaryKey Int Not Null Identity(1, 1) Primary Key,
TextData Varchar(50) Not Null
)

Table2(
PrimaryKey Int Not Null(Identity(1 , 1) Primary Key,
ForeignKey Int Not Null Foreign Key References Table1(PrimaryK ey),
TextData Varchar(50) Not Null
)

So now I'm wanting to access all my data using a DataAdapter and a DataSet.

I've set up my tables in my disconnected dataset and have created the relations that join the tables such that Table2.ForeignK ey is related to Table1.PrimaryK ey. I am using the FillSchema method to grab the primary key and column types.

I fill my datatables with data from my tables in my database. If I insert or modify data in Table1 and tell my data adapter to update, it runs off and adds/updates records in my database...same with Table2.

Now, I'm trying to wrap my head around the following scenario - I load my data into my dataset and add a new entry into Table1 in my DataSet (still disconnected), then someone else comes along and adds a record into Table1 in the database itself. Now the key I have in my DataTable clashes with the one in my SQL database. However, if I execute the DataAdapter.Upd ate method, it still runs off and processes the insert - it finds that there's an existing record in the SQL database and just inserts using the next available identity instead of the one it has - which I guess is good...it means my application doesn't crash after all.

So I start the process again and grab all my data, insert a record into the DataSet version of Table1 so that I'm given the next new key, lets say the next key is 11. I add a new record into Table1 in the database itself so that come time to execute the update record 11 already exists in the SQL database. But before I execute the update, I also want to add a row into Table2 in my dataset. So now I have one record in each table that are related to each other (presumably by the offline identity that was assigned when I created the row in Table1)

How do I figure out the correct information for the ForeignKey field in Table2 or is there some way of linking to a DataTable record uniquely such that if and when the key is changed in Table1, it cascades that change to linked records for Table2?