Solved

Closing sqlconnection objects in asp.net / .net 4.0

Posted on 2011-03-23
8
488 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

943 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

18 Experts available now in Live!

Get 1:1 Help Now