• Bobby Brady

Creating a SQL Exception

I am very new to Software Development. My biggest struggle appears to be not knowing what it is I need to search for. I have Problem A: and I look, and I search, but I cannot seem to find a solution. When I ask for help, others can find the solution in moments.

When I was tasked with Unit Tests, I was feeling confident. I had seen, and even implemented a few unit tests when I was learning to program. I was sure I could make this happen. The Unit Tests I was to write were to test some functions I had written that categorize different types of Exceptions. If a SQL Exception was passed into my function, it would be categorized based on the SQL Number, so I had to create a new SQL Exception to be able to use it while Unit Testing.

I scoured the Internet for the answer to this problem. It seemed that many people had had the problem back in 2004, and a lot of the code was outdated. So, after many hours I finally created a new SQL Exception, and here is how I did it:

//create the SQL Exception
    private SqlException MakeSqlException(int sqlNumber)
        //create new exception
        var newException = new Exception();

        //construct the SQL Error Collection
        SqlErrorCollection collection = Construct<SqlErrorCollection();

        //construct a SQL Error with a parameter (this case its a SQL Number)
        SqlError error = Construct<SqlError>(sqlNumber, (byte)2, (byte)3, "server name", "error message", "proc", 100, newException);

        //Using reflection inject the error into the SqlErrorCollection
        .GetMethod("Add", BindingFlags.NonPublic |         
        .Invoke(collection, new object[] { error });

        //Using reflection create the SQL exception
        var e = typeof(SqlException)
        .GetMethod("CreateException", BindingFlags.NonPublic |
       BindingFlags.Static, null, CallingConventions.ExplicitThis, 
       new[] { typeof(SqlErrorCollection), typeof(string) }, new ParameterModifier[] { })
       .Invoke(null, new object[] { collection, "11.0.0" }) as SqlException;

        return e;

    //constructs a class based off of the type and parameters
    private T Construct<T>(params object[] p)
        //declare a new var for type
        var t = new Type[p.Length];
        //for each parameter get the type
        for (var i = 0; i < p.Length; i++)
            t[i] = p[i].GetType();
        //get constructor info 
        var constructorInfo = typeof(T).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, t, null);
        if (constructorInfo == null)
            throw new InvalidOperationException(string.Format("Cannot find a matching private or static constructor for type {0} with the constructor parameters ({1})", typeof(T).Name, string.Join<Type>(", ", t)));
        //invoke and return constructor
        return (T)constructorInfo.Invoke(p);


Southport Solutions, LLC

Technology Architects At Your Service

(615) 212-5032

3401 Mallory Lane

Suite 200

Franklin TN 37067

Contact Us

To learn more, don’t hesitate to get in touch

  • White Twitter Icon
  • White LinkedIn Icon

© 2020 Southport Solutions, LLC