Large DataSets and GridView Problems displaying data more than once!

[Dave]

I'm having problems getting the GridView to reliably display a large
amount of data (50,000+ rows).

I am working my way through the excellent book “Real World ASP.NET
Best Practices” by Farhan Muhammad. In some cases I’m trying to
extend the examples.
Currently, I have the following code to read data from a SQL Server
2005 database and display in a GridView:


DataSet MyDataSet = new DataSet();
SqlDataAdapter MyAdapter = new SqlDataAdapter();
SqlCommand MyCommand = new SqlCommand();
MyCommand.Connection = new SqlConnection(MyConnection);
MyCommand.CommandText = " select * from Table_1";

MyAdapter.SelectCommand = MyCommand;
MyCommand.Connection.Open();
MyAdapter.Fill(MyDataSet);

GridView1.DataSourceID = "";
GridView1.DataSource = MyDataSet.Tables[0].DefaultView;
GridView1.DataBind();
MyCommand.Connection.Close();
MyCommand.Dispose();

I created Table_1. It’s just a list of firstnames, lastnames. The code
works the first time and the GridView displays the 50,000 data. But
when I hit the button again, I get “Internet Explorer cannot display
the webpage”. I’ve verified that the first line of code is not even
hit AND SQL Profiler shows no database activity. What’s going on?
I’d be the first to admit, that one would not normally have a table
with 50,000+ records, and want to display all of the records (no
filter!) in a GridView, but I’d still like to know what is going on.

Incidentally, the original exercise (from Ch.2 of the book) searched
for a specific record with:
MyCommand.CommandText = " select * from Table_1 where firstname = '" +
txtFirstName.Text + "' ";

The point of the exercise was to show that it was faster to go
directly to the database rather than hit cache. I found this to be
true if one used the binding methods suggested by the authors:

DataSet MyDataSet = Cache["Result"] as DataSet;
DataView MyDataView = new DataView(MyDataSet.Tables[0]);
MyDataView.RowFilter = "firstname = '" + txtFirstName.Text + "' ";
GridView1.DataSourceID = "";
GridView1.DataSource = MyDataView;
GridView1.DataBind();

However, if one uses the following:

DataSet MyDataSet = Cache["Result"] as DataSet;
DataSet newDataSet = new DataSet();
newDataSet.Merge(MyDataSet.Tables[0].Select("firstname = '" +
txtFirstName.Text + "' "));
GridView1.DataSourceID = "";
GridView1.DataSource = newDataSet.Tables[0].DefaultView;
GridView1.DataBind();

Then, going to cache is faster than going to the database.
Admittedly, this idea of Merging datasets may not have existed when
the book was written (2003), and/or I’ve missed something important.
Opinions?

Thanks,


Dave

 

[Munna]

Hi,,,

First of all 50,000 row in a single page.... not seems to be very
helpfull...
To create 50000 row at once will add huge amount of html markup in
page...

I would better do a paged view of the data ... and make sure that i
only
fetch only the data of the current page...

http://www.codeproject.com/KB/aspnet/PagingGridView.aspx

Best of luck

-----------
Munna

www.munna.shatkotha.com/blog
www.munna.shatkotha.com
www.shatkotha.com