Q: Datatables, Datasets and updating

[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

 

[Guest]

The problem seems to be that you have defined Primary Key to mean Identity
Column. Your question demonstrates one of the multitude of reasons why
identity columns are not generally suitable as primary keys. If your primary
keys were real attributes of your data, for instance SSN or "Orange" or
something like that, then you could always identify which item from table B
belongs to which item in table B.

You might like some of the heated debate on the subject on my blog at
http://www.dalepreston.com/Blog/2005/08/identity-crisis.html.

If you really must use an artificial key for your primary key, then you will
have to handle the database update yourself, inserting a row from table A,
returning SCOPE_IDENTITY after each insert, and then inserting the child rows
from table B substituting the temporary key you used in the DataSet with the
value of SCOPE_IDENTITY.

Then you can update the data in your DataSet with the SCOPE_IDENTITY value
to keep everything synchronized or just read the data back from the database
to update the DataSet.

HTH

 

[Geoff]

Hi Dale

Err, I think I haven' explained myself too well. I'll try again:

I have two tables A and B in the SQL database.

Table A has two fields: ID_A (the primary key of A) and NAME_A (a string
field).

Table B has three fields: ID_B (the primary key of B), FK_A (a link to a
primary key value in A) and NAME_B (a string field).

I load these tables into a DataSet. I then add values to table A. I also add
values into B; where the values of FK_A in B have corresponding values in
ID_A.

I call the update event of the DataSet (or corresponding DataTables) to
transfer the data inputted into the SQL database.

As far as I can see, adding (updating) entries in both tables of the SQL
database create, NOT NECESSARILY THE SAME, values in the ID_A field e.g.
what if another user has added rows during the time the disconnected data
has been modified. So my question is, how do the values in ID_A and FK_A
have matching values AFTER they have been updated to the database.

I'm obviously missing something because this type of scenario is so likely
to happen in writting database applications.

However, just in case I have got the wrong end of the stick with your kind
response, I'd be interested to see some example code for the returing of
SCOPE_IDENTITY.

Many thanks in advance

Geoff

 

[Guest]

You would have to create custom Update commands in your DataAdapter to handle
any concurrency issues. For example, if you're updating one field out of 12
in a table, you would include all eleven other fields in your where clause,
validating that they still had the original values.

But you are raising an important question. Most developers I know don't use
the automatic updating features of a DataSet and DataAdapter. Most I know
write their own update code.

And if you are referring to inserts only, where you created your "primary
key" value in table A and table B and now you are concerned that someone else
already put entries in the tables using the values you assigned your primary
key in your own app, it is the same thing as I mention ed the first time,
just a slightly different twist.

 

[Geoff]

Many thanks Dale

You would have to create custom Update commands in your DataAdapter to
handle
any concurrency issues. For example, if you're updating one field out of
12
in a table, you would include all eleven other fields in your where
clause,
validating that they still had the original values.

But you are raising an important question. Most developers I know don't
use
the automatic updating features of a DataSet and DataAdapter. Most I know
write their own update code.

And if you are referring to inserts only, where you created your "primary
key" value in table A and table B and now you are concerned that someone
else
already put entries in the tables using the values you assigned your
primary
key in your own app, it is the same thing as I mention ed the first time,
just a slightly different twist.