Paging in DataGridView using VScrollbar

• Download source - 36.1 KB

Introduction

This article provides a way to programmatically implement paging in a Windows Datagrid.

Background

Microsoft DataGridView control in Framework 2.0 does not offer an inbuilt paging functionality so as to accommodate real time loading of data from an underlying data source for handling larger data. As an example, when one has to load larger sets of data; say from a text file using OLEDB text driver.

There are certain options to manually implement paging in a DataGridViewControl; one conventional and rather easy approach is to user "button controls" to navigate back and forth into the data source. On the other hand, a little difficult approach is to implement paging on the scroll bar.

The only hurdle in this implementation is that the default Vertical Scroll Bar in DataGridView control is not that flexible to handle all the matter. For this reason, a Scroll Bar control can be best used for such an implementation. The default vertical scroll bar of the DataGridView control does not offer much customization like customizing larger and smaller change.

The following article describes how to implement paging in DataGridView control with the help of a separate Scroll Bar Control.

Using the Code

Before we look at the code, do the following:

• Create a new C# Windows application with Visual Studio 2005
• Drag & Drop a DatagridView control on to the Windows Form
• Drag & Drop VScrollBar control on to the form

Now that we have done this, let's look at the code.

Reading from File

We can query the text file in the same way as we query in SQL DB or any other if we use ODBC or OleDb data adapters. Here I am using Oledb data adapter to query from a text file, so for that, at first we need a connection string:

System.Reflection.Assembly a = System.Reflection.Assembly.GetEntryAssembly();
baseDir = System.IO.Path.GetDirectoryName(a.Location);
string strConneStringOLeDb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
strConneStringOLeDb += baseDir+";Extended Properties='text;HDR=Yes;";
strConneStringOLeDb += "FMT=Delimited';"; 

For more information on Connection strings, see this link.

The next step is to read data from the file. For this purpose, one has to place a schema.ini file with the file from which you want to load data. The code to write a schema file is as follows:

private void writeSchema()
 {
    try
     {
        FileStream fsOutput =
             new FileStream(baseDir + "\\schema.ini",
                 FileMode.Create, FileAccess.Write);
        StreamWriter srOutput = new StreamWriter(fsOutput);
        string s1, s2, s3, s4, s5;
        s1 = "[" + fileName + "]";
        s2 = "ColNameHeader=True";
        s3 = "Format=TabDelimited";
        s4 = "MaxScanRows=25";
        s5 = "CharacterSet=OEM";
        srOutput.WriteLine(s1.ToString() + '\n' + s2.ToString() +'\n' +
       s3.ToString() + '\n' +s4.ToString() + '\n' + s5.ToString());
        srOutput.Close();
        fsOutput.Close();
      }
      catch (Exception ex)
      {
          MessageBox.Show(ex.Message);
      }
  }

Now to read data from the file (not the complete file but a chunk of data) and load it in the dataset:

private void LoadFromFile()
 {
    try
     {
        writeSchema();
           
        //Open the connection 
        if(conn.State == ConnectionState.Closed)
            conn.Open();

        //Fetch records from CSV
        //Count the number of lines to set the total pages      
        string sqlScalar = "Select Count(*) from [" + fileName + "]";
           
        OleDbCommand cmd = new OleDbCommand(sqlScalar, conn);
        nTotalFileRows = (int)cmd.ExecuteScalar();
        nTotalPages = nTotalFileRows / nPageSize;

        //set the maximum property equals to the total lines 
        this.vScrollBar1.Maximum = nTotalFileRows;
           
        LoadDataPage(0,nPageSize);
        if (bFirstIteration)
          {
              dataGridView1.DataSource = ds.Tables[0];
              bFirstIteration = false;
           }
           
     }
     catch (Exception ex) //Error
     {
          MessageBox.Show(ex.Message);
     }
  } 

Now to get a new chunk of data on scroll event, write code in the VScrollBar scroll event.
The LoadDataPage function used in the code below will fetch the next page from the file and update the underlying datasource:

private void vScrollBar1_Scroll(object sender, ScrollEventArgs e)
 {
    this.SuspendLayout();
    if (e.ScrollOrientation == ScrollOrientation.VerticalScroll)
      {
         if (e.Type == ScrollEventType.SmallIncrement)
            {
               nStartPosition++;
               LoadDataPage(nStartPosition, nPageSize);
             }
         else if (e.Type == ScrollEventType.SmallDecrement)
             {
               if (nStartPosition > 0)
                {
                 nStartPosition--;
                 LoadDataPage(nStartPosition, nPageSize);
                }
             }
         else if (e.Type == ScrollEventType.LargeIncrement)
             {
               nStartPosition += vScrollBar1.LargeChange;

               LoadDataPage(nStartPosition, nPageSize);
             }
         else if (e.Type == ScrollEventType.LargeDecrement)
             {
               nStartPosition -= vScrollBar1.LargeChange;

               LoadDataPage(nStartPosition, nPageSize);
             }
         else if (e.Type == ScrollEventType.ThumbTrack)
             {
               nStartPosition = e.NewValue;
             }
         else if (e.Type == ScrollEventType.ThumbPosition)
             {
               if (nStartPosition - 1 > 0)
                  nStartPosition -= 1;
               LoadDataPage(nStartPosition, nPageSize);
             }
      }
      this.ResumeLayout();
 }

Paging on Mouse Wheel

To implement paging on mouse wheel, you have to implement the MouseWheel event.

DataGridView.MouseWheel += new
MouseEventHandler(timeSeriesItemsDataGrid_MouseWheel);  

In this event, you can identify through the e.Delta property to show the previous or next record.

private void DataGridView_MouseWheel(object sender, MouseEventArgs e)
 {
    try
    {
      this.SuspendLayout();
      if(e.Delta < 0) 
        {
          if (nStartPosition + nPageSize <= nTotalFileRows)
            {
               vScrollDGV.Value = nStartPosition++;
               LoadDataPage(nStartPosition, nPageSize);
            }
             DeltaValue = e.Delta;
         }
       else if (e.Delta > 0)
         {
          if (nStartPosition > 0)
            {
               vScrollDGV.Value = nStartPosition--;
               LoadDataPage(nStartPosition, nPageSize);
            }
            DeltaValue = e.Delta;
         }
       this.ResumeLayout();
    }
   catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
 } 

The mouse wheel combines the features of a wheel and a mouse button. The wheel has discrete, evenly spaced notches. When you rotate the wheel, a wheel message is sent as each notch is encountered. One wheel notch, a detent, is defined by the Windows constant WHEEL_DELTA, which is 120. A positive value indicates that the wheel was rotated forward, away from the user; a negative value indicates that the wheel was rotated backward, towards the user.

Currently, a value of 120 is the standard for one detent. If higher resolution mice are introduced, the definition of WHEEL_DATA might become smaller. Most applications should check for a positive or negative value rather than an aggregate total.

Points of Interest

The next step will be to implement paging on resizing the grid control.