C# DataGridView virtual mode: Enable sorting

Question

Is there a way to sort a DataGridView in virtual mode?

I have implemented a Gridview in virtual mode following this microsoft example: http://msdn.microsoft.com/de-de/library/ms171624.aspx . Also I have modified the example to be able to write data to database. This works out fine and the virtual mode gives a huge increase in speed, but my customer needs to sort the columns.

After searching the web for a while I found the Link http://social.msdn.microsoft.com/forums/en-US/winformsdatacontrols/thread/25b3f7c3-95b6-4c49-802b-b5a2a62915ac , but can´t get it to work.

Could anybody point out how if there is way to sort columns in virtual mode and if so, how I would have to do it?

Thanks a lot in advance!

Answer 1

Ok, I´ve solved this now. Using the microsoft example ( http://msdn.microsoft.com/de-de/library/ms171624.aspx ) I modified the DataRetrieverLog class to receive an additional value in the constructor: The column name to sort by (this includes the sort direction, e.g. "Name ASC"). For anyone interested, here´s the modified code:

public class DataRetrieverLog : IDataPageRetriever
{
    private string tableName;
    private string sortColumn;
    private SqlCommand command;
    private DataTable table;
    private SqlDataAdapter adapter;

    public DataRetrieverLog(string connectionString, string tableName, string sortColumn)
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        command = connection.CreateCommand();
        this.tableName = tableName;
        this.sortColumn = sortColumn;
    }

    private int rowCountValue = -1;

    public int RowCount
    {
        get
        {
            // Return the existing value if it has already been determined. 
            if (rowCountValue != -1)
            {
                return rowCountValue;
            }

            // Retrieve the row count from the database.
            command.CommandText = "SELECT COUNT(*) FROM " + tableName;
            rowCountValue = (int)command.ExecuteScalar();
            return rowCountValue;
        }
    }

    private DataColumnCollection columnsValue;

    public DataColumnCollection Columns
    {
        get
        {
            // Return the existing value if it has already been determined. 
            if (columnsValue != null)
            {
                return columnsValue;
            }

            // Retrieve the column information from the database.
            command.CommandText = "SELECT * FROM " + tableName;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            adapter.FillSchema(table, SchemaType.Source);
            columnsValue = table.Columns;
            return columnsValue;
        }
    }

    private string commaSeparatedListOfColumnNamesValue = null;

    private string CommaSeparatedListOfColumnNames
    {
        get
        {
            // Return the existing value if it has already been determined. 
            if (commaSeparatedListOfColumnNamesValue != null)
            {
                return commaSeparatedListOfColumnNamesValue;
            }

            // Store a list of column names for use in the 
            // SupplyPageOfData method.
            System.Text.StringBuilder commaSeparatedColumnNames =
                new System.Text.StringBuilder();
            bool firstColumn = true;
            foreach (DataColumn column in Columns)
            {
                if (!firstColumn)
                {
                    commaSeparatedColumnNames.Append("], [");
                }
                else
                {
                    commaSeparatedColumnNames.Append("[");
                }

                commaSeparatedColumnNames.Append(column.ColumnName);
                firstColumn = false;
            }
            commaSeparatedColumnNames.Append("]");
            commaSeparatedListOfColumnNamesValue =
                commaSeparatedColumnNames.ToString();
            return commaSeparatedListOfColumnNamesValue;
        }
    }

    // Declare variables to be reused by the SupplyPageOfData method. 
    private string columnToSortBy;


    public DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage)
    {



        // Store the name of the ID column. This column must contain unique  
        // values so the SQL below will work properly. 
        if (columnToSortBy == null)
        {
            columnToSortBy = this.Columns[0].ColumnName;
        }

        if (!this.Columns[columnToSortBy].Unique)
        {
            throw new InvalidOperationException(String.Format(
                "Column {0} must contain unique values.", columnToSortBy));
        }



        // Retrieve the specified number of rows from the database, starting 
        // with the row specified by the lowerPageBoundary parameter.
        String text = "Select Top " + rowsPerPage + " " +
            CommaSeparatedListOfColumnNames + " From " + tableName +
            " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
            lowerPageBoundary + " " + columnToSortBy + " From " +
            tableName + " Order By " + sortColumn +
            ") Order By " + sortColumn;
        command.CommandText = text;

        adapter = new SqlDataAdapter(text, GUI.dictSettings["connectionString"]);
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

        table = new DataTable();
        table.Locale = System.Globalization.CultureInfo.InvariantCulture;
        adapter.Fill(table);

        return table;
    }

    public DataTable getDataTable()
    {
        return table;
    }

    public SqlDataAdapter getAdapter()
    {
        return adapter;
    }
}

