Solved

SQL connections not closing in sql server

Posted on 2004-10-12
10
683 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

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.

 
LVL 6

Expert Comment

by:etmendz
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
From the article I gave you... Try the connection string property Pooling=false. Have fun...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

15 Experts available now in Live!

Get 1:1 Help Now