Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADO .Net :  After closing my oracle connection, it remains open on the oracle server. How can I close it ?

Posted on 2004-03-23
11
Medium Priority
?
297 Views
Last Modified: 2010-05-18

Hi,

I'm working with ASP .Net and WebServices.
In my webservice, I have an OracleConnection object.

I open that connection in the beginning of a lot of functions, and close it a the end of each function.
I thoses functions, I generaly made some updates, inserts, on select of single line results.
So I never use DataAdapters nor dataSet.

But, even if I close my connection at the end of each function, the connection remains open on the oracle server.

I guess this is due to the connection pooling.

But I wan't to definitly close my connection.
(because I sometimes reach the max connection on the oracle server)

How can I do that ???

Thanks to all of you.

Jemty
0
Comment
Question by:jemty
[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
  • 2
  • +1
11 Comments
 
LVL 1

Accepted Solution

by:
GordanM earned 1000 total points
ID: 10659344
You can turn the connection pooling off.  Take a look at this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql2k_connpooling.asp
0
 
LVL 6

Expert Comment

by:gtvingo14
ID: 10659394
also make sure that you close your resultsets and statements
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 2

Expert Comment

by:NetoMan
ID: 10662663
jemty:

can you resolve your problem ? I got exactly the same situation with asp. please, if you got a solution share it.

I follow the links to msdn and read the explanation of pooling. I did changed the to odbc settings to <don't pool> but still got teh same result. the connection is not getting closed.

thanks in advance

NetoMan:
0
 

Author Comment

by:jemty
ID: 10665711
Hi Guys,

In 2 words : SOLUTION FOUND

There is an article treating the problem of connection pooling for oracle :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingfornetdataproviderfororacle.asp

To give you just a little resume of this article, the solution consists of adding some parameters in the connection string.
The most powerfull of all those parameters is the following :
";pooling=false"
As simple as this ;-)
But now I have to test the persformance. Hoping I won't lose too much by disabeling pooling.
(If I loose to much, there is also a parameter to limit the max number of pooled connections).

NetoMan,
I didn't made any tests on ODBC, but perhaps is there also some parameters, like for oracle, to add to the connection string.

Thanks to all of you.

Jemty


0
 
LVL 2

Expert Comment

by:NetoMan
ID: 10668692
Jemty:

thanks, i'm following your comment but I get this error :

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

I'm using the conecction string like this:

cursor.open SQL, "DRIVER={Microsoft ODBC for Oracle};UID=fo;SERVER=solida;;User Id=fo;PASSWORD=fo
;pooling=false;"

or this, using a DSN with Oracle 7.3 odbc instead of microsoft odbc for oracle driver:

cursor.open SQL, "DSN=solidafo;UID=fo;SERVER=solida;PASSWORD=afopens078;pooling=false;"

I suppose its the ODBC driver ? what version of ODBC are you using ?

thanks in advance
0
 

Author Comment

by:jemty
ID: 10669594
NetoMan,

I neither use the ODBC driver from ORACLE nor from Microsoft.

In fact I'm using the Oracle Client ("System.Data.OracleClient").
That means that you have to add "System.Data.OracleClient" as reference in your project.

But (If I remember well), you have to install Oracle Client 9.X on your computer.
(So that it's compatible with .Net)
And, I don't know why but it still doesn't work before you install Oracle Data Access (ODA.NET) on your computer (downloadable on the oracle web site).
(I remember having read some articles on the net about that, but sorry, I don't remember where)

Once you'll have all that, you'll be able to use the native access for oracle, and you'll be able to add the "pooling=false" to your connection string.

Hoping I'm helping you

Jemty
0
 
LVL 2

Expert Comment

by:NetoMan
ID: 10670350
ok, thanks.

I think that the end of my road is still far. I'm using oracle 8i so I have to try some of your comments on my oracle version.

thanks a lot!
0
 
LVL 2

Expert Comment

by:NetoMan
ID: 10923978
ok.
thanks all.
Sorry I was on a trip

0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

618 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