CODEWITHSUNDEEP
.netsqlvb.netconnection
B1GB0Y
B1GB0Y

Reputation: 43

ASP.NET (VB) - Close an opened SQL connection inside Function

Can anyone tell me how I close an opened SQL connection inside a Function?

I call a Select Function like this:

   Function Selec(ByVal SQLStr As String) As SqlDataReader

        Dim SQLConn As New SqlConnection()
        Dim SQLCmd As New SqlCommand()

        SQLConn.ConnectionString = Session("bd")
        SQLConn.Open()

        SQLCmd.Connection = SQLConn
        SQLCmd.CommandText = SQLStr

        Selec = SQLCmd.ExecuteReader

    End Function

And in another page I do a While method to retrieve me the data like this:

(Note: BDcon.BD is the name of the Class that have Functions)

    Dim write as New BDcon.BD

    Dim menu As SqlDataReader = writeBD.Selec("SELECT something from Table")

While menu.Read

    'Do something

End While

    menu.Close 'This close just the DataReader and not the SqlConnection

Finally I want to Close my SQL Connection by Function like this:

    Function Close() As SqlConnection

        Dim SQLConn As New SqlConnection()
        SQLConn.ConnectionString = Session("bd")

        SQLConn.Close()

    End Function

I think that the problem is on the Close() Function, I want to close the connection but I don't know how to call my Opened Conneciton.

Upvotes: 0

Views: 2842

Answers (5)

B1GB0Y
B1GB0Y

Reputation: 43

I've solved my problem with Pranay Rana reply.

Added CommandBehavior.CloseConnection to my code.

Final code of Selec Function:

   Function Selec(ByVal SQLStr As String) As SqlDataReader

        Dim SQLConn As New SqlConnection()
        Dim SQLCmd As New SqlCommand()

        SQLConn.ConnectionString = Session("bd")
        SQLConn.Open()

        SQLCmd.Connection = SQLConn
        SQLCmd.CommandText = SQLStr

        Selec = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection)

    End Function

THANK YOU ALL AGAIN! :D

Upvotes: 0

Hobbes
Hobbes

Reputation: 147

In your original code, you can't close the original connection because the scope of the connection object is just to the function that created it (it's only accessible from inside the function where you created it). I agree with Marco's answer, you should return a DataTable rather than a SQLDataReader. This is a slightly different approach that doesn't require a SQLDataAdapter:

Function Selec(ByVal SQLStr As String) As DataTable
    Dim SQLConn As New SqlConnection(Session("bd"))
    Dim SQLCmd As New SqlCommand(SQLStr, SQLConn)
    Dim SQLDt As New DataTable()
    SQLConn.Open()
    SQLDt.Load(SQLCmd.ExecuteReader)
    'Close the connection as soon as it is no longer needed
    SQLConn.Close() 
    Return SQLDt
End Function

For using the returned table, you could walk through the collection of DataRows this way:

Sub DoSomthing()
    Dim menu As DataTable = Selec("SELECT * FROM SomeTable")
    For Each row As DataRow In menu.Rows
        ' do something
    Next
End Sub

An alternative way would be to create a DataTableReader over the returned DataTable, although I don't see any advantage to doing so other than it is more similar to your original code:

Sub AlternateDoSomething()
    Dim dt As DataTable = Selec("SELECT * FROM SomeTable")
    Dim menu As DataTableReader = dt.CreateDataReader
    While menu.Read
        'Do something
    End While
    menu.Close()
End Sub

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176886

i think its better to go for using consruct will do the task for you... 

using cn as new system.data.sqlclient.sqlconnection()
    cn.open
    '{do a bunch of other stuff with commands and datareaders here}
    cn.close 
end using

or

you can also make use of CommandBehavior Enumeration

Read for this : ExecuteReader with CommanBehavior ( automatically close connection after reading data)

CloseConnection - When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

Upvotes: 1

Tarun Tak
Tarun Tak

Reputation: 411

I think you should pass the object of SqlConnection class as argument in the Close function which you have created earlier. And then use that object in that function.

Upvotes: 0

Marco
Marco

Reputation: 57573

In my opinion you should change your function to return a DataTable and close connection immediately.
An example could be

Function Select(ByVal SQLStr As String) As DataTable
    Dim SQLConn As New SqlConnection(Session("bd"))
    Dim SQLCmd As New SqlCommand(SQLStr, SQLConn)
    Dim SQLAdapt As New SqlDataAdapter(SQLCmd)
    Dim SQLDt As New DataTable()
    SQLAdapt.Fill(SQLDt)
    SQLConn.Close()
    Return SQLDt
End Function

Or you could have a function GetConnection creating and returning a SQLConnection, pass this to Select function (without creating a new one inside) and then closing connection manually.

Upvotes: 0

Related Questions