c# - Maintaining state of checkboxes while filtering record using textbox

Question

I am working on a C# windows application to populate records from SQL Server to data grid view, with dynamic checkbox facility in each row. I want to select selected rows for some purpose via checkbox of that particular row. Till now I successfully achieve my target, but I'm facing a minor issue regarding saving a checked status.

For example I want to check only those records whose Name = Max. I have a textbox in that textbox I call text change event with like Query:

 try
    {
        SqlCommand cmd = null;
        SqlConnection con = null; Ranks rank = new Ranks();
        con = new SqlConnection(cs.DBcon);
        con.Open();
        cmd = con.CreateCommand();
        cmd.CommandText = "Select * from Records where Name like @Name order by Pno";
        cmd.Parameters.AddWithValue("@Name", "%" + FilterByNameTextbox.Text.Trim() + "%");
        SqlDataAdapter adapter1 = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        adapter1.Fill(dt);

        dataGridView1.DataSource = dt;
        Make_fields_Colorful();
    }
    catch (Exception exception)
    {
        MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
    }

If I write Max in filter by name textbox it would return 3 records with name starts with max using like query as I mention code above. So I only check 2 records out of 3 using dynamic checkbox, till now my code runs perfectly. Now I want to check records which name starts from Ali, now when I write ali in my filter by name textbox it will return rows where name like ali , but problem comes here it will remove my previous checked records, so how I would able to save checked records for both max and ali's rows:

Code for adding dynamic checkboxes in each row

DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
    checkBoxColumn.Name = "checkBoxColumn";
    checkBoxColumn.DataPropertyName = "Report";
    checkBoxColumn.HeaderText = "Report";
    dataGridView1.Columns.Insert(10, checkBoxColumn);
    dataGridView1.RowTemplate.Height = 100;
    dataGridView1.Columns[10].Width = 50;

Images:

Image 1

Image 2

Answer 1

I suggest you achieve this by caching selected rows, first you should have a list of cached rows:

List<DataGridViewRow> CachedRows = new List<DataGridViewRow>();

then add event handler on cell value change like the following:

dataGridView1.CellValueChanged += view_CellValueChanged;

and the handler should check if the column changed is the checkbox and checked, should be something like the following:

try
{
    if(e.ColumnIndex == indexOfCheckBoxColumn)
    {
       if((bool)dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value == true)
       {
        CachedRows.Add((DataGridViewRow)dataGridView1.Rows[e.RowIndex].Clone());
       }
       else if (CachedRows.Contains(dataGridView1.Rows[e.RowIndex]))//Validate if this works, if not you should associate each row with unique key like for example (id) using a dictionary
       {
          CachedRows.Remove(dataGridView1.Rows[e.RowIndex]);
       }
    }
}
catch(Exception ex)
{
}

then after the filter changes, re-add the cached rows again, so code becomes:

try
    {
        SqlCommand cmd = null;
        SqlConnection con = null; Ranks rank = new Ranks();
        con = new SqlConnection(cs.DBcon);
        con.Open();
        cmd = con.CreateCommand();
        cmd.CommandText = "Select * from Records where Name like @Name order by Pno";
        cmd.Parameters.AddWithValue("@Name", "%" + FilterByNameTextbox.Text.Trim() + "%");
        SqlDataAdapter adapter1 = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        adapter1.Fill(dt);

        dataGridView1.DataSource = dt;
        //add folowing
        if (CachedRows.Any())
        {
            dataGridView1.Rows.AddRange(CachedRows.ToArray());
            CachedRows.Clear();
        }
        Make_fields_Colorful();
    }
    catch (Exception exception)
    {
        MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
    }