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");

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.

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

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.

Since you are using Linq to Sql, here is a sample of testing the scenario you mentioned using NUnit and Moq. I don't know the exact details of your DataContext and what you have available in it. Edit for your needs.

You will need to wrap the DataContext with a custom class, you cannot Mock the DataContext with Moq. You cannot mock SqlException either, because it is sealed. You will need to wrap it with your own Exception class. It is not to difficult to accomplish these two things.

Let's start by creating our test:

[Test]
public void FindBy_When_something_goes_wrong_Should_handle_the_CustomSqlException()
{
    var mockDataContextWrapper = new Mock<IDataContextWrapper>();
    mockDataContextWrapper.Setup(x => x.Table<User>()).Throws<CustomSqlException>();
    
    IUserResository userRespoistory = new UserRepository(mockDataContextWrapper.Object);
    // Now, because we have mocked everything and we are using dependency injection.
    // When FindBy is called, instead of getting a user, we will get a CustomSqlException
    // Now, inside of FindBy, wrap the call to the DataContextWrapper inside a try catch
    // and handle the exception, then test that you handled it, like mocking a logger, then passing it into the repository and verifying that logMessage was called
    User user = userRepository.FindBy(1);
}

Let's implement the test, first let's wrap our Linq to Sql calls using the repository pattern:

public interface IUserRepository
{
    User FindBy(int id);
}

public class UserRepository : IUserRepository
{
    public IDataContextWrapper DataContextWrapper { get; protected set; }

    public UserRepository(IDataContextWrapper dataContextWrapper)
    {
        DataContextWrapper = dataContextWrapper;
    }

    public User FindBy(int id)
    {
        return DataContextWrapper.Table<User>().SingleOrDefault(u => u.UserID == id);
    }
}

Next create the IDataContextWrapper like so, you can view this blog post on the subject, mine differs a little bit:

public interface IDataContextWrapper : IDisposable
{
    Table<T> Table<T>() where T : class;
}

Next create the CustomSqlException class:

public class CustomSqlException : Exception
{
 public CustomSqlException()
 {
 }

 public CustomSqlException(string message, SqlException innerException) : base(message, innerException)
 {
 }
}

Here's a sample implementation of the IDataContextWrapper:

public class DataContextWrapper<T> : IDataContextWrapper where T : DataContext, new()
{
 private readonly T _db;

 public DataContextWrapper()
 {
        var t = typeof(T);
     _db = (T)Activator.CreateInstance(t);
 }

 public DataContextWrapper(string connectionString)
 {
     var t = typeof(T);
     _db = (T)Activator.CreateInstance(t, connectionString);
 }
  
 public Table<TableName> Table<TableName>() where TableName : class
 {
        try
        {
            return (Table<TableName>) _db.GetTable(typeof (TableName));
        }
        catch (SqlException exception)
        {
            // Wrap the SqlException with our custom one
            throw new CustomSqlException("Ooops...", exception);
        }
 }

 // IDispoable Members
}

Not sure if this helps, but seems to have worked for this person (pretty clever).

try
{
    SqlCommand cmd =
        new SqlCommand("raiserror('Manual SQL exception', 16, 1)",DBConn);
    cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
    string msg = ex.Message; // msg = "Manual SQL exception"
}

Found at: http://smartypeeps.blogspot.com/2006/06/how-to-throw-sqlexception-in-c.html

Theese solutions feel bloated.

The ctor is internal, yes.

(Without using reflection, the easiest way to just genuinely create this exception....

   instance.Setup(x => x.MyMethod())
            .Callback(() => new SqlConnection("Server=pleasethrow;Database=anexception;Connection Timeout=1").Open());

Perphaps there's another method that doesn't require the timeout of 1 second to throw.

If you are using the new Microsoft.Data.SqlClient nuget, you can use this helper method:

public static class SqlExceptionCreator
{
	public static SqlException Create(int number)
	{
		Exception? innerEx = null;
		var c = typeof(SqlErrorCollection).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance);
		SqlErrorCollection errors = (c[0].Invoke(null) as SqlErrorCollection)!;
		var errorList = (errors.GetType().GetField("_errors", BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(errors) as List<object>)!;
		c = typeof(SqlError).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance);
		var nineC = c.FirstOrDefault(f => f.GetParameters().Length == 9)!;
		SqlError sqlError = (nineC.Invoke(new object?[] { number, (byte)0, (byte)0, "", "", "", (int)0, (uint)0, innerEx}) as SqlError)!;
		errorList.Add(sqlError);
		SqlException ex = (Activator.CreateInstance(typeof(SqlException), BindingFlags.NonPublic | BindingFlags.Instance, null, new object?[] { "test", errors,
			innerEx, Guid.NewGuid() }, null) as SqlException)!;
		return ex;
	}
}

This should work:

