how to close database connection, connection pool issue

I have create a asp.net 2.0 application with mysql using the .net/connector 5.1.

Most of my pages use a ObjectDataSource control and a Repeater Control. I am calling Close() on both the reader and DB connection but it appears that the connections are not closing and the connection pool gets filled up and I get an error. Does anyone know why the connections aren't closing?

 Heres the function that the ObjectDataSource calls:

Function myFunc(ByVal subCat As String, ByVal sortBy As String)

Dim connStr As String = ConfigurationManager.ConnectionStrings("connector").ConnectionString

Dim conn As New MySqlConnection(connStr)

conn.Open()

Dim qry = "select * from test"

Dim command As New MySqlCommand(qry, conn)Dim reader As MySqlDataReader = command.ExecuteReader()

 

Return reader

reader.Close()

conn.Close()

End Function

The error:

error connecting: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

0
jmun
8/1/2007 2:58:22 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

12 Replies
934 Views

Similar Articles

[PageSpeed] 22

 There are 2 problems

  1. The "Return reader" statement will exit the function, and reader.Close and conn.Close will never be called
  2. A reader requires an open connection. You can't use it once the connection is closed

Return a datatable instead, or open and close the connection and reader outside the function.

-- "Mark As Answer" if my reply helped you --
0
gunteman
8/1/2007 3:23:38 AM

i am beginning to think that the code after the return does not run even thoug this site closes the connections the same way i have

http://msconline.maconstate.edu/tutorials/ASPNET20/ASPNET11/aspnet11-04.aspx

0
jmun
8/1/2007 3:23:49 AM

can an ObjectDataSource recieve a datatable?

how would i close the connection outside of the function when i am creating it inside the function?

0
jmun
8/1/2007 3:26:54 AM

does anyone know of a good way to manage database connections using the objectdatasource and repeater. i'd like to use the datareader over a dataset for performance reasons

0
jmun
8/1/2007 7:43:31 AM

 how about the try/catch block???

0
g2000
8/1/2007 1:27:32 PM

jmun:

can an ObjectDataSource recieve a datatable?

Yes. But the ObjectDataSource is definitely not tailored to be used with either DataReaders or DataTables.

jmun:

how would i close the connection outside of the function when i am creating it inside the function?

Don't create it inside the function.


-- "Mark As Answer" if my reply helped you --
0
gunteman
8/1/2007 2:33:03 PM

Listen, man get rid of the connector 5.1 you do not need it!  This is a simple process use a simple connection string. The problem it isnt closing is because your coding for opening and closing isnt working because you are using another device for running a connection. Then you are trying to run a connection VBCODE. DO NOT TRY TO REINVENT THE WHEEL. This should help. If you want to professionally hide your connection strings use a resource file and create a instance of that file in your project an inherit the connection string. 

0
Srheal
8/1/2007 5:12:42 PM

I am using a connection string, its defined in my web.config. Connector 5.1 is just an interface to the database like ODBC. It appears that returning a resultset is my only real option. I don't see a good way of managing connections outside of my function.

This seems like a common issue when using the objectdatasource, anyone have any experience with it?

0
jmun
8/1/2007 8:39:07 PM

Listen if you are trying to run read on the database you open the reader then you close the reader just like opening a connection and closing a connection.

But make sure you open a connection then run the reader object, then close the reader objec then close connection.

 

0
Srheal
8/2/2007 3:27:41 PM

It's not very common to use the ObjectDataSource when returning DataReaders or DataTables (it's mostly used to work with business classes). It is perfectly possible, though, and in fact (after thinking about it for a while) it's a much nicer solution than to work with the SqlDataSource.

 

Function myFunc(ByVal subCat As String, ByVal sortBy As String) as MySqlDataReader
Dim connStr As String = ConfigurationManager.ConnectionStrings("connector").ConnectionString
Dim conn As New MySqlConnection(connStr)
conn.Open()
Dim qry = "select * from test" Dim command As New MySqlCommand(qry, conn)
Dim reader As MySqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
Return reader
End Function
Adding CommandBehavior.CloseConnection to ExecuteReader should be enough. When the control has finished working with the data, it will call Dispose on the reader, which in turn will call Close on itself, which first (due to the defined behavior) will call Close on the connection.
/G 
 
 

-- "Mark As Answer" if my reply helped you --
0
gunteman
8/2/2007 9:02:52 PM

I actually tried adding the CommandBehavior to the function argument but for whatever reason it didn't work. I ended up return a dataset instead of a datareader and now I don't get the connection pool issue.

 here is the code incase anyone is interested:

