Solved

Closing sqlconnection objects in asp.net / .net 4.0

Posted on 2011-03-23
8
494 Views
Last Modified: 2012-05-11
In an asp.net application written using .Net Framework 4.0, I have a class cData which handles database operations. My connections to the database are not closing as intended (code attached). I'd be grateful for any help.
Thanks,
Louise
'Functions in my class cData which invoke, open and use instances of the sqlconnection object:

 Public Shared Function GetDataReader(ByVal db As String, ByVal sp As String, ByVal ParamArray params() As SqlParameter) As SqlDataReader

        Dim cmd As New SqlCommand

        Try

            With cmd
                .Connection = GetConnection(db)
                .Connection.Open()
                .CommandText = sp
                .CommandType = CommandType.StoredProcedure
            End With

            Dim p As SqlParameter

            For Each p In params
                p = cmd.Parameters.Add(p)
                p.Direction = Data.ParameterDirection.Input
            Next

            Dim r As SqlDataReader = cmd.ExecuteReader
            Return r

            cmd.Connection.Close()
            cmd.Dispose()

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Shared Function GetDataSet(ByVal db As String, ByVal sp As String) As DataSet


        Dim cmd As New SqlCommand

        Try

            With cmd
                .Connection = GetConnection(db)
                .Connection.Open()
                .CommandText = sp
                .CommandType = CommandType.StoredProcedure
            End With

            Dim da As New SqlDataAdapter
            da.SelectCommand = cmd
            Dim ds As New DataSet
            da.Fill(ds)

            Return ds

            cmd.Connection.Close()
            cmd.Dispose()

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Shared Function GetDataTable(ByVal db As String, ByVal sp As String) As DataTable


        Dim cmd As New SqlCommand

        Try

            With cmd
                .Connection = GetConnection(db)
                .Connection.Open()
                .CommandText = sp
                .CommandType = CommandType.StoredProcedure
            End With

            Dim r As SqlDataReader = cmd.ExecuteReader
            Dim t As New DataTable
            t.Load(r)

            Return t

            cmd.Connection.Close()
            cmd.Dispose()

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

Open in new window

' Example of code in the code behind for an .aspx page which calls one of these functions in cData:

Try

        Dim r As SqlDataReader = cData.GetDataReader(cData.db1, "my_stored_procedure_name", New SqlParameter("@myParamName", myParamValue))

        If r.Read Then
' do stuff
            End If

        Catch ex As Exception
          Response.Write(ex.ToString)
        End Try

    End Sub

Open in new window

Public Shared Function GetConnection(ByVal db As String) As SqlConnection

        Dim config As System.Configuration.Configuration
        config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MyApplication")

        Dim conn As New SqlConnection


        If db = dbMain Then conn.ConnectionString = config.ConnectionStrings.ConnectionStrings("cnMain").ToString

        If db = dbAlt Then conn.ConnectionString = config.ConnectionStrings.ConnectionStrings("cnAlternative").ToString

        GetConnection = conn

    End Function

Open in new window

0
Comment
Question by:louise001
  • 3
  • 3
  • 2
8 Comments
 
LVL 16

Accepted Solution

by:
ToddBeaulieu earned 250 total points
ID: 35198567
Is the problem that you're not disposing of the IDisposable objects? I don't see you disposing the connection, for instance.

In c# we use a using() {} construct which greatly helps with these issues. I don't know if there is an equivellent in VB.

Also, you're not dealing with exceptions adequately. If an exception occurs, you completely bypass your closing/disposing logic. In that case, you should include a Finally{} block in which you handle all closing/disposing (instead of above). You would need to check each object to see if it's open before calling Close() on it.
0
 
LVL 18

Expert Comment

by:Richard Lee
ID: 35198762
Try using the "using" keyword when dealing with classes that implement the IDisposable interface. Here is the perfect example for you scenario:

http://www.pluralsight-training.net/community/blogs/fritz/archive/2005/04/28/7834.aspx

DaTribe
0
 

Author Comment

by:louise001
ID: 35198801
Thanks. There is a using keyword in VB which is documented at http://msdn.microsoft.com/en-us/library/htd05whh.aspx . And guess what, the documentation states that:
"Sometimes your code requires an unmanaged resource, such as ... 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."
With regards to what happens in a finally clause, I found that if I closed or disposed in finally, there would be an error returned by the calling code to the effect that the SqlDataReader in question was closed and so could not be read.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 18

Assisted Solution

by:Richard Lee
Richard Lee earned 250 total points
ID: 35198863
Why not use the "using" keyword as demonstrated by the example in the link I provided.

http://www.pluralsight-training.net/community/blogs/fritz/archive/2005/04/28/7834.aspx

Using conn As New SqlConnection(dsn)
  Using cmd As New SqlCommand("SELECT * FROM Employees", conn)
    conn.Open()
      Using rdr As SqlDataReader = cmd.ExecuteReader()
        While rdr.Read()
          Console.WriteLine(rdr(0))
        End While
      End Using
  End Using
End Using

Open in new window


If you take this approach then you will never need to use the finally block to close the connection. The connection will always be closed whether there is an exception or not.

DaTribe
0
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 35199722
Well, if you want to leave the reader open, of course, you can't close that, nor include a using() for it. In that case, you really would want a finally(), most likely.

Again, in that finally, be sure not to try to close anything that's not open. Only dispose of them.

In the calling code, be sure to either watch for an exception (if you re-throw it here), or have it simply check the object for null and that it's open before operating on it.
0
 
LVL 18

Expert Comment

by:Richard Lee
ID: 35200032
Leaving a database connection open is a code smell that shows your design is not correct. The approach needed is simple open the connection retrieve the data required and then pass that data around rather than an open reader.

Connections should be close sooner rather than later enabling scale and avoiding the problems you are having right now.

DaTribe
0
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 35201387
I've been working outside ado.net for the past two years so I'm rusty. I thought the reader didn't need the connection, but if I'm reading this correctly, that it actually works with the cn while fetching, then DaTrive is spot on here. You should be opening the connection, fetching the data, closing the connection and then returning the data that's no longer reliant on the db. In fact, I never return data in sql format. It's always in entity collections.
0
 

Author Closing Comment

by:louise001
ID: 35393513
Thanks both of you, the using keyword is doing the trick.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question