Catching SQL Errors

Two distinct exception classes exist for catching exceptions, if they are thrown. Depending on which data class you are working with, you will obviously need to use the appropriate exception class. For the examples in this section, we will be using the SqlClient class, so we will use the SqlException class to catch any exception thrown. If you are using the OleDb class, you will need to use the corresponding OleDbException class to catch exceptions.

The SqlException class is designed to handle exceptions that are thrown while executing a SQL statement. When an exception is thrown, you can gather enough information in the SqlException class to figure out what is happening. We have written a small function (see Listings 16.1 and 16.2) that will display all the properties of the SqlException class when an exception is thrown. This gives you a good idea of what is happening when the error occurs.

This function was written to return a string that is formatted with some HTML tags to display on a web page. If you want to use it in a different context, such as writing to a file, you can just change the nl (short for “new line”) to a carriage return and line feed, if you prefer.

You might find that this function will be more helpful during more complicated SQL errors. Most of the time, you will find the error that you get will be the result of a simple spelling mistake.

This code shown in Listings 16.1 and 16.2 is a simple function that you can include in your program. If you are having problems debugging a SQL problem, this code might shed more light on the situation. The main purpose of this function is to serve as an additional utility to help you debug SQL problems.

Listing 16.1. GetSqlExceptionDump Function (C#)
private string GetSqlExceptionDump(SqlException Ex) 
             {     string sDump; 
                   string nl = "<br>"; 
                   sDump = ""; 

                   sDump = "Class: " + Ex.Class + nl + 
                   "Errors: " + Ex.Errors + nl + 
                   "Help Link: " + Ex.HelpLink + nl + 
                   "InnerException: " + Ex.InnerException + nl + 
                   "Line#: " + Ex.LineNumber + nl+ 
                   "Message: " + Ex.Message + nl+ 
                   "Procedure: " + Ex.Procedure + nl+ 
                   "Server: " + Ex.Server + nl+ 
                   "Source: " + Ex.Source + nl+ 
                   "Stack Trace: " + Ex.StackTrace + nl+ 
                   "State: " + Ex.State + nl+ 
                   "Target site: " + Ex. TargetSite + nl; 

                   return sDump; 
             }

Listing 16.2. GetSqlExceptionDump Function (Visual Basic .NET)
private Function GetSqlExceptionDump(Ex as SqlException ) as string 
                  dim sDump as String 
                  dim nl = "<br>" 
                  sDump = "" 

    sDump = "Class: " + Ex.Class + nl & _ 
             "Errors: " + Ex.Errors + nl & _ 
             "Help Link: " + Ex.HelpLink + nl & _ 
             "InnerException: " + Ex.InnerException + nl & _ 
             "Line#: " + Ex.LineNumber + nl & _ 
             "Message: " + Ex.Message + nl & _ 
             "Procedure: " + Ex.Procedure + nl & _ 
             "Server: " + Ex.Server + nl & _ 
             "Source: " + Ex.Source + nl & _ 
             "Stack Trace: " + Ex.StackTrace + nl & _ 
             "State: " + Ex.State + nl & _ 
             "Target site: " + Ex. TargetSite + nl 

                     GetSqlExceptionDump = sDump 

             End Function

Let’s take a look at what happens when an error occurs during the execution of a web page. Listings 16.3 and 16.4 present a small sample of code to connect to the database and select a few rows of data.What could go wrong with that?

Listing 16.3. Sample Code to Connect to the Database (C#)
try 
{
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated
 Security=SSPI;Initial Catalog=northwind"); 

nwindConn.Open(); 
SqlCommand myCommand = new SqlCommand("SELECT dCategoryID, CategoryName FROM Categories",
 nwindConn); 


SqlDataReader myReader = myCommand.ExecuteReader();//'This command will trigger an error 
while (myReader.Read()) 
      Response.Write("<br>" + myReader.GetValue(1)); 

      myReader.Close(); 

      nwindConn.Close(); 
} 
catch (SqlException Ex) 
{
      Response.Write(GetSqlExceptionDump(Ex)); 
}

Listing 16.4. Sample Code to Connect to the Database (Visual Basic .NET)
Try 

Dim nwindConn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI;Initial
 Catalog=northwind") 

nwindConn.Open() 
Dim myCommand = new SqlCommand("SELECT dCategoryID, CategoryName FROM Categories", nwindConn) 

Dim myReader = myCommand.ExecuteReader()'This command will trigger an error 

Do while (myReader.Read()) 
      Response.Write("<br>" + myReader.GetValue(1)) 

      myReader.Close() 

      nwindConn.Close() 
loop 

catch Ex as SqlException 
      Response.Write(GetSqlExceptionDump(Ex)) 
End Try

Everything seems to look okay. Looking at the code in Listings 16.3 and 16.4, you probably don’t see anything wrong. That’s because syntactically it is correct—but what about runtime errors? That’s what we are really talking about. Those can be the most difficult errors to track down and debug.

Now that you have an example to work with, let’s see what happens if this code is executed. Look at the output shown in Listing 16 .5.

Listing 16.5. Sample Output of GetSqlExceptionDump
Class:16 
Errors:System.Data.SqlClient.SqlErrorCollection 
Help Link: 
InnerException: 
Line#:1 
Message:Invalid column name 'dCategoryID'. 
Procedure: 
Server:DIGITAL-LAPTOP 
Source:SQL Server Managed Provider 
StackTrace: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
 RunBehavior runBehavior, Boolean returnStream) at 
System.Data.SqlClient.SqlCommand.ExecuteReader() at 
adoerrors.WebForm1.Page_Load(Object sender, EventArgs e) in 
c:inetpubwwwrootadoerrorswebform1.aspx.cs:line 53 
State:3 
Target site:System.Data.SqlClient.SqlDataReader 
ExecuteReader(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior, Boolean) 
Line:1Index #0 Error: System.Data.SqlClient.SqlError: Invalid column name 
'dCategoryID'.

As you can see, we were trying to retrieve data from an invalid column, called dCategoryID. This would not necessarily stand out to another developer debugging your code, so how you organize your code is important. It is not necessarily going the extra mile—it’s just taking that extra little step to ensure that your code won’t blow up in the client’s face. You can still have an error occur, but you can present it in a pleasant way that won’t alarm the user but that instead will inform him of the current situation.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset