Throw your own SqlException, part 2

Last year I wrote a very short post on a technique for creating and throwing a SqlException instance. The post links to a solution on another blog. Since I have been doing a bit of work using reflection lately I thought I should follow up on the original post and explain exactly what the problem is, how it's solved, and also include the full source code (in case the original post on the other blog is lost).

So, here we go. My original problem was that I needed to be able to throw an instance of SqlException with a specific error code. SqlException cannot be instantiated directly, however, because its constructor is marked as private. The only way to get around this is to use the very useful tools in the System.Reflection namespace and get at the private constructor via exploration of the metadata emitted by the System.Data assembly.

Before I go on, if you are not familiar with what reflection is I recommend that you first read about it here. Also, when working with reflection to discover and create types Lutz Roeder's Reflector is invaluable. Download it!

Now we're equipped for the task at hand. Using Reflector we'll first inspect the SqlException class to discover what's required to create an instance, and then we'll use the System.Reflection namespace to write a set of methods that allow us to create a SqlException instance with the required state. It really isn't difficult, but writing this kind of code (using System.Reflection) does not feel natural at first pass, so I'll go through it here step by step.

1. Inspect SqlException with Reflector.
Open Reflector and load the System.Data assembly. Expand the System.Data.dll node, and then the System.Data.SqlClient node. Scroll down until you find the SqlException node and expand this also. When you expand this node you'll find that the SqlException class has two constructors (both private), three methods (one public, one private, one internal), and several public properties.

One of the private constructors takes a string (an error message) and a SqlErrorCollection instance as parameters. This is the constructor we'll be working with. Using System.Reflection we'll create an instance of SqlException by passing in a string of our choosing, plus an instance of SqlException that we'll also create. But before we get carried away, let's have a look at the SqlErrorCollection class.

2. Inspect SqlErrorCollection with Reflector.
SqlErrorCollection is listed just above SqlException in the node list. Expand it and you'll find one internal constructor that takes no parameters (how useful for us!), one internal Add(SqlError) method (probably a bit more useful), three public methods, two public properties, and two inherited properties.

So, in order to create an instance of SqlErrorCollection for our SqlException constructor it seems we'll have to utilise System.Reflection a bit more. Now, an empty SqlErrorCollection is probably not going to be that useful to us either, so we'll have to create an instance of SqlError and add it to the collection using the Add(SqlError) method.

3. Inspect SqlError with Reflector.
SqlError is listed just above the SqlErrorCollection in the node list. When you expand it you'll see that it is quite simple, with an internal, seven-parameter constructor, and a single public method (overriding Object.ToString()).

Armed with this knowledge, the list of steps to complete our task is as follows:

1. Create a SqlError with an appropriate error message and error code.


2. Create a SqlErrorCollection and add the SqlError instance from step 1.


3. Create a SqlException instance by passing in an error message and the SqlErrorCollection from step 2.

Almost there, but not quite. How exactly do we go about doing this? Before we carry on, it's time to have a look at the System.Reflection namespace. Put simply the System.Reflection namespace allows you to discover the internal structure of a type, invoke methods, properties or constructors, and even create types during runtime. All of this is accomplished through the use of special purpose classes, some of which we will use here (I'm not going to discuss System.Reflection more in-depth here as it really deserves a long post on its own).

Specifically we will be making use of the ConstructorInfo class (for calling constructors) and the MethodInfo class (for calling methods). Instances of these classes are returned by calling GetMethod() or GetConstructor(), respectively, on a type. For example, calling typeof(SqlErrorCollection).GetConstructor(...params...) returns an instance of ConstructorInfo that we can use to invoke the constructor. In fact, the parameters we pass to GetConstructor performs a search on the type (in this case SqlErrorCollection) and returns a ConstructorInfo instance that corresponds to the constructor found in the search. (If the constructor is not found, the ConstructorInfo instance returned is null.)

Let's a have a bit of a closer look. GetConstructor() has three overloads, but I will only look at the second overload here as it is exactly what we need. The method takes four parameters: BindingFlags, Binder, Type[], and ParameterModifier[]. The first parameter is a bitmap constructed of stringing together individual BindingFlags that we use to indicate what type of constructor we're after (e.g. a non-public constructor). The second parameter, Binder, allows us to pass in a special instance of a Binder class that can be used to select a specific overload etc. The third parameter is an array of Type, and this array specifies the types and number of the parameters in the constructor declaration. The fourth and last parameter is an array of ParameterModifier - each ParameterModifier in this array can be used to specify which parameters are to be passed by reference and so on.

We only need to worry about two of these parameters, namely the BindingFlags and the Type array. The two others we'll pass in as nulls and let the framework use the built in default behaviour.

GetMethod() is similar to GetConstructor() but has a string parameter that can be used to pass in the name of the method to find. If the method we're looking for has no overloads we only need to specify the BindingFlags for the method and we're done! Otherwise we can also pass in a Type array to specify which overload, exactly, we're after.

Now we're both armed with knowledge and equipped with some nifty tools to get the job done. Let's get started on our first task, creating a SqlError instance.

Creating the SqlError instance.
The constructor on SqlError takes seven parameters of different types. To search for this constructor using the GetConstructor() method we'll have to declare at Type array that defines these types in the correct order. Also, when it comes to actually invoking the constructor we'll need to pass in some actual parameters, and we'll have to declare these in an array of Object. Then we'll get a reference to the SqlError constructor by calling GetConstructor() on its type, and finally create an instance of SqlError by invoking the constructor with our parameters. Put together in a method it looks like this (there are some default parameters used here; you can replace these with something more dynamic if you like):

private static SqlError GetError(int errorCode, string message)
{
object[] parameters = new object[] { errorCode, (byte)0, (byte)10, "server", message, "procedure", 0 };
Type[] types = new Type[] { typeof(int), typeof(byte), typeof(byte), typeof(string), typeof(string), typeof(string), typeof(int) };
ConstructorInfo constructor = typeof(SqlError).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, types, null);
SqlError error = (SqlError)constructor.Invoke(parameters);

return error;
}

Creating the SqlErrorCollection instance.
This is a simpler task than creating a SqlError instance because the SqlErrorCollection constructor doesn't take any parameters. All we have to do is get a reference to the constructor and then invoke it. Later we'll invoke the Add() method to add the SqlError instance returned by the method we wrote in the previous step. A method to create a SqlErrorCollection instance looks like this:

private static SqlErrorCollection GetErrorCollection()
{
ConstructorInfo constructor = typeof(SqlErrorCollection).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, new Type[] { }, null);
SqlErrorCollection collection = (SqlErrorCollection)constructor.Invoke(new object[] { });
return collection;
}

Creating the SqlException instance.
This step basically does the same as what we've seen in the previous two steps, with the difference being that we invoke a method, and we use the instances of SqlError and SqlErrorCollection created by the two methods that we have already written. In short, we create an instance of SqlErrorCollection, an instance of SqlError, call GetMethod() on the SqlErrorCollection type to get a reference to the Add() method, then add the SqlError instance to the collection. Finally we wrap it all up by instantiating a SqlException by getting a reference to its constructor and passing in an error message along with the SqlErrorCollection. As a method it looks like this:

public static SqlException CreateSqlException(string errorMessage, int errorNumber)
{
SqlErrorCollection collection = GetErrorCollection();
SqlError error = GetError(errorNumber, errorMessage);

MethodInfo addMethod = collection.GetType().GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance);
addMethod.Invoke(collection, new object[] { error });

Type[] types = new Type[] { typeof(string), typeof(SqlErrorCollection) };
object[] parameters = new object[] { errorMessage, collection };

ConstructorInfo constructor = typeof(SqlException).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, types, null);
SqlException exception = (SqlException)constructor.Invoke(parameters);
return exception;
}

And that's it. Putting it neatly together in a class, we've got ourselves a little SqlExceptionStub that is very handy for testing certain expected database exceptions. The complete code is below.

And that's that!


using System;
using System.Data.SqlClient;
using System.Reflection;

namespace SnowValley.Tools.Tests.Utility
{
public class SqlExceptionStub
{
public static SqlException CreateSqlException(string errorMessage, int errorNumber)
{
SqlErrorCollection collection = GetErrorCollection();
SqlError error = GetError(errorNumber, errorMessage);

MethodInfo addMethod = collection.GetType().GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance);
addMethod.Invoke(collection, new object[] { error });

Type[] types = new Type[] { typeof(string), typeof(SqlErrorCollection) };
object[] parameters = new object[] { errorMessage, collection };

ConstructorInfo constructor = typeof(SqlException).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, types, null);
SqlException exception = (SqlException)constructor.Invoke(parameters);
return exception;
}

private static SqlError GetError(int errorCode, string message)
{
object[] parameters = new object[] { errorCode, (byte)0, (byte)10, "server", message, "procedure", 0 };
Type[] types = new Type[] { typeof(int), typeof(byte), typeof(byte), typeof(string), typeof(string), typeof(string), typeof(int) };
ConstructorInfo constructor = typeof(SqlError).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, types, null);
SqlError error = (SqlError)constructor.Invoke(parameters);

return error;
}

private static SqlErrorCollection GetErrorCollection()
{
ConstructorInfo constructor = typeof(SqlErrorCollection).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, new Type[] { }, null);
SqlErrorCollection collection = (SqlErrorCollection)constructor.Invoke(new object[] { });
return collection;
}

}
}