Managing SqlConnection

Question

User368742804 posted

I use the variable name cnn for my Sql connection object in my web app.

I use cnn.Open() and cnn.Close() to open and close SqlConnections.

Are there any drawbacks to using

If cnn.State <> connectionState.Open Then cnn.Open()

instead of "cnn.Open()"?

Likewise, are there any drawbacks to using

If cnn.State = connectionState.Open Then cnn.Close()

instead of "cnn.Close()"?

Thanks!

Answer 1

User2103319870 posted

IMHO I don't think there is anything wrong in checking the state prior to open the connection. However you need to ensure that your connection is always opened prior to executing the command.

mmmtbig

If cnn.State = connectionState.Open Then cnn.Close()

Instead of closing connection like given above you can use the Using statement which will ensure that connection object will be automatically disposed.

'Get your connection string from config file
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Connection").ConnectionString.ToString()
        'Create Connection object here
        Using cnn As New SqlConnection(strConn)

            If cnn.State <> ConnectionState.Open Then
                'Open your connection
                cnn.Open()
            End If
            'Your Select Query
            Dim selectString As String = "SELECT COUNT(*) FROM YourTable"

            'Create COmmand object
            Dim myCommand As New SqlCommand(selectString, cnn)

            ' Get the Result query
            myCommand.ExecuteScalar()

            ' You can do your task here
        End Using 'You dont need to close your connection explicity it will be closed here

By making use of Using keyword You can automatically dispose objects that implement the IDisposable interface by using the  using statement.when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way.The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object.

Answer 2

User753101303 posted

Hi,

More generally you likely see this when you are unsure about the connection state ie. the same connection is used and reused.

Another option is to use short lived connection and so you don't care about this as the connection is opened/closed as needed. The connection pool does provide basically the same efficiency than above but it might be less error prone.

If you don't have a strong reason to do this I would suggest to open/close the connection for the shortest possible duration.

See for example http://stackoverflow.com/questions/9807268/is-it-best-to-pass-an-open-sqlconnection-as-a-parameter-or-call-a-new-one-in-ea

Answer 3

User1918509225 posted

Hi mmmtbig,

As far as I know ,there is no drawback to using the code below:

If cnn.State <> connectionState.Open Then cnn.Open()

In fact ,It 's a better way to open and close the database connection.

it will first check the connection status before open or close it.

For example ,if your connection didn't open in your code .and you call cnn.Close(),

it will throw exception for you.

Best Regards,

Kevin Shen.