ADO.NET and Connection Reuse ...

Posted on 2006-04-27
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

    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

    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

    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 application you wouldnt be able to leave a connection open if you wanted because its stateless.


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    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…
    Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now