ado.net 管理连接
我正在使用选定 SQL 实例上的数据库列表填充列表视图，然后从每个数据库检索一个值（这是内部产品版本，列并不总是存在）我调用下面的函数来填充第二列listview:
item.SubItems.Add(DBVersionCheck(serverName, database.Name))
Function DBVersionCheck(ByVal SelectedInstance As String, ByVal SelectedDatabase As String)
Dim m_Connection As New SqlConnection("Server=" + SelectedInstance + ";User Id=sa;Password=password;Database=" + SelectedDatabase)
Dim db_command As New SqlCommand("select Setting from SystemSettings where [Setting] = 'version'", m_Connection)
Try
m_Connection.Open()
Return db_command.ExecuteScalar().trim
m_Connection.Dispose()
Catch ex As Exception
'MessageBox.Show(ex.Message)
Return "NA"
Finally
m_Connection.Dispose()
End Try
End Function
这工作正常，除了它创建到每个数据库的连接并使其保持打开状态。 我的理解是 close()\dispose() 仅释放 ado 中池中的连接，而不是实际的 sql 连接。
检索到该值后如何关闭实际连接？ 将这些保持打开状态将创建数百个与数据库的连接，这些连接可能不会用于该会话。
I'm populating a listview with a list of databases on a selected SQL instance, then retrieving a value from each database (It's internal product version, column doesn't always exist) I'm calling the below function to populate the second column of the listview:
item.SubItems.Add(DBVersionCheck(serverName, database.Name))
Function DBVersionCheck(ByVal SelectedInstance As String, ByVal SelectedDatabase As String)
Dim m_Connection As New SqlConnection("Server=" + SelectedInstance + ";User Id=sa;Password=password;Database=" + SelectedDatabase)
Dim db_command As New SqlCommand("select Setting from SystemSettings where [Setting] = 'version'", m_Connection)
Try
m_Connection.Open()
Return db_command.ExecuteScalar().trim
m_Connection.Dispose()
Catch ex As Exception
'MessageBox.Show(ex.Message)
Return "NA"
Finally
m_Connection.Dispose()
End Try
End Function
This works fine except it's creating a connection to each database and leaving it open.
My understanding is the close()\dispose() releases only the connection from the pool in ado rather than the actual connection to sql.
How would I close the actual connections after I've retrieved the value?
Leaving these open will create hundreds of connections to databases that will probably not be used for that session.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将
Pooling=false添加到您的连接字符串。那应该解决它。Add
Pooling=falseto your connection string. That should take care of it.您可以使用两种方法：
1 - 调用
ClearAllPools或ClearPool方法。您可能更喜欢这样做，这样您就可以在应用程序中使用池，但完成后清除池。2 - 调整连接字符串以不池化连接。 转到此处并搜索“ ConnectionString 中的连接池值”了解更多信息。
Two approaches you can use:
1 - Call the
ClearAllPoolsorClearPoolmethod. You may prefer this so that you can make use of pooling with your application, but then clear the pools when you are done.2 - Adjust your connection string to not pool the connection. Go here and search for "connection pooling values within the ConnectionString" for more info.