When to use Microsoft.Data.SqlClient.SqlException vs System.Data.SqlClient.SqlException?

Question

I understand that around 2019 Microsoft created Microsoft.Data.SqlClient as their replacement for System.Data.SqlClient. System.Data.SqlClient will be supported ongoing but new dev & features will all be in Microsoft.Data.SqlClient. Both of these libraries have a class named 'SqlException'.

Assuming I'm using Microsoft.Data.SqlClient everywhere then presumably relevant exceptions will be of type Microsoft.Data.SqlClient.SqlException, BUT I use a bunch of 3rd-party libraries, how can I be sure whether they'll raise a a Microsoft.Data.SqlClient.SqlException or a System.Data.SqlClient.SqlException? Does this mean in a few cases where I have catch handlers for Microsoft.Data.SqlClient.SqlException I should also check for System.Data.SqlClient.SqlException? Or is there something clever that happens which means I only need to think about Microsoft.Data.SqlClient.SqlException?

e.g. I have some old code a bit like I've shown below, written before we started using Microsoft.Data.SqlClient. I fear if I simply change it to use Microsoft.Data.SqlClient then there will be some exceptions that are System.Data.SqlClient.SqlException and my code will no longer notice them.

    private static bool HandleSqlExceptionInSomeWay(Exception ex)
    {
        var se = ex as System.Data.SqlClient.SqlException;

        if (se != null)
        {
            // ... do something with the SqlException
            return true;
        }

        return false;
    }

So should I change it to be something like this, i.e. check separately for the different types?

    private static bool HandleSqlExceptionInSomeWay(Exception ex)
    {
        // handle old SqlExceptions (e.g. from any old 
        // libraries not using MS package yet)
        var se1 = ex as System.Data.SqlClient.SqlException;

        if (se1 != null)
        {
            // ... do something with the SqlException ...

            return true;
        }

        // handle shiny new SqlExceptions 
        var se2 = ex as Microsoft.Data.SqlClient.SqlException;

        if (se2 != null)
        {
            // ... do something with the SqlException ... 

            return true;
        }

        return false;
    }

Answer 1

The two classes are different but they do inherit from the same base class, DbException. That's the common class for all database exceptions though and won't have all the properties in the two derived classes

You should inspect the libraries/NuGet packages you use and ensure you use versions that support the new Microsoft.Data.SqlClient library. Mixing up data providers isn't fun and should be avoided when possible. Most popular NuGet packages use Microsoft.Data.SqlClient already.

If you can't do that, the options depend on how you actually handle database exceptions. Do you inspect the SQL Server-specific properties or not?

Another option is to postpone upgrading until all NuGet packages have upgraded too. Both libraries include native DLLs that need to be included during deployment. If you mix libraries, you'll have to include all native DLLs.

This can be painful.

Handling the exceptions

If both libraries need to be used, each exception type needs to be handled separately. Pattern matching makes this a bit easier :

switch (ex) 
{
    case  System.Data.SqlClient.SqlException exc:
        HandleOldException(exc);
        return true;
    case Microsoft.Data.SqlClient.SqlException exc:
        HandleNewException(exc);
        return true;
    case DbException exc:
        HandleDbException(exc);
        return true;
    default:
        return false;
}

Mapping Exceptions

Another option could be to map the two exception types to a new custom type that contains the interesting properties. You'd have to map both the SqlException and SqlError classes. Using AutoMapper would make this easier:

var configuration = new MapperConfiguration(cfg => {
    cfg.CreateMap<System.Data.SqlClient.SqlException, MySqlException>();
    cfg.CreateMap<System.Data.SqlClient.SqlError, MySqlError>();
    cfg.CreateMap<Microsoft.Data.SqlClient.SqlException, MySqlException>();
    cfg.CreateMap<Microsoft.Data.SqlClient.SqlError, MySqlError>();
});

This would allow mapping either exception to the common MySqlException type :

var commonExp=mapper.Map<MySqlException>(ex);

Answer 2

To expand on Richard's answer, it means your try/catch end up looking like:

try{
  //boom
} catch(Microsoft.Data.SqlClient.SqlException ex){
  Handler(ex);
} catch (System.Data.SqlClient.SqlException ex) {
  Handler(ex);
}

If you want to deal with both..

Handler is some accessible overloaded (or parent typed parameter) method that deals with each kind.. Not the prettiest, but alas, there isn't a way for one catch to catch N different types, unless the types all have the same usable parent that can be caught instead (and then the type of exception inspected if required)..

Edit; completely forgot about catch when which is useful for this last case, thanks @Heinzi

try{
  //boom
} catch (DbException ex) 
  when (ex is Microsoft.Data.SqlClient.SqlException || ex is System.Data.SqlClient.SqlException)
{
  //handle
}

Answer 3

If you can't be sure whether the third-party libraries are using the Microsoft or the System library, you'll need to handle both. There's no "magic sauce" that converts a SqlException from one library into a SqlException from the other.