In many .NET applications, it is common to make calls to SQL Server for various database operations. However, it is important to have a proper exception handling mechanism in place to handle any errors that may occur during these database calls. Exception handling allows you to report the exact issue to the users and control the flow of your application.

Sample Database

For the purpose of this example, we will be using a sample database called “SampleDB” with two tables: “employees” and “location”. The “employees” table has a foreign key pointing to the “location” table.

The Query Call

Let’s create a simple Visual C# Console App that makes a query call to the database. In this example, we will intentionally make a problematic call to see how errors are reported in our application.

using System;
using System.Data.SqlClient;

namespace QueryCall
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = @"Server=.\SQL2K17; Database=SampleDB; Trusted_Connection=True;";
            int empID;
            string empCode, empFirstName, empLastName, locationCode, locationDescr;

            using (SqlConnection conn = new SqlConnection(connString))
            {
                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;";

                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader dr = null;

                try
                {
                    dr = cmd.ExecuteReader();

                    Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
                    Console.WriteLine("Retrieved 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);

                            Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}", empID.ToString(), empCode, empFirstName, empLastName, locationCode, locationDescr);
                        }
                    }
                    else
                    {
                        Console.WriteLine("No data found.");
                    }

                    dr.Close();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("Inner Exception: " + ex.Message);
                    Console.WriteLine();
                    Console.WriteLine("Query Executed: " + query);
                    Console.WriteLine();
                    dr.Close();
                }
                finally
                {
                    // Any additional code to be executed regardless of an exception
                }

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

When running the program, we intentionally make a call to a non-existent table “employees1” to generate an error. Without proper exception handling, the program execution fails and returns an unhandled exception, terminating the program without executing the rest of the code.

Implementing Exception Handling

To handle exceptions in our code, we need to add two exception handling blocks. The first block handles exceptions for the SqlCommand.ExecuteReader method, specifically using the “SqlException” type. The second block handles any other exceptions that may occur using the “Exception” type.

try
{
    dr = cmd.ExecuteReader();
}
catch (SqlException ex)
{
    Console.WriteLine("Inner Exception: " + ex.Message);
    Console.WriteLine();
    Console.WriteLine("Query Executed: " + query);
    Console.WriteLine();
    dr.Close();
}
finally
{
    // Any additional code to be executed regardless of an exception
}

try
{
    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 proper exception handling in place, our program now fully manages any exceptions and displays more meaningful error messages to the user. It also allows the program to continue its execution, even in the event of an exception.

Discussion

Proper exception handling is essential in any application, including database applications. It allows you to control the flow of your application, inform users about errors, and avoid application freezes and crashes. When developing in .NET, it is important to follow best practices for exceptions and choose the appropriate exception types for your operations.

By implementing exception handling in your SQL Server .NET applications, you can ensure a more robust and user-friendly experience for your users.