Search code examples
c#mysqlwpfdatedatagrid

C# - DatePicker column (WPF DataGrid) not registering the "row modified" event for database update

I am trying to change a column in my DataGrid to a DatePicker to be able to pick the date from a calendar, the issue I have though, is whenever a date is picked and I change rows, it does not launch the event to update the database.

Here is some code:

Creating the column as DatePicker:

templateColumn = new DataGridTemplateColumn();
templateColumn.Header = "Date";
FrameworkElementFactory datePickerFactoryElem = new FrameworkElementFactory(typeof(DatePicker));
templateColumnBinding = new Binding("date_time");
templateColumnBinding.UpdateSourceTrigger = UpdateSourceTrigger.PropertyChanged;
templateColumnBinding.Mode = BindingMode.TwoWay;
datePickerFactoryElem.SetValue(DatePicker.SelectedDateProperty, templateColumnBinding);
datePickerFactoryElem.SetValue(DatePicker.DisplayDateProperty, templateColumnBinding);
DataTemplate cellTemplate = new DataTemplate();
cellTemplate.VisualTree = datePickerFactoryElem;
templateColumn.CellTemplate = cellTemplate;
DataGrid_training_dataEntry.Columns.Add(templateColumn);

Trigger 1 (detecting if a row has been updated):

private void DataGrid_training_dataEntry_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
    DataRowView rowView = e.Row.Item as DataRowView;
    rowBeingEdited_DataGrid_training_dataEntry = rowView;
}

Event 2 (inserting the modified row):

private void DataGrid_training_dataEntry_CurrentCellChanged(object sender, EventArgs e)
{
    if (rowBeingEdited_DataGrid_training_dataEntry != null)
    {
        DataGrid_training_dataEntry.CommitEdit();

        MySqlCommand cmdInsert;
        MySqlCommand cmdUpdate;
        cmdUpdate = new MySqlCommand("UPDATE training SET id_training=@id_training,id_type=@id_type,id_value_0001=@id_value_0001,id_value_0002=@id_value_0002,id_value_0003=@id_value_0003,id_value_0004=@id_value_0004,id_value_0005=@id_value_0005,value_value_0001=@value_value_0001,value_value_0002=@value_value_0002,value_value_0003=@value_value_0003,value_value_0004=@value_value_0004,value_value_0005=@value_value_0005,date_time=@date_time,duration=@duration where id_training=@id_training", conn);
        cmdUpdate.Parameters.Add("@id_training", MySqlDbType.LongText, 15, "id_training");
        cmdUpdate.Parameters.Add("@id_type", MySqlDbType.LongText, 15, "id_type");
        cmdUpdate.Parameters.Add("@id_value_0001", MySqlDbType.LongText, 15, "id_value_0001");
        cmdUpdate.Parameters.Add("@id_value_0002", MySqlDbType.LongText, 15, "id_value_0002");
        cmdUpdate.Parameters.Add("@id_value_0003", MySqlDbType.LongText, 15, "id_value_0003");
        cmdUpdate.Parameters.Add("@id_value_0004", MySqlDbType.LongText, 15, "id_value_0004");
        cmdUpdate.Parameters.Add("@id_value_0005", MySqlDbType.LongText, 15, "id_value_0005");
        cmdUpdate.Parameters.Add("@value_value_0001", MySqlDbType.LongText, 15, "value_value_0001");
        cmdUpdate.Parameters.Add("@value_value_0002", MySqlDbType.LongText, 15, "value_value_0002");
        cmdUpdate.Parameters.Add("@value_value_0003", MySqlDbType.LongText, 15, "value_value_0003");
        cmdUpdate.Parameters.Add("@value_value_0004", MySqlDbType.LongText, 15, "value_value_0004");
        cmdUpdate.Parameters.Add("@value_value_0005", MySqlDbType.LongText, 15, "value_value_0005");
        cmdUpdate.Parameters.Add("@date_time", MySqlDbType.DateTime, 15, "date_time");
        cmdUpdate.Parameters.Add("@duration", MySqlDbType.LongText, 15, "duration");
        cmdInsert = new MySqlCommand("INSERT INTO training (id_type,id_value_0001,id_value_0002,id_value_0003,id_value_0004,id_value_0005,value_value_0001,value_value_0002,value_value_0003,value_value_0004,value_value_0005,date_time,duration) VALUES (@id_type,2,id_value_0001,id_value_0002,@id_value_0003,@id_value_0004,id_value_0005,@value_value_0001,@value_value_0002,@value_value_0003,@value_value_0004,@value_value_0005,@date_time,@duration)", conn);
        cmdInsert.Parameters.Add("@id_type", MySqlDbType.LongText, 15, "id_type");
        cmdInsert.Parameters.Add("@id_value_0001", MySqlDbType.LongText, 15, "id_value_0001");
        cmdInsert.Parameters.Add("@id_value_0002", MySqlDbType.LongText, 15, "id_value_0002");
        cmdInsert.Parameters.Add("@id_value_0003", MySqlDbType.LongText, 15, "id_value_0003");
        cmdInsert.Parameters.Add("@id_value_0004", MySqlDbType.LongText, 15, "id_value_0004");
        cmdInsert.Parameters.Add("@id_value_0005", MySqlDbType.LongText, 15, "id_value_0005");
        cmdInsert.Parameters.Add("@value_value_0001", MySqlDbType.LongText, 15, "value_value_0001");
        cmdInsert.Parameters.Add("@value_value_0002", MySqlDbType.LongText, 15, "value_value_0002");
        cmdInsert.Parameters.Add("@value_value_0003", MySqlDbType.LongText, 15, "value_value_0003");
        cmdInsert.Parameters.Add("@value_value_0004", MySqlDbType.LongText, 15, "value_value_0004");
        cmdInsert.Parameters.Add("@value_value_0005", MySqlDbType.LongText, 15, "value_value_0005");
        cmdInsert.Parameters.Add("@date_time", MySqlDbType.DateTime, 15, "date_time");
        cmdInsert.Parameters.Add("@duration", MySqlDbType.LongText, 15, "duration");
        mySqlDataAdapter.InsertCommand = cmdInsert;
        mySqlDataAdapter.UpdateCommand = cmdUpdate;

        mySqlDataAdapter.Update(dataSet_training_dataEntry);
        conn.Close();
        conn.Dispose();

        DataGrid_training_dataEntry.ItemsSource = null;
        dataSet_training_dataEntry.Reset();

        rowBeingEdited_DataGrid_training_dataEntry.EndEdit();

        rowBeingEdited_DataGrid_training_dataEntry = null;

        conn = new MySqlConnection(connStr);
        mySqlDataAdapter = new MySqlDataAdapter("select * from training", conn);
        command = conn.CreateCommand();
        conn.Open();

        mySqlDataAdapter.Fill(dataSet_training_dataEntry);
        DataGrid_training_dataEntry.ItemsSource = dataSet_training_dataEntry.Tables[0].DefaultView;
        conn.Close();
        conn.Dispose();
        DataGrid_training_dataEntry.Columns[1].IsReadOnly = false;
    }
}

