CODEWITHSUNDEEP
c#winformsentity-frameworkdatagridviewado.net
Mody
Mody

Reputation: 153

Issues with DataGrid binding and saving new rows to database

I'm working on a simple windows application using SQL Server Compact with Entity Framework 6, I have a TreeView which is populated by a database, there is also a DataGrid which should be filled based on the selected node from TreeView. It looks like this Image

Database Model:

I used this code to fill the DataGrid, everything was working just fine, but it won't save new rows to the database, editing existed rows is working though.

private void treeView_AfterSelect(object sender, TreeViewEventArgs e)
{
    if (treeView.SelectedNode.Level == 1)
    {
        BindingSource bs = new BindingSource();
        bs.DataSource = (from a in context.Items where a.SubID == (int)treeView.SelectedNode.Tag select a).ToList();
        dataGrid.DataSource = bs;
    }
}

So, I tried this. Saving new rows and editing existed ones worked, the thing is when selecting a node in TreeView it should only show the Items linked to the selected node, but it doesn't it adds the new query result to the old one, displaying both queries on the DataGrid, would be probably more if I selected more nodes.

private void treeView_AfterSelect(object sender, TreeViewEventArgs e)
{
    if (treeView.SelectedNode.Level == 1)
    {
        context.Items.Where(a => a.SubID == (int)treeView.SelectedNode.Tag).Load();
        dataGrid.DataSource = context.Items.Local.ToBindingList();
    }
}

There is one more thing I'm trying to do, when selecting a Sub-Category from TreeView I should get Items linked to this particular Sub-Category displayed on DataGrid, so is it possible when adding a new row in DataGrid it automatically set the SubID field in Items to the ID of selected node, linking this newly added item to the selected Sub-Category in TreeView.

Any help would be appreciated, and sorry if the solution is obvious, but all of this is new to me. Thanks.

Upvotes: 1

Views: 1651

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109079

As for the previously loaded items appearing when selecting another node, you should add this line in treeView_AfterSelect:

foreach(var item in context.Items.Local.ToList())
    context.Entry(item).State = System.Data.Entity.EntityState.Detached;

and then continue with

context.Items.Where(a => a.SubID == (int)treeView.SelectedNode.Tag).Load();

The added line removes the items from the change tracker and they will effectively be removed from the Local collection. The Local collection of a DbSet<T> contains all items that a context has ever loaded and have not been deleted. So with each Load statement you keep adding items to this collection. By changing their state to Detached the context no longer "knows" about their existence and they disappear from the Local collection.

Notice that you can't do

context.Items.Local.Clear();

because that will mark all items for delete, besides removing them from the Local collection. SaveChanges will delete the items from the database.

As for adding new items to a new subcategory, the best thing to do is add these items to an Items collection in SubCategory. Doing that, you don't need a foreign key value, which isn't know at that moment, but when calling SaveChanges, EF will save the SubCategory first and set the generated FK value in the Items "just in time".

It will look like

var subcat = new SubCategory();
var item = new Item();
subcat.Items.Add(item); // subcat.Items must have been initialized! 
context.SubCategories.Add(subcat); // Also marks item as Added
context.SaveChanges();

Now EF first saves subcat, reads its generated key value, sets item's foreign key value and saves item, all in one transaction.

If you just create a new item for an existing SubCategory you can set item.SubId directly. But also subcat.Items.Add(item) can still be used, if subcat is attached to the context.

Upvotes: 2

icktoofay
icktoofay

Reputation: 129001

I’m not very familiar with the frameworks you're using, but I’ll take a stab at it:

Your first block of code has the behavior it does because ToList takes a snapshot of the current set of rows that match the criteria. When you add a new row, you’re adding an item to that snapshot, but aren’t touching the database from which the other rows were retrieved.

The second block of code has the behavior it does because the context keeps track of a cache of local objects. The first line that calls Load just brings those items into the cache, but the objects that were previously in the cache are left there as local objects.

From my limited knowledge, this would be the best solution:

  1. When the form is first created, create a BindingSource. Set this BindingSource as the DataGrid’s DataSource. Set the BindingSource’s DataSource to context.Items.Local.ToBindingList(). Now the BindingSource functions as a proxy to the cached items.

  2. When an item is selected, Load the appropriate entities as you’re doing now, but then also set the Filter of the BindingSource appropriately. (The documentation suggests the syntax for Filter should be the same as DataColumn.Expression accepts—it looks SQL-like, so I bet there’s some way to turn a Linq query into an appropriate string.)

The overall effect is that you’ll be using the data from the underlying database. When a category is selected, it will load the appropriate entities. Then it will filter the list of all the entities it has ever known to just those of interest.

Upvotes: 1

Related Questions