In addition to that, I implemented the ColumnHeaderMouseClick-Event in my Gridview. In that I get the column name and the sort direction and then reload all underlying table data by creating a new DataRetrieverLog instance and passing the column name + sort direction. Next I just refresh the Gridview: gridView.Refresh()

Thats it.

EDIT (20.07.2015):

To make things a bit clearer, here´s the full way of getting a sortable DataGridView in Virtual Mode to work. It´s been a while since I was playing around with this, so I hope I can make it clear enough for you to get it working and haven´t missed out on anything. Three Helper classes are needed to get this to work:

IDataPageRetriever.cs:

using System.Data;
using System.Data.SqlClient;

namespace ASC.Code.Forms.Helper
{
    public interface IDataPageRetriever
    {
        DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage);

        SqlDataAdapter getAdapter();
    }
}

CacheAll.cs:

using System.Data;
using System.Data.SqlClient;

namespace ASC.Code.Forms.Helper
{
    public class CacheAll
    {
        private static int RowsPerPage;

        // Represents one page of data.   
        public struct DataPage
        {
            public DataTable table;
            public SqlDataAdapter adapter;
            private int lowestIndexValue;
            private int highestIndexValue;

            public DataPage(DataTable table, SqlDataAdapter adapter, int rowIndex)
            {
                this.table = table;
                this.adapter = adapter;
                lowestIndexValue = MapToLowerBoundary(rowIndex);
                highestIndexValue = MapToUpperBoundary(rowIndex);
                System.Diagnostics.Debug.Assert(lowestIndexValue >= 0);
                System.Diagnostics.Debug.Assert(highestIndexValue >= 0);
            }

            public int LowestIndex
            {
                get
                {
                    return lowestIndexValue;
                }
            }

            public int HighestIndex
            {
                get
                {
                    return highestIndexValue;
                }
            }

            public static int MapToLowerBoundary(int rowIndex)
            {
                // Return the lowest index of a page containing the given index. 
                return (rowIndex / RowsPerPage) * RowsPerPage;
            }

            private static int MapToUpperBoundary(int rowIndex)
            {
                // Return the highest index of a page containing the given index. 
                return MapToLowerBoundary(rowIndex) + RowsPerPage - 1;
            }

            public DataTable getTable()
            {
                return this.table;
            }

            public SqlDataAdapter getAdapter()
            {
                return this.adapter;
            }
        }

        private DataPage[] cachePages;
        private IDataPageRetrieverAll dataSupply;

        public CacheAll(IDataPageRetrieverAll dataSupplier, int rowsPerPage)
        {
            dataSupply = dataSupplier;
            CacheAll.RowsPerPage = rowsPerPage;
            LoadFirstTwoPages();
        }

        // Sets the value of the element parameter if the value is in the cache. 
        private bool IfPageCached_ThenSetElement(int rowIndex,
            int columnIndex, ref string element)
        {
            if (IsRowCachedInPage(0, rowIndex))
            {
                element = cachePages[0].table
                    .Rows[rowIndex % RowsPerPage][columnIndex].ToString();
                return true;
            }
            else if (cachePages.Length > 1)
            {
                if (IsRowCachedInPage(1, rowIndex))
                {
                    element = cachePages[1].table.Rows[rowIndex % RowsPerPage][columnIndex].ToString();
                    return true;
                }

            }

            return false;
        }

        public string RetrieveElement(int rowIndex, int columnIndex)
        {
            string element = null;

            if (IfPageCached_ThenSetElement(rowIndex, columnIndex, ref element))
            {
                return element;
            }
            else
            {
                return RetrieveData_CacheIt_ThenReturnElement(
                    rowIndex, columnIndex);
            }
        }

        private void LoadFirstTwoPages()
        {
            DataTable table1 = dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(0), RowsPerPage);
            SqlDataAdapter adapter1 = dataSupply.getAdapter();

            DataTable table2 = dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(RowsPerPage), RowsPerPage);
            SqlDataAdapter adapter2 = dataSupply.getAdapter();

