.NET、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.

我们有一个使用SQL Server 2008 R2数据库的应用程序。在应用程序中，使用SqlConnection对象调用数据库。

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:

这个SqlConnection对象在第一次被访问时被初始化，然后在整个应用程序中重新使用。我们采取的行动如下:

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.

在挖掘了一点之后，我意识到这是因为在某些情况下，两个不同的线程都试图使用相同的SqlConnection对象。

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?

所以，在发现问题之后，我现在需要找到解决方案。显而易见的解决方案是，每次数据库调用需要一个SqlConnection对象时，都要重新创建这个对象——在这种情况下，它永远不会被共享。有什么理由不这样做吗?我最初假设，由于性能原因，每个应用程序会话只有一个连接对象，但实际上是这样吗?

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?

如果我们确实需要保持一个连接对象处于打开状态，建议的解决方案是什么?我是否应该设置某种计时器，它将一直循环到连接对象可用，然后访问它?

2 个解决方案

#1

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?

显而易见的解决方案是，每次数据库调用需要一个SqlConnection对象时，都要重新创建这个对象——在这种情况下，它永远不会被共享。有什么理由不这样做吗?

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.

相反，这绝对是你应该做的。这就是SqlConnection设计的行为。您应该使用一个Using语句来自动关闭正在使用它的块末尾的连接，连接池机制将自动处理到数据库的实际底层连接。

#2

I see no reason NOT to create a SQL connection every time you need it. In fact, that is probably the best way to do it because it gives the .NET framework the flexibility to manage and reuse connections most efficiently. Wrap each of your SQL connections in a USING so you hang on to them as short a time as possible.

我认为没有理由每次需要时都不创建SQL连接。事实上，这可能是最好的方法，因为它为. net框架提供了最有效地管理和重用连接的灵活性。将每个SQL连接封装在一个use中，以便尽可能短地挂起它们。

We've created a method that creates a connection and everyone uses that:

我们已经创建了一个创建连接的方法，每个人都使用它:

using (var conn = GetConnection())
    using (var proc = GetProcedure(conn, "procname"))
        using (var reader = proc.GetReader())
        {
            ... DB stuff
        }