.NET，SqlConnection对象和多线程 [英] .NET, the SqlConnection object, and multi-threading

问题描述

我们拥有其使用的SQL Server 2008 R2数据库的应用程序。在应用程序，调用使用的SqlConnection 的对象是国产数据库。

本的SqlConnection 对象初始化一次，第一次访问，然后再用于整个应用程序。我们使用的操作如下：

 保护_cn作为的SqlConnection =无

...

保护小组的open（）
    如果_cn是没有那么
        _cn =新的SqlConnection（_sqlConn）
    结束如果

    如果_cn.State = ConnectionState.Closed OrElse运算_cn.State = ConnectionState.Broken然后
        _cn.Open（）
    结束如果
结束小组
 

这工作程序的正常执行过程中完全没有问题。但是，也有被以多线程方式执行的应用程序的一些部分。如果其他操作是由当这些部件之一正在执行，频繁发生错误。

挖了一下后，我意识到，这是因为有段时间在两个不同的线程既试图使用相同的的SqlConnection 对象。

因此​​，确定问题后，我现在需要找到解决办法。最明显的解决方法就是每次数据库调用都需要一个重新创建的SqlConnection 的对象 - 在这种情况下，它永远不会被共享。是否有任何理由的没有的做到这一点？我认为本来我们有每个应用程序的性能的原因，会话中只有一个连接对象，但是这是真正的情况？

如果我们确实需要保持只有一个连接对象公开，有什么建​​议的解决方案？我应建立某种形式的计时器将继续循环，直到连接对象是可用的，然后访问它？

解决方案

  

显而易见的解决方案是只每次一个数据库呼叫需要一个重新创建SqlConnection对象 - 在这种情况下，它就不会被共享。有什么理由不这样做呢？

相反，这是的绝对的你应该做的。这是行为的SqlConnection 是设计的进行。您应该使用使用语句自动关闭你正在使用它的块结束的连接，连接池机制会自动处理潜在的真实连接数据库。

We have an application which uses an SQL Server 2008 R2 database. Within the application, calls to the database are made using a SqlConnection object.

This SqlConnection object is initialized once, the first time it is accessed, and then re-used throughout the application. The action that we use is the following:

Protected _cn As SqlConnection = Nothing

...

Protected Sub Open()
    If _cn Is Nothing Then
        _cn = New SqlConnection(_sqlConn)
    End If

    If _cn.State = ConnectionState.Closed OrElse _cn.State = ConnectionState.Broken Then
        _cn.Open()
    End If
End Sub

This works perfectly fine during normal execution of the program. However, there are a few portions of the application that are executed in a multi-threaded fashion. When one of these parts is executing, frequent errors occur if other actions are made.

After digging a bit, I realised that this is because there were times where two different threads both attempted to use the same SqlConnection object.

So, after identifying the problem, I now need to find solutions. The obvious solution is to just re-create the SqlConnection object every time a database call requires one - in this case, it would never be shared. Is there any reason not to do this? I assumed originally that we had only one connection object per session of the application for performance reasons, but is this actually the case?

If we do need to keep just one connection object open, what is the suggested solution? Should I put in place some sort of timer which will keep cycling until the connection object is available, and then access it?

解决方案

The obvious solution is to just re-create the SqlConnection object every time a database call requires one - in this case, it would never be shared. Is there any reason not to do this?

On the contrary, that's absolutely what you should do. That's the behaviour SqlConnection was designed for. You should use a Using statement to automatically close the connection at the end of the block you're using it for, and the connection pool mechanism will automatically handle the real underlying connections to the database.

这篇关于.NET，SqlConnection对象和多线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！