Introduce SqlState on DbException for standard cross-database errors

[roji]

Provider tracking issues

This new API has been merged, following are tracking issues for implementation in the different providers:

• SqlClient: .NET 5.0 | Implement SqlException.SqlState SqlClient#648
• Sqlite: Implement SqliteException.SqlState efcore#21599
• Npgsql: Override SqlState and IsTransient from DbException npgsql/npgsql#3058
• MySqlConnector: Implement MySqlException.SqlState mysql-net/MySqlConnector#848

Proposal

Following discussion with @cheenamalhotra in #34798, this proposes adding a nullable string SqlState property on DbException:

public class DbException
{
    public virtual string? SqlState { get; }
}

The SQL standard defines SQLSTATE, which is an 5-letter error code scheme for database errors; the first two characters express an error class, and the last 3 a subclass. Since the codes themselves are defined in the standard, it is possible to detect certain errors (e.g. unique constraint violation) in a portable way, regardless of the specific database being used. Databases are also free to add their own errors under special categories. Both JDBC and ODBC expose errors via SQLSTATE.

Unfortunately, databases do not implement this standard in a very reliable way:

• PostgreSQL implements SQLSTATE, and Npgsql already surfaces it on its exception type.
• MySQL also implements SQLSTATE, as well as another, proprietary error code. SQLSTATE is exposed on MySqlException.
• SQL Server has its own incompatible numeric error code scheme, currently surfaced via SqlError.Number.
  • However, the SQL Server ODBC driver apparently maps the native SQL Server errors to SQLSTATE values
  • As pointed out by @cheenamalhotra, the JDBC driver does the same, although it maps only a handful of codes (maybe the ODBC translation could be copied?)
• Sqlite doesn't seem to expose SQLSTATE, but instead has one numeric error code and an additional "extended" numeric code (docs).

Although only 2 our of the 4 databases examined above implement SQLSTATE natively, there seems to be enough value in exposing this property; hopefully this would also provide a reason for the others to map to SQLSTATE, providing more standardized behavior.

Notes

• This proposal is orthogonal (though related) to Database-agnostic way to detect transient database errors #34817, which is about exposing a flag expressing whether the error may be transient.
• To the best of my understanding, SQLSTATE errors represent errors originating from the RDBMS system itself (i.e. server), and not from the driver. In other words, it isn't clear to me at this point whether a connectivity issue between the driver and the server should be expressed via SQLSTATE or via other means. This doesn't impact this proposal directly, though - only the guidance for implementing it.

/cc @David-Engel @cheenamalhotra @bgrainger @bricelam @ajcvickers @mgravell @FransBouma @stephentoub @terrajobst

[mgravell]

I like the idea, but the fact that it already exists on two of them makes implementation awkward if you keep the name SqlState, as they would have to override something and change their own version to re-declare (new) the property to retain binary compatibility - and you can't both new and override a property in one layer currently. I suspect you may need to either:

• pick a different name (yuk)
• have a different virtual that they can override

something like (reduced):

abstract class DbException : Exception
{
    public string SqlState => GetSqlState();
    protected virtual string GetSqlState() => "";
}
class FooDbException : DbException
{
    protected override string GetSqlState() => "AB123";
    // the thing they already declared on their public API
    public new string SqlState => GetSqlState();
}

[bgrainger]

We already have that problem with the new "Async" methods: #28596

For providers that already implemented those methods (before they were added in netstandard2.1), it's an override in netstandard2.1/netcoreapp3.0 but just a regular instance method for other TFMs.

Is there a strong need to preserve binary compatibility when replacing a DLL that targets netstandard2.0 with one that targets (say) netcoreapp5.0?

[roji]

Yeah, I basically think the same as @bgrainger (this happened even earlier with Scale/Precision introduced at some point on DbParameter in .NET Framework), and I've dealt with it in the same way: you multitarget to the new TFM and override there (I don't see a need for binary compat because it's a new TFM), and keep the property as-is for older TFMs. You end up with some #ifs but it's not a huge deal.

[mgravell]

Fair enough. I can't help thinking this could cause a bait-and-switch problem if some intermediate library that uses the existing properties doesn't also update to multi-target, but: it seems like there's an existing pattern here.

[roji]

@mgravell that's definitely true, I just haven't seen it happen... It would have to be an Npgsql-specific (or MySqlConnector-specific) library that depends on this specific property, and doesn't update to multitarget. Possible, just not very likely...

[rgarrison12345]

@roji what about moving the proposed SQLState property into a new abstract class called DbError. From there it could be implemented from all other DbError based classes like SqlError. It would be a fresh start I believe this way it's not stepping on the toes of existing properties.

Oracle driver has OracleError which is almost the same as SqlError class

[roji]

@rgarrison12345 as written above, the fact that SqlState already exists on some providers' DbExceptions doesn't seem like a problem here, definitely not enough to warrant introducing a whole new type... This has already happened several times in the history of System.Data, and can pretty easily be taken care of via multitargeting. Also, most database providers don't seem to have a notion of an error that's distinct from the exception (PostgreSQL, MySQL, Sqlite...) - and it's definitely not part of the SQL standard. It seems to me that the concept would only add API complexity with no real benefits...

[terrajobst]

Video

• Makes sense as proposed.
• It seems if we had this, we could make the default implementation for DbException.IsTransient (Database-agnostic way to detect transient database errors #34817) check whether SqlState is not null and return true for transient SQLSTATE values.

namespace System.Data.Common
{
    public partial class DbException
    {
        public virtual string? SqlState { get; }
    }
}

[hangy]

I may be missing something, but from the docs, it doesn't look like DbException is limited to SQL databases. Having a SqlState on a NoSQL exception (ie. a MongoException might be derived from DbException) could be awkward.

[roji]

@hangy can you point to the place in the docs which suggests that? DbException is part of ADO.NET, along with DbConnection and DbCommand, which are very much about SQL databases (e.g. DbCommand has a textual CommandText which contains SQL).

[hangy]

Maybe I was thrown off by the prefix and the fact that some of the classes in the System.Data.Common could be derived without actually using SQL. Generally, it looks like in that namespace, no class/interface or their members have a Sql prefix, since that's reserved for the System.Data.SqlClient provider.

[cheenamalhotra]

Hi @hangy

"SQLSTATE" is a SQL Standard terminology applies to all SQL Databases (SQL Server, Oracle DB, MySQL PostgreSQL, etc.), and is not limited to SQL Server. Whereas SqlClient is only about SQL Server and Azure SQL. Hope that clarifies!

[hangy]

@cheenamalhotra Thanks! I'm aware that classes in namespaces like System.Data.SqlClient are provider specific. I guess I just didn't know that ADO.NET is positioned as data access for ANSI SQL providers. Please disregard my previous comments. 😆