I am using Dynamic Data and to apply Unique key and other database level validations i have overridden the SubmitChanges function.
This gives me original error, which i am able to show to the end user. But, this message is like this:
Violation of UNIQUE KEY constraint 'UK_CountryName_Country'. Cannot insert duplicate key in object 'dbo.Country'. The duplicate key value is (America).
The statement has been terminated.
I need the following keywords of the message:
I have written following Regex:
System.Text.RegularExpressions.Regex.Split(e.Message.ToString(), "^Violation of (UNIQUE KEY){1} constraint '([a-zA-Z_]*)'. Cannot insert duplicate key in object 'dbo.([a-zA-Z]*)'. The duplicate key value is ")
I have succeeded in getting #1 & #2, but #3.
Can anyone help me. Also, is there any cleaner way to achieve it. FYI: I would be catching other types of database errors as well.
Thanks
With your example expression, it seemed you were trying to match the Country from dbo.Country instead of America as you asked, so I included both in my expression/code:
Dim ExpressionText As String = "^Violation of (?<KeyType>.*?) constraint '(?<ConstraintName>[^']*)'\.[^']*'(?<ObjectName>[^']*)'[^(]*\((?<KeyValue>[^)]*)\)"
Dim SearchText As String = "Violation of UNIQUE KEY constraint 'UK_CountryName_Country'. Cannot insert duplicate key in object 'dbo.Country'. The duplicate key value is (America). " _
& vbCrLf & "The statement has been terminated."
Dim regex As New System.Text.RegularExpressions.Regex(ExpressionText)
Dim mc As System.Text.RegularExpressions.Match = regex.Match(SearchText)
Response.Write(mc.Groups("KeyType").Value) ' writes UNIQUE KEY
Response.Write(mc.Groups(1).Value) ' writes UNIQUE KEY
Response.Write(mc.Groups("ConstraintName").Value) ' writes UK_CountryName_Country
Response.Write(mc.Groups(2).Value) ' writes UK_CountryName_Country
Response.Write(mc.Groups("ObjectName").Value) ' writes dbo.Country
Response.Write(mc.Groups(3).Value) ' writes dbo.Country
Response.Write(mc.Groups("KeyValue").Value) ' writes America
Response.Write(mc.Groups(4).Value) ' writes America
The expression is designed to be very tolerant of the type of error message, as there are other types of Key Constraints that can be violated. I could not find a list of the possible errors, but here is a breakdown of the expression:
^Violation of # Match literal text
(?<KeyType>.*?) constraint # Capture the words that come before " constraint"
'(?<ConstraintName>[^']*)'\. # Capture whatever is inside the single quotes: '
[^']* # Match anything up to the next single quote
'(?<ObjectName>[^']*)' # Capture whatever is inside the single quotes
[^(]* # Match anything up to the next open parentheses: (
\((?<KeyValue>[^)]*)\) #Capture whatever is inside the parentheses
You can get rid of the parts that look like ?<Something> if you do not want to use named capturing groups, and only want to use the numbers