Using DataGridView with large database tables

[Steve Richter]

what is the best way to use DataGridView to view data from large
database tables? Where the sql select statement might return millions
of rows?

string connectionString = GetFinancesConnString();
string sqlSelect =
"select CheckNbr, CheckDate, CheckAmt from Disbursements" ;

SqlDataAdapter adapter =
new SqlDataAdapter(sqlSelect, connectionString);
DataTable data = new DataTable();
adapter.Fill(data);

mBindingSource1.DataSource = data;
mGrid1.DataSource = mBindingSource1;

Is the DataTable immediatately filled with all the rows returned by
the sql select stmt?

Since the grid is only displaying a page of rows at a time, do the
DataTable, SqlDataAdapter, BindingSource and DataGridView classes work
together to only return the rows actually needed to paint the control?

A 2nd question: I have an "add" button on the form which adds to the
sql table displayed in the DataGridView. I understand that to refresh
the contents of the DataGridView you have to rebind to the data
source. What about when you want the top row in the DataGridView to
be the last added row? Currently I do the following:
- prompt the form to add the row. return the keys to the added row.
- run the above code to do the sql select from database into the
DataTable and bind the DataTable to the DataGridView.
- read the DataTable sequentially to find the RowIndex of the keys
of the just added row.
- set the FirstDisplayedScrollingRowIndex property of the
DataGridView to the found DataTable row

This seems like potentially a lot of work for the system. I understand
the Sql Select stmt should be as narrowly focused as possible. What I
would like to know is if the DataGridView scales well or if I should
be looking at a custom control that displays the table contents a page
at a time.

thanks,

-Steve

[RobinS]

1) Check out Virtual Mode.

2) You don't have to rebind, technically speaking. If you use BindingSource
between your DGV and your data, you can just change the data source for the
BindingSource, and it will update your display immediately.

BindingSource myBindingSource = new BindingSource();
myBindingSource.DataSource = myDataSet.Tables(0);
myDataGridView.DataSource = myBindingSource;

The BindingSource also exposes a bunch of properties and methods you can
use, like Find and Filter.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
"Steve Richter" <St************@gmail.comwrote in message
news:11**********************@8g2000cwh.googlegrou ps.com...

what is the best way to use DataGridView to view data from large
database tables? Where the sql select statement might return millions
of rows?

string connectionString = GetFinancesConnString();
string sqlSelect =
"select CheckNbr, CheckDate, CheckAmt from Disbursements" ;

SqlDataAdapter adapter =
new SqlDataAdapter(sqlSelect, connectionString);
DataTable data = new DataTable();
adapter.Fill(data);

mBindingSource1.DataSource = data;
mGrid1.DataSource = mBindingSource1;

Is the DataTable immediatately filled with all the rows returned by
the sql select stmt?

Since the grid is only displaying a page of rows at a time, do the
DataTable, SqlDataAdapter, BindingSource and DataGridView classes work
together to only return the rows actually needed to paint the control?

A 2nd question: I have an "add" button on the form which adds to the
sql table displayed in the DataGridView. I understand that to refresh
the contents of the DataGridView you have to rebind to the data
source. What about when you want the top row in the DataGridView to
be the last added row? Currently I do the following:
- prompt the form to add the row. return the keys to the added row.
- run the above code to do the sql select from database into the
DataTable and bind the DataTable to the DataGridView.
- read the DataTable sequentially to find the RowIndex of the keys
of the just added row.
- set the FirstDisplayedScrollingRowIndex property of the
DataGridView to the found DataTable row

This seems like potentially a lot of work for the system. I understand
the Sql Select stmt should be as narrowly focused as possible. What I
would like to know is if the DataGridView scales well or if I should
be looking at a custom control that displays the table contents a page
at a time.

thanks,

-Steve

[Yuancai \(Charlie\) Ye]

Hi, Steve Richter:

You have to use server cursor for accessing data tables. See the article
at http://www.udaparts.com/devguide.htm. The article tells you limitations
of ADO.NET and the reasons why you can NOT use ADO.NET for large datatable.

Wish the article helps you understanding ADO.NET.
Regards,

"Steve Richter" <St************@gmail.comwrote in message
news:11**********************@8g2000cwh.googlegrou ps.com...

what is the best way to use DataGridView to view data from large
database tables? Where the sql select statement might return millions
of rows?

string connectionString = GetFinancesConnString();
string sqlSelect =
"select CheckNbr, CheckDate, CheckAmt from Disbursements" ;

SqlDataAdapter adapter =
new SqlDataAdapter(sqlSelect, connectionString);
DataTable data = new DataTable();
adapter.Fill(data);

mBindingSource1.DataSource = data;
mGrid1.DataSource = mBindingSource1;

Is the DataTable immediatately filled with all the rows returned by
the sql select stmt?

Since the grid is only displaying a page of rows at a time, do the
DataTable, SqlDataAdapter, BindingSource and DataGridView classes work
together to only return the rows actually needed to paint the control?

A 2nd question: I have an "add" button on the form which adds to the
sql table displayed in the DataGridView. I understand that to refresh
the contents of the DataGridView you have to rebind to the data
source. What about when you want the top row in the DataGridView to
be the last added row? Currently I do the following:
- prompt the form to add the row. return the keys to the added row.
- run the above code to do the sql select from database into the
DataTable and bind the DataTable to the DataGridView.
- read the DataTable sequentially to find the RowIndex of the keys
of the just added row.
- set the FirstDisplayedScrollingRowIndex property of the
DataGridView to the found DataTable row

This seems like potentially a lot of work for the system. I understand
the Sql Select stmt should be as narrowly focused as possible. What I
would like to know is if the DataGridView scales well or if I should
be looking at a custom control that displays the table contents a page
at a time.

thanks,

-Steve