[API Proposal]: Specialized Sql Exception Type for Duplicate Key Values

[TonyValenti]

Background and motivation

When a duplicate key value interrupts a SQL command, you get a SqlException with a message like this:

 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.__M_Matters' with unique index 'IX___M_Matters_Id'. The duplicate key value is (SB2 --- Accounts Payable).

It would be really nice if the duplicate key value was available as a property.

API Proposal

namespace Microsoft.Data.SqlClient;

public class DuplicateKeyException : SqlException
{
  public string Key {get; set;}
}

API Usage

It is a new exception that would be thrown.

Alternative Designs

No response

Risks

No response

Tagging subscribers to this area: @cheenamalhotra, @David-Engel
See info in area-owners.md if you want to be subscribed.

Issue Details

---

Background and motivation

When a duplicate key value interrupts a SQL command, you get a SqlException with a message like this:

 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.__M_Matters' with unique index 'IX___M_Matters_Id'. The duplicate key value is (SB2 --- Accounts Payable).

It would be really nice if the duplicate key value was available as a property.

API Proposal

namespace Microsoft.Data.SqlClient;

public class DuplicateKeyException : SqlException
{
  public string Key {get; set;}
}

API Usage

It is a new exception that would be thrown.

Alternative Designs

No response

Risks

No response

Author:	TonyValenti
Assignees:	-
Labels:	api-suggestion, area-System.Data.SqlClient
Milestone:	-

[Clockwork-Muse]

The catch here is that almost certainly the drivers for the various RDBMSs have to be updated to take advantage of this.
And I don't know that they all surface the duplicate key in their own messages.

[Wraith2]

The message comes from the sql server as an error message string meaning that the SqlClient driver would have to parse the message to extract it. this would not work with with localized messages.

[David-Engel]

I don't see this happening any time soon. As Wraith2 said, this has some technical blockers in that the server doesn't surface the information in a guaranteed way that the client can rely on to implement it.