Connection Pooling with MS .NET provider for Oracle

[Guest]

Can you do connection pooling with the Microsoft ADO.NET provider for Oracle. if so, how do you set it up. Is it similar to SQL Server where you definne it in the connection string

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

If possible, use the Oracle downloadable provider, rather than the Microsoft
provider. You can get it free from Oracle TechNet (otn.oracle.com). You will
have to register, but it is worth it if you work in Oracle.

You can use the same syntax in the Microsoft provider, but I have had so
many issues with advanced features in the Microsoft provider, that I would
not trust it in these areas.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************************************************
Think outside the box!
***************************************************************

Can you do connection pooling with the Microsoft ADO.NET provider for

Oracle. if so, how do you set it up. Is it similar to SQL Server where you
definne it in the connection string?

 

[Miha Markic [MVP C#]]

Hi,

Just a note that Oracle's provider require 9i or newer client (whereas MS is
happy with 8.1.7), I think.

 

[Guest]

ODP.net for the 9i Client allows you to pool connections and still logon each individual user (over what Oracle calls a lightweight session, which is created by passing "proxy user" credentials for the connection in addition to the standard user credentials for the data access). The ID user to make the connections only need to be granted "connection" priviledge and the ability to be a proxy ID for a user is also controlled by security settings within Oracle, so the HUGE security risk that comes from using a common ID on an Internet-accessible server is virtually eliminated. You can create a stored procedure to detect whether the user is connected through their ID or a pooled ID and apply a different Oracle security policy when they user is connecting through the .Net application vs connecting with other tools (e.g. enable calling of stored procs through the application only)

I can't believe MS does not yet support this feature under the 1.1 framework - it is a very important feature that reconciles the competing principles of the MS application architecture - which tries to load everything into the middle tier (sells lots of serveers, I guess) and the Oracle design principle of protecting the data at the database, where the strongest "vault" can be created to protect the data "asset" (Oracle really has a valid point here when you consider that .net-oracle apps will typically run on a multi-platform, multi-vendor, continually-updated infrastructure). The ability to combine pooling with individual log ons also makes Internal Audit groups happy because Oracle's audit trails can be used to track user activity and the access limitations imposed by the DBAs cannot be overridden by "enterprising" application developers.

As I write this, I think "why am I surprised at the lack of support for this from Microsoft"? MS chose not to support SPs returning "Data Table" arrays, a method that is widely used in legacy applications, explaining that the feature is not very efficient (Does that justify intentional backwards incompatibility?)

I had a bad taste in my mouth over an earlier decision to use Oracle's OLEDB provider with VB 6 COM/MTS components instead of MS's provider, but for ADO.Net today, it looks like Oracle's currently doing a better job of building and supporting a ADO.Net connectivity layer for Oracle

One option worth considering is the OraDirect provider from CoreLabs - if you don't mind using third party components. They claim to be fully compatible with MS provider yet still support all Oracle capabilities