CODEWITHSUNDEEP
c#sql.netdatagridviewsqldataadapter
sashoalm
sashoalm

Reputation: 79447

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 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?

Upvotes: 1

Views: 1656

Answers (2)

Ňɏssa Pøngjǣrdenlarp
Ňɏssa Pøngjǣrdenlarp

Reputation: 38865

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:

Upvotes: 1

Mohammad Aslam
Mohammad Aslam

Reputation: 7

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.

Upvotes: 1

Related Questions