ODP NET Oct 2004 Access Oracle from NET

ODP. NET

Oct 2004 Access Oracle from. NET Using C# Alex Hoyos

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 20 K feet high view…

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

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. Data. Access. dll Namespaces: Oracle. Data. Access. Client (e. g. Oracle. Connection) Oracle. Data. Access. Types (e. g. Oracle. Clob 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 (Data. Direct) • 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 Oracle 8, Oracle 8 i, Oracle 9 i, and Oracle 10 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 Disconnected Layer Data. Set Connected Layer (ODP. NET) Oracle Data. Adapter Data Layer Oracle. Command Builder Oracle Data. Reader Oracle Command Oracle Transaction Oracle Connection Oracle

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, Time. Stamps, Longs, Raws, etc. – Safe type mapping to. NET data types • *Connection pooling – Min, Max, Timeout, Lifetime, Increment, Decrement * Performance tip

ODP. NET Installation • • The Oracle. Data. Access. dll assembly is installed in the ORACLE_ BASEORACLE_HOMEbin directory. Documentation and the readme. txt are installed in the ORACLE_BASEORACLE_HOMEODP. NETdoc directory. Samples are provided in the ORACLE_BASEORACLE_HOMEODP. NETSamples directory. * Performance tip

Visual Studio. NET Integration • Dynamic Help – ODP. NET documentation available – Hit F 1 key • Intellisense • Connection pooling is enabled in ODP. NET (by default).

ODP. NET Connection Options • Proxy user authentication Oracle. Connection con = new Oracle. Connection(); con. Connection. String = "User Id=customer; Password=lion; " + "Data Source=oracle; Proxy User Id=appserver; Proxy Password=eagle; "; con. Open(); • Operating System Authentication Oracle. Connection con = new Oracle. Connection(); con. Connection. String = "User Id=/; Data Source=oracle; "; con. Open(); * Performance tip

ODP. NET Connection – Connection pools are created by the connection pooling service using the Connection. String as a signature to uniquely identify a pool. Oracle. Connection con = new Oracle. Connection(); con. Connection. String = "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. Web. Ex 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. Oracle. Connection) – 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 Fetch. Size (Oracle. Data. Reader) and Row. Size (Oracle. Command) to tune data retrieval performance • Use Initial. LOBFetch. Size and Initial. Long. Fetch. Size to tune LOB and LONG retrieval performance

Performance • Use Anonymous PL/SQL blocks.

FAQ • Q: Can ODP. NET work with Oracle 8, Oracle 8 i, Oracle 9 i, and Oracle 10 g database (DB) servers? • Yes, but you will need to use an Oracle 9 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 middletier machine, wherever you use the. NET Framework.

“Thank you. ”