            cachePages = new DataPage[]{
            new DataPage(table1, adapter1, 0), 
            new DataPage(table2, adapter2, RowsPerPage)};
        }

        private string RetrieveData_CacheIt_ThenReturnElement(
            int rowIndex, int columnIndex)
        {
            // Retrieve a page worth of data containing the requested value.
            DataTable table = dataSupply.SupplyPageOfData(
                DataPage.MapToLowerBoundary(rowIndex), RowsPerPage);
            SqlDataAdapter adapter = dataSupply.getAdapter();

            // Replace the cached page furthest from the requested cell 
            // with a new page containing the newly retrieved data.
            cachePages[GetIndexToUnusedPage(rowIndex)] = new DataPage(table, adapter, rowIndex);

            return RetrieveElement(rowIndex, columnIndex);
        }

        // Returns the index of the cached page most distant from the given index 
        // and therefore least likely to be reused. 
        private int GetIndexToUnusedPage(int rowIndex)
        {
            if (rowIndex > cachePages[0].HighestIndex &&
                rowIndex > cachePages[1].HighestIndex)
            {
                int offsetFromPage0 = rowIndex - cachePages[0].HighestIndex;
                int offsetFromPage1 = rowIndex - cachePages[1].HighestIndex;
                if (offsetFromPage0 < offsetFromPage1)
                {
                    return 1;
                }
                return 0;
            }
            else
            {
                int offsetFromPage0 = cachePages[0].LowestIndex - rowIndex;
                int offsetFromPage1 = cachePages[1].LowestIndex - rowIndex;
                if (offsetFromPage0 < offsetFromPage1)
                {
                    return 1;
                }
                return 0;
            }

        }

        // Returns a value indicating whether the given row index is contained 
        // in the given DataPage.  
        private bool IsRowCachedInPage(int pageNumber, int rowIndex)
        {
            return rowIndex <= cachePages[pageNumber].HighestIndex &&
                rowIndex >= cachePages[pageNumber].LowestIndex;
        }

        public DataPage[] getCachePages()
        {
            return cachePages;
        }

    }
}

…and the DataRetrieverAll.cs class:

using System;
using System.Data.SqlClient;
using System.Data;

namespace ASC.Code.Forms.Helper
{
    public class DataRetrieverAll : IDataPageRetrieverAll
    {
        private string tableName;
        private string sortColumn;
        private SqlCommand command;
        private DataTable table;
        private SqlDataAdapter adapter;

        public DataRetrieverAll(string connectionString, string tableName, string sortColumn)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            command = connection.CreateCommand();
            this.tableName = tableName;
            this.sortColumn = sortColumn;
        }

        private int rowCountValue = -1;

        public int RowCount
        {
            get
            {
                // Return the existing value if it has already been determined. 
                if (rowCountValue != -1)
                {
                    return rowCountValue;
                }

                // Retrieve the row count from the database.
                command.CommandText = "SELECT COUNT(*) FROM " + tableName;
                rowCountValue = (int)command.ExecuteScalar();
                return rowCountValue;
            }
        }

        private DataColumnCollection columnsValue;

        public DataColumnCollection Columns
        {
            get
            {
                // Return the existing value if it has already been determined. 
                if (columnsValue != null)
                {
                    return columnsValue;
                }

                // Retrieve the column information from the database.
                command.CommandText = "SELECT * FROM " + tableName;
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                adapter.FillSchema(table, SchemaType.Source);
                columnsValue = table.Columns;
                return columnsValue;
            }
        }

        private string commaSeparatedListOfColumnNamesValue = null;

        private string CommaSeparatedListOfColumnNames
        {
            get
            {
                // Return the existing value if it has already been determined. 
                if (commaSeparatedListOfColumnNamesValue != null)
                {
                    return commaSeparatedListOfColumnNamesValue;
                }

                // Store a list of column names for use in the 
                // SupplyPageOfData method.
                System.Text.StringBuilder commaSeparatedColumnNames =
                    new System.Text.StringBuilder();
                bool firstColumn = true;
                foreach (DataColumn column in Columns)
                {
                    if (!firstColumn)
                    {
                        commaSeparatedColumnNames.Append("], [");
                    }
                    else
                    {
                        commaSeparatedColumnNames.Append("[");
                    }

                    commaSeparatedColumnNames.Append(column.ColumnName);
                    firstColumn = false;
                }
                commaSeparatedColumnNames.Append("]");
                commaSeparatedListOfColumnNamesValue =
                    commaSeparatedColumnNames.ToString();
                return commaSeparatedListOfColumnNamesValue;
            }
        }

        // Declare variables to be reused by the SupplyPageOfData method. 
        private string columnToSortBy;


