Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

.NET Exception Handling for Database Calls to SQL Server with Try, Catch and Finally


By:   |   Last Updated: 2019-05-24   |   Comments (1)   |   Related Tips: More > Application Development

Problem

In many of my previous Application Development tips, among other, we've talked about calling SQL Server via queries, functions, stored procedures and more, from within our .NET code.  Besides the implementation of the calls, another important aspect, is exception handling. With the proper exception handling mechanisms, you can report back to your application's users, what is the exact issue in case a database call fails, as well as fully control your application's flow. Moreover, this can help the user take possible corrective actions that will help the application to work properly.

In this tip, we will talk about setting up the proper exception handling mechanism, for .NET applications that make calls to SQL Server.

Solution

In order to better understand proper exception handling in .NET when it comes to SQL Server database calls, we will be using a simple query call to SQL Server from within our .NET app (C#).

For the above database call, we will be intentionally making a problematic call, in order to see what errors are being reported in our application with and without explicit exception handling, as well as see how the flow of the program is affected.

Sample Database

In order to be able to better understand the different concepts, we will need a sample database. This database is called "SampleDB" and I have created it on a test SQL Server named instance on my local machine. The name of the instance is "SQL2K17".

Here's a screenshot of the SQL Server instance, as it can be seen in SSMS:

This is a sample database that will be used for this tip examples.

Also, here's the database diagram of the tables in the "SampleDB" database which will be used for this tip's example:

Database Diagram - This is the database diagram for the SampleDB database, illustrating the two tables that will be used in this tip example.

As you can see, there is a table named "employees" and another table named "location".

The "employees" table has a foreign key (locationID) pointing to the "location" table and more specifically, to the "id" column.

Sample Data

The sample data in the "location" table are:

Sample Data - This is a screenshot of the sample data in the location table.

The sample data in the "employees" table are:

Sample Data - This is a screenshot of the sample data in the employees table.

The Query Call

Now, let's create a simple Visual C# Console App (.NET Framework), name it "QueryCall" and make a simple query call to the database.

Creating a Visual C# Console App .NET Framework

The query to execute is the below:

SELECT e.id,e.code,e.firstName,e.lastName,l.code,l.descr
FROM employees1 e
INNER JOIN location l on e.locationID=l.id;

As you can see in the above code, even though the correct table names in our sample database is "employees" and "location", instead of calling "employees", I'm calling the table "employees1", which does not exist, in order to get an error. So, the initial code for our console application is the below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
 
namespace QueryCall
{
    class Program
    {
        static void Main(string[] args)
        {
 
            //set the connection string
            string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";
 
            //variables to store the query results
            int empID;
            string empCode, empFirstName, empLastName, locationCode, locationDescr;
 
 
            //sql connection object
            using (SqlConnection conn = new SqlConnection(connString))
            {
 
                //retrieve the SQL Server instance version
                string query = @"
SELECT e.id,e.code,e.firstName,e.lastName,l.code,l.descr
FROM employees1 e
INNER JOIN location l on e.locationID=l.id;";
 
                //define the SqlCommand object
                SqlCommand cmd = new SqlCommand(query, conn);
 
                //open connection
                conn.Open();
                SqlDataReader dr = null;
 
                //execute the SQLCommand
                dr = cmd.ExecuteReader();
 
                Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
                Console.WriteLine("Retrieved records:");
 
                //check if there are records
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        empID = dr.GetInt32(0);
                        empCode = dr.GetString(1);
                        empFirstName = dr.GetString(2);
                        empLastName = dr.GetString(3);
                        locationCode = dr.GetString(4);
                        locationDescr = dr.GetString(5);
 
                        //display retrieved record
                        Console.WriteLine("{0},{1},{2},{3},{4},{5}", empID.ToString(), empCode, empFirstName, empLastName, locationCode, locationDescr);
                    }
                }
                else
                {
                    Console.WriteLine("No data found.");
                }
 
                //close data reader
                dr.Close();
 
                //close connection
                conn.Close();
            }
 
 
            Console.WriteLine();
            Console.WriteLine("...program execution after database calls...");
 
        }
    }
}

After running the program, this is what we get as an error:

Program output without exception handling

As you can see, the program's execution failed and returned an unhandled exception and therefore, it displayed the stack trace, which is the default behavior in such cases. That means that there is not a proper exception handling mechanism in the code, in order to handle any issues that may have to do with the interaction between our C# app and SQL Server.

Moreover, since an unhandled exception took place, the program's execution was terminated (application crash) without being able to proceed with the execution of the rest of the program's logic, that is executing the code line:

Console.WriteLine("...program execution after database calls...");

Further Analysis – The Need for Exception Handling

If we further analyze the source code, we can see that the actual code that executes the query is:

dr = cmd.ExecuteReader();

If we check MS Docs about the SqlCommand.ExecuteReader method, we can see that it provides comprehensive information about the supported exception types.

So, from the MS Docs article, we can see that the above method supports the below exception types:

This helps us decide how to make use of a more precise exception mechanism. In this case, we will make use of the "SqlException" type. To this end, the approach to be followed regarding exception handling for our app is the following:

  • Use an exception handling mechanism for the SqlCommand.ExecuteReader method, using the exception type "SqlException" (inner code exception handling)
  • Use an exception handling mechanism for the "using" code block, using the exception type "Exception" (outer code exception handling)

