ODP.NET

1
ODP.NET
2
Access Oracle from .NET Using C
Oct 2004

• Alex Hoyos

3
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

4
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

5
What is MS .NET
VB
C
C
J
..
ASP.NET Web Forms Web Services
Windows FORMS
ODP.NET
ADO.NET
BASE CLASS LIBRARY
CLR - COMMON LANGUAGE RUNTINE
OPERATING SYSTEM
6
ODP .NET lingo

• Assembly Microsofts 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)

7
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)

8
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
• 3rd 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

9
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, Oracle8i, Oracle9i, and
  Oracle10g 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

10
ODP.NET Object Model
Disconnected Layer
Connected Layer (ODP.NET)
Data Layer
Oracle DataAdapter
OracleCommand Builder
DataSet
Oracle Command
Oracle DataReader
Oracle Transaction
Oracle Connection
Oracle
11
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
12
ODP.NET Installation

• 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_HOM
  E\ODP.NET\Samples directory.

Performance tip
13
Visual Studio .NET Integration

• Dynamic Help
• ODP.NET documentation available Hit F1 key
• Intellisense
• Connection pooling is enabled in ODP.NET (by
  default).

14
ODP.NET Connection Options

• Proxy user authentication
• OracleConnection con new OracleConnection()
• con.ConnectionString "User IdcustomerPassword
  lion"
• "Data SourceoracleProxy User IdappserverProxy
  Passwordeagle "
• con.Open()
• Operating System Authentication
• OracleConnection con new OracleConnection()
• con.ConnectionString "User Id/Data
  Sourceoracle"
• con.Open()

Performance tip
15
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 IdscottPasswordtig
  erData Sourceoracle"
• "Min Pool Size5Max Pool Size20Connection
  Lifetime120
• Connection Timeout60 Incr Pool Size5 Decr
  Pool Size2"
• 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
16
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

17
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

18
Performance

• Use Anonymous PL/SQL blocks.

19
FAQ

• Q Can ODP.NET work with Oracle8, Oracle8i,
  Oracle9i, and Oracle10g database (DB) servers?
• Yes, but you will need to use an Oracle9i Release
  2 client or higher

20
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.

21
Thank you.