locked
Managing SqlConnection RRS feed

  • 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!

    Monday, October 27, 2014 10:37 AM

Answers

  • 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. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 28, 2014 4:04 AM

All replies

  • 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.

    Monday, October 27, 2014 11:12 AM
  • 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

    Monday, October 27, 2014 11:17 AM
  • 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. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 28, 2014 4:04 AM