?
Solved

ASP.NET / SQL2005 Close Connection

Posted on 2011-03-01
2
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 18

Accepted Solution

by:
ChetOS82 earned 1000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

765 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