PC Review


Reply
Thread Tools Rate Thread

Typed DataSet Problem

 
 
William Ryan eMVP
Guest
Posts: n/a
 
      26th Apr 2004
No problem , and actually I'm glad you posted it. Many times people don't
ever respond so I usually only look back a day or two.

As far as your questions, I don't really see any way around the frequent
calls to the db considering the situation. If you want to use Update and the
dataadapter, then there's not much you can do, it's going to fire the Insert
command against whatever is "added". You can write some really complex
update logic, but like you found out, it's not simple. So the crux of the
problem is when to call insert and when to call update. And if you want to
depend on rowstate, you'll have to make sure you've verified the rowstate
and AcceptChanges will get you there. As far as DB stress goes, 100 queries
is 100 queries, so your app or the other one, same difference. No one app
may respond better if it doesn't have to fire a bunch of queries but it's
all the same to the db.

Anyway, your solution below sounds good based on the project constraints.

Let me know how it goes
"Sarah" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> William,
>
> Thanks for all your help and I hope I did not offend you by calling your
> attention with another post. After reading your reply, I think your
> suggestion will work however there are alot of tables the app works with.
> This means the app will be communicating alot with the db which I would
> rather not have. However, I see no other way around it. Like I said in

an
> earlier post I have one table which has multiple tables related to it

where
> depending on user input could have data in each table or have no data

which
> is related back to the main table. In the case above, I could get data

from
> the other app which already has a record where the data has changed I need
> to remove data from different tables and also add data to other tables
> (sorry for the confusion). I thought about this approach, when the record
> already exists. When the data is coming from the other app, I verify if

the
> data exists and if so I fill the datasets and then I modify the data from
> the other app. What do you think?
>
> Thanks
>
> "William Ryan eMVP" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Sarah:
> >
> > I think I understand. Ok, whenver you add a value to the Row, it may be

> in
> > the db or it may not. Assuming it's not in there, a simple insert

command
> > will work. But if it is, that causes some drama. So here's

esssentially
> > what I propose. If the data exists in the db, you still want to use the
> > dataadapter and you want it to use the Update command. However, since it
> > sees the rowstate as added, it will use the Insert command. If it 'knew'

> to
> > use Insert when it didn't exist in the database and rowstate was added

> and
> > to use "Update" when it was in the DB and rowstate would be added, life
> > would be good, correct?
> >
> > Ok, you'll have to add the value to the row either way b/c if it's not

in
> > the datatable, neither command is going to much matter. So, after

adding
> > the row to the datatable, run a function that returns whether or not the

> row
> > was in the db. If it is already in there, Immediately call

..AcceptChanges
> > only on THAT ROW. Otherwise no acceptchanges is necessary
> >

>

http://msdn.microsoft.com/library/de...us/cpref/html/
> > frlrfsystemdatadatarowclassacceptchangestopic.asp.
> >
> > So, you add a row and it happens to exist in the db. Right after you add

> it
> > to the db, you call your function (something like DoesValueExist) which
> > returns True (indicating it does). Currenlty the rowstate is added.

So,
> > the very next line you call NewlyAddedDataRow.AcceptChanges (only on

this
> > row). Now the Rowstate is unchanged. Anything you do to this row will

> cause
> > its rowstate to be Modified... After calling AcceptChanges, Rowstate

won't
> > ever be added unless you delete or remove it and add it again. So, you
> > change the values, call dataAdapter.UPdate(dataset) and then it will see

a
> > rowstate or modified, call the Update command instead of Insert, and

life
> > should be good.
> >
> > Now, you add another new row which isn't in the db. Immediately

afterward
> > you call DoesValueExist and it returns false. In this instance, the
> > Rowstate is currently Added and you want it to remain that way. You may
> > still need to change some values, but the final values are what you want

> and
> > you need it to be inserted into the db. So, you call update, the

adapter
> > sees the rowstate as added, and calls the InsertCommand as planned.
> >
> > The whole thing is governed by checking if it exists immediately after

you
> > add the row. If it does, call AcceptChanges which makes it as though it
> > came from the db originally. If it doesn't, then leave the rowstate

