Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 13th, 2007, 02:42 AM
Authorized User
 
Join Date: Sep 2004
Location: Sydney, NSW, Australia.
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default Re: Try/Finally to prevent connection pooling leak

Howdy!

I'm having an issue with connection pooling leaks somewhere in my application and after having done some reading on the issue I've concluded that this can be solved by using Try/Finally.

Problem is, when I place that inside my SelectDB function, the application falls over. Can anyone spot what I am doing wrong??


Code:
Shared Function SelectDB(ByVal strSqlStatement As String) As SqlDataReader

Dim strConnString As String = System.Configuration.ConfigurationManager.AppSettings("SqlConnString")
        Dim objSqlConn As New SqlConnection(strConnString)

    If objSqlConn.State = ConnectionState.Open Then
            objSqlConn.Close()
        End If

    Dim objSqlReader As SqlDataReader
        Dim strSqlCmd As New SqlCommand(strSqlStatement, objSqlConn)
      Try
        objSqlConn.Open()
        objSqlReader = strSqlCmd.ExecuteReader()
        Return objSqlReader
      Finally
        objSqlReader.Close()
        objSqlConn.Close()
      End Try

End Function
It returns the error "Invalid attempt to Read when reader is closed". Fair enough, I thought, perhaps I need to move the "return objSqlReader" down into the "finally" part of the code. No such luck though I'm afraid, that produced another error "Branching out of a 'Finally' is not valid". D'oh!
Reply With Quote
  #2 (permalink)  
Old June 13th, 2007, 08:39 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Simply put, you cannot use a reader that was created on a connection that is now closed. Thus, you either need to return the reader WITHOUT closing the connection or return some other object.

I have used both methods.

You can create the reader with the CommandBehavior.CloseConnection option. This will close the connection when the consumer closes the reader. HOWEVER, this requires the consumer to reliably close the reader in order to free up the connection. I won't recommend NOT doing this as it results in more possibly unstable code.

Instead, go with the latter solution. If you are writing a method to return data, return a data structure such as a DataSet or DataTable instead of a data reading mechanism (the DataReader). That way you can free up the connection resource very quickly.

-Peter
Reply With Quote
  #3 (permalink)  
Old June 13th, 2007, 04:39 PM
Authorized User
Points: 333, Level: 6
Points: 333, Level: 6 Points: 333, Level: 6 Points: 333, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: Fresno, California, USA.
Posts: 94
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also, just wanted to add thaty when you use a Try, you wanna include a catch. So in actuality it is called a Try/catch. Finally excutes no matter what but the catch is to catch the exception that is thrown in the try loop. No catch means unhandled exception if it happens

Reply With Quote
  #4 (permalink)  
Old June 15th, 2007, 11:15 AM
Authorized User
 
Join Date: Sep 2004
Location: Sydney, NSW, Australia.
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by planoie
You can create the reader with the CommandBehavior.CloseConnection option. This will close the connection when the consumer closes the reader. HOWEVER, this requires the consumer to reliably close the reader in order to free up the connection. I won't recommend NOT doing this as it results in more possibly unstable code.
Thanks for the reply Peter.

I have read a bit about the CommandBehaviour.CloseConnection in my various research attempts at solving my connection leak problem although I'm still a bit confused as to whether that will totally solve my problem though. I'm guessing from your response that I'm correct to assume that it probably won't?

What do you mean by returning some other object? Do you mean the other data structures like DataSet or DataTable? Problem is, I really don't understand enough about those to know how to fit them into my function or how they help to close my DB connections easier than the DataReader in order to solve my connection leak problem.

Reply With Quote
  #5 (permalink)  
Old June 19th, 2007, 08:48 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Yes, I mean the other ADO.NET data structures. Technically speaking, the DataReader is not a data structure but a data access class. It does not really "contain" data like the DataTable. Also, the DataSet is nothing but a collection of DataTables.

The difference between using a DataReader and other methods comes down to the way that the DataReader works with the database connection. I don't know specifically how it works internally, but the theory is fairly simple. Consider this psuedo code:
Code:
Open connection
Execute SQL query for DataReader (DB engine executes query, prepares result set)
DataReader.Read() (fetch 1 record from database engine result set)
   Do something with data
Loop
Close connection
This results in code processing WHILE you are fetching records from the database which occupies 1 database connection from the connection pool. Now what happens if the "Do something with data" code takes some time? You are sitting on an open connection while doing processing.

Now consider this psuedo code:
Code:
Open connection
Execute SQL query to a DataSet/DataTable (DB engine executes query, dumps to DataTable)
Close connection

For each record in result set
   Do something with data
Loop
The utilization of the database connection (the most precious resource) is optimized to take the least amount of time. Now your "do something with data" code can take all the time it needs without utilizing an open connection.

One golden rule you will see often with database interactions is "open late, close early", meaning open the connection as late as possible and close it as soon as you can.

-Peter
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connection Pooling problem. nitinp ASP.NET 2.0 Professional 2 July 9th, 2007 02:12 AM
Connection Pooling nigam.anand Apache Tomcat 0 October 18th, 2006 08:39 AM
Connection pooling and Timeout Raghavendra_Mudugal SQL Server 2000 2 May 24th, 2006 01:42 AM
Connection pooling in VB vijayma VB How-To 0 March 18th, 2005 03:51 AM
Connection Pooling with Beans angrycat Apache Tomcat 4 September 16th, 2004 06:11 PM



All times are GMT -4. The time now is 10:41 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.