Using Wisej’s DataGridView VirtualMode to implement a Database Paging with SQL Server 2016

In some cases, it is necessary to load many rows from a database table into a DataGridView but this can result in performance issues. If you want to load e.g. 50.000 rows into the DataGridView control, you will have to first fetch all the rows from the database to your server and then send them to the client DataGridView control. This will for sure take some time.

Fortunately, Wisej provides a kind of VirtualMode, which gives us the possibility to fetch the data from the database when we really need it.

When using this feature, you need to handle the process of bringing data into the grid and saving it back to your storage. Therefore, Wisej provides 2 events, CellValueNeeded and CellValuePushed.

I want to show you now how you can implement a possible database paging with the help of the Wisej VirtualMode implemented in the DataGridView control.

1. First, you have to download the AdventureWorks2016CTP3 file from Microsoft.
2. Import this backup file to your SQL Server 2016 database.
3. Open your Visual Studio and create a new Wisej web application.
4. Add a DataGridView control to your Window1 designer.
5. Choose the Columns Editor from the property windows and add a DataGridViewTextBoxColumn to the DataGridView control.
   
   
6. Set the property VirtualMode of the DataGridView control to 'true'.
   
   
7. Subscribe to the CellValueNeeded and CellValuePushed events.
   
   
8. Before we go on with the C# implementation, we add the ConnectionString to our web.config.
   
   

   <connectionStrings>
       <add name="AdventureWorks2016CTP3ConnectionString" connectionString="Data Source=(local);Initial Catalog=AdventureWorks2016CTP3;Integrated Security=True"
         providerName="System.Data.SqlClient" />
     </connectionStrings>

9. We’re using a DataSet. Therefore, we have to add a reference to the System.Data assembly to our project.
10. Next, we define some instance variables within our Window1.
    
    

    DataSet dsResult = null;
    const string ResultTableName = "ResultTable";
    int VirtualPageSize = 100;
    int LastBlock = -1;
    

11. Wisej DataGridView control needs to know how many rows will be displayed, so we subscribe to the Load-Event of the form and assign a value to the RowCount property.
    
    

    private void Window1_Load(object sender, EventArgs e)
    {
        this.dgvDatabasePaging.RowCount = GetRowCountFromDataBase();
    }
    
    private int GetRowCountFromDataBase()
    {
        using (SqlConnection connection = new SqlConnection())
        {
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["AdventureWorks2016CTP3ConnectionString"].ConnectionString;
            connection.Open();
            string queryString1 = $@"SELECT Count(*) FROM  Person.Person";
            SqlCommand com = new SqlCommand(queryString1, connection);
            int count = Convert.ToInt32(com.ExecuteScalar().ToString());
            return count;
        }
    }
    

12. To cut a long story a bit shorter, we only implement the CellValueNeeded functionality. So, we need to add a function which loads data from the database within a specific range/block. Here it is:
    
    

    private void GetVirtualResultSetFromDb(int start, int end)
    {
        string sWhereClause = string.Empty;
        using (SqlConnection connection = new SqlConnection())
        {
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["AdventureWorks2016CTP3ConnectionString"].ConnectionString;
            connection.Open();
    
            sWhereClause = $@"WHERE RowNum >=  {start}
            AND RowNum <= {end}";
    
            string queryString1 = $@"SELECT BusinessEntityID FROM  (
                SELECT RowNum = ROW_NUMBER() OVER (ORDER BY BusinessEntityID), COUNT(BusinessEntityID) OVER() [Total], *
                FROM Person.Person ) AS a {sWhereClause} 
                ORDER BY a.BusinessEntityID";
    
            SqlDataAdapter adapter = new SqlDataAdapter(queryString1, connection);
            dsResult?.Clear();
            dsResult = new DataSet();
            adapter.Fill(dsResult, ResultTableName);
        }
    }
    

    In a Sub-Select we only select the rows from the database within the specific range/block.
13. As a last step, we implement our CellValueNeeded handler. For more information see the comments in the code below.
    
    

    private void dgvDatabasePaging_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
    {
        //determine the valid RowIndex within our block/range
        int rowIndex = e.RowIndex % VirtualPageSize;
        //determine in which block/range we currently are
        int block = e.RowIndex / VirtualPageSize;
    
        if (dsResult == null || LastBlock != block)
        {
            //Scroll direction
            bool down = block > LastBlock;
            LastBlock = block;
    
            //define range values
            int start = e.RowIndex + 1;
            int end = VirtualPageSize;
    
            //update end value, scrollup or scrolldown
            if (down)
                end = start + VirtualPageSize;
            else
                end = start - VirtualPageSize;
            //fill the DataSet with new values from the specific range
            if (start <= end)
                GetVirtualResultSetFromDb(start, end);
            else
            {
                if (end < 0)
                    end = 0;
                GetVirtualResultSetFromDb(end, start);
            }
        }
    
        //assign cell value
        e.Value = dsResult.Tables[0].Rows[rowIndex].ItemArray[e.ColumnIndex];
    }
    

As you can see: with the help of Wisej DataGridView VirtualMode it is possible to easily implement a database paging, which will improve your web application performance and lower the server-side memory usage.

Download the code: Wisej DataGridView VirtualMode example for Visual Studio 2015 (Zip file)