> aalone.
> >
> > In doing so, the rowstate of your datatable will match the db which is

> what
> > you want. The whole prolem is that they are out of sync at times but
> > calling acceptchanges on any row that already exists will set it to
> > UnChanged which is what you want (it's what happens when you call
> > dataadapter.Fill with the default setting of AcceptChangesDuringFill set

> to
> > false.)
> >
> > Does this make sense? Basically, you are just using AcceptChanges to

make
> > sure the datatable matches the db as closely as possible, and in doing

so,
> > you can use the Update/Insert/Delete command as you would normally.
> >
> > Let me know if you have any problems. Sorry about the delay. I'll keep

> my
> > eyes open for your response.
> >
> > Bill
> > <Sarah> wrote in message news:(E-Mail Removed)...
> > > Thanks again for your help.
> > >
> > > In my application the majority of the data comes from another

> application
> > > which is run before it. These two application are tightly integrated

via
> > > XML. So, the application can get data these ways:
> > > 1) Via XML from the other app including some user input (majority of

the
> > > time)
> > > 2) Via the app database (updating a current record)
> > > 3) User input
> > >
> > > I have no problem with 2 because I fill my datasets and when I change

> the
> > > data my rowstate equals "modified". And when I call update my

> > dataadapter
> > > it calles my update command. The problem I have is with 1 and 3

because
> > the
> > > record could already be in the database (App does not know - I can

test
> it
> > > before insert). My datasets rowstate equals "added" and of coarse the
> > > dataadapter calls the insert command. I can create a stored procedure

> > that
> > > can test for the record and if exists then update it. This works fine
> > > however I do not like tricking to the application. I could design the
> > > application not to use the data adpater and just manually pass the
> > > parameters. The real problem I run into is one of my tables is

designed
> to
> > > have multiple child tables. There are times when these tables do not

> have
> > > any data in them which means the keys in the main table are null. So,

> on
> > > the update I would need at times to delete data (rows) which I could

> > really
> > > use the dataadpater and the three comands (insert, update and delete).

> > This
> > > could simplify my updating logic if I could somehow make the rowstate

> > work.
> > > I hoped I explained my situation.
> > >
> > > Thanks again.
> > > William Ryan eMVP <(E-Mail Removed)> wrote in message
> > > news:#(E-Mail Removed)...
> > > > Sarah:
> > > >
> > > > If you send me the code I"d be more than happy to look at it and see

> if
> > I
> > > > can find anything. The rowstate mechanism is one of the most robust
> > > > features and while I'm not implying there can't be any bugs or

issues,
> I
> > > > know of none and I'm fairly active in this regard. If you are

> > submitting
> > > > the row in an update statement and it's being inserted, then the

> > rowstate
> > > > should no longer indicate added. If you change anything to it, it

> > should
> > > be
> > > > modified. If it's still showing added, I suspect that you may have

an
> > > > exception being raised that you are just eating thereby giving the
> > > > appearance that it's updated when it's not or some other type

problem.
> > > Just
> > > > to be safe, I'd call update, for testing purposes I'd fire another

> query
> > > > just to verify unquestionably that it worked, then I'd try an edit.

> So
> > > > assuming we are talking about Row 0 in datatable, I'd do the

> following:
> > > >
> > > > Debug.Assert(dt.Rows[0].RowState == RowState.Added); file://Call

this
> > > before
> > > > update. This assertion should pass
> > > > next, call the update
> > > > int i = dataAdapter.Update(dt);
> > > > Now, make sure you are catching any exceptions here and not eating

> > them..
> > > > Verify something happened:
> > > >
> > > > Debug.Assert(i > 0);
> > > > file://If this fails, the update didn't work as expected. Assuming

it
> > did
> > > the
> > > > rowstate should be Unchanged
> > > > Debug.Assert(dt.Rows[0].RowState == RowState.Unchanged);
> > > >
> > > > This too should pass. If it fails, but the other assertions didn't,
> > > > something weird is happening.
> > > >
> > > > Ok, but assume it does, just for the sake of addressing this one

