Moq and throwing a SqlException

Question

I have the following code to test that when a certain name is passed to my method, it throws a SQL exception (there is reason to that one, although it sounds a little odd).

   mockAccountDAL.Setup(m => m.CreateAccount(It.IsAny<string>(), 
"Display Name 2", It.IsAny<string>())).Throws<SqlException>();

However, this won't compile because SqlException's constructor is internal:

'System.Data.SqlClient.SqlException' must be a non-abstract type with a public parameterless constructor in order to use it as parameter 'TException' in the generic type or method 'Moq.Language.IThrows.Throws()'

Now, I could change this to state that it should throw Exception, but that wouldn't work for me, because my method should return one status code if it is a SqlException and another if it is any other exception. That's what my unit test is testing.

Is there any way to achieve this without either changing the logic of the method I'm testing, or not testing this scenario?

Answer 1

If you need test cases for the Number or Message properties of the exception, you could use a builder (which uses reflection) like this:

using System;
using System.Data.SqlClient;  // .NetCore using Microsoft.Data.SqlClient;
using System.Linq;
using System.Reflection;

public class SqlExceptionBuilder
{
    private int errorNumber;
    private string errorMessage;

    public SqlException Build()
    {
        SqlError error = this.CreateError();
        SqlErrorCollection errorCollection = this.CreateErrorCollection(error);
        SqlException exception = this.CreateException(errorCollection);

        return exception;
    }

    public SqlExceptionBuilder WithErrorNumber(int number)
    {
        this.errorNumber = number;
        return this;
    }

    public SqlExceptionBuilder WithErrorMessage(string message)
    {
        this.errorMessage = message;
        return this;
    }

    private SqlError CreateError()
    {
        // Create instance via reflection...
        var ctors = typeof(SqlError).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance);
        var firstSqlErrorCtor = ctors.FirstOrDefault(
            ctor =>
            ctor.GetParameters().Count() == 7); // .NetCore should be 8 not 7
        SqlError error = firstSqlErrorCtor.Invoke(
            new object[] 
            { 
                this.errorNumber, 
                new byte(), 
                new byte(), 
                string.Empty, 
                string.Empty, 
                string.Empty, 
                new int() 
            //,new Exception()  // for .NetCore 
            }) as SqlError;

        return error;
    }
 
    private SqlErrorCollection CreateErrorCollection(SqlError error)
    {
        // Create instance via reflection...
        var sqlErrorCollectionCtor = typeof(SqlErrorCollection).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance)[0];
        SqlErrorCollection errorCollection = sqlErrorCollectionCtor.Invoke(new object[] { }) as SqlErrorCollection;

        // Add error...
        typeof(SqlErrorCollection).GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance).Invoke(errorCollection, new object[] { error });

        return errorCollection;
    }

    private SqlException CreateException(SqlErrorCollection errorCollection)
    {
        // Create instance via reflection...
        var ctor = typeof(SqlException).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance)[0];
        SqlException sqlException = ctor.Invoke(
            new object[] 
            { 
                // With message and error collection...
                this.errorMessage, 
                errorCollection,
                null,
                Guid.NewGuid() 
            }) as SqlException;

        return sqlException;
    }
}

Then you could have a repository mock (for instance) throw an exception like this (this example uses the Moq library):

using Moq;

var sqlException = 
    new SqlExceptionBuilder().WithErrorNumber(50000)
        .WithErrorMessage("Database exception occured...")
        .Build();
var repoStub = new Mock<IRepository<Product>>(); // Or whatever...
repoStub.Setup(stub => stub.GetById(1))
    .Throws(sqlException);

Answer 2

This should work:

using System.Runtime.Serialization;

var exception = FormatterServices.GetUninitializedObject(typeof(SqlException)) 
                as SqlException;

mockAccountDAL.Setup(m => m.CreateAccount(It.IsAny<string>(), "Display Name 2", 
                     It.IsAny<string>())).Throws(exception);