Function myFunc(ByVal subCat As String, ByVal sortBy As String)
        Dim connStr As String = ConfigurationManager.ConnectionStrings("connector").ConnectionString
        Dim conn As New MySqlConnection(connStr)
        conn.Open()

        Dim ds As DataSet = New DataSet()

        Try
            Dim qry = "select * from test"

            Dim command As New MySqlCommand(qry, conn)
            Dim adapter As New MySqlDataAdapter()

            adapter.SelectCommand = New MySqlCommand(qry, conn)
            adapter.Fill(ds)
        Finally
            conn.Close()
        End Try

        Return ds
    End Function

0
jmun
8/3/2007 1:32:42 AM

 Yes, that is the preferred way. The MySqlDataAdapter should also be able to handle the connection for you.  

Function myFunc(ByVal subCat As String, ByVal sortBy As String) As DataTable
	Dim connStr As String = ConfigurationManager.ConnectionStrings("connector").ConnectionString
	Dim qry As String = "select * from test"
	Dim adapter As New MySqlDataAdapter(qry, connStr)
	Dim dt As DataTable = New DataTable()
	adapter.Fill(dt)
	Return dt
End Function
 

-- "Mark As Answer" if my reply helped you --
0
gunteman
8/3/2007 1:50:50 AM
Reply:

Similar Artilces:

Connection.close() not closing my connection?
My servlet calls con.close() within the finally {} of a try..catch block. Yet I notice that sa_conn_info still lists the connection. Shouldn't the connection disappear from the sa_conn_info list when I close it? And if the answer to that is "yes, it should", has anybody seen situations like mine where it *doesn't* close it? Thanks for your help, Kathleen My bad... through a series of really stupid coding mistakes, I managed to cause a SQLException to be thrown from within the finally {} block before the connection could be closed... and I was ignoring the ...

During connection lost, how to close database connection
I'm using TidTCPServer OnExecute method. When client connect to server, I create Database connection with Unidac to PostGreSQL (connection ceated and handled inside onExecute). Problem is when the connection to client is lost, the connection to database does'nt disappear. When cheking from PostGRE pgAdmin there is several connections more than what we have clients. I have tried to put dbConnect.close or FreeandNil(dbConnect) or dbConnect.Free in try.. except.. end except block. But it looks like when the connection is lost, the thread stop/collaps and the database connection stay...

Timeout connecting with .NET connection object
I have a SQL Anywhere 9 database that I can connect to fine via Sybase Central or ISQL. However, when I attempt to connect using the AsaConnection object from the IAnywhere.Data.AsaClient namespace, the open connection call times out. The code works fine on my development box, but I cannot get the connection to work on the production box. Any ideas? Thanks, David ...

SFtp connect
The following Ftp connect to a sftp site has the right port/usrname/password [verified thru filezilla], but when I try to connect via TIDFTP -- I get "Connection Closed Gracefully" upon ftp->Connect();; The ftp properties are below the code. I have no other Indy objects on the form. It ocurred to me that I may need something for sftp. ftp->Connect(); if (ftp->Connected()) { ::Sleep(1); } object ftp: TIdFTP AutoLogin = True Host = 'ftp.dailyaccess.com' Password ='XXXXX' Port = 21 Username = 'charles.leggette' ProxySet...

Recycling connections in a ADO.Net connection pool
I have a requirement where I will need to get a handle on the existing connections in a particular ADO.Net connection pool (identified by the unique connection string) and close and open them one by one. This is because whenever we compile a package or stored procedure, ADO.Net does not realise this and keeps giving "Package is invalid" error. I am using ODP.Net Client 9.2.0.4. The current solution is to restart the application pool to flush the connection pool. Does anyone know how to get a list of all connection objects in the ADO.Net connection pool given a connection stri...

How to clear or close connection in connection pool mode?
HI, I use ADO.NET in connection pool mode, however, we have password change mechanism regularly so that we need to close all existing connections in the connection pool once the sybase password is changed. In ASEConnection object, I can't find any method to close all connections. What ways I should handle this situation? Any suggestions are welcome. Thanks, Raymond Hi You must have the 2.0 Provider, Ase.AdoNet2.AseClient.dll Use the props per the MS ADO.NET 2.0 spec on AseConnection: [AseConnection].ClearPool ClearAllPools for all the pools -Paul ...

Open/Close connection VS. Connection pool?
My site have very heavy traffic and many users reach the database at the sametime. I wonder witch it is the best strategy to connect to the database: open/close the connections as soon as possible OR use a connection pool? If a connection pool it is a better strategy please submit some code in C# about a connection pool. ASP.NET does connection pooling all by itself, presuming connection strings are identical. It will do a better job than you are likely to do, so open as late as possible, close as early as possible, and ASP.NET will handle connection pooling for you.Starting with ASP.NET ...

Database Connection???How to connect a Database in an other server?
How to connect a Database(.mdb) in an other server? Can I use the connection string like below? mycon.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=http://www.****.com/***/mydb.mdb" You can not. That simple. Access is file based. No a client/server architecture.Thomas TomiczekPowerNodes ApS(Microsoft MVP C#/.NET)---Building a Website? Try the PowerNodes CMS - http://www.powernodes.com/ Correction: you can. Just make sure you can reach the acces db file - through a file share that is bound as drive. Not that you should. Performance will be horrib...