> issue,
> > > call
> > > > AcceptChanges just to be sure, then check the rowstate again There

is
> > the
> > > > possilibity that the row is being modified in another thread or if

you
> > > > created an event, then it's firing before we check again.
> > > >
> > > > dt.AcceptChanges();
> > > >
> > > > Now,check the rowstate again... All of the assertions should pass

but
> > if
> > > > they don't, somethign else like what I mention above may be at play.

> If
> > > so,
> > > > then send me the code it you'd like and I'll take a look at it.
> > > >
> > > > However,based on our earlier dialog, I think the insert may be

failing
> > b/c
> > > > it already exists so you may need to check beforehand or use some

> other
> > > > mechanism.
> > > >
> > > > Let me know if you need any help.
> > > >
> > > > Bill
> > > > "Sarah" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > Thanks,
> > > > >
> > > > > I agree with you I think in my case I cannot use the data adapter

> but
> > > > rather
> > > > > create my own logic. I have tested using the data adapter and it

> > never
> > > > > fires the update command when I manually enter in the data even if

> the
> > > the
> > > > > data exists in the db. I can never get the rowstate to be equal

to
> > > > > "modified" when I change the data on my manually added data row

> after
> > it
> > > > has
> > > > > been updated to the db.
> > > > >
> > > > > Thanks again
> > > > >
> > > > > "William Ryan eMVP" <(E-Mail Removed)> wrote in

message
> > > > > news:OXrGF$(E-Mail Removed)...
> > > > > >
> > > > > > "Sarah" <(E-Mail Removed)> wrote in message
> > > > > > news:On$(E-Mail Removed)...
> > > > > > > Thanks,
> > > > > > >
> > > > > > > I do call Ds.AcceptChanges() and it changes the row state to
> > > > > "unchanged".
> > > > > > > Is the rowstate functionality behaving differently because I

the
> > > data
> > > > is
> > > > > > not
> > > > > > > coming from the db but rather manually?
> > > > > > No! This is a very common misconception. When you use a

> > DataAdapter
> > > > and
> > > > > > get the data from a Database, many things happen, the rows are
> > > created,
> > > > > the
> > > > > > columns are created, the columns are mapped based on the schema

> etc.
> > > > > > However, if you manually created the columns and manually added

> the
> > > > > values,
> > > > > > there's no way you could tell the difference between the two

just
> by
> > > > > looking
> > > > > > at the tables. All else being equal, you aren't going to get
> > > different
> > > > > > behavoir from a Datatable because it got filled from a database.
> > > > > >
> > > > > > There is a scenerio that the user
> > > > > > > data entered into the dataset could be in the db already and

it
> > will
> > > > > need
> > > > > > to
> > > > > > > update that record. Does the dataadapter rely only on the

> > rowstate
> > > to
> > > > > > > decide when to update or insert?
> > > > > >
> > > > > > Yes, The Adapter first checks if the HasChanges flag is set to

> true.
> > > If
> > > > > > false, it does nothing b/c nothing has changed. If so, it goes

row
> > by
> > > > row
> > > > > on
> > > > > > the changed rows and depending on the rowstate, decides which

> > command
> > > > it's
> > > > > > going to fire, uses the values in the rows to set the parameters

> and
> > > > fires
> > > > > > the update. You can modify this behavior somewhat by filtering

on
> > > > > Rowstate
> > > > > > so you could for instance, only call Update on rows that have

been
> > > added
> > > > > for
> > > > > > instance, or you could do added first, modified second and

deleted
> > > > third.
> > > > > >
> > > > > > If you call update or you call AcceptChanges, then the row

should
> be
> > > no
> > > > > > longer considered added. However, if you then modify it, and

it's
> > not
> > > > in
> > > > > > the db b/c you called acceptchanges and the Insert Commmand

never
> > > > executed
> > > > > > against it, then you'll most likely get a concurrency exception

