none
Databinding of WPF DataGrid with database via ADO.NET Entity framework

    Question

  • I develop WPF 4.0 desktop-based application that has to work with SQL Server database. In order to connect database with my GUI WPF-application I decided to use ADO.Net Entity Framework. I made edmx-connection, all works as expected, I see the structure of my DB in model browser.

    After that in WinWorkers.xaml.cs I wrote basic LINQ-query that returns all data from tbl_users and I put it to IBindingList, here is a code fragment:
    //databinding with database via LINQ query
    IBindingList users = ((from d in App.glidusContext.tbl_users
                select new { d.userID, d.userName, d.userPassword }
              ) as IListSource).GetList() as IBindingList;
    

    In order to show this data I chose to use DataGrid control inside of XAML-code, here is a code fragment of that:
    <!--databinded content from database-->
    <DataGrid x:Name="ContentDataGrid" Style="{StaticResource ContentDataGrid}">
      <DataGrid.Columns>
        <DataGridTextColumn Header="User ID" Width="100" />
        <DataGridTextColumn Header="User name" Width="100" />
        <DataGridTextColumn Header="Password" Width="*" />
      </DataGrid.Columns>
    </DataGrid>
    

    Now all what is left to me to do is to declare DataGrid binding with LINQ results, in my case — with IBindingList users. As I saw in multiple examples of databinding I wrote in WinWorkers.xaml.cs:
    this.ContentDataGrid.DataContext = users;
    

    and in WinWorkers.xaml I changed code of DataGrid to:
    <DataGrid x:Name="ContentDataGrid"
        Style="{StaticResource ContentDataGrid}"
        ItemsSource="{Binding}">
      <DataGrid.Columns>
        <DataGridTextColumn Header="User ID"
            Width="100"
            Binding="{Binding userID}" />
      <DataGridTextColumn Header="User name"
            Width="100"
            Binding="{Binding userName}" />
      <DataGridTextColumn Header="Password"
            Width="*"
            Binding="{Binding userPassword}" />
      </DataGrid.Columns>
    </DataGrid>
    

    Where ContentDataGrid defines all visual style, performance/virtualization, CanUserSortColumns=True stuff.

    I compiled my code, all works, I see DataGrid with data from my database.

    BUT there are some issues:

    I can't change row content, when I double click on row application crashes and throws exception «InvalidOperationException» with the message: «A TwoWay or OneWayToSource binding cannot work on the read-only property 'userPassword' of type '<>f__AnonymousType0`3[System.Int32,System.String,System.String]'».

    Can't sort rows by specific column when I click on column header. I see arrows in column header, but no sorting is performed in the fact.

    If I begin to play with DataContext/ItemSource all data in DataGrid disappears.

    I'm new to C#/WPF and now I'm totally confused with a tonnes of different examples of DataBinding (DataContext/ItemSource/MVVM etc.), so please, can you give me code-suggestion how to fix those issues.

    If you need some additional information, feel free to ask.

    Thanks a lot!
    Saturday, October 09, 2010 6:06 PM

Answers

  • Toucki, first thanks for the detailed explanation of binding a collection of entities to a datagrid; it saved me some researching time.  I was able to save changes back to the database by 1) setting this.ContentDataGrid.DataContext = App.glidusContext.tbl_users; then 2) calling App.glidusContext.SaveChanges(); as the window closes. Admittedly it avoids LINQ altogether, so this may not be the answer you are working for, but it did work for me.
    Scott D Duncan
    • Marked as answer by Toucki Monday, October 18, 2010 1:02 AM
    Wednesday, October 13, 2010 9:10 PM

All replies

  • Sort related issue fixed, so now I need the help only with update DataGrid rows and putting this data to DB.

     

    Please, help me.

    Monday, October 11, 2010 11:42 PM
  • Toucki, first thanks for the detailed explanation of binding a collection of entities to a datagrid; it saved me some researching time.  I was able to save changes back to the database by 1) setting this.ContentDataGrid.DataContext = App.glidusContext.tbl_users; then 2) calling App.glidusContext.SaveChanges(); as the window closes. Admittedly it avoids LINQ altogether, so this may not be the answer you are working for, but it did work for me.
    Scott D Duncan
    • Marked as answer by Toucki Monday, October 18, 2010 1:02 AM
    Wednesday, October 13, 2010 9:10 PM