Datagrid checkbox logic to update table when checked

Question

I am having difficulties in getting my checkbox column to work. What I am trying to achieve is to have the checkbox checked when the app loads if the condition matched a value from the table and send an update command to the database every time the checkbox gets checked or unchecked.

How should I write it to get it off the ground? I am looking to achieve my goal without MVVM. I would be grateful if someone can help me get unstuck.

This is how far I have got:

<DataGrid>
    <DataGrid.Columns>
        <DataGridTemplateColumn Header="Audited" >
            <DataGridTemplateColumn.CellTemplate>
                <DataTemplate>
                    <CheckBox x:Name="cBox" Checked="DataGridCheckBoxColumn_Checked" Unchecked="DataGridCheckBoxColumn_Unchecked"
              IsChecked="{Binding Audited, UpdateSourceTrigger=PropertyChanged}"/>
                </DataTemplate>
            </DataGridTemplateColumn.CellTemplate>
            <DataGridTemplateColumn.CellEditingTemplate>
                <DataTemplate>
                    <CheckBox x:Name="cBox" Checked="DataGridCheckBoxColumn_Checked" Unchecked="DataGridCheckBoxColumn_Unchecked"
              IsChecked="{Binding Audited, UpdateSourceTrigger=PropertyChanged}"/>
                </DataTemplate>
            </DataGridTemplateColumn.CellEditingTemplate>
        </DataGridTemplateColumn>
        <DataGridTextColumn Binding="{Binding Location}" Header="Location" Visibility="Collapsed"/>
        <DataGridTextColumn Binding="{Binding Date, StringFormat=MM-dd-yy}" Header="Date"/>
    </DataGrid.Columns>
</DataGrid>

xaml.cs

 public MainWindow()
        {
            InitializeComponent();

            string connectionString = "datasource=; Port=; Username=; Password=";
            string sMonth = DateTime.Now.ToString("MM");
            string sYear = DateTime.Now.ToString("yyyy");
            string sDate = DateTime.Now.ToString("yyyy-MM-dd");

            MySqlConnection connection = new MySqlConnection(connectionString);

            MySqlCommand Audit = new MySqlCommand("Select Audited from Daily.Table where MONTH(Date) = @sMonth AND YEAR(Date) = @sYear", connection);
            Audit.Parameters.Add(new MySqlParameter("sDate", sDate));

            try
            {
                connection.Open();

                MySqlDataReader AuditR = Audit.ExecuteReader();

                while (AuditR.Read())
                {
                    if (AuditR["Audited"] != DBNull.Value)
                    {
                        //How I can set the checkbox to checked?
                    }; 
                }

                AuditR.Close();
                AuditR.Dispose();


        private void DataGridCheckBoxColumn_Checked(object sender, RoutedEventArgs e)
        {
            string connectionString = "datasource=; Port=; Username=; Password=";

            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand AuditUpdate = new MySqlCommand("update Daily.Table set Audited='Yes' where ID= '" + this.txtID.Text + "'", connection);
            CheckBox checkBox = sender as CheckBox;

            //How can I mark the checkbox as checked from here?

        }

        private void DataGridCheckBoxColumn_Unchecked(object sender, RoutedEventArgs e)
        {
            string connectionString = "datasource=; Port=; Username=; Password=";

            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand AuditUpdate = new MySqlCommand("update Daily.Table set Audited=NULL where ID= '" + this.txtID.Text + "'", connection);
            CheckBox checkBox = sender as CheckBox;

            //How can I mark the checkbox as unchecked from here?
        }

Answer 1

I faced a similar kind of problem. I solved it without using MVVM. Something like this might help you.

<DataGridTextColumn Header="Operand" Binding="{Binding Path=MyVal}">
                        <DataGridTextColumn.EditingElementStyle>
                            <Style TargetType="{x:Type CheckBox}">
                                <EventSetter Event="SelectionChanged" Handler="CheckBox_SelectionChanged" />
                            </Style>
                        </DataGridTextColumn.EditingElementStyle>
                    </DataGridTextColumn>

Create a class for containing the rows of the datagridview

class MyRowItem
{
    public string ID {get;set;}
        public Checkbox Audited {get;set;}
        public string Location {get;set;}
        public string Date {get;set;}

}

Create a list of MyRowItem and store all the rows there

List<MyRowItem> rowList = new List<MyRowItem>();
rowList.Add(new MyRowItem(){ Audited = yourvalue, Location = "yourvalue", Date = "yourvalue"});

Now inside your Checkbox_SelectionChanged event handler

private void Checkbox_SelectionChanged(object sender, RoutedEventArgs e)
        {
            string connectionString = "datasource=; Port=; Username=; Password=";

            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand AuditUpdate = new MySqlCommand("update Daily.Table set Audited='"Yes"' where ID= '" + this.txtID.Text + "'", connection);
            CheckBox checkBox = sender as CheckBox;

        foreach(MyRowItem mri in rowList)
        {
        if(mri.ID == "your matching ID")
        {
            //something like this
            mri.Audited = checkBox;
        }
        }

        }

At the end,

Datagrid.Source = rowList;
Datagrid.Refresh();