Winforms Datagridview Sort by Combobox Displaymember

Question

I have an issue where I am trying to sort a Datagridview by the Combobox DisplayMember instead of the ValueMember.

The Datagridview is Databound to a Datatable which is populated from a SQL query and the Combobox is populated by the results of a LinqQuery.

Code-behind for Form populating Datagridview and Sorting

Dictionary<string, string> search = new Dictionary<string, string>();
            search.Add("ID", "ID");
            search.Add("ClientID", "ClientID");
            search.Add("ClientCode", "ClientCode");
            search.Add("BatchRef", "BatchRef");
            search.Add("CaseRef", "CaseRef");
            search.Add("AccountNo", "AccountNo");
             search.Add("MeterName", "MeterName");
            search.Add("MeterAddress", "MeterAdd");
            search.Add("MeterPostcode", "MeterPostcode");
            search.Add("AgentID", "AgentID");
            search.Add("CompletedDate", "CompletedDate");
            search.Add("SubmittedDate", "SubmittedDate");

            Systems.PopulateDataGrid(search, dgvSearch, SQLHelper.FillDataTable(sql, CommandType.Text, searchParameters));

private void dgvSearch_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            ListSortDirection direction;

            if (dgvSearch.Columns[e.ColumnIndex].HeaderText == "Agent")
            {
                if (dgvSearch.SortOrder == System.Windows.Forms.SortOrder.Ascending)
                    direction = ListSortDirection.Descending;
                else
                    direction = ListSortDirection.Ascending;

                dgvSearch.Sort(dgvSearch.Columns[e.ColumnIndex], direction);
            }
        }

Systems.PopulateDatagrid

 internal static void PopulateDataGrid(Dictionary<string, string> dataGrid, DataGridView dgv, DataTable dt)
        {
            dgv.AutoGenerateColumns = false;
            foreach (KeyValuePair<string, string> row in dataGrid)
            {
                dgv.Columns[row.Key].DataPropertyName = row.Value;
            }
            dgv.DataSource = dt;
        }

SQLHelper.FillDatatable

internal static DataTable FillDataTable(string strSQL, CommandType CT, List<SqlParameter> parameters)
        {
            var ds = new DataSet("UMDS");
            DataTable dt = ds.Tables.Add("UMDT");
            ds.EnforceConstraints = false;

            using (var cn = new SqlConnection(SQLHelper.ConnectionString))
            {
                using (var cmd = new SqlCommand(strSQL, cn))
                {
                    cmd.CommandType = CT;

                    if (parameters != null)
                    {
                        DateTime dateTime;

                        foreach (SqlParameter p in parameters)
                        {
                            if (p.Value == null || string.IsNullOrEmpty(p.Value.ToString()) || p.Value.ToString() == Systems.MASKEDDATE || p.Value.ToString() == Systems.MASKEDTIME)
                            {
                                p.Value = DBNull.Value;
                            }
                            else if (DateTime.TryParse(p.Value.ToString(), out dateTime))
                            {
                                p.Value = dateTime;
                            }
                            cmd.Parameters.Add(p);
                        }
                    }

                    try
                    {
                        cn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            dt.Load(dr);
                        }

                    }
                    catch (SqlException ex)
                    {
                        Systems.Msg("Database Error", "Error Loading Data from Database. Please try again.\n\n" + ex.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
            return dt;
        }

When clicking on the Header for "AgentID" it is sorting by the ValueMember instead of the DisplayMember.

Answer 1

Answering my own question:

Managed to find the answer on another SO question after some more googling.

Sorting DataGridView by Column.DisplayMember