.net 在需要进行模拟和单元测试时如何抛出 SqlException？

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

提问by chobo2

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

我正在尝试测试我的项目中的一些异常，我捕获的异常之一是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).

似乎你不能去，new SqlException()所以我不确定如何抛出异常，尤其是在不以某种方式调用数据库的情况下（并且由于这些是单元测试，因此通常建议不要调用数据库，因为它很慢）。

I am using NUnit and Moq, but I am not sure how to fake this.

我正在使用 NUnit 和 Moq，但我不知道如何伪造它。

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.

回应一些似乎都基于 ADO.NET 的答案，请注意我使用的是 Linq to Sql。所以那些东西就像在幕后。

More info as requested by @MattHamilton:

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

采纳答案by Dale Ragan

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.

由于您使用的是 Linq to Sql，这里是使用 NUnit 和 Moq 测试您提到的场景的示例。我不知道您的 DataContext 的确切细节以及您在其中可用的内容。根据您的需要进行编辑。

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.

您需要使用自定义类包装 DataContext，您不能使用 Moq 模拟 DataContext。您也不能模拟 SqlException，因为它是密封的。你需要用你自己的 Exception 类来包装它。完成这两件事并不难。

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:

让我们实现测试，首先让我们使用存储库模式包装我们的 Linq to Sql 调用：

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 poston the subject, mine differs a little bit:

接下来像这样创建 IDataContextWrapper，您可以查看有关该主题的这篇博客文章，我的有点不同：

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

Next create the CustomSqlException class:

接下来创建 CustomSqlException 类：

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

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

Here's a sample implementation of the IDataContextWrapper:

这是 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
}

回答by Sam Saffron

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.

这还允许您控制 SqlException 的数量，这可能很重要。

回答by Dylan Beattie

I have a solution to this. I'm not sure whether it's genius or madness.

我有一个解决方案。我不确定这是天才还是疯狂。

The following code will create a new SqlException:

以下代码将创建一个新的 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)

然后你可以像这样使用（这个例子使用的是 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.

以便您可以在存储库、处理程序和控制器中测试 SqlException 错误处理。

Now I need to go and lie down.

现在我需要去躺下。

回答by default.kramer

Depending on the situation, I usually prefer GetUninitializedObjectto 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.

根据情况，我通常更喜欢GetUninitializedObject来调用 ConstructorInfo。您只需要注意它不会调用构造函数 - 来自 MSDN 备注：“因为对象的新实例被初始化为零并且没有运行构造函数，该对象可能不代表被视为有效的状态被那个物体。” 但我想说它不如依赖某个构造函数的存在那么脆弱。

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

回答by Matt Hamilton

EditOuch: I didn't realise SqlException is sealed. I've been mocking DbException, which is an abstract class.

编辑哎哟：我没有意识到 SqlException 是密封的。我一直在嘲笑 DbException，它是一个抽象类。

You can't create a new SqlException, but you can mock a DbException, which SqlException derives from. Try this:

您不能创建新的 SqlException，但可以模拟 SqlException 派生自的 DbException。尝试这个：

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 Messageproperty on the mocked exception then don't forget to Expect (or Setup, depending on your version of Moq) the "get" on those properties.

如果您希望读取Message模拟异常上的属性以外的任何内容，那么不要忘记对这些属性进行“获取”（或设置，取决于您的 Moq 版本）。

回答by David

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

位于：http: //smartypeeps.blogspot.com/2006/06/how-to-throw-sqlexception-in-c.html

回答by MusiGenesis

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.

Hacks-R-Us 带给您的代码。

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

更新：不，第二种方法抛出一个 ArgumentException，而不是一个 SqlException。

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

更新 2：这工作得更快（SqlException 在不到一秒的时间内被抛出）：

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

回答by FrenchData

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)

我注意到您的问题已有一年历史，但为了记录，我想添加我最近使用 microsoft Moles 发现的解决方案（您可以在此处找到参考资料Microsoft Moles）

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

一旦你使用了 System.Data 命名空间，你可以简单地模拟 SqlConnection.Open() 上的 SQL 异常，如下所示：

//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.

我希望这可以帮助将来遇到这个问题的人。

回答by khebbie

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

回答by Grokodile

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

（Sry 它晚了 6 个月，希望这不会被视为 necroposting 我登陆这里寻找如何从模拟中抛出 SqlCeException ）。

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