Solved

ASP.NET / SQL2005 Close Connection

Posted on 2011-03-01
2
256 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

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

10 Experts available now in Live!

Get 1:1 Help Now