none
Formal way to handle SQLException in LINQ-to-SQL RRS feed

  • Question

  • Hi. I have tried searching for an answer to this, but I cannot find it (or don't know where/how to search for it).
    My question relates to LINQ-to-SQL in C#.

    If a SQLException occurs when SubmitChanges() is called on a DataContext, the exception is not flushed away and every successive call to SubmitChanges() will throw the same exception.

    Firstly, what is the justification for this? Secondly, what is the formal way to handle a SQLException so that I can use the same DataContext to interact with the SQL database for further database inserts, deletes and updates?
    I have tried creating a new DataContext in the catch clause (as shown in the code sample below), but this causes other problems.

    class MyClass
    {
    DataContext dc;

    //....

    public InteractWithDatabase()
    {
    //....

    dc.InsertOnSubmit( ... );

    try
    {
    dc.SubmitChanges();
    }
    catch (SQLException)
    {
    this.dc = new DataContext();
    }
    }
    }


    Thanks!
    Monday, August 4, 2008 10:50 AM

All replies

  • It is unlikely that LINQ to SQL can intelligently recover from every SQLException. It is best for the application code to examine the exception and take the appropriate action. (In that regard, I'm not sure it's best to re-create a DataContext whenever you see a SQLException -- the exception probably points to a more fundamental issue that you need to address.)

     

    Hope that helps,

     

    --Samir

     

    Monday, August 4, 2008 4:22 PM
  • It does help a bit.

    But then, what is the point of having a SQLException and being able to catch if it your code should ensure that it should never happen anyway? I guess that's a also a theoretical question for lots of other kinds of exceptions as well, but the difference between this case and other exceptions is that when other exceptions get thrown, once they are handled, the offending operation is flushed. In the case of the SQLException being thrown, LINQ continues to try executing the same offending statement on every successive call to dataContext.SubmitChanges().
    Imagine if you got a DivideByZeroException because you tried to divide by zero, but even after catching and handling this exception, everytime you tried to perform successive division operations (that wouldn't throw an exception), you'd continue to get this exception because the offending operation hasn't been flushed.

    What I'm saying is that surely there must be a graceful way to handle a SQLException caused by an invalid LINQ operation. If there is I can't find it anywhere.
    Tuesday, August 5, 2008 7:54 AM
  • I am now just ensuring that all my Linq inserts/deletes/updates don't generate SQLExceptions.
    But surely others before me have had to deal with SQLExceptions.
    Maybe I should rephrase: what should I do when I get a SQLException? How do I examine the problem that caused the SQLException to be thrown and correct it?
    Wednesday, August 6, 2008 12:02 PM
  • I think the problem here is that there are many different SQL Exceptions that can be thrown and how to handle each is dependent on your application, for example:

    • Primary key violation - is it possible the data already exists or is it that the key choosing algorithm is not optimal? Whether you load the existing object out and compare/apply field changes or whether you chose a new key is entirely dependent on your scenario.
    • Foreign key constraint - if deleting do you delete all the associated records, associate them with another record or ask the user to choose? For example if deleting a customer you may want to and legally have to delete his addresses etc. but not his sales history. Deleting a stock location should require that the stock is associated with a new location.
    • Precision/range - if you get a range or precision exception is this bad data or a new system requirement?

    As has been said good validation will go a long way to ensure they don't exist. Are you getting or have in mind some specific exceptions you wish to tackle?

     

    [)amien

     

    Tuesday, August 19, 2008 5:58 PM
    Moderator
  • Sorry, to raise this from the dead. I took a break from the project I was working on...

    Well, I want to know how to handle any SQLException. But let's start simply, how does one deal with a unique entry violation being thrown inside a SQLException? To be clear, I don't want my code to ensure that the SQLException never gets thrown, I want to know what will happen if for example I try to insert a row with a duplicate unique value? How would I handle that exception?


    Wednesday, August 20, 2008 9:54 AM
  • Hi,

     

    Any news on this issue. I do not seem to get a 'proper' exception in the EF even if I del. raise an error in a sproc. This is really strange! Thanks.

     

    C

    Friday, November 14, 2008 2:38 PM
  • Trying to see if there is any answer to this issue.  It doesn't make sense to me that there is no way to reset the state on a DataContext without creating a new one.  In my application, I have a list of rows that I want to delete from the database.  If the first one throws a SqlException because of a foreign key constraint, I have no way of recovering from that short of creating a new DataContext and trying again without the offending record.  This requires me to keep track which records were problematic instead of just being able to catch the Exception and then move to the next record. 

    I would think the DataContext would have some sort of reset button that allowed me to reset it in a catch block and then keep moving through my list of objects.  Is this misguided thinking on my part? 

    Any information would be greatly appreciated.
    Monday, December 8, 2008 5:15 PM
  •  

    I'm in a similar situation except, I don't really need to handle the situation or recover and keep processing, but I do want my application to log as much detail as to why the SqlException occured and then exit, so that I can properly handle the situation manually.  But I can't seem to find a way, when using LINQ to insert thousands of rows, to find out which record threw the SqlException.
    Wednesday, December 10, 2008 10:44 PM
  • Here's how I deal with this example. If I'm updating/inserting multiple tables at one time, I've done it two ways..


    Option One
    First wrap a .."using".. statement around your code utilizing the using System.Transactions.TransactionScope like so:

    using(System.Transactions.TransactionScope ts = new TransactionScope()) 
                    { 
                        //Table one Create/Insert code 
     
     
                        //table two Create/Insert code 
     
                        //Complete the Transaction(s) 
                        ts.Complete(); 
                    } 

    If fails inside the statement, it will rollback the transactions.


    Option Two

    This is a longer way that I've also used with requires more code using "System.Data.Common.DbTransaction" on the DataContext.


    public enum TransactionState 
            { 
                Begin = 0x01, 
                Commit = 0x02, 
                Rollback = 0x04 
            } 
     
    //_transactions is intialized on DataContext creation. 
    //private static DbTransaction _transactions; 
     
    public static void Transaction(TransactionState transState) 
            { 
                try 
                { 
                    switch (transState) 
                    { 
                        case TransactionState.Begin: 
                            try 
                            { 
                                _transactions = DB.Connection.BeginTransaction(); 
                                DB.Transaction = _transactions;                  
                            } 
                            catch (Exception ex) 
                            { 
                                throw ex; 
                            } 
                            break
                        case TransactionState.Commit: 
     
                            if (_transactions != null
                                _transactions.Commit(); 
     
                            if (DB.Connection.State == ConnectionState.Open) 
                                DB.Connection.Close(); 
     
                            break
                        case TransactionState.Rollback: 
     
                            if (_transactions != null
                            { 
                                _transactions.Rollback(); 
                            } 
     
                            if (DB.Connection.State == ConnectionState.Open) 
                                DB.Connection.Close(); 
     
                            break
                        default
                            break
                    } 
                } 
                catch (Exception ex) 
                { 
                    throw ex; 
                } 
     
            } 

    Just remember to either dispose your DataContext after each command or create new one before each.

    Hope that helps.


    Regards,

    Aaron



    Sunday, December 14, 2008 7:52 PM
  • You might also want to look into this option(s) as well:

    //check out System.Data.Linq.ConflictMode.FailOnFirstConflict 
    //   and System.Data.Linq.ConflictMode.ContinueOnConflict when submitting your changes. 
        protected virtual void SubmitChanges(DataContext cx)   
        {   
            try   
            {   
                cx.SubmitChanges(ConflictMode.ContinueOnConflict);   
            }   
            catch (ChangeConflictException)   
            {   
                cx.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);   
                cx.SubmitChanges();   
            }   
            catch (Exception ex)   
           {   
                throw ex;   
            }   
        }  

    You can read more about this option above at:
    http://geekyrule.blogspot.com/2008/07/common-base-class-for-linq-to-sql.html


    Sunday, December 14, 2008 8:04 PM
  • Can I add try/catch block in .designer.cs file before IExecuteResult??????
    Tuesday, December 30, 2008 8:12 PM
  • Hallo Doehoe,

    I had the same problem with SqlExceptions. I closed the Connection and disposed the DataContext Class but there aren't any problems. For me this solution works quite well. I habe automated this in a little method

    public class MyLINQMethods 
        { 
            public static void MySubmitChanges<T>(ref T db, System.Data.Linq.ConflictMode failureMode) 
                where T : System.Data.Linq.DataContext, new()//New Einschränkung, DataContext muss einen parameterlosen Konstruktor besitzen 
            { 
                 
                try 
                { 
                    db.SubmitChanges(failureMode); 
                } 
                catch (System.Data.SqlClient.SqlException ex) 
                { 
                    db.Connection.Close(); 
                    db.Dispose(); 
                    db = new T(); 
                    throw new System.Exception("SQL Server Error: " + ex.Message, ex); 
                } 
                 
            } 
     
        } 
    If you had problems with this solution could you name them and maybe there are better ways to handle it.

    Thanks in advance,

    mausknut
    Thursday, January 22, 2009 2:58 PM