Transcript ODP.NET - Oracle

DEV 301
Oracle Developer Tools for Visual
Studio .NET
Christian Shay, Oracle Corporation
Alex Keh, Oracle Corporation
Agenda
Introduction
Oracle Developer Tools for VS.NET
.NET Stored Procedures Demo
Oracle Data Provider for .NET New Features
ODP.NET demo – DB Change Notification
Windows Development Environment
Support
Oracle Developer
Tools for VS.NET
Visual Studio
Environment
Develop
C++, C#,
VB .NET
Application
Deploy
IIS
MTS/
(ASP
COM+
ASP.NET)
Application
Deploy
IIS
Database
Development
DB
Oracle DB
Extensions
For .NET
ODP.NET
Oracle Services for MTS
Agenda
Introduction
Oracle Developer Tools for VS.NET
.NET Stored Procedures Demo
Oracle Data Provider for .NET New Features
ODP.NET demo – DB Change Notification
Oracle Developer Tools for Visual
Studio .NET
Tightly integrated “Add-in” for Visual Studio
.NET
Oracle is a premier-level partner in MS Visual
Studio Industry Partner Program (VSIP)
Available for free download now
http://otn.oracle.com/dotnet
Develop on Windows, Database on
any platform
Oracle Developer
Tools for VS.NET
Visual Studio
Environment
Windows
Linux
Unix
Windows
Other
Oracle 10g, 9i, or 8i
Benefits
Easy to learn and intuitive
Leverage your SQL Server experience
Powerful features that enhance productivity –
e.g. designers, code generation
Exposes native Oracle functionality not typically
available in generic tools
Stay in Visual Studio for more of your project’s
lifecycle
Feature Overview
Oracle Explorer – browse and alter schema
Wizards & Designers
Automatic Code Generation
PL/SQL Editor with intellisense
Oracle Data Window
Oracle Query Window (Ad Hoc SQL)
Integrated Help System – SQL, PL/SQL Keywords
demo
Oracle Developer Tools
Oracle Explorer
Tree control, similar to “Server Explorer”
View Oracle schema objects
Generate SQL for schema objects
Filter hides unwanted schema objects
A starting place for most tasks
View, alter, create schema objects
Auto generate code
View and edit Oracle data
Run stored procedures and functions
Designers and Wizards
Table Designer
View Designer
Stored Procedure/Function Wizard
Package Wizards
Sequence Designer
Synonym Designer
Trigger Designer
Data Adapter Wizard
Automatic code generation
Drag and drop a schema object from Oracle
explorer
Generated code uses OracleDataAdapter class
(ODP.NET)
Data Adapter Wizard can be run to customize
Generate “typed datasets” as needed
PL/SQL Code Editor
Syntax Coloring
Collapsible regions
Intellisense
Context sensitive help for SQL and PLSQL
Keywords and Datatypes
Intelligent Error display in task list
Context sensitive help for Oracle error numbers
Data Window
View and Update Table data
Run Stored procedures and view data
Supports Oracle datatypes
View complex data types such as Ref Cursors
Oracle Query Window
Execute “Ad Hoc” SQL
Drag and drop objects from Oracle Explorer to
generate SQL
Result of select statements in choice of grid or
text format
Execute multiple statements in a row
Run scripts (“@myscript.sql”)
Integrated Online Help
Getting started
Walkthroughs
Oracle Developer Tools Reference Guide
Context sensitive help for UI elements
Context sensitive SQL and PLSQL keywords
Oracle Error Manual
Oracle SQL Reference Guide
Oracle PL/SQL Reference Guide
Coming Soon
PL/SQL Debugging
Many other features…..
Agenda
Introduction
Oracle Developer Tools for VS.NET
.NET Stored Procedures Demo
Oracle Data Provider for .NET New Features
ODP.NET demo – DB Change Notification
Oracle Database Extensions for
.NET
Oracle 10g Release 2 on Windows
Supports C#, VB.NET, C++ Stored Procedures
Build assembly in Visual Studio .NET
Deploy from Visual Studio .NET using
Deployment Wizard
Write .NET Stored Procedures for Oracle
Oracle Developer
Tools for VS.NET
Client
Application
Visual Studio
Environment
Develop
Invoke
C#,VB.NET,C++
Server Project
(UseODP.NET
If required)
Build
Invoke
Deploy
Assembly
foo.dll
(proc sp)
Stored Proc
Stored
Procedure,
SQL ..
Oracle DB
Extensions
for .NET
Creating a .NET SP
1) Use Visual Studio .NET to build a NET
procedure or function in the usual way
2) If data access is required, use ODP.NET
classes
3) Result is a .NET Assembly
4) Run .NET Stored Procedure Deployment
Wizard
5) Call .NET SP as you would any other SP
demo
Creating and Deploying a .NET
Stored Procedure into Oracle
Agenda
Introduction
Oracle Developer Tools for VS.NET
.NET Stored Procedures Demo
Oracle Data Provider for .NET New Features
ODP.NET demo – DB Change Notification
ODP.NET 10g Release 2 Themes
Better ease of use
More flexibility
Access to advanced Oracle DB features
Faster performance
ODP.NET - RAC and Grid
Automatic connection cleanup
ODP.NET frees connections to bad nodes
automatically
Set connection pool parameter
“HA events = true”
Works with 10gR2 RAC
Runtime connection load balancing
Connections divided among nodes based on
metrics and admin policies
Set connection pool parameter:
“load balancing = true”
Works with 10gR2 RAC
ODP.NET - DB Change Notification
When a DB data change occurs, client app
using the data is notified
Change in a query result set, schema objects,
or the state of the database
Works with 10gR2 DB
Properties
Create/Remove DB change notifications
Group multiple notifications into one
registration
Persist notifications on DB server
Change Notification Process
1. When command is executed, the
notification registration is created in the DB
2. ODP.NET starts the application listener
upon notification registration
3. When a change occurs, the application is
notified through either
Callback - event delegate assigned to the
OracleDependency.OnChange event property
Polling - OracleDependency.HasChanges
property
Setting up ODP.NET Change
Notification
1. Create OracleDependency
2. Assign event handler to
OracleDependency.OnChange event property
Event handler invoked when the change notification is
received
3. Set the port for the listener to listen on
4. Bind the OracleDependency instance to the
OracleCommand with the query
Internally, the DB change notification request is created
and assigned to the OracleCommand.Notification
property
demo
DB Change Notification
ODP.NET - Connection Pool
Management
Explicitly refresh connections in a pool or
pools
Clear connections from a pool or pools
One pool - Execute
OracleConnection.ClearPool method
All pools – Execute
OracleConnection.ClearAllPools method
Works with 10gR2 and earlier DBs
ODP.NET - LOB (and LONG)
Retrieval
Faster performance
Fewer DB round trips for LOB retrieval
Transparent to developer
Works with 10gR2 DB
More tunable and usable
Full LOB functionality when
InitialLOBFetchSize > 0
GetOracleBlob() and GetOracleClob() methods in
OracleDataReader supported
InitialLOBFetchSize can be up to 2GB
Works with 10gR2 DB
ODP.NET - Security
Application security context
ODP.NET can set the Client Identifier in the
Application Context for every database
session
Similar to proxy user authentication, but
requires only one session and no pre-created
proxy user
Set OracleConnection.ClientIdentifier = “gold
customer”
Works with 10gR2 and earlier DBs
ODP.NET - Additional Features
Input REF Cursors into PL/SQL
Only works when REF Cursor is output from
PL/SQL first
Works with 10gR2 DB
ODP.NET works with .NET Stored
Procedures
Additional ADO.NET 2.0 features for
ODP.NET in 2nd half 2005
Next Steps
Come to the Oracle booth (Exhibition Hall Stand A21) for
a one on one demonstration with .NET experts
Next Session: SVR340 Oracle on Windows
Thu Jul 7 10:15 - 11:30 Room: 2c
.NET Developer Center - free downloads, demos,
walkthroughs, and whitepapers
http://otn.oracle.com/dotnet
Oracle Developer Tools & .NET Stored Proc questions?
Email: [email protected]
Oracle Data Provider (ODP.NET) questions? Email:
[email protected]