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

Question

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

Answer 1

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
}