Connection Object Issue in .NET?
Hi, I am using an OLEDB connection object and I was confused to see that the following code snippet is running fine. In this snippet I am disposing the OleDB connection object and then again checking the same object's connection state. Logically it should not find the object anymore, but since it is working fine, so I am a bit confused that is there any bug with the dispose method or Am I doing something wrong? Can anybody let me know how it is working fine? Below is the code snippet I am using:               &n...

underlying connection was closed: The connection was closed unexpectedly
Hi ,   I am working with wcf and linq  and i am facing a problem"The underlying connection was closed: The connection was closed unexpectedly. " when i am trying to return my linq query to the client.I tried for changing the code in web.config for client and server.like   Client :   <behaviors> <endpointBehaviors> <behavior name="debuggingBehaviour"> <dataContractSerializer maxItemsInObjectGraph="2147483646" />                 &nb...

difference between connection.close and command.connection.close
hello, What is the difference between odbcconnection.close and odbccommand.connection.close? I am making connection once in my application in global.asax and opening connection at application_start event and closing it at application_end event. I am accessing connection through "global.myconnection" in webforms. NOw in the webforms,do I have to mycommand.connection.open before comand.executereader?? Does mycommand.connection.close , closes the odbcconnection that I make in gloabl.asax? It is confusing, because on the first function executing on webform, when I myc...

not connected when I am connected
What do I need to change on my 'puter when the icon in the toolbar shows and tells me I am connected and the error message tells me I am not and I can not go to web pages, get or send emails. I have to close down and restart to get connected again. This only happens if I am on line and leave the 'puter for an hour or so. As long as I am using it I don't have a problem. This just started about a month ago. I am on very slow dialup, DSL and Cable not in my area, thanks you SBC/ATT tks Elaine wrote: > What do I need to change on my 'puter when the icon in ...

Single open connection vs connection pool (opening and closing each time)
I see that log4net use single open connection instead of connection pool. They don't open and close the connection each time they just do it once in the beginning.  What are the risks of such approach in a multythreaded environment?  What can happen if two threads want to use the connection at the same time?  Is there a risk associated with that?  The fact is that if using just a single connection there is a performance benefit, then what is the down side? Or we are just wrong by doint this? using(var c = new SqlConnection(str)){   // use connection ......

Connection object connecting to server
I'm moving from Sybase Central Java Edition 3.2 to EAServer 6.1 and Powerbuilder 8 to PB 10.5. The PB app has been migrated and to the best of my knowledge, I've set up EAServer 6 the same way as Sybase Central was set up. When attempting to login to the application using my loginID and password, I receive the following message: this connection object is not connected to a server. If I use the same login that's used for EAServer 6.1 then it's fine. Any constructive input would be appreciated. Thanks. Developer wrote... > I'm moving from Sybase Central Java...

Web resources about - how to close database connection, connection pool issue - asp.net.object-datasource

Connection - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

iMedia Connection: Interactive Marketing News, Features, Podcasts and Video - iMediaConnection.com
High-quality data, if not used properly, can still lead marketers to make bad decisions. Consider these common ways that numbers are used to ...

HTTP persistent connection - Wikipedia, the free encyclopedia
... tacked on to an existing protocol. If the browser supports keep-alive, it adds an additional header to the request: Following this, the connection ...

CareerSonar Turns Facebook Friends Into Job Connections
Looking for a job ? Among your Facebook friends lies the potential for employment. CareerSonar , a new service, brings together a person’s connections ...

Ben Garcia gives Penrith Panthers a new French connection
Should he jag a game in the NRL, Ben Garcia will become just the third genuine French import to do so.

Man Charged With Aggravated Arson In Connection To Columbus Warehouse Fire
Police have charged 30-year-old Robin Toms with aggravated arson.

Facebook becomes more adept at dealing with crappy connections
... to get a decent phone signal to allow you to post a photo of your meal. Joking aside, in countries where people are struggling with 2G connections ...

Adam Savage from 'MythBusters' has an incredible connection to the 'Star Wars' franchise
Adam Savage, co-host of the popular " MythBusters " television show, soured on the plot of "Interstellar." But when it comes to the newest films ...

French authorities detain suspects in connection to attacks 10 months apart
CNN French authorities detain suspects in connection to attacks 10 months apart CNN A forensic scientist works near a Paris cafe on Saturday, ...

UK Police Make Arrest in Connection With VTech Hacking
British law enforcement officials arrest a 21-year-old man in connection with attack on toy maker VTech that exposed 6 million parents and children ...

Resources last updated: 12/22/2015 9:24:10 AM