Ability to manage exceptions when selecting or updating data is very crucial from the data integrity standpoint. One could say that it is a very well understood problem and one could hardly find any production application that would not have better or worse exception handling when selecting or updating data when working with databases - whether it's SQL Server, Oracle, DB2 or other database. From my experience I can tell that developers do a pretty good job with catching exceptions as they know that any caught and logged error can help them find the problem and possible eliminate it. But one thing I have noticed is that not many .NET developers have a thorough understanding of databases - they can create tables, views, stored proc but when it comes to understanding a database architecture and its engine, they usually leave it to database administrators. Therefore, I common thing I have noticed is that not many .NET applications that work with Microsoft SQL Server database have been implemented to take a full advantage of error handling mechanism that Microsoft SQL Server offers. I remember a few applications that would catch only System.Exception totally forgetting that there was System.Data.SqlClient.SqlException class that offers a lot more than just a message, so this post is dedicated to that class - probably because almost all .NET developers have worked or work with SQL Server database (especially ASP.NET developers) and that exception classes of other database providers have less to offer, hence their classes are less richer than SqlException class. Below is the diagram of the SqlException class with the new properties in that class, therefore without those inherited from System.Data.Common.DbException class, plus a chart with description of each property.
using System;using System.Data.SqlClient;namespace SqlErrorTester{class Program{static void Main(string[] args)
{Test_Severity_10();
Test_Severity_16();
Test_Severity_16_With_InfoMessage();
Test_Severity_24();
Console.Read();
}
static String ConnectionString{get
{SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();cb.DataSource = ".";cb.InitialCatalog = "AdventureWorksLT";cb.IntegratedSecurity = true;return cb.ConnectionString;}
}
static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{Console.WriteLine("Info Message received: " + e.Message);foreach (SqlError er in e.Errors)
Console.WriteLine("SqlError: Severity: {0} Message: {1}", er.Class, er.Message);}
static void Test_Severity_10()
{Console.WriteLine("TEST SEVERITY 10");using (var conn = new SqlConnection(ConnectionString))
{using (var cmd = conn.CreateCommand()){conn.FireInfoMessageEventOnUserErrors = true;conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);cmd.CommandText = "RAISERROR('my custom error message',10,1);select getdate();";try{conn.Open();
Object date = cmd.ExecuteScalar();
if (date != null)
Console.WriteLine("Date on server: " + date.ToString());elseConsole.WriteLine("Date on server not found");}
catch (SqlException ex){Console.WriteLine("SqlException: " + ex.Message);foreach (SqlError er in ex.Errors)
Console.WriteLine("SqlError: Severity: {0} Message: {1}", er.Class, er.Message);}
catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);}
}
}
}
static void Test_Severity_16()
{Console.WriteLine("TEST SEVERITY 16");using (var conn = new SqlConnection(ConnectionString))
{using (var cmd = conn.CreateCommand()){conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);cmd.CommandText = "RAISERROR('my custom error message',16,1);select getdate();";try{conn.Open();
Object date = cmd.ExecuteScalar();
if (date != null)
Console.WriteLine("Date on server: " + date.ToString());elseConsole.WriteLine("Date on server not found");}
catch (SqlException ex){Console.WriteLine("SqlException: " + ex.Message);foreach (SqlError er in ex.Errors)
Console.WriteLine("SqlError: Severity: {0} Message: {1}", er.Class, er.Message);}
catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);}
}
}
}
static void Test_Severity_16_With_InfoMessage()
{Console.WriteLine("TEST SEVERITY 16 WITH INFO MESSAGE");using (var conn = new SqlConnection(ConnectionString))
{using (var cmd = conn.CreateCommand()){conn.FireInfoMessageEventOnUserErrors = true;conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);cmd.CommandText = "RAISERROR('my custom error message',16,1);select getdate();";try{conn.Open();
Object date = cmd.ExecuteScalar();
if (date != null)
Console.WriteLine("Date on server: " + date.ToString());elseConsole.WriteLine("Date on server not found");}
catch (SqlException ex){Console.WriteLine("SqlException: " + ex.Message);foreach (SqlError er in ex.Errors)
Console.WriteLine("SqlError: Severity: {0} Message: {1}", er.Class, er.Message);}
catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);}
}
}
}
static void Test_Severity_24()
{Console.WriteLine("TEST SEVERITY 24");using (var conn = new SqlConnection(ConnectionString))
{using (var cmd = conn.CreateCommand()){conn.FireInfoMessageEventOnUserErrors = true;conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);cmd.CommandText = "RAISERROR('my custom error message',24,1) WITH LOG;select getdate();";try{conn.Open();
Object date = cmd.ExecuteScalar();
if (date != null)
Console.WriteLine("Date on server: " + date.ToString());elseConsole.WriteLine("Date on server not found");}
catch (SqlException ex){Console.WriteLine("SqlException: " + ex.Message);foreach (SqlError er in ex.Errors)
Console.WriteLine("SqlError: Severity: {0} Message: {1}", er.Class, er.Message);}
catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);}
}
}
}
}
}
0 comments: