Tip on filling DataSet in C#

[Martin Arvidsson]

Hi all gurus out there!

When i use the Wizard to create some fields from a table it creates, the
dataset, bindingsource and tableadapter.

Now for the problem. When i open that form in my program, it automaticly
fills the dataset with all the records in the table. In this case there is
over one milion records. It takes ages to fill the dataset and view the
form.

So i created a Fill procedure that only gets the first 1000. Now if the
customer want to search beond this record how can i do that, ie fill the
dataset with the next record from the table....

Alla in all, i just want to fill the dataset with x records, then fill x
record at a time when clicking next.

Any suggestions, links to a nice page describing this problem?

Regards
Martin

 

[William \(Bill\) Vaughn]

This is a common question so be sure to search the archives.
The default behavior of the TableAdapter assumes you're working with a home
database that can fit on a floppy. IMHO, it should encourage developers to
build parameter-based queries to return just the subset of table rows that
are needed. No, there is no built-in mechanism to page through a large table
like yours. However, most developers approach this problem by using
"boundary" parameters. For example, one type of boundary parameter sets a
global criteria for the query--as when you choose to search for Pizza stores
in a specific zip code. By forcing the user to limit the scope of the query,
you permit the DBMS to make an intelligent search and return an intelligent
(and small) rowset. If this approach returns a lot of Pizza stores (more
than will fit on a form or page), you'll want to limit the rows by using TOP
(if available) or other SQL that limits the total rows returned. To fetch
the next set of rows, include a parameter that specifies that the first row
should be the same as (or one higher than) the last row fetched based on an
indexed column.

All of these parameter-driven queries can be incorporated into the
TableAdapter by using the TableAdapter diagram page in VS. Right-click the
"Fill" and create a new query that includes the needed parameters. When you
invoke the Fill, you'll be prompted with Intellisense to provide the
parameter values.

I describe these techniques in depth in my book.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------