1

Let's say I executed an SQL statement from a C# application that caused an SqlException with the following error message to be thrown:

Violation of UNIQUE KEY constraint 'MyUniqueConstraint'. Cannot insert duplicate key in object 'MyTable'. The duplicate key value is (MyValue).

I understand that the entire string comes from the database. I would like to extract the relevant parts from the message - similar to this question, but I want to actually extract data such as MyUniqueConstraint, MyTable and MyValue, not just the SQL error number.

Actually parsing the message is not an option as it is error-prone: you have to do it for every possible error, and if the error text changes from one version of SQL server to another, there would be serious problems.

Is it possible to reasonably obtain such structured information from the application when a database-level error occurs (ideally from the exception)?

Community
  • 1
  • 1
Gigi
  • 28,163
  • 29
  • 106
  • 188
  • In the SqlException.Errors property there is the standard T-SQL error number, severity, and state values that would help to classify exceptions. But to what purpose would it serve? How will your code respond differently based on what flavor of SQL exception it encounters? – Keith Payne May 08 '14 at 12:24
  • It is to make error messages more user-friendly at the UI level, especially when the exception has to traverse different application tiers which may not all reside on the same machine. – Gigi May 08 '14 at 13:59
  • Wow! - that's the data https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlerror.number.aspx – user2864740 Sep 15 '17 at 16:54
  • @user2864740 see accepted answer which touches upon this. – Gigi Sep 16 '17 at 08:02

2 Answers2

2

Not long ago I faced a similar problem, and I found out that even filtering SqlExceptions based on their numbers is problematic.

Other than that, I haven't yet encountered a solution that could get data details from SqlException. That is, other than parsing the message. One of the problems here will be that text of server message is different for different languages and server versions

Actually I recall there is a system table/view in the mssql master database that contains all error messages for different languages. You can try parsing the messages based on that, but some SqlExceptions aren't even from the server side of things...

I want to say at least one remotely helpful thing here, so:

In the namespace System.Data there is DBConcurrencyException which seems to be usefull to an extent (meaning: in a single case that is not entirely related to this question). It has Row property (row that caused the exception to be thrown, you can use it to get the table name and the data).

Community
  • 1
  • 1
Arie
  • 5,251
  • 2
  • 33
  • 54
0

Wishfull thinking.

I understand that the entire string comes from the database

Yes, it does. It is a string.

Actually parsing the message is not an option as it is error-prone

Parsing is the ONLY option as there are only 2 items provided by the database: The sql error number and the string. So, either you come up with a generic crystal ball or you work with what you have - which means parsing.

No database that I Know of provides more information mysteriously hidden in the exception - and if it would do so, it would not be a standard approach across databases anyway.

TomTom
  • 61,059
  • 10
  • 88
  • 148