How to display large number of rows in a DataGridView?

[dasm]

Hi,

I have a stored procedure which gives around 8000 rows.
While datagridview displays smaller number of rows from another stored procedure, it times out to display the results for this one!!!

Any help is greatly appreciated..

[opedog]

I don't have many specifics as I've yet to delve into this a lot yet, but research the VirtualMode property on the DataGridView and see how you can use it. VirtualMode only renders the rows the user can view at the time. When the user scrolls it will retrieve the new rows as they scroll instead of overloading the control in the beginning.

I keep meaning to look this up and implement it on some of my controls in my app but haven't yet had the time.

[jasonli]

DataGridView for sure can show 8000 rows of data for quite a while. But what you should think about is users, how do they get what they want after long time scrolling up and down. It's too hard for them, not practical way.

I think you could put in more conditions to limit result data, or page the result data.

[dasm]

Page the result data ?

Can you give an example?

Thanks.

[dasm]

Hi,

Just figured out that - OdbcCommand property CommandTimeOut needs to be set up. By default it is 30 sec. So if the stored procedure takes more than that, you need to set it up.
Example:

 OdbcConnection thisConnection = getConnection();
 OdbcCommand nonqueryCommand =   thisConnection.CreateCommand();
 nonqueryCommand.CommandTimeout = 300;

Hope it will be useful..

[dglienna]

Read the values into an array, or list, either at start up, or as needed. Then, wait for the user to press the first letter of the name he wants, and then show them.

As he types another letter, show less and less records that match the result.

[Shuja Ali]

What is the use of showing 8000 rows at a time. That would not make any sense unless it is a report. You should look at Pagination.

[dasm]

Hi ,

Actually , the problem was to fetch the result itself before even displaying it!!. The stored proc takes more time than 30 sec..
Now that it is given required time to fetch the result, i can see that the datagridview displays all the 8000 rows..
Yes, now i have to think of displaying it in pages.. how to do pagination, by the way.. !
dglienna' idea of displaying based on the user selected letter is also a nice one.. but if there is standard property or method for datagridview to page, i do not know .. can someone help??

[Shuja Ali]

I would build this logic into stored procedure itself. The Stored procedure used to retrieve data would then give me results in pages. This is better and more efficient way of doing this. All you would have to do is provide couple of buttons for Previous and Next.

The stored procedure would take two extra parameters, one for the number of records per page and second that will tell the Stored Procedure which page to return. I don't have enough time on hand to write code for you, but then if you check the database that you are using there might be several examples of Pagination present on the internet. Just google them.

Now in case you don't want to tweak your stored procedure, just look at the overload of the Fill() method of the dataadapater. You will see an overload that lets you select the number of records for each fill. Take a look http://msdn.microsoft.com/en-us/libr...pter.fill.aspx

[dasm]

Thanks for the reply - but i wanted fastest way.. just if someone has the code ready..
Its just a dumb idea to know about search engine for different options...
I will post the code here once i am done with it.

[dasm]

Hi,
Here is one way to paginate large number of rows in datagridview. Create two text boxes and pass the starting position and ending position. The OdbcAdapter- fill method takes parameter for starting and ending row position.
Here is the code : (someone may find it useful !!)

public void execute(DataGridView d, ComboBox c1,ComboBox c2,ComboBox c3)
        {
            string dbname = c2.SelectedItem.ToString(); //pass database name from the list of databses you are working on
            OdbcConnection thisConnection = getConnection(dbname);
            OdbcCommand nonqueryCommand = thisConnection.CreateCommand();
            nonqueryCommand.CommandTimeout = 300;

/* initialize variables: startPos and endPos from the textboxes' Text in the event of submit button. Get the values of them from get method */

            int startPos = getStartPos(); 
            int endPos = getEndPos();

            try
            {
                thisConnection.Open();
                
                nonqueryCommand.CommandText = "your stored procedure";
                nonqueryCommand.CommandType = CommandType.StoredProcedure;
                
                OdbcDataAdapter da = new OdbcDataAdapter(nonqueryCommand);
                DataSet ds = new DataSet();

                  if (startPos != 0 && endPos != 0)
                {
                    MessageBox.Show("Display Record from " + startPos.ToString() + " to " + endPos.ToString());

                    da.Fill(ds, startPos, endPos, "TableA");   //It happens here

                } 
               else 
                {
                    da.Fill(ds, "TableA");    //display all if startPos and endPos not set.
                }

                ds.Tables["TableA"].DefaultView.AllowNew = false; 
                

                d.AutoGenerateColumns = true;
                d.DataSource = ds.Tables[0];
                thisConnection.Close();
            }
            catch (OdbcException ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }