Solved

SQL connections not closing in sql server

Posted on 2004-10-12
10
693 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
[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
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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