Re: Q: Datatables, Datasets and updating
- From: "Dale" <dale0973@xxxxxxxxxxxxx>
- Date: Sun, 29 Jan 2006 19:30:27 -0800
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.
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Geoff" wrote:
> 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
>
> "Dale" <dale0973@xxxxxxxxxxxxx> wrote in message
> news:5D4EEB1C-BFD0-40F9-943A-048A7FEBB3B5@xxxxxxxxxxxxxxxx
> > 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
> > --
> > Dale Preston
> > MCAD C#
> > MCSE, MCDBA
> >
> >
> > "Geoff" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
>
>
>
.
- Follow-Ups:
- Re: Q: Datatables, Datasets and updating
- From: Geoff
- Re: Q: Datatables, Datasets and updating
- References:
- Q: Datatables, Datasets and updating
- From: Geoff
- RE: Q: Datatables, Datasets and updating
- From: Dale
- Re: Q: Datatables, Datasets and updating
- From: Geoff
- Q: Datatables, Datasets and updating
- Prev by Date: Re: String parser Using Regular Expression for Date format display using Locale Settings)
- Next by Date: Re: ? and : for conditional expressions in v2
- Previous by thread: Re: Q: Datatables, Datasets and updating
- Next by thread: Re: Q: Datatables, Datasets and updating
- Index(es):
Relevant Pages
|
