Refreshing BindingSource after insert (Linq To SQL)

Question

I have a grid bound to a BindingSource which is bound to DataContext table, like this:

myBindingSource.DataSource = myDataContext.MyTable;
myGrid.DataSource = myBindingSource;

I couldn't refresh BindingSource after insert. This didn't work:

myDataContext.Refresh(RefreshMode.OverwriteCurrentValues, myBindingSource);
myBindingSource.ResetBinding(false);

Neither this:

myDataContext.Refresh(RefreshMode.OverwriteCurrentValues, myDataContext.MyTable);
myBindingSource.ResetBinding(false);

What should I do?

score 2 · Accepted Answer · answered Oct 23 '09 at 08:58

I have solved the problem but not in a way I wanted.

Turns out that DataContext and Linq To SQL is best for unit-of-work operations. Means you create a DataContext, get your job done, discard it. If you need another operation, create another one.

For this problem only thing I had to do was recreate my DataContext like this.dx = new MyDataContext();. If you don't do this you always get stale/cached data. From what I've read from various blog/forum posts that DataContext is lightweight and doing this A-OK. This was the only way I've found after searching for a day.

score 1 · Answer 2 · answered Apr 08 '10 at 17:29

And finally one more working solution.

This solution works fine and do not require recreating DataContext.

You need to reset internal Table cache. for this you need change private property cachedList of Table using reflection.

You can use following utility code:

    public static class LinqDataTableExtension
    {
        public static void ResetTableCache(this ITable table)
        {
            table.InternalSetNonPublicFieldValue("cachedList", null);
        }

        public static void ResetTableCache(this IListSource source)
        {
            source.InternalSetNonPublicFieldValue("cachedList", null);
        }

        public static void InternalSetNonPublicFieldValue(this object entity, string propertyName, object value)
        {
            if (entity == null)
                throw new ArgumentNullException("entity");
            if(string.IsNullOrEmpty(propertyName))
                throw new ArgumentNullException("propertyName");

            var type = entity.GetType();
            var prop = type.GetField(propertyName, BindingFlags.NonPublic | BindingFlags.Instance);
            if (prop != null)
                prop.SetValue(entity, value);
// add any exception code here if property was not found :)
        }
    }

using something like:

   var dSource = Db.GetTable(...)
   dSource.ResetTableCache();

You need to reset your BindingSource using something like:

_BindingSource.DataSource = new List(); _BindingSource.DataSource = dSource; // hack - refresh binding list
Enjoy :)

score 1 · Answer 3 · edited Sep 24 '11 at 03:52

I have the same problem. I was using a form to create rows in my table without saving the context each time. Luckily I had multiple forms doing this and one updated the grid properly and one didn't.

The only difference?
I bound one to the entity similarly (not using the bindingSource) to what you did:

myGrid.DataSource = myDataContext.MyTable;

The second I bound:

myGrid.DataSource = myDataContext.MyTable.ToList();

The second way worked.

score 1 · Answer 4 · edited Sep 24 '11 at 01:28

Grid Data Source Referesh by new query instead just Contest.Table. Simple Solution < But Working.

Whre is eg. !!!!! Thanks - Problem Solved after no of days !!! but with so simple way ..

CrmDemoContext.CrmDemoDataContext Context = new CrmDemoContext.CrmDemoDataContext();

var query = from it in Context.Companies select it;
// initial connection
dataGridView1.DataSource = query;

after changes or add in data

Context.SubmitChanges();
//call here again 
dataGridView1.DataSource = query;

score 0 · Answer 5 · answered Oct 20 '09 at 13:05

0

I think you should also refresh/update datagrid. You need to force redraw of grid.

answered Oct 20 '09 at 13:05

Sharique

4,199
6
36
54

Clemens · Answer 6 · 2013-04-19T08:26:59.130

the answer from Atomosk helped me to solve a similar problem - thanks a lot Atomosk!

I updated my database by the following two lines of code, but the DataGridView did not show the changes (it did not add a new row):

this.dataContext.MyTable.InsertOnSubmit(newDataset);
this.dataContext.SubmitChanges();

Where this.dataContext.MyTable was set to the DataSource property of a BindingSource object, which was set to the DataSource property of a DataGridView object. In code it does looks like this:

DataGridView dgv = new DataGridView();
BindingSource bs = new BindingSource();
bs.DataSource = this.dataContext.MyTable; // Table<T> object type
dgv.DataSource = bs;

Setting bs.DataSource equals null and after that back to this.dataContext.MyTable did not help to update the DataGridView either.

The only way to update the DataGridView with the new entry was a complete different approach by adding it to the BindingSource instead of the corresponding table of the DataContext, as Atomosk mentioned.

this.bs.Add(newDataset);
this.dataContext.SubmitChanges();

Without doing so bs.Count; returned a smaller number as this.dataContext.MyTable.Count(); This does not make sense and seems to be a bug in the binding model in my opinion.

score 0 · Answer 7 · answered Nov 03 '11 at 01:20

Not sure how you insert rows. I had same problem when used DataContext.InsertOnSubmit(row), but when I just inserted rows into BindingSource instead BindingSource.Insert(Bindingsource.Count, row) and used DataContext only to DataContext.SubmitChanges() and DataContext.GetChangeSet(). BindingSource inserts rows into both grid and context.

Refreshing BindingSource after insert (Linq To SQL)

7 Answers7