However, using GetUninitializedObject has this caveat:

Because the new instance of the object is initialized to zero and no constructors are run, the object might not represent a state that is regarded as valid by that object.

If this causes any problems, you can probably create it using some more involved reflection magic but this way is probably the simplest (if it works).

Answer 3

I just tried this out, and it worked for me:

private static void ThrowSqlException()
{
    using (var cxn = new SqlConnection("Connection Timeout=1"))
    {
        cxn.Open();
    }
}

// ...
mockAccountDAL.Setup(m => m.CreateAccount(It.IsAny<string>),
                     "Display Name 2", It.IsAny<string>()))
              .Callback(() => ThrowSqlException());

Answer 4

For me to produce an SqlException with a message it was the simplest way using the Uninitialized Object method:

const string sqlErrorMessage = "MyCustomMessage";
var sqlException = FormatterServices.GetUninitializedObject(typeof(SqlException)) as SqlException;
var messageField = typeof(SqlException).GetField("_message", BindingFlags.NonPublic | BindingFlags.Instance);
messageField.SetValue(sqlException, sqlErrorMessage);

Answer 5

I wrote this before finding this question/answer. Might be useful for someone just wanting a SQL exception with a particular number.

private static SqlException CreateSqlExceptionWithNumber(int errorNumber)
{
    var sqlErrorCollectionCtor = typeof(SqlErrorCollection).GetConstructor(
        BindingFlags.NonPublic | BindingFlags.Instance,
        null,
        CallingConventions.Any,
        new Type[0],
        null);

    var sqlErrorCollection = (SqlErrorCollection)sqlErrorCollectionCtor.Invoke(new object[0]);

    var errors = new ArrayList();

    var sqlError = (SqlError)FormatterServices.GetSafeUninitializedObject(typeof(SqlError));

    typeof(SqlError)
        .GetField("number", BindingFlags.NonPublic | BindingFlags.Instance)
        ?.SetValue(sqlError, errorNumber);

    errors.Add(sqlError);

    typeof(SqlErrorCollection)
        .GetField("errors", BindingFlags.NonPublic | BindingFlags.Instance)
        ?.SetValue(sqlErrorCollection, errors);

    var exception = (SqlException)FormatterServices.GetUninitializedObject(typeof(SqlException));

    typeof(SqlException)
        .GetField("_errors", BindingFlags.NonPublic | BindingFlags.Instance)
        ?.SetValue(exception, sqlErrorCollection);
    
    return exception;
}

Answer 6

public class SqlExceptionMock { public static SqlException ThrowSqlException(int errorNumber, string message = null) { var ex = (SqlException)FormatterServices.GetUninitializedObject(typeof(SqlException)); var errors = GenerateSqlErrorCollection(errorNumber, message); SetPrivateFieldValue(ex, "_errors", errors); return ex; }

    private static SqlErrorCollection GenerateSqlErrorCollection(int errorNumber, string message)
    {
        var t = typeof(SqlErrorCollection);
        var col = (SqlErrorCollection)FormatterServices.GetUninitializedObject(t);
        SetPrivateFieldValue(col, "_errors", new List<object>());
        var sqlError = GenerateSqlError(errorNumber, message);
        var method = t.GetMethod(
          "Add",
          BindingFlags.NonPublic | BindingFlags.Instance);
        method.Invoke(col, new object[] { sqlError });
        return col;
    }

    private static SqlError GenerateSqlError(int errorNumber, string message)
    {
        var sqlError = (SqlError)FormatterServices.GetUninitializedObject(typeof(SqlError));

        SetPrivateFieldValue(sqlError, "_number", errorNumber);
        if (!string.IsNullOrEmpty(message)) SetPrivateFieldValue(sqlError, "_message", message);
        return sqlError;
    }

    private static void SetPrivateFieldValue(object obj, string field, object val)
    {
        var member = obj.GetType().GetField(
          field,
          System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance
          );
        member?.SetValue(obj, val);
    }
}