Connecting to Oracle Database using ODP.NET Download OPD.NET and install on the machine from where you are trying to access the DB. It’s available freely for download. Once you have ODP.NET installed on your machine, you need to add a reference to the assembly Oracle.DataAccess For Visual Studio 2005
After adding the reference, proceed with the following code to connect & check to Oracle database using ODP.NET:
OracleConnection class used in the above code is available as part of the namespace Oracle.DataAccess.Client (Do not confuse with the same class available in System.data.OracleClient).
Connecting with Connection Pooling Connection Pooling is enabled by default and it is not only limited to ODP.NET but also available with other .NET data providers. You can simply add pooling=false to your connection string to disable Connection Pooling. You can customize pooling with your own specification within the connection string. con.ConnectionString ="Data Source=xe; User id=scott;Password=tiger; Min Pool Size= 5; Connection Lifetime=120; Connection Timeout=60; Incr Pool size=2; Decr Pool size=1" The connection string in the code above is defined with several parameters. Connection Lifetime sets the maximum duration in seconds of the connection object in the pool. Connection Timeout is the maximum number of seconds to wait for the connection to the server (before raising an error). Min Pool Size is the number of connection objects it needs to hold at any time (similarly Max Pool Size is also available). Based on the demands of requests and activity, the number of connections in the pool gets decreased or increased based on the specification of Incr Pool size and Decr Pool size.
Dynamic Connecting String Using OracleConnectionStringBuilder and app.config or Web.config
You can dynamically build a connection string using the available in ODP.NET 10.2.0.2.
class
From the above code, you can observe that we are trying to retrieve all the connection parameters from the app.config file using the My object introduced in .NET Framework 2.0. The OracleConnectionStringBuilder object simply needs to have a few properties (like DataSource , UserID , Password etc.) set. Once the properties are set, it automatically frames a connection string internally and returns this when used with the ConnectionString property.
Executing a PL/SQL Stored Procedure Let’s execute a stored procedure named p_My_Procedure
While Working with STORED PROCEDURES SET Following Properties CommandType CommandText Passing Parameter Values to a PL/SQL Stored Procedure Same old OracleParameter needs to be used for this purpose. Let’s execute a stored procedure named p_My_Procedure which accepts a parameter amt (input type of parameter) of type NUMBER.
For every existing parameter of a stored procedure, a separate OracleParameter object must be defined corresponding to it. In the highlighted code above, we created an OracleParameter and assigned a few properties according to the needs. It is very similar to working with bind variables except that the bind variables are replaced with parameter names. Make sure that you always specify the parameter's Direction property.