(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
Go Back  Xtreme Visual Basic Talk > > > (re)fill datatable with dataadaptor does not reflect deleted rows


Reply
 
Thread Tools Display Modes
  #1  
Old 04-24-2007, 06:52 AM
Tom Tom is offline
Newcomer
 
Join Date: Aug 2003
Posts: 10
Default (re)fill datatable with dataadaptor does not reflect deleted rows


Hi all,

I am using a dataadaptor to populate datatables. All goes well but when i fill an already populated datatable it only reflects modified and added rows, but NOT deleted rows. So in multi-user applicastions if some other user deletes a row i can (re)fill all i want the row doesnt disappear until I clear the datatable an fill from scratch.
This is quite annoying because clearing the datatable flashes the datagridcontrol back to row 1.

Sollution anyone ?

Thanks a lot

Tom
Reply With Quote
  #2  
Old 04-24-2007, 07:41 AM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

Hi
you probably need to call DataSet.AcceptChanges to delete all the rows marked for delete.
if this doesn't work for you here is my approach I usually bind my DataGridView to a DataView object so it's easier to filter and find records, use commands and SQL to delete my record and then delete the same record from the Dataview without the need to reFill the dataSet.
This way I minimize the trips to the data base for the basic four operation "Add, Delete, Insert and Update"

I'm sure that there is so many different ways to do this so you might want to see other ways and choose which is best in your case

I hop this will help


Good luck
Reply With Quote
  #3  
Old 04-24-2007, 07:52 AM
Tom Tom is offline
Newcomer
 
Join Date: Aug 2003
Posts: 10
Default

I'm not sure if we're talking about the same thing here.
To clarify : user 1 deletes a row in a table on wich user 2 had previously performed a fetch (fill) operation. User 2 refreshes the dataset and finds all user1's changes but NOT his deletes. All deleted rows are still in the dataset.
I am realy talking about deletes happening from outside the program(instance).
Reply With Quote
  #4  
Old 04-24-2007, 08:36 AM
MKoslof's Avatar
MKoslof(re)fill datatable with dataadaptor does not reflect deleted rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

How are you "refreshing" the dataset? Are you refetching from the original database? A dataset or DataTable is really a disconnected piece of data - - unlike a DataReader which is a cursor that iterates the returned records until the cursor is at the end of the page file and it is released.

Your DataAdapter/or DataSet needs to refresh its stale data.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 04-24-2007, 08:43 AM
Tom Tom is offline
Newcomer
 
Join Date: Aug 2003
Posts: 10
Default

Hi,

This is the code.

Dim adapter As New SqlDataAdapter
Dim previousConnectionState As System.Data.ConnectionState

Try
previousConnectionState = HandleOpenConnection() ' open connection to DB
SetTsqlConnectionAndTransaction(sql) ' set connection sqlCommand object
adapter.SelectCommand = sql.SqlCommand ' connect sqlCommand to adapter

adapter.FillSchema(dataTable, SchemaType.Source) ' build table-schema for the dataAdapter

Dim pk(dataTable.Columns.Count) As DataColumn ' iterate all columns in the datatable to find al primary key fields
Dim counter As Integer = 0
For Each column As DataColumn In datatable.Columns
If column.ColumnName.StartsWith("PK") = True Then
pk(counter) = column ' add primary key field to the primary key array
counter += 1
End If
Next
Array.Resize(pk, counter) ' resize the array to the number of found primary keys

dataTable.PrimaryKey = pk ' assign primary keys to the datatable
adapter.Fill(dataTable) ' and refresh the data

Catch ex As Exception
' do some logging, removed for this post
Finally
HandleCloseConnection(previousConnectionState)
End Try


I'm not sure if the part where i get the primary keys is the most efficient way to handle it, but appart form the failure to retrieve the deleted rows it works fine. I get all changes in the underlying data reflected in the dataset and the grids respond by changing the displayed data but not resetting the grid back to row 1.
Reply With Quote
  #6  
Old 04-24-2007, 09:02 AM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Unhappy Hi

Hi
now I understand your problem, I'm afraid that there is no way in ADO .NET to reflect the changes on the database without clearing and refilling the dataset with your table, I miss the dynamic recordsets in VB6 ;-) . two solutions come to my mind the first is to try binding your datagrid view to a DataView that corresponds to your table in the dataset and see if it flashes when you refill your data set. I'm not sure if it will work.

the other one is a long and stupid one just bear with me for a moment:-
1-fill another table in your data set this new table has the latest records from your database.
2-you can compare the two tables row by row.
3-if a row in the old table doesn't exist in the new one you can delete it from your old, if that makes any sense

the problem with this method is when do you read your data to determine the new changes, and how often will you do it? this will cause more traffic on your database and I'm pretty sure that it's not optimal for scaling your application.

your problem IMO is one of the faces of concurrency problems, that i faced so many times before and here is how i deal with it DO NOT show the user any information on a datagrid unless it's absolutely necessary so you minimize the code you will write for user interaction, and solve any concurrency issues in the update or delete operations through the use of message boxes.

I apologize for the long post but this problem really touched an exposed nerve and until now I couldn't find a clear cut solution for it.

I hope that one of the Gurus in this forum notices this post and share his thoughts on how to best tackle this issue

Good luck
Reply With Quote
  #7  
Old 04-24-2007, 09:46 AM
Tom Tom is offline
Newcomer
 
Join Date: Aug 2003
Posts: 10
Default

Hey sgm,

I was comming to simmilar conclusions. I brewed something that does a merge of the old and the new datatable and then checks for the rowcounts. If the old (merged) table has more rows, there have been deletions.
The hard part is now to find them. I may seem very stupid here but i cant figure out how to compare the rows in both datatables. = obviously doenst work :-) but the query is generic and there may be more than 1 PK fields....
Reply With Quote
  #8  
