Deleting rows from dataview or datatable

.NET
1

I fill a datatable, do some calculations, sort it with a dataview, and then bind the datatable to a datagrid. Before binding to the grid though, I need to delete some rows. If I delete the rows from the view, the row count decreases as i delete rows, throwing off the indexes, causing it to not delete the correct rows. If I delete the rows from the datatable instead of the view, the datatable doesn’t seem to be sorted as I set it in the view until it binds to the grid, so it’s also deleting incorrect rows. Make sense? How can I delete rows from a sorted datagrid without the indexes being thrown off?! Thanks.

2

i am guessing that you need to do a DataTable.Select() method. Then delete them via the DataTable.

My guess is that the dataview doesnt sort them until you call the DataBind method on the control.

Also some code will help

3

Actually, the dataview sorts them before the bind, but deleting from the view causes the rows to be removed, and the indexes are then off as it loops through the deletes.

If I delete from the datatable, the indexes are correct, but the rows aren’t sorted yet, so I’m not deleting the correct rows.

Isn’t the .select method for using SQL? I need to delete rows based on their index from the dataview (rows 0-199, or rows 200-399, etc.). Since the datatable isn’t sorted for the final output, I don’t see how a .select would work.

4

You should delete based on the primary key in the datatable, and not the index that it has in the datatable.

Deleting via the primary key should make it easier on yourself because you do not have to worry about the index and you can delete it regardless of if its sorted or not.

5

It would be great to delete via the primary key and not have to mess with the indexes, but I’m not clear on how I would determine the group of records that I’m deleting. My code performs some logic on the records ordered by modify date, and then resorts them alphabetically for display in the datagrid. How then would I get a handle on the primary keys for, say, records 1000 through 1500 out of 2000?

6

Well i am assuming that your DataTable has primary keys from the database. first you would do a DataTable.Select() to get the rows you want to delete. then you would loop throught the select results with a foreach


DataRow[] results = dt.Select("...");
foreach(DataRow dr in results)
{
dr.Delete();
}

As I said DataTable has a function called Select() that is completely disconnected to the database. You do not have to be connected to the data source.

7

Thanks for the help, archigamer. I’ve got it working, with a little different approach. At first, I was considering paging by the number of records (show records 1-1000, show records 1001-2000, etc.). With that approach, I’m not sure that there was a way I could filter, since the primary keys wouldn’t have necessarily been in order. I’ve gone to viewing by groups of last names now. FYI: I use a rowfilter on a dataview of the datatable to show the last names in a range (A-F, G-L, etc.) bind that view to the grid, and cache the datatable. A dropdown menu allows choosing a different range of names, and then I create a new view of the cached datatable. All seems good.

One concern is the load on the server of caching datatables of 10000-15000 rows. I’ve set them to expire after 30 minutes, so that will help, and luckily only about half a dozen users out of 100s will have a datatable big enough needing to be cached. Just thought I’d let you know, and if you’ve got any thoughts/suggestions on the cache size, I’m all ears. It appears the aspnet_wp.exe is holding the cached info? Thanks again.

8