> b/c
> > > the
> > > > > > dataadapter will think the row used to exist (b/c its in the

> > datatable
> > > > and
> > > > > > it's rowstate isn't added, it's modified) and has been deleted

> when
> > in
> > > > > fact
> > > > > > it didn't exist at all. This is dependent on your UPdate logic

to
> a
> > > > large
> > > > > > degree and the object you use, (You have very little control

with
> a
> > > > > > CommandBuilder) but from the sounds of your scenario, you may

need
> > > todo
> > > > > > something different.
> > > > > > Why are you calling AcceptChanges before you call update? This

> will
> > > > > ensure
> > > > > > that your update never fires as expected (at all in most cases).
> > > > > > Or am I misunderstanding it?
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > > "William Ryan eMVP" <(E-Mail Removed)> wrote in

> > message
> > > > > > > news:(E-Mail Removed)...
> > > > > > > > It won't change to modified until AcceptChanges is called on

> the
> > > > > dataset
> > > > > > > or
> > > > > > > > you call dataadatper.UPdate successfully which does the

same.
> > At
> > > > that
> > > > > > > > point, it's still Added. The DataAdapter will need to use

its
> > > > Insert
> > > > > > > > Command to submit the update. Your Insert Command is

> different
> > > from
> > > > > > your
> > > > > > > > Update Command... so if the row didn't exist yet in the db

and
> > it
> > > > used
> > > > > > and
> > > > > > > > Update statement, it would either not work and do nothing or

> it
> > > > would
> > > > > > > assume
> > > > > > > > it was deleted since it first saw it and throw a concurrency
> > > > > exception -
> > > > > > > it
> > > > > > > > would depend on how you were using the dataadapter..
> > > > > > > >
> > > > > > > > Either way, remember that Rowstate dictates what Command the
> > > Adapter
> > > > > is
> > > > > > > > going to use against the DB and it's going to use fields of

> that
> > > row
> > > > > as
> > > > > > > > Command Parameters.
> > > > > > > >
> > > > > > > > However, once you call acceptchanges, then all rows marked

> > Deleted
> > > > > > > (rowstate
> > > > > > > > is deleted) will by physically removed from the datatable

> > (until
> > > > > then,
> > > > > > > they
> > > > > > > > are still there, it's just that their rowstate is set to

> > Deleted),
> > > > and
> > > > > > all
> > > > > > > > of the Original values are reset to the current values.

This
> > way
> > > > the
> > > > > > > state
> > > > > > > > of the datatable matches the database (assuming you fired an
> > > Update
> > > > > > > > Command). This keeps the dataadapter from reissuing commands

> it
> > > > > already
> > > > > > > sent
> > > > > > > > for a given row.
> > > > > > > >
> > > > > > > > Hopefully this explains it?
> > > > > > > >
> > > > > > > > If notlet me know.
> > > > > > > >
> > > > > > > > Bill
> > > > > > > > "Sarah" <(E-Mail Removed)> wrote in message
> > > > > > > > news:(E-Mail Removed)...
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > In my application I am manually adding a datarow (with

user
> > data
> > > > not
> > > > > > > from
> > > > > > > > > db) in a typed dataset. At that time the datarow row

state
> is
> > > > > "Added"
> > > > > > > > which
> > > > > > > > > is correct. The problem I am running into is when I

modify
> > that
> > > > row
> > > > > > > later
> > > > > > > > > in the application the row state is still "Added" and not
> > > > > "Modified".
> > > > > > > Why
> > > > > > > > > is this the case?
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Strongly Typed DataSet Vs Un-Typed Dataset =?Utf-8?B?Sm9iIExvdA==?= Microsoft Dot NET 1 5th May 2005 08:43 AM
Typed DataSet Vs Un-Typed DataSet =?Utf-8?B?Sm9iIExvdA==?= Microsoft Dot NET 1 22nd Feb 2005 11:18 AM
Typed dataset questions: Translation into another typed dataset =?Utf-8?B?QmVhblRvd25CaXpUYWxrR3VydQ==?= Microsoft C# .NET 2 30th Dec 2004 04:55 PM
typed vs strong typed dataset Brad Allison Microsoft ADO .NET 3 21st Jul 2004 04:34 PM
Cast weakly typed DataSet to a strongly typed dataset??? Microsoft Dot NET 0 18th Sep 2003 10:18 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:25 PM.