does a code behind datasource and connection need to be closed after use?

Question

does a code behind datasource and/or a connection need to be closed after databind() ? my code is vb.net

    Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DefaultDataBaseServer").ConnectionString)
    conn.Open()
    Dim sql As String = lblConcept.Text.Replace("@xxxxxx", xxxxxID.Value)
    Dim myCommand As SqlCommand = New SqlCommand(sql, conn)
    ckConceptList.DataSource = myCommand.ExecuteReader()
    ckConceptList.DataBind()

score 3 · Answer 1 · edited May 23 '17 at 10:30

does a code behind datasource and/or a connection need to be closed after databind()

The terms "code behind" and DataBind() hint that this is related to some web technology, but there arent any such tags.

In general however, anything which has a Dispose() method indicates that it likely allocates some resource under the hood which should not only be closed, but Disposed as well to release those resources.

Graphics objects like pens, brushes and Bitmaps which are not disposed can (will!) eventually raise the notorious "Generic Error in GDI+" exception. But DB objects such as Connections and Command objects left undisposed can cause your app to leak. This question was creating new DBCommand objects in a For Loop without disposing of them and encountered a System resources exceeded exception.

NET Using blocks (using() in C#) make it simple to close and dispose of these things. The following will also show how to use Parameters rather than String.Replace() on the SQL:

Dim SQL = "SELECT a, b, c FROM SomeTable WHERE Foo = @name"
Dim dt As New DataTable
Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(sql, dbcon)

        cmd.Parameters.Add("@name", MySqlDbType.Text).Value = lblConcept.Text

        dbcon.Open()
        dt.Load(cmd.ExecuteReader())
        dgv2.DataSource = dt
    End Using         ' dispose of cmd
End Using             ' dispose of dbcon

String.Replace wont protect against even simple things like embedded ticks as in Carol's Cookie Shoppe
Parameters.Add() allows you to specify the data type and avoid data type errors when you leave it to VB to guess

Using declares (Dim) a new variable and initializes it, so there cannot be other dbCon or cmd variables in the same scope. It also creates a new Block Scope: Those target variables and anything declared inside it, will be local to that block. The above declares the datatable outside the block so it can be used elsewhere. You can stack or combine Usings to reduce indentation like so:

Using dbcon As New MySqlConnection(MySQLConnStr),
    cmd As New MySqlCommand(sql, dbcon)

    dbcon.Open()
    dt.Load(cmd.ExecuteReader())
    dgv2.DataSource = dt

End Using

The VB editor is a little quirky about that - intellisense wont help with the dbcon variable parameter for cmd until you finish and close the block.

More information:

Connection Pooling describes how NET minimizes the cost of creating DBConnections.

Using Statement (Visual Basic). From which, quote:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

IDisposable is (usually) the way the Dispose method is implemented. The Remarks are well worth reading.

Avoiding Problems with the Using Statement explains the whys and wherefores of combining a Try/Catch block with a Using statement. There are also plenty of posts here describing how to use the two in combination in various situations.

How do I create a parameterized SQL query? Why Should I?

score 1 · Answer 2 · answered May 12 '16 at 19:37

You should use a try catch and finally.

 try 
 Dim conn As SqlConnection = New SqlConnection   (System.Configuration.ConfigurationManager.ConnectionStrings("DefaultDataBaseServer").ConnectionString)
conn.Open()
Dim sql As String = lblConcept.Text.Replace("@xxxxxx", xxxxxID.Value)
Dim myCommand As SqlCommand = New SqlCommand(sql, conn)
ckConceptList.DataSource = myCommand.ExecuteReader()
ckConceptList.DataBind()


Catch ex As Exception

 Finally
        If pConn.State = ConnectionState.Open Then
            pConn.Close()
        End If
    End Try

does a code behind datasource and connection need to be closed after use?

2 Answers2