SqlConnection bogusConn = 
    new SqlConnection("Data Source=myServerAddress;Initial
    Catalog=myDataBase;User Id=myUsername;Password=myPassword;");
bogusConn.Open();

That takes a bit before it throws the exception, so I think this would work even faster:

SqlCommand bogusCommand = new SqlCommand();
bogusCommand.ExecuteScalar();

Code brought to you by Hacks-R-Us.

Update: nope, the second approach throws an ArgumentException, not a SqlException.

Update 2: this works much faster (the SqlException is thrown in less than a second):

SqlConnection bogusConn = new SqlConnection("Data Source=localhost;Initial
    Catalog=myDataBase;User Id=myUsername;Password=myPassword;Connection
    Timeout=1");
bogusConn.Open();

I noticed that your question is one year old, but for the record I would like to add a solution I discovered recently using microsoft Moles (you can find references here Microsoft Moles)

Once you haved moled the System.Data namespace, you can simply mock an SQL exception on a SqlConnection.Open() like this :

//Create a delegate for the SqlConnection.Open method of all instances
        //that raises an error
        System.Data.SqlClient.Moles.MSqlConnection.AllInstances.Open =
            (a) =>
            {
                SqlException myException = new System.Data.SqlClient.Moles.MSqlException();
                throw myException;
            };

I hope this can help someone that hits this question in the future.

I suggest using this method.

    /// <summary>
    /// Method to simulate a throw SqlException
    /// </summary>
    /// <param name="number">Exception number</param>
    /// <param name="message">Exception message</param>
    /// <returns></returns>
    public static SqlException CreateSqlException(int number, string message)
    {
        var collectionConstructor = typeof(SqlErrorCollection)
            .GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, //visibility
                null, //binder
                new Type[0],
                null);
        var addMethod = typeof(SqlErrorCollection).GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance);
        var errorCollection = (SqlErrorCollection)collectionConstructor.Invoke(null);
        var errorConstructor = typeof(SqlError).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null,
            new[]
            {
                typeof (int), typeof (byte), typeof (byte), typeof (string), typeof(string), typeof (string),
                typeof (int), typeof (uint)
            }, null);
        var error =
            errorConstructor.Invoke(new object[] { number, (byte)0, (byte)0, "server", "errMsg", "proccedure", 100, (uint)0 });
        addMethod.Invoke(errorCollection, new[] { error });
        var constructor = typeof(SqlException)
            .GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, //visibility
                null, //binder
                new[] { typeof(string), typeof(SqlErrorCollection), typeof(Exception), typeof(Guid) },
                null); //param modifiers
        return (SqlException)constructor.Invoke(new object[] { message, errorCollection, new DataException(), Guid.NewGuid() });
    }

I was only successful with @jjxtra's approach (which I upvoted), but the code needed to be modified since I am using System.Data.SqlClient which does not have a 9 parameter constructor for SqlError, and SqlErrorCollection has a field named "errors" (not "_errors") which is of type ArrayList (not List<object>).

// Assembly location: C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Data.dll

Here is the modified code that works for me:

  public static SqlException CreateSqlException(int number)
  {
    Exception? innerEx = null;
    var c = typeof(SqlErrorCollection).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance);
    SqlErrorCollection errors = (c[0].Invoke(null) as SqlErrorCollection);
    ArrayList errorList = (ArrayList)errors.GetType().GetField("errors", BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(errors);
    c = typeof(SqlError).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance);
    var theC = c.FirstOrDefault(f => f.GetParameters().Length == 8);
    SqlError sqlError = (theC.Invoke(new object?[] { number, (byte)0, (byte)0, "", "", "", (int)0, (uint)0}) as SqlError);
    errorList.Add(sqlError);
    SqlException ex = (Activator.CreateInstance(typeof(SqlException), BindingFlags.NonPublic | BindingFlags.Instance, null, new object[] { "test", errors,
      innerEx, Guid.NewGuid() }, null) as SqlException);
    return ex;
  }

(Sry it's 6 months late, hope this won't be considered necroposting I landed here looking for how to throw a SqlCeException from a mock).

If you just need to test the code that handles the exception an ultra simple workaround would be:

public void MyDataMethod(){
    try
    {
        myDataContext.SubmitChanges();
    }
    catch(Exception ex)
    {
        if(ex is SqlCeException || ex is TestThrowableSqlCeException)
        {
            // handle ex
        }
        else
        {
            throw;
        }
    }
}



public class TestThrowableSqlCeException{
   public TestThrowableSqlCeException(string message){}
   // mimic whatever properties you needed from the SqlException:
}

var repo = new Rhino.Mocks.MockReposity();
mockDataContext = repo.StrictMock<IDecoupleDataContext>();
Expect.Call(mockDataContext.SubmitChanges).Throw(new TestThrowableSqlCeException());

Based on all the other answers I created the following solution:

    [Test]
    public void Methodundertest_ExceptionFromDatabase_Logs()
    {
        _mock
            .Setup(x => x.MockedMethod(It.IsAny<int>(), It.IsAny<string>()))
            .Callback(ThrowSqlException);

        _service.Process(_batchSize, string.Empty, string.Empty);

        _loggermock.Verify(x => x.Error(It.IsAny<string>(), It.IsAny<SqlException>()));
    }

    private static void ThrowSqlException() 
    {
        var bogusConn =
            new SqlConnection(
                "Data Source=localhost;Initial Catalog = myDataBase;User Id = myUsername;Password = myPassword;Connection Timeout = 1");
        bogusConn.Open();
    }

This is really old and there are some good answers here. I am using Moq, and I can't mock up Abstract classes and really didn't want to use reflection, so I made my own Exception derived from DbException. So:

public class MockDbException : DbException {
  public MockDbException(string message) : base (message) {}
}   

obviously, if you need to add InnerException, or whatever, add more props, constructors, etc.

then, in my test:

MyMockDatabase.Setup(q => q.Method()).Throws(new MockDbException(myMessage));

Hoepfully this will help anyone that's using Moq. Thanks for everyone that posted in here that led me to my answer.

You could use reflection to create SqlException object in the test:

        ConstructorInfo errorsCi = typeof(SqlErrorCollection).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, new Type[]{}, null);
        var errors = errorsCi.Invoke(null);

        ConstructorInfo ci = typeof(SqlException).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, new Type[] { typeof(string), typeof(SqlErrorCollection) }, null);
        var sqlException = (SqlException)ci.Invoke(new object[] { "Exception message", errors });