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

user_130015 0
Viewed 75447

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");
sqlexception moq asp.net-mvc .net
16 Answers

Microsoft.Data.SqlClient

If you are using the new Microsoft.Data.SqlClient Nuget package, 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;
    }
}
jjxtra_560790

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.

user_9820570

System.Data.SqlClient

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

(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());
user_2076870
Related