c# - Does SqlDataAdapter::Fill fetch the whole result set or is it on-demand? -

i trying connect datagridview sql server, , solution outlined in https://stackoverflow.com/a/18113368/492336 uses sqldataadapter , datatable:

var adapter = new sqldataadapter("select * foo", "server=myhost-pc\\sqlexpress;trusted_connection=yes;database=test1"); var table = new datatable(); adapter.fill(table); view.datasource = table; 

i'm trying figure out if method fetches entire dataset server, or if connects datagridview server can fetch new rows on demand.

for example if table has 1 million rows, of them fetched , written datatable object before sqldataadapter::fill has returned?

limiting number of rows loaded via sql limits them either qualitatively (where...) or via rather blunt limit clause. can use dataadapter load rows in "pages" or groups - bit @ time. uses mysql works many of other (all?) of dbproviders:

int pagesize = 10000; int page = 0; ... 

the initial loading:

string sql = "select * sample";  using (mysqlconnection dbcon = new mysqlconnection(mysqlconnstr)) {     dtsample = new datatable();      dasample = new mysqldataadapter(sql, dbcon);               dasample.fillschema(dtsample, schematype.source);     dbcon.open();      int rows = dasample.fill((page*pagesize), pagesize, dtsample); }  dgv2.datasource = dtsample; this.lblpages.text = string.format("rows {0} - {1}",                          ((page * pagesize) + 1),                          (page + 1 * pagesize)); page += 1; 

the key dataadapter(int, int, datatable) overload: allows specify first row , number of rows load. rather recreating dataadapter each page i'd use form/class level one. reading next pages leaves options:

dgv2.suspendlayout(); dtsample.rows.clear(); int rows = dasample.fill((page * pagesize), pagesize, dtsample); dgv2.resumelayout();  this.lblpages.text = string.format("rows {0} - {1}",                          ((page * pagesize) + 1),                          (page + 1 * pagesize));  if (rows != pagesize)    // last page?     page = 0;            else     page += 1; 

if do not clear rows, datatable accumulate them: is, after loading second set, have rows pages 1 , 2.

it can useful allow them accumulate given set loaded once only. if important still limit display 1 page @ time, can use dataview display current group: