To catch specific SQLExceptions using Microsoft.NET and Microsoft Sequel Server you can use the snippet below. You also need to look for the specific error numbers/codes you want to catch. To do this you can either execute the following query in the SQL Management Studio or you can use this
List of SQLException Numbers/Codes (Direct-DL around 15mb).
Samples
T-SQL Sample
|
1 |
SELECT * FROM master.dbo.sysmessages ORDER BY error ASC |
C# Sample
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
private static void MySqlMethod() { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder { DataSource = "localhost", InitialCatalog = "Northwind", IntegratedSecurity = true }; try { using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { //inserting invalid sql (for example: column not exists) command.CommandText = "SELECT InvalidColumnName FROM Customers"; command.ExecuteNonQuery(); } } } catch (SqlException ex) { // Log the original exception here foreach (SqlError sqlError in ex.Errors) { switch (sqlError.Number) { case 207: // 207 = InvalidColumn //do your Stuff here break; case 547: // 547 = (Foreign) Key violation //do your Stuff here break; case 2601: // 2601 = (Primary) key violation //do your Stuff here break; default: //do your Stuff here break; } } } } |
VB.NET Sample
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Private Sub MySqlMethod() Dim builder As New SqlConnectionStringBuilder() With { _ .DataSource = "localhost", _ .InitialCatalog = "Northwind", _ .IntegratedSecurity = True _ } Try Using connection As New SqlConnection(builder.ConnectionString) connection.Open() Using command As SqlCommand = connection.CreateCommand() 'inserting invalid sql (for example: column not exists) command.CommandText = "SELECT InvalidColumnName FROM Customers" command.ExecuteNonQuery() End Using End Using Catch ex As SqlException ' Log the original exception here For Each sqlError As SqlError In ex.Errors Select Case sqlError.Number Case 207 ' 207 = InvalidColumn 'do your Stuff here Exit Select Case 547 ' 547 = (Foreign) Key violation 'do your Stuff here Exit Select Case 2601 ' 2601 = (Primary) key violation 'do your Stuff here Exit Select Case Else 'do your Stuff here Exit Select End Select Next End Try End Sub |
If you have any questions or suggestions feel free to rate this snippet, post a comment or Contact Us via Email.
Related links: