Datasets and identities in ADO.NET 2.0

22 Jun 2005 12:26 AM

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

22 Jun 2005 7:33 AM

Cor Ligthert

Massimo,

Beta2 is not a released product. For testing is it much appriciated when you
use these newsgroups

http://lab.msdn.microsoft.com/vs2005/community/

I hope this helps,

Cor

22 Jun 2005 7:55 AM

Miha Markic [MVP C#]

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.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Show quoteHide quote

"Massimo" <bar***@mclink.it> wrote in message
news:Ot9HMEsdFHA.2880@TK2MSFTNGP10.phx.gbl...
> 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
>

22 Jun 2005 10:21 AM

Sahil Malik [MVP]

Your answer is right here -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/06/62893.aspx

--

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------

Show quoteHide quote

"Massimo" <bar***@mclink.it> wrote in message
news:Ot9HMEsdFHA.2880@TK2MSFTNGP10.phx.gbl...
> 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
>

23 Jun 2005 1:25 AM

Massimo

"Massimo" <bar***@mclink.it> ha scritto nel messaggio
news:Ot9HMEsdFHA.2880@TK2MSFTNGP10.phx.gbl...

> I'd like to know if ADO.NET 2.0 solves this problem

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