By using the above exception handling approach, not only we will be handling any unexpected issues that may have to do with the database calls, but also, we will be allowing our program to continue its execution, if of course, this is the intended behavior. More precisely, we will be able to fully control the program's flow, even in the event of an exception.

Implementing Exception Handling

To this end, based on the above analysis, we need to add two exception handling blocks in our code.

The first block which will be handling any exceptions for the SqlCommand.ExecuteReader method will be:

try
{
    //execute the SQLCommand
    dr = cmd.ExecuteReader();
}
catch (SqlException ex)
{
    Console.WriteLine("Inner Exception: " + ex.Message);
    Console.WriteLine();
    Console.WriteLine("Query Executed: " + query);
    Console.WriteLine();
    dr.Close();
}
finally
{
    ; //here you can add any code you want to be executed
      //regardless if an exception is thrown or not
}

In the above exception handling code, you can note the syntax which uses the keywords trycatchfinally.

The "try" keyword encapsulates the code to be executed and whenever an exception is thrown, it passes the execution to the "catch" code block. Therefore, in the "catch" code block, you can include the handling code you want to be executed in the event of an exception. Last, the "finally" keyword/code block, is always executed, regardless if an exception is thrown or not.

The outer exception handling block will encapsulate the entire "using…" block in order to catch any other exceptions besides the SqlException block.

This code block will look like this:

try
{
    //sql connection object
    using (SqlConnection conn = new SqlConnection(connString))
    {
 
       //… application code …
    }
}
catch (Exception ex)
{
    Console.WriteLine("Outer Exception: " + ex.Message);
}
finally
{
    Console.WriteLine();
    Console.WriteLine("...program execution after database calls...");
}

With the above two exception handling blocks, we will be able to fully manage any exception in our code and control the application's flow in those cases as well.

So, based on all the above, our application's entire source code changes as per below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
 
namespace QueryCall
{
    class Program
    {
        static void Main(string[] args)
        {
 
            //set the connection string
            string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";
 
            //variables to store the query results
            int empID;
            string empCode, empFirstName, empLastName, locationCode, locationDescr;
 
            try
            {
 
 
                //sql connection object
                using (SqlConnection conn = new SqlConnection(connString))
                {
 
                    //retrieve the SQL Server instance version
                    string query = @"
SELECT e.id,e.code,e.firstName,e.lastName,l.code,l.descr
FROM employees1 e
INNER JOIN location l on e.locationID=l.id;";
 
                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(query, conn);
 
                    //open connection
                    conn.Open();
                    SqlDataReader dr = null;
 
                    try
                    {
                        //execute the SQLCommand
                        dr = cmd.ExecuteReader();
                    }
                    catch (SqlException ex)
                    {
                        Console.WriteLine("Inner Exception: " + ex.Message);
                        Console.WriteLine();
                        Console.WriteLine("Query Executed: " + query);
                        Console.WriteLine();
                        dr.Close();
                    }
                    finally
                    {
                        ; //here you can add any code you want to be executed
                          //regardless if an exception is thrown or not
                    }
 
 
                    Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
                    Console.WriteLine("Retrieved records:");
 
                    //check if there are records
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            empID = dr.GetInt32(0);
                            empCode = dr.GetString(1);
                            empFirstName = dr.GetString(2);
                            empLastName = dr.GetString(3);
                            locationCode = dr.GetString(4);
                            locationDescr = dr.GetString(5);
 
                            //display retrieved record
                            Console.WriteLine("{0},{1},{2},{3},{4},{5}", empID.ToString(), empCode, empFirstName, empLastName, locationCode, locationDescr);
                        }
                    }
                    else
                    {
                        Console.WriteLine("No data found.");
                    }
 
                    //close data reader
                    dr.Close();
 
                    //close connection
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Outer Exception: " + ex.Message);
            }
            finally
            {
                Console.WriteLine();
                Console.WriteLine("...program execution after database calls...");
            }
        }
    }
}

And this is the new output of our program's execution:

Program output when using exception handling

Now, as you can see, our program's code fully manages any exception, as well it is more user-friendly, because instead of displaying an unhandled exception along with displaying its stack trace, it now displays more meaningful error messages to the user.

Discussion

It is natural during a software application's lifecycle, the application to sometimes experience errors due to either invalid user input or unexpected computing environment issues. For example, a database server might not be accepting connections via TCP/IP, a user might not be having access to a database object, etc.

If you don't handle these exceptions in your code, then your application will not have a controlled behavior, meaning that some things might run and some not.

With proper exception handling, you can fully control any unexpected issues, inform the user accordingly and in general, smoothly control your application's flow in such cases and thus avoid application freezes and crashes.

Proper exception handling is always a must in any application, not only database apps.

Especially when developing in .NET, make sure you follow the Best Practices for Exceptions guide, as well as, always find the proper exception type for the operations you perform in your app. Moreover, always include an outer Exception try…catch…finally block in order to catch and handle any exceptions that might have not being caught by your inner exception handling blocks.

Next Steps


Last Updated: 2019-05-24


get scripts

next tip button



About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018).

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, May 24, 2019 - 9:12:25 AM - D2 Back To Top
Tip Comments Pending Approval

Learn more about SQL Server tools