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?

Answer 1

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

Answer 2

No matter what I did I couldn't refresh BindingSource. Here's a test code I've used for a DataContext with a single TmpTable:

DataClasses1DataContext db = new DataClasses1DataContext();

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadBinding();
        }

        private void LoadBinding()
        {
            tmpTableBindingSource.DataSource = db.TmpTables;   
        }

        private void AddNewRecord_Click(object sender, EventArgs e)
        {
            TmpTable newRecord = new TmpTable()
            {
                ad = "asd"
            };

            db.TmpTables.InsertOnSubmit(newRecord);
            db.SubmitChanges();
            LoadBinding();
        }

        private void ShowRecordCounts_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Context Table: " + db.TmpTables.Count().ToString()+" BindingSource: "+ tmpTableBindingSource.Count.ToString());
        }

Let's say I had 3 records. ShowRecordCounts displays "Context Table: 3 BindingSource: 3", which is correct. After insert it displays "Context Table: 4 BindingSource: 3" which means BindingSource didn't recieve the new record. No matter what I did I couldn't refresh it. I'd appreciate any help with this..

Answer 3

I have same issue, it seems that bindingsource is not updating it self. it shows that it's datasource changed, but bindingsource count(row count) shows old value.

Answer 4

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.

Answer 5

And finally one more working solution.

This solution works fine and do not require recreating DataContext.

1. 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();

1. You need to reset your BindingSource using something like:

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

2. Enjoy :)