        public DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage)
        {



            // Store the name of the ID column. This column must contain unique  
            // values so the SQL below will work properly. 
            if (columnToSortBy == null)
            {
                columnToSortBy = this.Columns[0].ColumnName;
            }

            if (!this.Columns[columnToSortBy].Unique)
            {
                throw new InvalidOperationException(String.Format(
                    "Column {0} must contain unique values.", columnToSortBy));
            }



            // Retrieve the specified number of rows from the database, starting 
            // with the row specified by the lowerPageBoundary parameter.
            String text = "Select Top " + rowsPerPage + " " +
                CommaSeparatedListOfColumnNames + " From " + tableName +
                " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
                lowerPageBoundary + " " + columnToSortBy + " From " +
                tableName + " Order By " + sortColumn +
                ") Order By " + sortColumn;
            command.CommandText = text;

            adapter = new SqlDataAdapter(text, GUI.dictSettings["connectionString"]);
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

            table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            adapter.Fill(table);

            return table;
        }

        public DataTable getDataTable()
        {
            return table;
        }

        public SqlDataAdapter getAdapter()
        {
            return adapter;
        }
    }
}

So, how do I fill the Grid? I use another Helper-Class and call a method within the Helper-Class from the Main GUI- class:

Main.cs:

//...
private String columnToSortByAll;

public GUI()
{
    InitializeComponent();
    init();
}
private void init()
{
    helper = new GUIHelper(grid, this);

    //Tabellen mit Werten füllen
    fillTablesInit();
}

private void fillTablesInit()
{
    helper.getData("(SELECT * FROM TOOL_materialSumme WHERE Display IS NULL OR Display = 1)a", "ID", asc_all); //asc_all = Boolean value, indicating sort direction asc /desc
}

private void grid_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
    String columnName = grid.Columns[e.ColumnIndex].Name;

    if (columnName.Equals(columnToSortBy))
    {
        if (asc_all) asc_all = false; else asc_all = true;
    }
    else
    {
        columnToSortBy = columnName;
        asc_all = true;
    }

    helper.getDataALL("(SELECT * FROM TOOL_materialSumme WHERE Display IS NULL OR Display = 1)a", columnToSortBy, asc_all);
}

private void grid_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
{
    String actualValue = helper.getMemoryCache().RetrieveElement(e.RowIndex, e.ColumnIndex);
    e.Value = actualValue;
}

private void grid_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
    //MessageBox.Show(e.Context.ToString());
}

private void grid_CellValuePushed(object sender, DataGridViewCellValueEventArgs e)
{
    String newValue = "";
    if (e.Value != null) newValue = e.Value.ToString();

    int column = e.ColumnIndex;

    ASC.Code.Forms.Helper.CacheAll.DataPage[] pages = helper.getMemoryCache().getCachePages();
    DataTable[] tables = new DataTable[2];
    SqlDataAdapter[] adapters = new SqlDataAdapter[2];

    tables[0] = pages[0].getTable();
    tables[1] = pages[1].getTable();

    adapters[0] = pages[0].getAdapter();
    adapters[1] = pages[1].getAdapter();

    String id = grid.Rows[e.RowIndex].Cells["ID"].Value.ToString();

    for (int x = 0; x < tables.Length; x++)
    {
        for (int a = 0; a < tables[x].Rows.Count; a++)
        {

            String temp = tables[x].Rows[a][column].ToString();
            if (tables[x].Rows[a]["ID"].ToString() == id)
            {
                tables[x].Rows[a][column] = newValue;
                adapters[x].Update(tables[x]);
                break;
            }
        }
    }

    grid.Refresh();
}
//...

GUIHelper.cs:

private DataGridView grid
private CacheAll memoryCache;
private DataRetrieverAll retriever;

public GUIHelper(DataGridView grid, GUI gui)
{
    this.gui = gui;
    this.grid = grid;
    init();
}

private void init()
{
    //...
}

public void getData(string selectCommand, string sortColumn, Boolean asc_all)
{
    grid.VirtualMode = true;
    try
    {
        if (asc_all) sortColumn = "["+sortColumn + "] ASC"; else sortColumn = "["+sortColumn + "] DESC";

        retriever = new DataRetrieverAll("Insert ConnectionString here...", selectCommand, sortColumn);
        memoryCache = new CacheAll(retriever, GUI.amountDatasets); //amountDatasets = Amount of Datasets per cached-page

        if (grid.Columns.Count == 0)
        {
            foreach (DataColumn column in retriever.Columns)
            {
                grid.Columns.Add(column.ColumnName, column.ColumnName);
            }
        }

        grid.Rows.Clear();
        grid.RowCount = retriever.RowCount;

        grid.Refresh();
    }
    catch (SqlException)
    {
        MessageBox.Show("Connection could not be established. " +
            "Verify that the connection string is valid.");
        Application.Exit();
    }
}

public CacheAll getMemoryCache()
{
    return memoryCacheAll;
}

Well, that´s the way I did it and it worked out well. I ended up not using it, but still have the code... It´s important to set grid.VirtualMode = true and to implement the ColumnHeaderMouseClick event for the grid in the Main class. Happy coding :-)