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:
Also, here’s the database diagram of the tables in the “SampleDB” database which will be used for this tip’s 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:
The sample data in the “employees” table are:
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.
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:
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 try…catch…finally.
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 = nulltry { //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:
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
- Check my tip: Querying SQL Server Tables from .NET
- Check my tip: Working with SQL Server Functions and .NET
- Check my tip: Working with SQL Server Stored Procedures and .NET
- Check the MS Docs article: Best Practices for Exceptions
- Check the MS Docs article: Exception Class
- Check the MS Docs article: SqlException Class
