dotnet Exception Handling

.NET Exceptions – System.Data.SqlTypes.SqlTypeException

Fast approaching the conclusion of our current .NET Exception Handling series, today we’ll be looking into the System.Data.SqlTypes.SqlTypeException. The appearance of an SqlTypeException is the result of something going wrong while using the System.Data.SqlTypes namespace classes.

In this article we’ll examine the SqlTypeException by seeing where it resides in the overall .NET exception hierarchy. We’ll then look at a fully functional C# code sample that will illustrate one specific technique for connecting to an ADO.NET data source, performing queries, and how we could encounter SqlTypeExceptions under certain circumstances, particularly when dealing with abnormal or difficult to manage data types. Let’s get to it!

The Technical Rundown

All .NET exceptions are derived classes of the System.Exception base class, or derived from another inherited class therein. The full exception hierarchy of this error is:

Full Code Sample

Below is the full code sample we’ll be using in this article. It can be copied and pasted if you’d like to play with the code yourself and see how everything works.

This code sample also uses the Book.cs class, the full code of which can be seen here via GitHub.

This code sample also uses the Logging.cs helper class, the full code of which can be seen here via GitHub.

When Should You Use It?

Since an SqlTypeException only appears when dealing with System.Data.SqlTypes namespaced classes, let’s start at a more basic level with a simple SQL database connection and query. Our Program class has a property and an enumeration to start things off, which we’ll use in a moment to simplify our connection and query methods:

Our primary query logic takes place in the ExecuteQuery(string query, SqlCommandExecutionType type = SqlCommandExecutionType.NonQuery) method:

This method starts by establishing a connection to the local ConnectionString property, which, in this case, is connecting to a local Sql Express instance (but would work with any valid connection string). By establishing the connection within a using block we ensure that the connection closes itself once this code block has completed execution.

We use the connection and passed query string to create a new SqlCommand instance, then attempt to Open() the connection within our try-catch block. We ensure that the CommandText property isn’t empty, since this can occur if we get exceptions elsewhere in the code that might prevent the SqlCommand from being properly formed.

Finally, we perform a switch check for the passed SqlCommandExecutionType type parameter to determine how we need to process this particular query string. For stuff like INSERT or DELETE commands we’d typically use ExecuteNonQuery(), while reading via SELECT is often going to use ExecuteReader(). This basic structure can obviously be expanded a great deal to properly handle different types of incoming data structures, but it’ll serve our basic purposes here. In the event we’re executing a query with an output, we use the Logging.Log(...) method to output the information to the log.

We’re making use of our Book class here to have a simple data structure we can try to insert into our database. I’ve already created the Book database table via this SQL query:

With this basic data structure and the properties of our Book class we can create the GetQueryFromBook(IBook book, bool shouldChangeDataType = false) method:

This method attempts to create a simple INSERT query string from the passed IBook book parameter object. There’s not much logic here, save for the bool shouldChangeDataType parameter value, which determines if we should attempt to change the data type of our DateTime parameter before inserting it into our query string. We’ll see what this does in a moment.

To test everything out we’ll start simple by creating a new Book instance, retrieving an INSERT query string from this new Book's properties, and then executing the query via the ExecuteQuery(...) method. Just to confirm things work properly, we’ll try a simple SELECT query to follow our insertion, to see if our Book was actually added to the database:

Executing the code above works as expected and produces the following output:

You’ll have to pardon the odd string formatting in the output. Since ExecuteQuery(...) doesn’t optimize the output, and merely pushes all column values into an object[] array, we get some strange formatting. But, the look doesn’t matter. What matters is we’ve confirmed our INSERT worked and our Book was properly added to the database!

However, let’s try another Book insertion with a slightly invalid DateTime of January 1st, 1750:

Executing these lines produces an unexpected SqlException (not to be confused with an SqlTypeException):

As the error message indicates, the problem here is that our code has attempted to convert a varchar to a datetime type that is out of range of expected values. In other words, our PublicationDate property of January 1st, 1750 is being converted to a valid format (i.e. 1/1/1750 12:00:00 AM). However, the SQL column type of DATETIME, which is what the dbo.Book.PublicationDate column is set to, can only accept date values of January 1, 1753, through December 31, 9999. Since the year 1750 is before this period, we get the SqlException seen above.

There are a few solutions to this issue. The first (and arguably best) option is to simply avoid using DATETIME column types in SQL tables. DATETIME is a bit outdated, and should be replaced with the DATETIME2 SQL column type, which was designed to be both backward compatible with all previous DATETIME values, while also giving a bigger date range of January 1,1 CE through December 31, 9999 CE. Thus, if our dbo.Book.PublicationDate column was a DATETIME2 type, we’d be fine.

However, since changing database columns is dangerous and not always feasible for existing data sets, another alternative solution introduced in .NET is the aforementioned System.Data.SqlTypes classes. These SQL-specific data types are explicitly designed to mirror the functionality of SQL column types in your database. Thus, if your .NET class objects use SqlDateTime instead of the normal DateTime type, this will ensure that you cannot create a date within an object that isn’t compatible with your SQL database.

To illustrate this, here we’re creating another Book and generating an INSERT query:

The second true argument passed to GetQueryStringFromBook(...) will return this generated query string:

As you can see, this explicitly creates a new SqlDateTime instance with the value of book.PublicationDate.Value.

Executing this code produces the following output:

Now we’re actually getting an SqlTypeException, rather than the SqlException we saw above. This error message is much more explicit, informing us that SqlDateTime cannot accept a date value outside of the specified range. If we were to refactor our Book class we could change the PublicationDate type to SqlDateTime, which would prevent this issue from coming up again since we’d be unable to use invalid date values.

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.