SqlDataReader vb.net 保持连接打开
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13563617/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SqlDataReader vb.net keep connection open
提问by babboon
I am using this code to get data:
我正在使用此代码获取数据：
Dim connetionString As String
Dim connection As SqlConnection
Dim sqlq As String
sqlq = "select top 1 * from table1 where smth"
Dim ds As New DataSet
connetionString = "Data Source=db;Initial Catalog=ic;User ID=id;Password=pass"
connection = New SqlConnection(connetionString)
Try
Using connection
Dim command As SqlCommand = New SqlCommand(sqlq, connection)
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.HasRows Then
Do While reader.Read()
x = reader.GetString(5)
Loop
End If
reader.Close()
End Using
Catch ex As Exception
End Try
This type of connection (with different sqlq [query]) I use a lot in diffenrent functions and every time I close the connection. I want to optimize it so it would take less time to get data. How do I do that?
这种类型的连接（使用不同的 sqlq [查询]）我在不同的函数中使用了很多，每次关闭连接时。我想对其进行优化，以便获取数据所需的时间更少。我怎么做？
回答by Tim Schmelter
It is best practise to always dispose/close a connection as soon as you're finished with it. Actually the connection-poolwill not close the underlying physical connection but only flag this connection as reusableif it is closed. So if you don't close a connection it cannot be reused, hence the pool needs to create a new physical connection which is very expensive.
最好的做法是在完成连接后立即处理/关闭连接。实际上，连接池不会关闭底层物理连接，而只会在此连接关闭时将该连接标记为可重用。因此，如果您不关闭连接，它就无法重用，因此池需要创建一个非常昂贵的新物理连接。
So only some minor improvements:
所以只有一些小的改进：
Const sql = "select top 1 * from table1 where smth"
Dim table As New DataTable()
Using con = New SqlConnection("Data Source=db;Init ....")
Using command = New SqlCommand(sql, con)
Using da= New SqlDataAdapter(command)
da.Fill(table)
End Using
End Using
End Using
You should use Usingfor any object implementing IDisposable. You should not use an empty Catchblock.
您应该Using用于任何实现IDisposable. 您不应该使用空Catch块。
回答by Arsen Mkrtchyan
Use connection poolinginstead.
改用连接池。
By default as long as the connection string is the same, connections will be taken from the same connection pool.
默认情况下，只要连接字符串相同，就会从同一个连接池中获取连接。
Shortly connection pool keeps not needed connections open, and return that connections on the next request, so you don't need to think about the time it take to open connection.
很快，连接池保持打开不需要的连接，并在下一个请求时返回该连接，因此您无需考虑打开连接所需的时间。
Keeping connections open for longer than you needed is bad idea, because it costs resources, and in general some servers can accept limited connections.
保持连接打开的时间超过您需要的时间是个坏主意，因为它会消耗资源，而且通常某些服务器可以接受有限的连接。