Event 3 (registering a cell change as a row update):

 private void DataGrid_training_dataEntry_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
 {
     if (!isManualEditCommit)
     {
         isManualEditCommit = true;
         DataGrid grid = (DataGrid)sender;
         grid.CommitEdit(DataGridEditingUnit.Row, true);
         isManualEditCommit = false;
     }
 }

When I change cells or rows, nothing happens, although if I modify any other cell after I selected a different date, the update goes fine (as it detects the row change from the other cell being modified).

May anyone provide some help about this please?

Thank you for your time and help, it is greatly appreciated.

Solution

  • Editing it happening in the DatePicker control and not in the DataGrid row level.

    Solution:- You need to DatePicker control to subscribe for SelectedDateChangedEvent and inside the event, you need to call datagrid.BeginEdit(); which will later raise the DataGrid_RowEditEndingevent once you have done with DatePicker control editing.

    How to subscribe to event in C# Code.

    Declare Action delegate variable at class level 

    Action delegateCommand = null;

    Create a Method, which has no return type and no parameter, incase of your example I didn't see any requirement therefore I went with no return type and no parameter.

       private void DateTimePicker_SelectionChange()
    {
        da.BeginEdit();
    }

    Add reference of the newly created method to the action delegate.

      delegateCommand = DateTimePicker_SelectionChange;
  datePickerFactoryElem.AddHandler(DatePicker.SelectedDateChangedEvent, delegateCommand, false);

    When you change the date from DatePicker, the selectionChanged event will be raised and internally you will call the RowEditEnding event of the DataGrid.