Accessing Oracle from C# and Microsoft .NET - Nyoug

acceptableseashoreSecurity

Nov 5, 2013 (3 years and 9 months ago)

92 views

ODP.NET

Access Oracle from .NET

Using C#

Alex Hoyos



Oct 2004

Agenda


Define MS .NET


Web development and .NET (basic stuff)


IDE used


Different ways of talking to Oracle from
.NET


ODP.NET


crack it open and get it ready


Samples


What is MS NET. Architecture




IL (Intermediate Language),


CLR (Common Language Runtime),


Base Class Library (BCL) is a common library used by all
languages, GC (Garbage collection) is the responsibility of the
CLR, not the language.


.NET 20K feet high view…



What is MS .NET


OPERATING SYSTEM

CLR
-

COMMON LANGUAGE RUNTINE

BASE CLASS LIBRARY

ADO.NET

ODP.NET

ASP.NET Web Forms

Web Services


Windows FORMS


VB

C++

C#

J#

..

ODP + .NET lingo


Assembly:
Microsoft’s term for the module that is
created when a DLL or .EXE is complied by a .NET
compiler.


ODP.net Assembly is
Oracle.DataAccess.dll


Namespaces:



Oracle.DataAccess.Client (e.g. OracleConnection)



Oracle.DataAccess.Types (e.g.
OracleClob Class)





Oracle: Best Database for .NET


ODP.NET allows full access to .NET


No limitation to using .NET functionality.


Still requires you install the Oracle Client
Software (9 or above)




Oracle and .NET Data Access


Oracle Data Provider for .NET (ODP.NET)


Developed by Oracle


Best performing provider


Exposes the most Oracle DB features


Based upon Microsoft ADO.NET spec


3
rd

party Oracle providers


Microsoft .NET Data Provider for Oracle (Microsoft)


Connect for .NET (DataDirect)


Other Oracle data access methods


OLE DB .NET via OLE DB


ODBC .NET via ODBC

ODP.NET Basics


Available today in production for free


http://otn.oracle.com/tech/windows/odpnet


Original release: Dec. 2002


Can be used with Oracle8, Oracle8
i
, Oracle9
i
, and
Oracle10
g

database servers


Database server can be on Unix, Linux, Windows, etc.


Database client on Windows


Supports VS.NET 2002 and 2003


Supports .NET Framework 1.0 and 1.1

ODP.NET Object Model

DataSet

Oracle

DataAdapter

Oracle

Transaction

Oracle

Command

Oracle

Connection

Oracle

DataReader

Connected Layer

(ODP.NET)

Disconnected

Layer

Oracle

OracleCommand

Builder

Data

Layer

ODP.NET Features


Full PL/SQL support


Packaged, non
-
packaged, anonymous, autonomous


*Batch SQL available with anonymous PL/SQL


Native Oracle data types


LOBS, REF Cursors, BFiles, N
-
data types, Dates,
TimeStamps, Longs, Raws, etc.


Safe type mapping to .NET data types


*Connection pooling


Min, Max, Timeout, Lifetime, Increment, Decrement

* Performance tip

ODP.NET Installation

* Performance tip


The
Oracle.DataAccess.dll
assembly is installed in the
ORACLE_


BASE
\
ORACLE_HOME
\
bin
directory.


Documentation and the
readme.txt
are installed in the
ORACLE_BASE
\
ORACLE_HOME
\
ODP.NET
\
doc directory
.


Samples are provided in the
ORACLE_BASE
\
ORACLE_HOME
\
ODP.NET
\
Samples
directory.

Visual Studio .NET Integration


Dynamic Help


ODP.NET documentation available


Hit F1 key


Intellisense


Connection pooling is enabled in ODP.NET
(by default).



ODP.NET Connection Options


Proxy user authentication

OracleConnection con = new OracleConnection();

con.ConnectionString = "User Id=customer;Password=lion;" +

"Data Source=
oracle
;
Proxy User Id
=
appserver
;
Proxy Password
=
eagle
; ";

con.Open();



Operating System Authentication

OracleConnection con = new OracleConnection();

con.ConnectionString = "User Id=/;Data Source=oracle;";

con.Open();



* Performance tip

ODP.NET Connection


Connection pools are created by the connection pooling service
using the
ConnectionString
as a signature to uniquely identify a
pool.


OracleConnection con = new OracleConnection();

con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;" +

"Min Pool Size=5;Max Pool Size=20;Connection Lifetime=120;” +

“Connection Timeout=60; Incr Pool Size=5; Decr Pool Size=2";

con.Open();

NOTES:


Connection Lifetime

=
Maximum life time (in seconds) of the connection (default
0)


Connection Lifetime enforced
when a connection is going back to the
connection pool.


Connections are closed every three minutes (connection Lifetime..)



* Performance tip

Workshop Example VS.NET



NOTE
: Right
-
click and select “open in new Window” to prevent the
current powerpoint from being overlaid and thus allowing you to
continue. If you have a popup blocker, this may prevent a new window
from opening. E.g. with Google Popup blocker, you need to press the ctrl
key simultaneously.


WebEx Pre
-
recorded Sessions


Create Skeleton ASP.NET Web Application



Add Populate Dept functionality


Add Insert Dept Functionality


Execute Application

ODP.NET Performance and
Scalability Tips


Close all ODP.NET objects when finished (e.g. OracleConnection)


Do not count on the garbage collector to do this automatically


Use anonymous PL/SQL when batching commands


Makes only one DB round trip


Use associative arrays to bind parameters


Use FetchSize (OracleDataReader) and RowSize (OracleCommand) to
tune data retrieval performance


Use InitialLOBFetchSize and InitialLongFetchSize to tune LOB and
LONG retrieval performance

Performance


Use Anonymous PL/SQL blocks.

FAQ


Q: Can ODP.NET work with Oracle8,
Oracle8
i
, Oracle9
i
, and Oracle10
g

database (DB) servers?


Yes, but you will need to use an
Oracle9
i

Release 2 client or higher


FAQ


Q: Do I need to install ODP.NET on
my Oracle DB server?



A: No. You only need to install
ODP.NET on your client or middle
-
tier machine, wherever you use the
.NET Framework.



“Thank you.”