BindingSource.RemoveCurrent Undo

Question

Hi,

I'm using LINQ for my data access layer and binding all generated linq objects, tables using binding sources.

I'm having an issue undoing a bindingsource.removecurrent if there is a foreign key violation.

For example I have a software and licenses table, software item can have many licenses, 

I have a binding source bound to my software items, I try to remove a software item using the bindingsource.removecurrent()

to delete the current software a user has selected. If the software has no licenses it works great.

If there are licenses I want the database to reject the attempt to delete and pass back an error and I want to recover from that error in the application.

Essentially I don't want to have to check in my application if the software has any licenses, I want to let the database decide that.

Sample Code

            DataAccessLayer.Software currentSoftwareItem =

               (DataAccessLayer.Software)pCurrentBS.Current;

            try

            {

                pCurrentBS.RemoveCurrent();

//or pCurrentBS.Remove(currentSoftwareItem);

                pCurrentBS.EndEdit();

                DataAccessLayer.Singleton_CorporatePOData.GetInstance.Context.SubmitChanges();

            }//on submit the database generates an error

            catch (System.Data.SqlClient.SqlException sqlException)

            {

            }

This generates sqlerror # 547, the FK violation.

At this point, I am unable to rebind the software correctly to a binding source.

if I access the software table

linqContext.software I can see my software item in there just fine.

if I try to bind it to a binding source

eg. BindingSOurce bs = new bindingSource();

bs.datasource = linqContext.software the binding source count is 0,

the isbindingSuspended property is set to true, 

Short of recreating a new data context which I really don't want to do I can't seem to undo the bindingsource.remove action.

Any help is greatly appreciated, I really don't want to have to manually check if my software has any licenses before I try deleting the item.

Thanks, 

Chris

Answer 1

Hi Chris,

 

Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

 

From your code snippet, I think you are using LINQ to SQL databinding and the WinForm BindingSource class, right?  If so, LINQ to SQL forum is more appropriate for this question.  

 

For the root cause of the problem, we need to research the implementation of the DataContext, Table<T>, and BindingSource via .NET Reflector. 

 

If we set the bs.DataSource to the certain Table<T> again in the CATCH block, the BindingSource internal list won’t be modified if we set it to the same data source reference as the original one.  Here is DataSource property implementation of BindingSource class:

=======================================================================
public object DataSource

{

    get

    {

        return this.dataSource;

    }

    set

    {

        if (this.dataSource != value)

        {

            this.ThrowIfBindingSourceRecursionDetected(value);

            this.UnwireDataSource();

            this.dataSource = value;

            this.ClearInvalidDataMember();

            this.ResetList();

            this.WireDataSource();

            this.OnDataSourceChanged(EventArgs.Empty);

        }

    }

}
=======================================================================

 

Also, the Table<T> calls its method GetList to return a IList collection to the BindingSource.  The GetList method will check whether the internal cachedList is null.  If not, it directly returns the cachedList, otherwise, it creates a new collection based on the database data.   Such design is to reduce the database calls for performance concern, see the Caching section in Data Binding (LINQ to SQL).

=======================================================================
IList IListSource.GetList()

{

    if (this.cachedList == null)

    {

        this.cachedList = this.GetNewBindingList();

    }

    return this.cachedList;

}
=======================================================================

 

If you don’t want to create a new DataContext, one workaround would be using Reflection to clear the cachedList inside the Table<T> and changing the BindingSource.DataSource to null, then back to the Table<T>. 

 

Another problem is that the original marked as Deleted objects are still tracked by the DataContext, I used Reflection to call the internal method ClearCache of the DataContext. 

=======================================================================
            catch (SqlException ex)

            {

                var binding = BindingFlags.NonPublic | BindingFlags.Instance;

                var type = bs.DataSource.GetType();

                var field = type.GetField("cachedList", binding);

                field.SetValue(bs.DataSource, null);

 

                var method = LinqContext.GetType().GetMethod("ClearCache", binding);

                method.Invoke(LinqContext, null);

               

                bs.DataSource = null;

                bs.DataSource = LinqContext.ParentTables;

            }
=======================================================================

Important to mention:  Although, such a workaround can resolve the problem, it is very complicated.  I strongly recommend you validate the ParentTables before removing them, or just create a new DataContext since DataContext is intended to be in short life, http://blogs.msdn.com/dinesh.kulkarni/archive/2008/04/27/lifetime-of-a-linq-to-sql-datacontext.aspx.

 

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi,

Thanks a lot for your prompt response, it was very helpful.

I rarely have time to read in detail about how linq works and just learn on the fly, your response  clarified a lot about the databinding implementation in linq to sql.

Sorry about posting this on the entities forum, I didn't realize this was actually a linq to sql specific question till after.

I hope its ok if I ask one last question, instead of clearing the cache a what would be a potential downfall of just using the getNewBindingList() method of the table<t> directly

So my current solution for now which seems to work  (for now) is 

        public void Delete()

        {

            DataAccessLayer.Software currentSoftwareItem =

               (DataAccessLayer.Software)pCurrentBS.Current;

            try

            {

                pCurrentBS.RemoveCurrent();

                pCurrentBS.EndEdit();

                DataAccessLayer.Singleton_CorporatePOData.GetInstance.Context.SubmitChanges();

            }

            catch (System.Data.SqlClient.SqlException sqlException)

            {

                //FK violation Exception

                if (sqlException.Number == 547)

                {

                    try

                    {

//remove from deletes changeset

                        DataAccessLayer.Singleton_CorporatePOData.GetInstance.Context.Software.InsertOnSubmit(currentSoftwareItem);

                        pCurrentBS.DataSource = DataAccessLayer.Singleton_CorporatePOData.GetInstance.Context.Software.GetNewBindingList();

                    }

                    catch (Exception unhandledSQLException)

                    {

                        throw unhandledSQLException;

                    }

                }

            }

            catch (Exception unhandledGeneralException)

            {

                throw unhandledGeneralException;

            }

        }

Thanks again,

Chris

Answer 3

 

Hi Chris,

 

It’s really my pleasure to help you on this case.  Also, your workaround is great and simpler!  J 

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.