Datasets and identities in ADO.NET 2.0

  • Thread starter Thread starter Massimo
  • Start date Start date
M

Massimo

Hi to all, I'm developing a test application with Visual Studio 2005 Beta 2
and the 2.0 framework (along as SQL Server 2005 June CTP), and I'd like to
know if they introduce some improvements in order to solve an old problem:
how to deal with inserting values from a DataSet into two related tables
when one of them has an auto-generated ID column.

Let's say I have these two tables in the DB, Table1 and Table2, and Table1
has an "ID" column which is configured as an auto-generating identity;
Table2 also has an ID column, which references Table1.ID in a foreign key
relation.

Now, I'm loading these two tables in a typed DataSet, which, being typed,
knows the structure of the tables and the relation between them; I put a new
row into Table1, and then another one in Table2 referencing the new
Table1.ID.

The question is: how do I put those new data into the DB, since I can't
specify the value of the column in the master table?

I know there are some workarounds for this problem (like retrieving the new
auto-generated ID and storing it in the DataSet, or using stored procedures
to update the tables, or using placeholder values in the DataSet before
submitting the changes, or using GUIDS), but I'd like to know if ADO.NET 2.0
solves this problem in a more elegant way than 1.0 and 1.1 did; the new
framework has so many improvements... this one would be quite helpful.

Thanks for any answer.


Massimo
 
Hi Massimo,

No, the idea is still the same and AFAIK it is a good solution.
You just save the rows in proper order and upon successful save you retrieve
auto generated ids.
DataRelations will take care of updating the ids in DataSet.
 
I'd like to know if ADO.NET 2.0 solves this problem
Click to expand...

Well, it seems the framework doesn't solve the problem by itself, but Visual
Studio does (at least the 2005 version, I don't know how 2002/2003 handles
this); when creating the insert and update commands, if you select the
"refresh dataset" option, it notes the column is an ID and builds the select
query to retrieve the modified row using @@IDENTITY.

Now, another little question: when creating a typed DataSet in Visual Studio
2005, the relations are set by default to not cascade updates, *even if they
are cascading in the DB*. So, when adding some tables and relations to a
DataSet, I need to manually configure each one of them. Is this by design,
or is it a bug of the beta version that I should report to the developers?
Shouldn't the IDE create cascading or non-cascading relations based on how
the DB handles them?

Massimo
 
Back
Top