?
Solved

How to close the database connection before coming out of the function

Posted on 2005-03-31
6
Medium Priority
?
226 Views
Last Modified: 2010-04-24
I have a function of type DataReader and I'm getting the results of the function to Privatedatareader like

  Private privateDataReader As OleDbDataReader

My function looks like this. I want to close the connection after I get the results to the above constant(PrivateDataReader). It goes to endfunction immediatly after return PrivateDataReader.


 Public Function runDBDataReader(ByVal SQL As String) As OleDbDataReader
            Try
                'Check to see if this object has already been disposed
                If privateDisposedBoolean = True Then
                    Throw New ObjectDisposedException(privateModuleName, "This object has already been disposed. You cannot reuse it.")
                End If

                'Set a new Connecton
                privateConnection = New OleDbConnection(privateConnectionString)
                'Set a new Command that accepts an SQL statement and the connection.
                'The command.commandtype does not have to be set since it defaults to text
                privateCommand = New OleDbCommand(SQL, privateConnection)
                'We need to open the connection for the DataReader explicitly
                privateConnection.Open()
                'Run the Execute Reader method of the Command Object
                privateDataReader = privateCommand.ExecuteReader

                Return privateDataReader

            Catch ExceptionObject As Exception
                'Any exception will be logged through our private logexception function
                LogException(ExceptionObject)
                'If an exception occurs, close the connection now!
                privateConnection.Close()
                'The exception is passed back to the calling code, with our custom message and specific exception information
                Throw New Exception(privateExceptionMessage, ExceptionObject)
            End Try
        End Function
0
Comment
Question by:vmandem
[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
  • 4
  • 2
6 Comments
 
LVL 9

Expert Comment

by:Lacutah
ID: 13674296
You cannot close the connection until your are finished with the OleDbDataReader object being returned by the function.  The OleDbDataReader retrieves from the database server one row at a time, so if you close the connection (or try to) at the end of your function, you make the OleDbDataReader useless.
0
 

Author Comment

by:vmandem
ID: 13674483
Lacutah

I read this function probably 163 times and one stage I get hanged and get oracle error saying the oracle connection has reached maximum connections. How to avoid that. I'm calling this
function from a differnt class like this:

  Dim localOutPutServer As New OracleDB(PrivateConnectionStringTest)
                        localDSOutput = localOutPutServer.runDBDataSet(strsql)

IT IS EATING ALL THE SERVER RESOURCES AND LARGE AMOUNT OF CONNECTIONS ARE SHOWN IN THE EVENTVIEWER
0
 
LVL 9

Expert Comment

by:Lacutah
ID: 13674580
When you're finishing up with each OleDbDataReader, you need to close it's associated connection object.  Unfortunately, the OleDbDataReader doesn't have an associated connection object, so you will need some way to access it to close the conenction when you are done with the OleDbDataReader.

Another method would involve returning a datatable instead (open connection, get all records at onece, Close Connection)

This would make your function look like:
'******** Changed Function Declaration to return DataTable ***********
 Public Function runDBDataReader(ByVal SQL As String) As DataTable
            Try
                'Check to see if this object has already been disposed
                If privateDisposedBoolean = True Then
                    Throw New ObjectDisposedException(privateModuleName, "This object has already been disposed. You cannot reuse it.")
                End If

                'Set a new Connecton
                privateConnection = New OleDbConnection(privateConnectionString)
                'Set a new Command that accepts an SQL statement and the connection.
                'The command.commandtype does not have to be set since it defaults to text
                privateCommand = New OleDbCommand(SQL, privateConnection)

                '*******Change Here ********
                dim da as New OleDbDataAdapter(privateCommand)
                dim dt as new DataTable()
                da.Fill(dt) 'Opens connection, fills datatable, and closes connection automatically.
                return dt
            Catch ExceptionObject As Exception
                'Any exception will be logged through our private logexception function
                LogException(ExceptionObject)
                'If an exception occurs, close the connection now!
                privateConnection.Close()
                'The exception is passed back to the calling code, with our custom message and specific exception information
                Throw New Exception(privateExceptionMessage, ExceptionObject)
            End Try
        End Function
0
Technology Partners: 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!

 
LVL 9

Expert Comment

by:Lacutah
ID: 13674611
Another way is to use an overridden form of the ExecuteDataReader:  privateCommand.ExecuteReader(CommandBehavior.CloseConnection)
When you are at the end of the datareader, the connection will automatically close.

0
 

Author Comment

by:vmandem
ID: 13674750
Where should I use the above one:
privateCommand.ExecuteReader CommandBehavior.CloseConnection)
0
 
LVL 9

Accepted Solution

by:
Lacutah earned 120 total points
ID: 13677675
in your own code (not the example I provided), replace your line "privateDataReader = privateCommand.ExecuteReader" with the overloaded methd above.  This still has caviats in that you will want to make sure you move all the way to the end of the reader, or it may not close in a timely fashon.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

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