Transcript Jerry Held - New York Oracle User Group

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 20K 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.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
• 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
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
ODP.NET Object Model
Disconnected
Layer
DataSet
Connected Layer
(ODP.NET)
Oracle
DataAdapter
Data
Layer
OracleCommand
Builder
Oracle
DataReader
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,
TimeStamps, 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.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.
* Performance tip
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.
* Performance tip
•
Connections are closed every three minutes (connection Lifetime..)
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,
Oracle8i, Oracle9i, and Oracle10g
database (DB) servers?
• Yes, but you will need to use an
Oracle9i 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.”