468,785 Members | 1,730 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,785 developers. It's quick & easy.

Using DataGridView with large database tables

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

Mar 11 '07 #1
2 15108
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

Mar 11 '07 #2
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

Mar 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by thomasp | last post: by
1 post views Thread by RSH | last post: by
5 posts views Thread by John Dann | last post: by
reply views Thread by =?Utf-8?B?SmltIFdhbHNo?= | last post: by
1 post views Thread by weird0 | last post: by
1 post views Thread by Garima12 | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.