Home » WPFRSS

WPF DataGrid C# LINQ SubmitChanges()

Hi,

I have a table stored in an SQL database that I wish to display in a WPF DataGrid using LINQ. Some cells are displayed as they appear in the database while other cells are the results of computations I make on data from the SQL database. Then I wish to edit some cells which must be updated in the SQL database and after such an edit the entire DataGrid must be updated because some of the cells need to be re-computed based on what I just entered.

Let me demonstrate with a brief example. I have the following XAML code:

<DataGridx:Name="Holdings"AutoGenerateColumns="False"><DataGrid.Columns><DataGridTextColumnHeader="Name"Binding="{Binding Name}"IsReadOnly="True"/><DataGridTextColumnHeader="Count"Binding="{Binding Count, Mode=TwoWay}"IsReadOnly="False"/><DataGridTextColumnHeader="Result"Binding="{Binding Result}"IsReadOnly="True"/></DataGrid.Columns></DataGrid>

Here the Name and Count columns are taken directly from the database and I wish to edit the Count, and then Result must be re-computed for all rows.

In the code-behind I have:

publicpartialclass HoldingsList : UserControl
{
  public HoldingsList()
  {
    InitializeComponent();

    // New connection to SQL database.
    MyDB db = new MyDB();

    // Display table from database in DataGrid.
    Holdings.ItemsSource = db.Holdings;
  }
} 

And I have extended the Holding-class (which is generated by the SQL-to-LINQ tool where I drag and drop the SQL tables and it creates the code-behind automatically) so as to compute the Result-value using the Count-values for all entries in the database (actual calculation omitted here):

publicpartialclass Holding
{
  publicdouble Result
  {
    get
    {
      // Make calculation using the Count-value for all database-entries. (Omitted.)return 12.34;
    }
  }
}

I have two problems:

(1) I cannot get the edited Count-value to actually get updated in the SQL database. It seems I need a db.SubmitChanges() but I can't find out where to place this, I've tried implementing CellEditEnding and RowEditEnding but these are called before any data in the database has actually been changed. I've also tried implementing CurrentCellChanged which seemed to work but it is not the proper use of this event as it is used for changing cells in general. So where should I call db.SubmitChanges()? Or is there a way to set up the XAML code so that it automatically calls db.SubmitChanges()?

(2) How do I get the entire DataGrid to refresh / recalculate all of its values? I've tried creating a new MyDB and assign it to Holdings.ItemsSource but combined with the problem I describe in (1) it doesn't seemto work. I have also tried calling InvalidateVisual which doesn't work either. The important thing to note is that some of the data is displayed directly from the database while the Result-column is computed.

Thanks in advance!

 

 

5 Answers Found

 

Answer 1

Hi CoderMP,

You created a custom class  to wrap the data  from the database, so I have one question, do you implement the INotifyPropertyChanged interface? If yes, then we can discuss the next question.

For the (1) question: You could set  the Binding.NotifyOnTargetUpdated property and the Binding.NotifyOnSourceUpdated property to ture, that you could add the event  handler of the SourceUpdated and the TargetUpdated. The SourceUpdated event will be fired after the Binding Source is updated. So you could update the datat into database  in this event handler. Please refer to this document: http://msdn.microsoft.com/en-us/library/system.windows.data.binding.sourceupdated.aspx

For the (2): It is unnecessary to call  the InvalidateVisual or UpdateLayout, they are used to update the visual element of the DataGrid, not the data. If you have implemented the INotifyPropertyChanged interface, any changes in the Source can be notified to the element and the DataBinding can update the element automatically.

Hope this helps.

Sincerely,

Bob Bao

 

Answer 2

Hi Bob Bao,

Thanks. It's not really a wrapper-class I've made I've just extended  the Holding-class that was automatically generated  from the SQL database  (note the 'partial' keyword) and which already implements INotifyPropertyChanged, so I guess I don't have to?

I've solved my problems  but I'm not sure it is the correct / intended way. What I've done is added the following to the Holdings-class:

// The database connection  remains open.
MyDB db = new MyDB();

// We are not allowed to call  Refresh() inside CellEditEnding so we need a hack.bool justSaved = false;

privatevoid Holdings_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
{
  if (e.EditAction == DataGridEditAction.Commit)
  {
    db.MergeChanges();

    justSaved = true;
  }
}

privatevoid Holdings_SelectedCellsChanged(object sender, SelectedCellsChangedEventArgs e)
{
  if (justSaved)
  {
    Holdings.Items.Refresh();

    justSaved = false;
  }
}

 And then in the WPF I've added event-handlers to these and I've added the statement UpdateSourceTrigger=PropertyChanged to the binding. This seems to work. Is it a proper  way of doing this or have I opened the door for a swarm of bugs? Please provide source-code if you have a solution that is better than mine, it will be helpful for future readers as well.

Thanks.

 

Answer 3

CoderMP,

I think I have a better way for you to solve this problem... the intended way, so to speak.  I encountered the same issue.  I'm not going to include source code, however, because it is all tangled with stuff that would only obfuscate the simplicity of the solution:

When you set  the datasource for your datagrid  you will want to subscribe to the PropertyChanged events of each item in the grid.  In that event  handler method you can then submit your changes to the database.  You just want to be careful to unsubscribe to these events when you change your datasource, otherwise the objects will never get garbage collected.

 

-Jeromeyers

 

Answer 4

Hi CoderMP,

As Psychlotron said, handle the event  will not bring the bugs. The following article introduces the usage of the DataGrid in WPF in details, you may need it: http://www.codeproject.com/KB/WPF/WPFDataGridExamples.aspx#dataset

Sincerely,

Bob Bao

 

Answer 5

Thanks for the replies, but without source-code I'm afraid I don't understand what must be done. I've also looked at the page linked to by Bob Bao but I'm not sure the solution provided there is simpler or better than mine(?)

When working with WPF it keeps occurring to me that WPF is much more complicated than C# itself. Hopefully the Microsoft developers will prioritize the streamlining of WPF in the future? Also, the MSDN pages on WPF could be improved significantly. If you note  the number of views this forum-thread has had in such a short period of time it would seem that many people are confused by WPF.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter