Updating Multiple Tables via DataSet

[Steven Livingstone]

I have done a bit of looking around and found some sources of information on
updating multiple tables within a dataset, but hopefully i can get a better
answer and direction here.

Say I have a "Company" table and "Consultant" table, with table
"CompanyConsultants" joining them both (a company can have many consultants
and a consultant can be part of many companies).

In case 1 I add a new Consultant and the primary key of the row auto
increments and from there i can add the binding to the company. What is the
best way of doing this?

In case 2 you add a new company and consultant (both pri keys are auto inc),
which are related as above, build the dataset and update the database. What
is the best way of doing this?

At the moment we run a series of updates within an ADO transaction and call
the dataadapter multiple times for each table which has a stored procedure
associated with it. Seems long winded to me, but i haven't worked on
DataSets enough to know what the best technique for working on multiple
tables actually is.

Any advice/direction much appreciated!

- best,
Steven

 

[Kathleen Dollard]

Steven,

Assuming you are using autoincrement fields on your database backend.

Scenario 1: Just assign the primary key of the company to teh consultant. It
isn't clear what issue you see with this.

Scenario 2: Use Cascade Primary Keys on your relation. Use a seed of
negative one and a skip of negative one to your increment field in your
company (not required but helpful). Add records however you like. Update the
company, insuring its primary key is updated via either return values or a
select as part of the update. Update the Consultants table. ADO.NET
cascades the Company primary key for you.

Kathleen