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

Question

I was trying to connect a DataGridView to a SQL Server, and the solution outlined in https://stackoverflow.com/a/18113368/492336 uses a SqlDataAdapter and a DataTable:

var adapter = new SqlDataAdapter("select * from foo", "server=myhost-pc\\sqlexpress;trusted_connection=yes;database=test1");
var table = new DataTable();
adapter.Fill(table);
view.DataSource = table;

I'm trying to figure out if this method fetches the entire dataset from the server, or if it connects the DataGridView to the server so it can fetch new rows on demand.

For example if the table has 1 million rows, will all of them be fetched and written into the DataTable object before SqlDataAdapter::Fill has returned?

Yes, All data returned by query will be filled in DataTable. You can modify query using where clause in order to filer data — Dheeraj Kumar, Aug 08 '16 at 12:14

score 1 · Answer 1 · answered Aug 08 '16 at 12:22

1

yes , it will create object and will implement on your grid view to show your all data, the thing is that how you write the SQL query , you can limit the data rows by using some SQL keywords like TOP, LIMIT, OFFSET.

answered Aug 08 '16 at 12:22

Mohammad Aslam

21
6

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-08-08T13:38:43.067

Limiting the number of rows loaded via SQL limits them either qualitatively (WHERE...) or via a rather blunt LIMIT clause. You can also use the DataAdapter to load rows in "pages" or groups - a bit at a time. This uses MySQL but it works with many of the other (all?) of the DBProviders:

int pageSize = 10000;
int page = 0;
...

The initial loading:

string SQL = "SELECT * FROM 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 is the DataAdapter(int, int, DataTable) overload: it allows you to specify the first row and the number of rows to load. Rather than recreating the DataAdapter for each page I'd use a form/class level one. Reading the next pages leaves you some 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 you do not clear the rows, the DataTable will accumulate them: that is, after loading the second set, it will have all the rows for pages 1 and 2.

It can be useful to allow them to accumulate so that any given set is loaded once only. If it is important to still limit the display to one page at a time, you can use a DataView to display only the current group:

This answer is very detailed, thanks. So the way I used it it fetched all the data, but there is a way to make it on-demand after all. — sashoalm, Aug 08 '16 at 13:43
You can combine SQL limits with it, such as `WHERE Active = True`, then load *that* subset in pages. — Ňɏssa Pøngjǣrdenlarp, Aug 08 '16 at 13:46

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

2 Answers2