Q: Updating the correct keys

[Geoff]

Hi

I'm hoping somebody can help me with the following problem that has occurred
to me.

Suppose I have two tables in an SQL Server database. Let's call these tables
A and B. Assume that A has two fields: a primary key and another holding a
string. In table B there are three fields: a primary key, a foreign key
(which links to the primary key in A) and other field holding a string.

Suppose I load these tables into a DataSet. I populate table A with some
rows. For each of these rows I create some rows in B which are linked by the
foreign key to A i.e. there is a one to many relationship between A and B.
Once this is done, I update the DataSet via a DataAdaptor such that these
tables are transferred to the SQL database.

My question is this. As far as I can see, the values in the primary key of A
and the corresponding keys in B will not necessarily be the same in the
DataSet and the SQL database! Do you see what I mean? I can't see how the
relationship specified is still valid after the Update.

Can anybody explain this to me?

Thanks in advance

Geoff

 

[Jim Rand]

Hi Geoff,

You're right, the keys aren't the same. The question is, "how do you get the
dataset keys to sync up with the real keys in the database and how do you
maintain the parent/child relationship at the same time?

Let's assume the database issues the primary keys as autoincrement values.

On the dataset side:

In the xsd designer, for the primary key in each table, set AutoIncrement to
true, AutoIncrementSeed to -1 and AutoIncrementStep to -1. Each row added
will have a unique primary value. It won't be "right", but at least it won't
conflict with the database or with any other user.

Edit each relationship between each table; in this case the relationship
between parent table "A" and child table "B".
Set the update rule to "cascade". As a result of this setting, a change in
the value of parent table "A's" primary key will cascade down to child table
"B's" foreign key referencing parent table "A". Thus, if something changes
the primary key in "A" from -3 to 35736328, the value of the foreign key in
table "B" will also change from -3 to 35736328 preserving the parent/child
relationship.

Moving on to the data adapter:

In the INSERT statement, have something that brings the SCOPE_IDENTITY()
from the database with the real primary key. For example, the INSERT
statement for table "A" would be:

INSERT INTO A ( SomeString )
VALUES ( @SomeString ); SELECT A_ID WHERE A_ID =SCOPE_IDENTITY()

Notice two important points. First, we don't insert the primary key (-3)
that was generated by the dataset. Second, we bring back the real primary
key (35736328) issued by the database. Through Microsoft magic, the data
adapter's Update() method will change the primary key value in table "A" and
will cascade the new foreign key values to table "B".

Now for the code:

If you've used the data adapter's Update() method on the original dataset,
you're done. However, if you created a small dataset with only changes to
send back to the database using the dataset's GetChanges() method and
subsequent Merge() method, you will discover that Microsoft's magic left
your original (-3) row intact and added a new row (35736328).

To get around this behavior, add the following event handler to the data
adapter:

private void daTblA_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
if ( e.StatementType == StatementType.Insert) e.Status =
UpdateStatus.SkipCurrentRow;
}

Note, with the dataset's WriteXml() method, examine the GetChanges dataset's
diffgram after the update to the database but prior to the Merge() operation
back to the original dataset.

Jim

 

[Geoff]

Many thanks Jim for your comprehensive reply.

Hi Geoff,

You're right, the keys aren't the same. The question is, "how do you get
the
dataset keys to sync up with the real keys in the database and how do you
maintain the parent/child relationship at the same time?

Let's assume the database issues the primary keys as autoincrement values.

On the dataset side:

In the xsd designer, for the primary key in each table, set AutoIncrement
to
true, AutoIncrementSeed to -1 and AutoIncrementStep to -1. Each row added
will have a unique primary value. It won't be "right", but at least it
won't
conflict with the database or with any other user.

Edit each relationship between each table; in this case the relationship
between parent table "A" and child table "B".
Set the update rule to "cascade". As a result of this setting, a change
in
the value of parent table "A's" primary key will cascade down to child
table
"B's" foreign key referencing parent table "A". Thus, if something
changes
the primary key in "A" from -3 to 35736328, the value of the foreign key
in
table "B" will also change from -3 to 35736328 preserving the parent/child
relationship.

