How to throw a SqlException when needed for mocking and unit testing?

Edit Ouch: I didn't realise SqlException is sealed. I've been mocking DbException, which is an abstract class.

You can't create a new SqlException, but you can mock a DbException, which SqlException derives from. Try this:

var ex = new Mock<DbException>();
ex.ExpectGet(e => e.Message, "Exception message");

var conn = new Mock<SqlConnection>();
conn.Expect(c => c.Open()).Throws(ex.Object);

So your exception is thrown when the method tries to open the connection.

If you expect to read anything other than the Message property on the mocked exception then don't forget to Expect (or Setup, depending on your version of Moq) the "get" on those properties.

I have a solution to this. I'm not sure whether it's genius or madness.

The following code will create a new SqlException:

public SqlException MakeSqlException() {
    SqlException exception = null;
    try {
        SqlConnection conn = new SqlConnection(@"Data Source=.;Database=GUARANTEED_TO_FAIL;Connection Timeout=1");
        conn.Open();
    } catch(SqlException ex) {
        exception = ex;
    }
    return(exception);
}

which you can then use like so (this example is using Moq)

mockSqlDataStore
    .Setup(x => x.ChangePassword(userId, It.IsAny<string>()))
    .Throws(MakeSqlException());

so that you can test your SqlException error handling in your repositories, handlers and controllers.

Now I need to go and lie down.

Depending on the situation, I usually prefer GetUninitializedObject to invoking a ConstructorInfo. You just have to be aware that it doesn't call the constructor - from the MSDN Remarks: "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." But I'd say it's less brittle than relying on the existence of a certain constructor.

[TestMethod]
[ExpectedException(typeof(System.Data.SqlClient.SqlException))]
public void MyTestMethod()
{
    throw Instantiate<System.Data.SqlClient.SqlException>();
}

public static T Instantiate<T>() where T : class
{
    return System.Runtime.Serialization.FormatterServices.GetUninitializedObject(typeof(T)) as T;
}

You can do this with reflection, you will have to maintain it when Microsoft make changes, but it does work I just tested it:

public class SqlExceptionCreator
{
    private static T Construct<T>(params object[] p)
    {
        var ctors = typeof(T).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance);
        return (T)ctors.First(ctor => ctor.GetParameters().Length == p.Length).Invoke(p);
    }

    internal static SqlException NewSqlException(int number = 1)
    {
        SqlErrorCollection collection = Construct<SqlErrorCollection>();
        SqlError error = Construct<SqlError>(number, (byte)2, (byte)3, "server name", "error message", "proc", 100);

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


        return typeof(SqlException)
            .GetMethod("CreateException", BindingFlags.NonPublic | BindingFlags.Static,
                null,
                CallingConventions.ExplicitThis,
                new[] { typeof(SqlErrorCollection), typeof(string) },
                new ParameterModifier[] { })
            .Invoke(null, new object[] { collection, "7.0.0" }) as SqlException;
    }
}      

This also allows you to control the Number of the SqlException, which can be important.