Partially fill the datatable?

  • Thread starter Thread starter Earl
  • Start date Start date
E

Earl

Right now, I have an app with an SQL2k backend that is just getting revved
up. Currently, I fill my datatables with everything that is in the real
database table (I've done a good job of normalizing the database, so the
tables are fairly thin). After fill, I then populate a grid for historical
purposes and use the in-memory datatable to allow the user to
add/update/delete. Everything works well. For the time being.

But what happens when I have 10,000 or even 100,000 records in a particular
table? I surely do not want to create an in-memory datatable with that many
records. While I can probably figure out how to deal with TOP 100 or
whatever for display purposes, I'm curious how others handle this situation
for Update and Delete scenarios? What happens if you are displaying TOP 100
and the record you need is not in there for, let's say, Update -- another
trip to the server to retrieve the record you want to Update -- and another
partially filled table?
 
Hi Earl,

Yes, retrieving that many records doesn't make sense in most situations.
I do it like this:
First, I have a form with a filter that lets user retrieve only data he
wants (for viewing only).
Then I let user select the row and click on properties to edit it.
Only now, I load only the data that belongs to that row.
 
Hi Earl,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to fill a part of the database
table to the DataSet. If there is any misunderstanding, please feel free to
let me know.

If there is about 10,000 or 100,000 records in the database table, filling
all of them to the DataSet really hits performance. Generally, we can use
some clauses such as WHERE in the SELECT statement to filter the useless
records and fill what we need to the DataSet. We can also use TOP keyword
to get the top n records in the resultset.

Please check the following link for more information:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
sa-ses_9sfo.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top