ADO.NET and Connection Reuse ...

Posted on 2006-04-27
Medium Priority
Last Modified: 2008-03-04
Im using Visual Studio 2003 .NET and ADO.NET to connect to an Oracle 10g database.  Im making use of the Oracle .NET provider.  I will be making several calls to the same Oracle dbase so figured I should leave "open" the connection to make all subsequent query requests.  One thing I';ve been strugling with in .NET is .ExecuteRead() requests failing due to "connection is already open".  Normally i have to close the current connection, create a brand new Connection and Command object then make the new SQL query.  This just seems so inefficent and slow.  Is it possible to leave open the connection to make subsequent query calls with little overhead and speed reduction or is it a requirement to create a new connection and object in .NET?

Can u please provide me with a sample C# conenction to the Oracle .NET Provider to achieve this.

Question by:aniston
LVL 52

Accepted Solution

Carl Tawn earned 100 total points
ID: 16551741
You shouldn't need to create a new connection. Simply calling the Close() method on the connection when you are finished with it should be enough. It will then be closed ready to be reused in a subsequent call.
LVL 44

Expert Comment

ID: 16552128
a Connection can be opened, used, then closed as many times as required, without needing to destroy the connection, and then create a New one.  Once a Connection has been closed, you can have your code OPEN it again, when it is needed.


LVL 13

Expert Comment

ID: 16552224
I would not leave a connection open to the database.  Most db providers have built in connection pooling so feel free to open/close as often as you need without hurting performance.  I am a SQL Server guy and I know it does pooling without a special connection string (there are strings that let you have custom pooling settings) but I cant say the same for oracle.

Just think if this was an asp.net application you wouldnt be able to leave a connection open if you wanted because its stateless.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

840 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