CODEWITHSUNDEEP
sqlvb.netsqlconnection
babboon
babboon

Reputation: 793

SqlDataReader vb.net keep connection open

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?

Upvotes: 2

Views: 7664

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460108

It is best practise to always dispose/close a connection as soon as you're finished with it. Actually the connection-pool will not close the underlying physical connection but only flag this connection as reusable if 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 Using for any object implementing IDisposable. You should not use an empty Catch block.

Upvotes: 5

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50712

Use connection pooling instead.

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.

Upvotes: 3

Related Questions