Solved

ASP.NET / SQL2005 Close Connection

Posted on 2011-03-01
2
259 Views
Last Modified: 2012-06-27
When executing my web application I noticed that the database connection stays opened even after I close the connection.  I see this by running the sp_who in SQL2005.  I listed below a piece of code that shows I closed the connection and even try to set it to nothing.  Is there something I am missing?  I am ready to deploy but do not want to run into any connection issues.  There will be roughly 100 uses accessing the application.


    Function GetUserNameByID(ByVal lUserID As Long) As String

        Dim ds As New DataSet
        Dim cn = New SqlConnection(msSQLConnectionString)
        cn.Open()

        Try

            Dim cmd As New SqlCommand("getUserByID", cn)
            cmd.CommandType = CommandType.StoredProcedure


            '...Set up parameter for stored procedure
            cmd.Parameters.Add("@UserID", SqlDbType.Int)
            cmd.Parameters("@UserID").Value = lUserID
            Dim Reader As SqlDataReader = cmd.ExecuteReader

            Reader.Read()
            GetUserNameByID = Reader("Fullname").ToString

            cmd.Dispose()
            Reader.Close()
            Reader = Nothing

        Catch ex As Exception
            cn.Close()
            GetUserNameByID = ""
        Finally
            cn.Close()
            cn = Nothing

        End Try



    End Function
0
Comment
Question by:jseg
  • 2
2 Comments
 
LVL 18

Accepted Solution

by:
ChetOS82 earned 250 total points
ID: 35008153
If you run the page a bunch of times (like press and hold Ctrl+F5) do you get dozens of connections that won't close, or only one or two?

Connection pooling is probably enabled (as it should be) so even though you "close" the connection, the SQL client maintains the actual connection so it can be resused.
0
 
LVL 18

Expert Comment

by:ChetOS82
ID: 35008166
Also, you don't need to close the connection in the catch if you close it in the finally.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

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