Close connection after calling ExcuteScalar

Question

i have the following function, the problem is since im using ExecuteScalar, the connection never closes when using any other function again...

Public Function Valor(tabla As String, campos As String, condicion As String)
        cn.Open()
        Dim sql As String = "SELECT " & campos & " FROM " & tabla & " WHERE " & condicion
        comando = New SqlCommand(sql, cn)
        Return comando.ExecuteScalar
     If cn.State = ConnectionState.Open Then
            cn.Close()
        End If

    End Function

This Function returns me a Time value from SQL time(7) to TIMESPAN on the app, i am able to get the value but Since Return skips anything after it, connection is not closed. ANy idea how to close the connection this? or there is another method on how can i get the value of my query. Thanks in advance

Joel Coehoorn · Answer 1 · 2015-01-23T23:55:02.837

First of all, connections in .Net work best when you create an entirely new object for each query. Don't try to re-use the same connection all the time.

Second, that code could still leak connections even if you had closed the connection before returning, because it would never reach .Close() function call if an exception is thrown.

Finally, that code is horribly vulnerable to sql injection. It's practically begging to get hacked.

Here is code that solves all three problems:

Public Function Valor(ByVal sql As String, ByVal ParamArray condicion() As SqlParameter)
    'cnString is a made-up string variable for the connection string that you will create in the same place (and instead of) that you currently have cn
    Using cn As New SqlConnection(cnString), _ 
          cmd As New SqlCommand(sql, cn)

        If condicion IsNot Nothing Then cmd.Parameters.AddRange(condicion)
        cn.Open()

        Return cmd.ExecuteScalar()
    End Using
End Function

score 1 · Answer 2 · answered Jan 23 '15 at 23:37

Instead of returning immediately, store the result in a variable, clean up everything, then return the cached variable:

Public Function Valor(tabla As String, campos As String, condicion As String)
    cn.Open()
    Dim sql As String = "SELECT " & campos & " FROM " & tabla & " WHERE " & condicion
    comando = New SqlCommand(sql, cn)
    Dim retorno As Object = comando.ExecuteScalar()
    If cn.State = ConnectionState.Open Then
        cn.Close()
    End If
    Return retorno
End Function

Close connection after calling ExcuteScalar

2 Answers2