views:

2125

answers:

7

Hi

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

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

So I am using nunit and moq so I am not sure how to fake this.

Edit

Based on the answers they seem to all be based on ado.net I am using linq to sql. So that stuff is like behind the scenes.

Edit @ Matt Hamilton

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

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();
MusiGenesis
This was my own implementation before I came across this SU page looking for another way because the timeout was unacceptable. Your Update 2 is good but it's still one second. Not good for unit test sets as it doesn't scale.
stimpy77
A: 

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

David
I tried this, but you still need an open SqlConnection object in order to get a SqlException thrown.
MusiGenesis
I use linq to sql so I don't do this ado.net stuff. Its all behind the scenes.
chobo2
+7  A: 

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.

Matt Hamilton
you should add expectations for "Number" which allow you to figure out what type of exception is is (deadlock, timeout etc)
Sam Saffron
Hmm how about when your using linq to sql? I don't actually do an open(its done for me).
chobo2
If you're using Moq then presumably you're mocking some sort of database operation. Set it up to be thrown when that happens.
Matt Hamilton
So on the actual operation(the actual method that would call on the db)?
chobo2
Are you mocking your db behaviour? Like, mocking your DataContext class or something? Whatever operation would throw this exception if the database operation returned an error.
Matt Hamilton
Hmm I tried this and it won't let me even mockup this. I get a error saying it can't be mocked up. See original post.
chobo2
+1  A: 

You can do this with reflection, you will have to maintain it when Microsoft make changes, but it does work I just tested it:

        static T Construct<T>(params object[] p) { 
            return (T)typeof(T).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance)[0].Invoke(p);
        }

        static void Main(string[] args) {

            SqlErrorCollection collection = Construct<SqlErrorCollection>();
            SqlError error = Construct<SqlError>(1, (byte)2, (byte)3, "server name", "error message", "proc", 100);

            typeof(SqlErrorCollection)
                .GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance)
                .Invoke(collection, new object[] { error });


            var e = typeof(SqlException)
                .GetMethod("CreateException", BindingFlags.NonPublic | BindingFlags.Static)
                .Invoke(null, new object[] { collection, "7.0.0" } ) as SqlException;

            throw e;
        }
Sam Saffron
Matts does not work.
chobo2
Well this does work :p
Sam Saffron
+2  A: 

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
}
Dale Ragan
A: 

(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());
panamack
A: 

Hello,

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.

FrenchData