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
Solved

SQL connections not closing in sql server

Posted on 2004-10-12
10
688 Views
Last Modified: 2008-01-09
Hi,

I am having serious problems with a VB.NET application I am developing.  I am using .net version 1.1.4 on a windows 2003 server and SQL Server 2000 with SP3.

My problem is that my connections to the database do not close when i use with a SQLDataReader.  I am using  <SQLDataReader>.close and <SQLConnection>.close when I am done with it.

I am using Microsoft DAAB v 2.0 for my database calls.  I have also tried not using DAAB layer and done the normal way and had the same problem.

The main function is GetRelatedItemsDetail, which takes in the open connection and datareader BYREF, then executes reader and assigns it to the SqlDatareader passed in.

I open and close my connection using functions that take the connection in byref.

What am I missing here?  I figure as long as I pass the connections and datareaders byref when i do a .close it will close the same one originally created.  

Here is my code:

     Dim myRelatedItemsDetail As SqlDataReader
     Dim OpenSqlConnection As New SqlConnection
     GetSqlConnection(OpenSqlConnection)  'open connection, in byref        
     GetRelatedItemsDetail(OpenSqlConnection, myRelatedItemsDetail, ItemID.ToString,1, 1)
     While myRelatedItemsDetail.Read()
        'do something
     End While
    myRelatedItemsDetail.Close()
    Connection.CloseSqlConnection(OpenSqlConnection) 'close connection

'SUPPORTING FUNCTIONS


 Public Function GetRelatedItemsDetail(ByRef OpenSqlConnection As SqlConnection, ByRef myDataReader As SqlDataReader, ByVal ItemID As String, ByVal RelationTypeID As Integer, ByVal PublishStateID As Integer)
        Dim SQL
        PublishStateID = GetPublishStateID(ItemID)
        SQL = " sp_get_Stage_RelatedItemDetails '" & ItemID & "', " & RelationTypeID          
        Try
          myDataReader = SqlHelper.ExecuteReader(OpenSqlConnection, CommandType.Text, SQL)
        Catch ex As Exception
          DBExceptions.HandleException(ex, SQL)
        End Try
End Function


Function GetSqlConnection(ByRef UnInitSqlConnection As SqlConnection)

    'set and open connection
    UnInitSqlConnection.ConnectionString= ConfigurationSettings.AppSettings("ConnectionString")
    UnInitSqlConnection.Open()

End Function
         
Function CloseSqlConnection(ByRef pConnection As SqlConnection)
   
       pConnection.Close()
       pConnection.Dispose()
       pConnection = Nothing

End Function



Help!  I hope I'm just being a retard am just missing something here!  Either way 500 points to someone who helps me solve this problem!!
0
Comment
Question by:troycusolle
10 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12294824
Debug and Check Whether your CloseSqlConnection is being called at all

Also try and put a Finally clause in case Errors stop execution of Connection Close statement.

Why are you calling Connection.CloseSqlConnection  What is Connection here is CloseSqlConnection  in some other class??

0
 

Expert Comment

by:DXSE
ID: 12295066
Have a look at the GC.Collect and place it at the end

     While myRelatedItemsDetail.Read()
        'do something
     End While
    myRelatedItemsDetail.Close()
    Connection.CloseSqlConnection(OpenSqlConnection) 'close connection
    GC.COLLECT

I cant tell if that will fix your problem, but it has helped me a few times. Can't explain exactly why though... :)
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12295137
Well GC.Collect explicitly forces the garbage collector to start Garbage collecting
So the garbage collector will clean up the References which your program has not cleaned up explicitly. So it works

But Grabage collector is resource insentive it will take up a lot of CPU cycles so try to avoid explicitly calling it. Our programs should not depend upon GC but we should clean up references created by us ourselves.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 6

Expert Comment

by:etmendz
ID: 12295718
DAAB is good but it inherently supports connection pooling. You might want to turn off connection pooling through a property in the connection string. Try to change the Connection Lifetime in the connection string. Its default value is 0 which means it will use the maximum possible.

Read more here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp

Have fun...
0
 

Author Comment

by:troycusolle
ID: 12298935
Wow,
Didn't think I would get responses so quickly!

arif,
CloseSqlConnection is being called. I assured that before.  Connection.CloseSqlConnection is just my CloseSqlConnection() function I just copied this code in from other modules and left the module name on it.

DXSE,
GC.Collect would work but I don't even want to imagine what havoc it would reek once 500 people are on the site simultaneously.

etmendz,
You are not saying that you can't shut off connection pooling with DAAB are you?
If I change the Connection Lifetime to say 1 will that drop ALL unused connections.  Ones closed but still waiting in connection pool for a connection AND ones that were not closed properly?
0
 
LVL 6

Accepted Solution

by:
etmendz earned 500 total points
ID: 12304501
Actually it is not DAAB. It is ADO.Net and SqlClient that's using connection pooling. So I stand corrected and should say that connection pooling is inherent to ADO.Net and SqlClient in the first place. Since DAAB's using them, it is inherent to DAAB. ;-)

The Connection Lifetime is only one of the properties you can use to control connection pooling. The link I gave you shows more properties you can experiment with. Note that SqlClient's connection pooling is based on the connection string. A different connection string is a different connection in the pool (I think it is even case sensitive). So you can have different connections in the pool enforced by simply having different forms of the connection string.

A new connection in the pool is created when 1) no connection of the same connection string exists and 2) when the existing connection for the same connection string is active and cannot be used at the moment. That means, you can have many connection instances for the same connection string as ADO.Net finds it necessary. These things are managed in the background by .Net so the best you can do for now is to experiment on the properties you can use to control connection pooling.

Have fun.
0
 
LVL 6

Expert Comment

by:etmendz
ID: 12304536
From the article I gave you... Try the connection string property Pooling=false. Have fun...
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get the selected ValueMember of Combobox 5 39
how to wrap a merged cells of datagridview column in vs2010 (vb.net) 8 44
Regex validation 2 28
SQL LINE CONTINUATION ISSUE 12 37
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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