Moving on to the data adapter:

In the INSERT statement, have something that brings the SCOPE_IDENTITY()
from the database with the real primary key. For example, the INSERT
statement for table "A" would be:

INSERT INTO A ( SomeString )
VALUES ( @SomeString ); SELECT A_ID WHERE A_ID =SCOPE_IDENTITY()

Notice two important points. First, we don't insert the primary key (-3)
that was generated by the dataset. Second, we bring back the real primary
key (35736328) issued by the database. Through Microsoft magic, the data
adapter's Update() method will change the primary key value in table "A"
and
will cascade the new foreign key values to table "B".

Now for the code:

If you've used the data adapter's Update() method on the original dataset,
you're done. However, if you created a small dataset with only changes to
send back to the database using the dataset's GetChanges() method and
subsequent Merge() method, you will discover that Microsoft's magic left
your original (-3) row intact and added a new row (35736328).

To get around this behavior, add the following event handler to the data
adapter:

private void daTblA_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
if ( e.StatementType == StatementType.Insert) e.Status =
UpdateStatus.SkipCurrentRow;
}

Note, with the dataset's WriteXml() method, examine the GetChanges
dataset's
diffgram after the update to the database but prior to the Merge()
operation
back to the original dataset.

Jim

 

[Jim Rand]

You're welcome.

Upon further examination, the INSERT statement should read:
INSERT INTO A ( SomeString )
VALUES ( @SomeString ); SELECT A_ID FROM A WHERE A_ID =SCOPE_IDENTITY()

Jim

 

[Otis Mukinfus]

Hi

I'm hoping somebody can help me with the following problem that has occurred
to me.

Suppose I have two tables in an SQL Server database. Let's call these tables
A and B. Assume that A has two fields: a primary key and another holding a
string. In table B there are three fields: a primary key, a foreign key
(which links to the primary key in A) and other field holding a string.

Suppose I load these tables into a DataSet. I populate table A with some
rows. For each of these rows I create some rows in B which are linked by the
foreign key to A i.e. there is a one to many relationship between A and B.
Once this is done, I update the DataSet via a DataAdaptor such that these
tables are transferred to the SQL database.

My question is this. As far as I can see, the values in the primary key of A
and the corresponding keys in B will not necessarily be the same in the
DataSet and the SQL database! Do you see what I mean? I can't see how the
relationship specified is still valid after the Update.

Can anybody explain this to me?

Thanks in advance

Geoff

Your assumption is correct if you are assigning the key value to the
new rows.

If you use identity columns the new identities will become part of the
relationship when inserted into the DB if you have set the DataSet up
properly.

There is a good explanation of this in Sceppa's book "ADO.NET" (MS
Press).

HTH

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[Geoff]

Hi Jim

As a matter of interest, do you know if the CommandBuilder automatically
generates this code? Is there an option for it to do so?

Geoff

 

[Geoff]

Thanks Otis

Geoff

Your assumption is correct if you are assigning the key value to the
new rows.

If you use identity columns the new identities will become part of the
relationship when inserted into the DB if you have set the DataSet up
properly.

There is a good explanation of this in Sceppa's book "ADO.NET" (MS
Press).

HTH

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[Jim Rand]

Hi Geoff,

The SQL generated by the command builder only provides a starting point. It
doesn't add the SELECT statement after either the INSERT or UPDATE
statements. Moreover, the WHERE clause includes every field - not too smart
if you are using timestamps to handle concurrency conflicts.

Jim

 

[Geoff]

Thanks Jim

Hi Geoff,

The SQL generated by the command builder only provides a starting point.
It
doesn't add the SELECT statement after either the INSERT or UPDATE
statements. Moreover, the WHERE clause includes every field - not too
smart
if you are using timestamps to handle concurrency conflicts.

Jim