System.Web.Services.Protocols.SoapException

.NET Exception Handling: System.Data.SqlClient.SqlException

As we continue down the beautiful path that winds through our .NET Exception Handling series, today we’ll be examining the System.Data.SqlClient.SqlException. The System.Data.SqlClient.SqlException is typically thrown when an accessed SQL Server returns a warning or error of its own.

In this article, we’ll explore where System.Data.SqlClient.SqlException resides within the .NET exception hierarchy, examine when System.Data.SqlClient.SqlExceptions most commonly appear, and see how to handle them should you encounter one yourself. So, let’s get to it!

The Technical Rundown

When Should You Use It?

Since the occurrence of a System.Data.SqlClient.SqlException is directly related to a problem with the SQL server, it’s important to take a moment to understand how to connect a C# application to an SQL server, and therefore what scenarios System.Data.SqlClient.SqlExceptions might occur.

As with most anything in .NET, there are many ways to tackle the problem of database connection and usage, so we’ll just provide an example for this article, and you can apply it to your own experiences or setups. For the following code, we’re using a Microsoft Azure cloud-based SQL Server and SQL Database, so there’s no need to setup an SQL server on our local machine. Setting up an SQL Server/Database on Azure is beyond the scope of this article, but once it’s configured, we can connect to it using a standard ADO Connection String, which is just a group of key/value pairs that informs an application where our database server is located and how to connect to it. An ADOstring typically looks something like: Server=[server],1433;Initial Catalog=[catalog];...

To make use of our database, and to keep our example code a bit cleaner, we’ve opted to store our ADO string and our SQL credentials within the App.config file for our C# project. Below you can see the full App.config we’re using, with a bit of obfuscation where necessary to retain privacy. It includes the applicationSettings element, where we’ve stored our app settings related to our SQL server:

With that in place, we’ll take a look at the example application code, which aims to perform two SQL queries (one successfully, and one unsuccessfully). The full code is below, after which we can break it down a bit more to see what’s going on:

The Utility namespace is where our Logging class resides, which is just used as a convenience for outputting information during debugging, so we won’t go into anymore detail on that section. The meat and potatoes of our code here is in the Program.PerformQuery method:
private static void PerformQuery(string query)

The comments provide a bit of guidance, but effectively this is just one way we can create a new connection to our SQL server (using the SqlConnectionStringBuilder class), open it, pass our query parameter to the CommandText, then issue an ExecuteReader method call to make that request to the server. Once a result is returned, we loop through it, outputting the information to our log, before closing everything out. We also are checking to see if any System.Data.SqlClient.SqlExceptions occur, and catching those if necessary.

Lastly, we have two actual query strings we’re trying:

The first is a basic query using the well-known AdventureWorks sample database, grabbing the first 20 Productsand their associated ProductCategory. This query works fine and the output is as expected:

However, our second query explicitly makes a call to a stored procedure that doesn’t exist in our database, so we’re expecting a System.Data.SqlClient.SqlException to be raised. Sure enough, the output shows us exactly the problem:

While this is just a brief glimpse and example, hopefully it illustrates just how a System.Data.SqlClient.SqlExceptionmight show up, and give some insight for your own future projects working with SQL servers.

To get the most out of your own applications and to fully manage any and all .NET Exceptions, check out the Airbrake .NET Bug Handler, offering real-time alerts and instantaneous insight into what went wrong with your .NET code, along with built-in support for a variety of popular development integrations including: JIRA, GitHub, Bitbucket, and much more.