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

I am trying to test some exceptions in my project and one of the Exceptions I catch is SQlException.

It seems that you can't go new SqlException() so I am not sure how I can throw an exception especially without somehow calling the database (and since these are unit tests it is usually advised not to call the database since it is slow).

I am using NUnit and Moq, but I am not sure how to fake this.

Responding to some of the answers that seem to all be based on ADO.NET, note that I am using Linq to Sql. So that stuff is like behind the scenes.

More info as requested by @MattHamilton:

System.ArgumentException : Type to mock must be an interface or an abstract or non-sealed class.       
  at Moq.Mock`1.CheckParameters()
  at Moq.Mock`1..ctor(MockBehavior behavior, Object[] args)
  at Moq.Mock`1..ctor(MockBehavior behavior)
  at Moq.Mock`1..ctor()

Posts to the first line when it tries to mockup

 var ex = new Mock<System.Data.SqlClient.SqlException>();
 ex.SetupGet(e => e.Message).Returns("Exception message");

Solution 1:

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.

Solution 2:

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.

Solution 3:

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;
}

Solution 4:

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.