Old 04-24-2007, 09:51 AM
MKoslof's Avatar
MKoslof(re)fill datatable with dataadaptor does not reflect deleted rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

ADO.Net facilities are primarily aimed at an optimistic approach based on the expectation of a small number of columns and a relatively low concurrency rate. There are pros and cons to using the ADO.net model. If you are working in a high concurrent user environment and this is a windows application (Web apps can provide some other caching and refresh techniques) you are going to have some tradeoffs and drawbacks to using ADO.net and the Data objects.

The first thing you need to do is take a "complete" concurrency approach. What I mean by that is at the core, forget about the DataSet objects, how do you handle if a user is working with a stale piece of data. A common approach is to use Last Update columns in your database tables. You store the last fetch date at the application tier and on the next user "interaction" (Delete, update, etc.) you run a query and verify the Last Update date is not greater than the preserved date at the app tier. If changes have occured, you refresh the data and prompt the user - - telling them the data has changed, review it again before attempting the transaction, etc. The real issue is putting together a strategy where user's don't conduct an action without first being presented with the most current data.

Again, remember that a DataSet and other ADO.net data objects are disconnected from the database server. In order to "know" something has changed, you need to re-fetch and use some sort of logic, such as the Last Updated Time Stamp to retrieve only new records, flag if existing records have changed, etc.

You could leverage stored procedure here and within your transactional statements pass the preserved last changed date. Based on this, you can pass specific return codes, raise errors from SQL or basically automate the process of "refreshing" or "validating" the data. But you will need to go back to the SQL Server or database object in some fashion.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 04-24-2007, 06:28 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Talking future Plans

Hi
Thank you MKoslof for your input on this always appreciated.

I was wondering is there any future plans from microsoft for ADO.NET to support connected data or better yet come up with an easier solution for heavily concurrent users environments.
what is the next step for ADO.NET ???

best regards
Reply With Quote
  #10  
Old 04-24-2007, 07:20 PM
MKoslof's Avatar
MKoslof(re)fill datatable with dataadaptor does not reflect deleted rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Well, for "connected data" we can use the DataReader object . The problem is a DataSet is simply a transport or disconnected way to merge, copy, relate and view data between the app tier and the underlying database.

I highly doubt there will be any future ADO.net implementation of the DataSet object that handles high user concurrency - - by its nature its not supposed to. If you are dealing with data that must be validated against mulitple users the concept of using any sort of disconnected container is somewhat flawed . Sure, there are tons of scenerios where it would be helpful or make sense - - but I'm not sure Microsoft has any plans to take a concept and completely re-work it to be something that strays from what its original purpose was.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #11  
Old 04-25-2007, 03:16 AM
Tom Tom is offline
Newcomer
 
Join Date: Aug 2003
Posts: 10
Default

This is a very clear view towards this problem you posted. The choise for the dataset was made explicitly because of its disconnected nature. So i guess i will have to live with it.
We decided to implement the following : all 'edits' are performed on a freshly loaded copy of the record to be edited, and refreshes of the entire datatable are checked on deletions (count old vs new resulttable) and the entire datatable is cleared and reloaded if necessary. Since the nature of the data is such that deletions are rare i think this is acceptable.

Thanks for the advice

Tom
Reply With Quote
  #12  
Old 04-25-2007, 08:30 AM
MKoslof's Avatar
MKoslof(re)fill datatable with dataadaptor does not reflect deleted rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Yes, that sounds like a good approach. Typically, any design that must cater to high user concurrency needs to work around the ADO.net DataSet, rather than depend on it. In previous projects, when a DataSet or DataTable had to be used, basically we had the stored procedure track "invalid" states or "contradictory" data in relation to the state assumed at the app tier - - then proper methods or notifications were conducted.

Sounds like you are on your way.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

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 Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows (re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
 
(re)fill datatable with dataadaptor does not reflect deleted rows
(re)fill datatable with dataadaptor does not reflect deleted rows
 
-->