Deleted Records and DataAdapter.Fill

  • Thread starter Thread starter rawCoder
  • Start date Start date
R

rawCoder

Dear All,

Question
Why DataAdapter.Fill does not refresh the deleted records in the dataset?
If this is by design then how can I refresh the data and get rid of removed
records without actually refilling the dataset with clear and fill
operations?

Detailed
I have a DataAdapter refering to a table in database. The data is being
shown in a DataGrid. As the data is updated, in the table (from other
connection), it is reflected in the grid by a call to the Fill method of the
DataAdapter. All is well , but if some row is deleted then the call to Fill
method does not delete the record from its underlying DataSet. I have come
to know that calling clear method is an option but it seems so dirty -
clearing all the data and then getting it again.

Can someone plz guide Why it is done this way and what is the resolution.

Thank You
 
Hi Rawcoder

Is this a Web or a Windowform datagrid?

For a windowform datagrid this sounds strange, as soon as you delete a row
from the datatable, it should be reflect in a windowform datagrid.

However when you refill that, they have to be correctly updated in the
database of course.

I hope this helps?

Cor
 
If all of the deletion is being done client side, why pull over two separate
instances of the same datatable. Why not just share it and let the deleting
function work on the same table as the one in the grid. You can easily
share a table by making it a static property for instance (shared in
VB.NET). As soon as the local deletion is done, it will be reflected in
both places. When you call update from one, it will immediately affect the
other.

This has a few benefits
1) Solves this problem
2) Saves the overhead associated with calling to Select statement and
filling both of them
3) Keeps everything in sync, much easier to code and maintain.

HTH,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
 
Ok please let me clear out the scenario here.

I have making kinda reporting tool (WinForm) which uses Extended Stored
Procedure and Triggers to get notified when there is a change in the
Database in certain table. When the notification (via Socket) arrives i call
the dataadapter.Fill method ... which works fine for addition and edition of
rows. But when there is a row/record deleted, the Fill method just deos
nothing and keeps showing the deleted row unlesss I .Clear the dataset (
which i think is not such a pretty way ).

So I repeate the question - Is there any way to avoid the .Clear & .Fill
strategy? and out of curiosity why does DataAdapter's Fill method act this
way?

Thank You
 
No, I see his point, and a quick experiment (and a check of the
documentation) indicates there may be an issue here.

1. Client A requests information from the database and retrieves a DataSet.
2. Client B requests the same information and gets the same DataSet.
3. Client A makes some changes, and most notably deletes record '123'.
4. Client A generates a new DataAdpater with the same SELECT, uses Fill() to
update the DataSet, and issues an Update(), which deletes record '123' from
the database (along with any other changes).
5. Client B makes some changes to the DataSet, but does not affect record
'123'.
6. Client B generates a new DataAdapter with the same SELECT and uses Fill()
in preparation for the Update() call -- but the record '123' remains in the
DataSet!

-ken
 
Hi Ken,

Did you see the message I was answering to RawCoder today?

In your message I do not understand this.
1. Client A requests information from the database and retrieves a DataSet.
2. Client B requests the same information and gets the same DataSet.
3. Client A makes some changes, and most notably deletes record '123'.
4. Client A generates a new DataAdpater with the same SELECT, uses Fill()
Click to expand...
to

Why has he to do a Fill before the update the dataset?
update the DataSet, and issues an Update(), which deletes record '123' from
the database (along with any other changes).
5. Client B makes some changes to the DataSet, but does not affect record
'123'.
6. Client B generates a new DataAdapter with the same SELECT and uses
Click to expand...
Fill()

The same as above?
 
Actually, this came from another thread that I was reading and for which I
had not completely worked out the logic. The basic premise is that when
multiple clients can update the same database table, each update from a
single client needs to retrieve the changes made by the other clients as
well.

For the purpose of this discussion it does not matter whether the Fill() is
called before or after the Update() call -- although it will likely have an
impact on the actual changes committed to the database -- the point is that
the Fill() call to an existing DataSet is supposed to merge the existing
contents into the existing DataSet, but it does not seem to deal with
records deleted by another client! I suspect that one really needs to do is
commit their own changes via Update() and then replace the contents of the
entire DataSet (or at least the affected DataTable).

There are some larger issues here that I will address in a separate thread.

-ken
 
Hi Ken,

In my opinion is the normal sequence

Fill 'clean dataset
update 'which does at the end acceptchanges
Fill 'refresh dataset with information changed by other users (and yourself
by autokey)
update
Fill 'refresh dataset

Cor
 
Thanx Allen,Lighthert and Ryan

Well all this discussion guides me to the usage of Data Adapter in case of
single user and in case of multi user the DataAdapter's Fill method actually
MERGES the data so the rows deleted by one user are not updated in other
users DataSet. And that to refresh the deleted rows one needs to clear the
datatable in this scenario.

right ???

Well then its .Clear to the rescue I guess. Although i will hate to use it.
I still wonder why this design was chosen by MSFT ... i hope it isnt
influenced by some J Branded technology.

Any thing you might wanna add is most welcome.

Thank You All Again
 
Hi Raw Coder
Well all this discussion guides me to the usage of Data Adapter in case of
single user and in case of multi user the DataAdapter's Fill method actually
MERGES the data so the rows deleted by one user are not updated in other
users DataSet. And that to refresh the deleted rows one needs to clear the
datatable in this scenario.

right ???
Click to expand...

In my opinion yes.

Cor
 
Back
Top