SQL Server和TransactionScope的（与MSDTC）：偶尔不能连接 [英] SQL Server and TransactionScope (with MSDTC): Sporadically can't get connection

问题描述

我已经写了一些测试.NET code调用打到我的SQL Server。看来，使用 System.Transactions的是回滚任何修改导致该数据库的最佳选择。我知道，有些纯粹主义者会认为，我可能要嘲笑的数据库，但我不打算在这条道路;这不是严格的纯单元测试。

在我编写和运行几个测试，这完全按预期工作。我只是把code初始化和中止.NET交易的测试设置和测试方法拆卸。这似乎是一个很好的解决方案。

不过，我的问题是，当我尝试运行这些测试的100，很多人会抛出异常运行一个接一个，当他们无法连接，即使它们通过SQL Server中。为了使事情更糟的是，该表在我的数据库偶尔被锁定有时当我运行我的测试。我得问一下我的DBA手动删除锁。

正如你们许多人都知道，使用的TransactionScope在一个开发工作站上运行（如在运行这个测试）对SQL服务器将使用.NET Framework中MSDTC code。

下面是一个code样本来说明我在做什么：

 ＆LT; TestInitialize（）＆GT;公用Sub MyTestInitialize（）
    _scope =新System.Transactions.TransactionScope（_
        System.Transactions.TransactionScopeOption.Required，新时间跨度（0,2，0））
结束小组
 

 ＆LT; TestCleanup（）＆GT;公用Sub MyTestCleanup（）
    _scope.Dispose（）
结束小组

＆其中; TestMethod的（）＆GT;公用Sub CurrentProgramUser_Get_UserID（）
    昏暗ProgramSessionId作为字符串
    昏暗CurrentProgramUserId作为整数
    昏暗dSession作为ProgramSession
    CurrentDCMAUserId = Convert.ToInt32（_
    SqlHelper.ExecuteScalar（testDcmaConnString，System.Data.CommandType.Text，_
    INSERT INTO Program_Users（用户名，名字，姓氏）和放大器; _
    VALUES（'GuitarPlayer'，'鲍勃'，'马利'）＆放大器; _
    SELECT IDENT_CURRENT（'Program_users'））_
                         ）
    ProgramSessionId = session.getCurrentSession（）
    session.WriteUserParam（计划，ProgramSessionId，USERID，CurrentProgramUserId.ToString（），testSource，testConnString）

    昏暗readValue作为整数
    readValue = session.User.UserID

    Assert.AreEqual（CurrentProgramUserId，readValue）
结束小组
 

正如你所看到的，没有什么特别花哨的位置。我只是有一个测试方法，那将写一些东西到我的数据库，我想我的方法去寻找。这仅是一个示例;还有很多其他的测试喜欢它。

我的测试的逻辑似乎是合理的。什么引起我的测试，不仅失败了，但偶尔锁定用户从表中？

解决方案

我发现这个问题。其中一个方法，我在测试使用了 SqlDataReader的对象。显然，关闭（）必须调用方法之前， SqlDataReader的超出范围，以释放连接，但对我来说，我没有这样做。

简单地增加 mySqlDataReader.Close（）被测方法的末尾解决了这一问题。此外，该测试方法测试这个特定的方法是数据驱动的超过100个测试用例，这样解释我怎么能用完的连接。

I've written some tests for .net code that invokes calls to my SQL Server. It appears that using System.Transactions is an excellent choice for rolling back any modifications to the database that result. I'm aware that some purists would suggest that I might want to mock the database, but I'm not going down that road; this is not strictly pure unit-testing.

When I write and run several tests, this works exactly as expected. I simply put the code to initialize and abort .net transactions in the test setup and test teardown methods. It seemed like a great solution.

However, the problem I have is that when I try to run 100 of these tests, many of them will throw exception that they could not connect to the SQL Server even though they pass when run one-by-one. To make matters even worse, the tables in my database sporadically get locked sometimes when I run my tests. I have to ask my DBA to manually remove the locks.

As many of you will know, using TransactionScope on code that runs on a development workstation (as in running this test) against a SQL server will make the .net framework use MSDTC.

Here's a code sample to illustrate what I'm doing:

 <TestInitialize()> Public Sub MyTestInitialize()
    _scope = New System.Transactions.TransactionScope( _
        System.Transactions.TransactionScopeOption.Required, New TimeSpan(0, 2, 0))
End Sub

<TestCleanup()> Public Sub MyTestCleanup()
    _scope.Dispose()
End Sub

<TestMethod()> Public Sub CurrentProgramUser_Get_UserID()
    Dim ProgramSessionId As String
    Dim CurrentProgramUserId As Integer
    Dim dSession As ProgramSession
    CurrentDCMAUserId = Convert.ToInt32( _
    SqlHelper.ExecuteScalar(testDcmaConnString, System.Data.CommandType.Text, _
    "INSERT into Program_Users(UserName,FirstName,LastName) " & _
    "VALUES('GuitarPlayer','Bob','Marley')" & _
    "SELECT IDENT_CURRENT('Program_users') ") _
                         )
    ProgramSessionId = session.getCurrentSession()
    session.WriteUserParam("Program", ProgramSessionId, "USERID", CurrentProgramUserId.ToString(), testSource, testConnString)

    Dim readValue As Integer
    readValue = session.User.UserID

    Assert.AreEqual(CurrentProgramUserId, readValue)
End Sub

As you can see, there's nothing particularly fancy here. I just have a test method that's going to write some stuff to my database that I want my method to find. This is only one example; there are many other tests like it.

The logic of my tests appears to be sound. What could be causing my tests to not only fail, but sporadically lock users out of tables?

解决方案

I found the problem. One of the methods I was testing uses a SqlDataReader object. Apparently, the Close() method must be called before the SqlDataReader goes out of scope in order to release the connection, but in my case, I failed to do so.

Simply adding mySqlDataReader.Close() to the end of the method under test fixed the problem. Also, the test method to test this particular method was data-driven with over 100 test cases, so that explains how I could have run out of connections.

这篇关于SQL Server和TransactionScope的（与MSDTC）：偶尔不能连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！