sashoalm sashoalm - 4 months ago 14
SQL Question

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

I was trying to connect a DataGridView to a SQL Server, and the solution outlined in http://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?

Answer

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: