Solved

Closing sqlconnection objects in asp.net / .net 4.0

Posted on 2011-03-23
8
482 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now