How to throw a SqlException if necessary for mocking and unit testing?

Question

I am trying to check some exceptions in my project, and one of the Exceptions that I catch is a SQlException .

It seems you cannot go new SqlException() , so I'm not sure how I can throw an exception, especially if you are not calling the database in some way (and since these are unit tests, it is usually recommended not to call the database, because it slow).

I use NUnit and Moq, but I'm not sure how to fake this.

Answering some of the answers that seem to be all based on ADO.NET, please note that I am using Linq for Sql. So the material is like a scene.

Further information on @MattHamilton's request:

 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() 

Messages on the first line when trying to layout

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

Answer 1

Since you are using Linq for Sql, here is an example of testing the script that you mentioned using NUnit and Moq. I do not know the exact data of your DataContext and what you have in it. Change for your needs.

You will need to wrap the DataContext with a special class, you cannot Mock the DataContext with Moq. You also cannot scoff at SqlException because it is sealed. You will need to wrap it with your own Exception class. It is not 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 me implement the test, first enable our Linq to Sql calls using the repository template:

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

Then create an IDataContextWrapper so that you can view this blog post by topic, mine is a little different:

 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 is an example implementation of 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 } 

Answer 2

You can do it with reflection, you have to support it when Microsoft makes changes, but it works, 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; } } 

It also allows you to control the number of SqlException that may be important.

Answer 3

I have a solution to this. I'm not sure if this is genius or insanity.

The following code will throw 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 this (Moq is used in this example)

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

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

Now I need to go and lie down.

Answer 4

Depending on the situation, I usually prefer GetUninitializedObject to call ConstructorInfo. You just need to know that it does not call the constructor - from MSDN Notes: "Since the new instance of the object is initialized to zero and no constructors start, the object may not represent the state that is considered valid by this object." But I would say that it is less fragile than relying on the existence of a particular 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; } 

Answer 5

Change Ouch: I did not understand that SqlException is sealed. I mocked a DbException, which is an abstract class.

You cannot throw a new SqlException, but you can make fun of the DbException that the SqlException comes from. Try the following:

 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 in the mocked exception, be sure to Expect (or Setup, depending on your version of Moq) to "get" these properties.

Answer 6

This should work:

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

This will take a bit before it throws an exception, so I think it will work even faster:

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

Code provided to you by Hacks-R-Us.

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

Update 2 : this works much faster (SqlException 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(); 

Answer 7

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

 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

Answer 8

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

After you have lowered the System.Data namespace, you can simply make fun of the SQL exception in SqlConnection.Open () as follows:

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

Hope this helps someone who addresses this issue in the future.

Answer 9

(Sry was 6 months late, I hope that this will not be considered necropolis. I landed here to learn how to throw a SqlCeException from the layout).

If you just need to check the code that handles the exception, a very simple solution could be made:

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

Answer 10

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

Answer 11

It is really old and there are good answers here. I use Moq, and I can’t mock abstract classes and really didn’t want to use reflection, so I made my own exception derived from a DbException. So:

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

obviously, if you need to add InnerException or something else, add extra details, constructors, etc.

then in my test:

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

Awfully this will help anyone who uses Moq. Thanks to everyone who posted here, which led me to my answer.

Answer 12

You can use reflection to create an SqlException object in a 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 }); 

Answer 13

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