O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Pro ODP .NET for Oracle Database 11g

Pro ODP .NET for Oracle Database 11g

1 review
by Edmund Zehoo
Publisher: Apress
Release Date: April 2010
ISBN: 9781430228202
Topics:
View table of contents
Start reading

Book Description

This book is a comprehensive and easy-to-understand guide for using the Oracle Data Provider (ODP) version 11g on the .NET Framework.

Table of Contents

  1. Copyright
  2. About the Author
  3. About the Technical Reviewer
  4. Acknowledgments
  5. 1. Introduction to Oracle .NET Connectivity
    1. 1.1. Making the Transition from SQL Server to Oracle
    2. 1.2. Introducing Oracle Connectivity
    3. 1.3. Accessing Oracle from Unmanaged Code
      1. 1.3.1. Using OLEDB in Unmanaged Code
        1. 1.3.1.1. Oracle Provider for OLEDB
        2. 1.3.1.2. MS OLEDB Provider for Oracle
      2. 1.3.2. Using ODBC in Unmanaged Code
        1. 1.3.2.1. Oracle ODBC Driver
        2. 1.3.2.2. Microsoft ODBC for Oracle
      3. 1.3.3. Using OO4O in Unmanaged Code
    4. 1.4. Accessing Oracle from Managed Code
      1. 1.4.1. Using OLEDB.NET in Managed Code
      2. 1.4.2. Using ODBC.NET in Managed Code
      3. 1.4.3. Using the Microsoft .NET Managed Provider for Oracle
    5. 1.5. Introducing ODP.NET
      1. 1.5.1. Understanding the ODP.NET Architecture
      2. 1.5.2. Understanding the ODP.NET Classes
      3. 1.5.3. Accessing Data using ODP.NET
      4. 1.5.4. Using ODP.NET in ASP.NET Projects
      5. 1.5.5. Considering ODP.NET Performance
    6. 1.6. Introducing the Oracle Suite of Products
    7. 1.7. Summary
  6. 2. ODP.NET: A Functional Overview
    1. 2.1. Exploring Oracle Features Accessible in ODP.NET Version 9
      1. 2.1.1. Manipulating XML
      2. 2.1.2. Manipulating LOBs
      3. 2.1.3. Using PL/SQL Associative Array Binding
      4. 2.1.4. Supporting Active Data Objects (ADO.NET) 2.0
    2. 2.2. Accessing Oracle Features from ODP.NET Version 10
      1. 2.2.1. Supporting Oracle Grids
      2. 2.2.2. Supporting Multiple Oracle Homes
      3. 2.2.3. Using Floating Point Data Types
      4. 2.2.4. Using Statement Caching
      5. 2.2.5. Supporting Command Cancellation and Timeout
      6. 2.2.6. Retrieving Parameters Programmatically
      7. 2.2.7. Supporting .NET Stored Procedures
      8. 2.2.8. Using Client Identifiers
      9. 2.2.9. Using Database Change Notifications
      10. 2.2.10. Managing Connection Pools
      11. 2.2.11. Optimizing Connection Pools for RAC
      12. 2.2.12. Using a REF Cursor as an IN/OUT Parameter
      13. 2.2.13. Using 64-bit ODP.NET
      14. 2.2.14. Controlling the FetchSize Property
      15. 2.2.15. Configuring ODP.NET
    3. 2.3. Accessing Oracle Features from ODP.NET Version 11
      1. 2.3.1. Enhancing Performance
      2. 2.3.2. Deploying ODP.NET Using xcopy
      3. 2.3.3. Supporting Oracle User Defined Types (UDTs)
      4. 2.3.4. Performing Bulk Copy Operations
      5. 2.3.5. Using Windows Authenticated User Connections Pooling
      6. 2.3.6. Publishing Connection Pool Performance Counters
      7. 2.3.7. Supporting Self-Tuning for Applications
      8. 2.3.8. Using Oracle Streaming AQ
      9. 2.3.9. Supporting Promotable Local Transactions
      10. 2.3.10. Using ODP.NET Security Enhancements
      11. 2.3.11. Running Callbacks for HA Event Notifications
      12. 2.3.12. Starting Up and Shutting Down Databases
    4. 2.4. Getting Started
      1. 2.4.1. Installing Oracle Database 11g
      2. 2.4.2. Installing ODAC.NET
    5. 2.5. Summary
  7. 3. Connecting to Oracle with ODP.NET
    1. 3.1. Connecting via TNS
    2. 3.2. Understanding the TNSNames.ora file
    3. 3.3. Connecting in Other Ways
      1. 3.3.1. Connecting Without TNSNames.ora
      2. 3.3.2. Connecting via EZConnect
    4. 3.4. Learning the ODP.NET Connection Parameters
      1. 3.4.1. Connecting with Connection Pooling Activated
      2. 3.4.2. Connecting via Integrated Windows Authentication
      3. 3.4.3. Connecting with Special Privileges
      4. 3.4.4. Using Other Connection String Attributes
    5. 3.5. Checking Whether ODP.NET Is Installed
    6. 3.6. Dynamically Building an ODP.NET Connection String
      1. 3.6.1. Using the OracleConnectionStringBuilder Class
      2. 3.6.2. Retrieving Available Oracle Data Sources
    7. 3.7. Understanding Transparent Application Failover
      1. 3.7.1. Enabling TAF in Your Application
      2. 3.7.2. Using TAF Callbacks
    8. 3.8. Summary
  8. 4. Retrieving and Manipulating Data with ODP.NET
    1. 4.1. Understanding ODP.NET Data Types
    2. 4.2. Creating a Sample Table
    3. 4.3. Retrieving Multiple Rows of Data
    4. 4.4. Retrieving a Single Value
    5. 4.5. Handling NULL Values in ODP.NET
    6. 4.6. Retrieving Data into a Dataset
    7. 4.7. Using Parameterized Queries
    8. 4.8. Updating Data
      1. 4.8.1. Executing a Single INSERT, UPDATE, or DELETE Statement
      2. 4.8.2. Committing Dataset Changes to the Database
      3. 4.8.3. Generating Command Objects
    9. 4.9. Handling Master-Detail Relationships
      1. 4.9.1. Creating a Second Table
      2. 4.9.2. Retrieving from Multiple Tables
      3. 4.9.3. Binding a .NET Form to Your Dataset
      4. 4.9.4. Committing Changes to Multiple Tables
      5. 4.9.5. Defining Table Relationships and Constraints in a DataSet
    10. 4.10. Manipulating LOBs and BFILEs
      1. 4.10.1. Uploading BLOB Data
      2. 4.10.2. Retrieving BLOB Data
      3. 4.10.3. Inserting CLOB/NCLOB Data
      4. 4.10.4. Retrieving CLOB/NCLOB Data
      5. 4.10.5. Creating BFILE Directory Mappings
      6. 4.10.6. Inserting BFILE Data
      7. 4.10.7. Retrieving BFILE Data
    11. 4.11. Manipulating RAW Data Types
    12. 4.12. Creating Automatically Incrementing Columns
    13. 4.13. Executing DDL from ODP.NET
    14. 4.14. Discovering Schema in ODP.NET
    15. 4.15. Handling ODP.NET Exceptions
    16. 4.16. Summary
  9. 5. Using PL/SQL and .NET CLR Stored Procedures with ODP.NET
    1. 5.1. Understanding the Basics of PL/SQL
    2. 5.2. Working with Anonymous PL/SQL Blocks
      1. 5.2.1. Executing an Anonymous PL/SQL Block
      2. 5.2.2. Passing Data into an Anonymous Block
      3. 5.2.3. Returning Data from an Anonymous Block
    3. 5.3. Working with PL/SQL Stored Procedures
      1. 5.3.1. Executing a PL/SQL Stored Procedure
      2. 5.3.2. Passing Data into a PL/SQL Stored Procedure
      3. 5.3.3. Retrieving Data from a PL/SQL Stored Procedure
    4. 5.4. Executing a PL/SQL Function
    5. 5.5. Handling Special IN and OUT Data Types
      1. 5.5.1. Using Associative Arrays
        1. 5.5.1.1. Passing Associative Arrays to PL/SQL Code
        2. 5.5.1.2. Retrieving Associative Arrays from PL/SQL Code
      2. 5.5.2. Using VARRAYs
      3. 5.5.3. Using Nested Tables
      4. 5.5.4. Using REF Cursors
        1. 5.5.4.1. Reading a Result Set from a REF Cursor Using the OracleDataReader
        2. 5.5.4.2. Reading a Result Set from a REF Cursor Using the OracleDataAdapter
      5. 5.5.5. Retrieving Multiple Active Result Sets
      6. 5.5.6. User Defined Types (UDT) / OBJECT Types
    6. 5.6. Handling Custom-Defined PL/SQL Errors
    7. 5.7. Creating Your First .NET CLR Stored Procedure
      1. 5.7.1. Deploying a .NET CLR Stored Procedure
      2. 5.7.2. Executing the .NET CLR Stored Procedure
    8. 5.8. Summary
  10. 6. ODP.NET Globalization
    1. 6.1. Storing and Retrieving Double-Byte Data
    2. 6.2. Using the OracleGlobalization class
      1. 6.2.1. Setting Attributes at the Client Level
      2. 6.2.2. Setting Attributes at the Session Level
      3. 6.2.3. Setting Attributes at the Thread Level
    3. 6.3. Changing the Session Language
    4. 6.4. Formatting Calendar Dates
      1. 6.4.1. Displaying Various Date Formats and Languages
      2. 6.4.2. Designating Calendar Systems
    5. 6.5. Representing Currencies
    6. 6.6. Formatting Numbers
    7. 6.7. Dealing with Time Zones
    8. 6.8. Sorting and Comparing Strings
    9. 6.9. Applying Country-Based Formatting
    10. 6.10. Safely Mapping to .NET Data Types
    11. 6.11. Summary
  11. 7. Transactions with ODP.NET
    1. 7.1. Understanding Transactions and the OracleTransaction Class
      1. 7.1.1. Executing Your First Transaction
      2. 7.1.2. Executing Stored Procedures in a Transaction
    2. 7.2. Performing Partial Rollbacks
    3. 7.3. Working with Distributed Transactions
      1. 7.3.1. Creating a Second Database Instance
      2. 7.3.2. Executing Implicit Distributed Transactions
      3. 7.3.3. Executing Explicit Distributed Transactions
      4. 7.3.4. Executing Promotable Transactions
    4. 7.4. Summary
  12. 8. Oracle Database Change Notifications with ODP.NET
    1. 8.1. Understanding Database Change Notification
    2. 8.2. Registering for Query-Based Change Notifications
    3. 8.3. Registering for Object-Based Change Notifications
    4. 8.4. Grouping Multiple Notification Requests
    5. 8.5. Removing a Registration
    6. 8.6. Retrieving Change Notification Information
    7. 8.7. Choosing to Poll
    8. 8.8. Considering Typical Usage Scenarios
    9. 8.9. Thinking About Performance
    10. 8.10. Summary
  13. 9. Using Oracle Database Streams Advanced Queuing with ODP.NET
    1. 9.1. Understanding the Basics of AQ
    2. 9.2. Creating a Single-Consumer Queue
      1. 9.2.1. Setting Up a Single-Consumer Queue
      2. 9.2.2. Enqueuing and Dequeuing a Single Message
      3. 9.2.3. Enqueuing and Dequeuing Multiple Messages
    3. 9.3. Creating a Multiple-Consumer Queue
      1. 9.3.1. Defining Recipients at the Queue Level
      2. 9.3.2. Defining Recipients at the Message Level
    4. 9.4. Enqueuing and Dequeuing Various Data Types in AQ
      1. 9.4.1. Using UDT Data Types
      2. 9.4.2. Using XML Data Types
    5. 9.5. Waiting for Incoming Messages
      1. 9.5.1. Dequeuing Messages Synchronously (Blocking)
      2. 9.5.2. Dequeuing Messages Asynchronously (Nonblocking)
    6. 9.6. Understanding the Useful OracleAQMessage Properties
    7. 9.7. Summary
  14. 10. Oracle XML Support
    1. 10.1. Accessing Native XML Data (XMLTYPE)
      1. 10.1.1. Creating an XMLTYPE Column
      2. 10.1.2. Receiving XMLTYPE Data with XMLReader
      3. 10.1.3. Receiving XMLTYPE Data with OracleXMLType
      4. 10.1.4. Receiving XMLTYPE Data as a String
    2. 10.2. Passing XML Data to and from PL/SQL Stored Procedures
    3. 10.3. Validating Against XML Schema
    4. 10.4. Using XSLT to Transform XML Data
    5. 10.5. Retrieving Relational Data as XML
      1. 10.5.1. Using the XMLCommandType property
      2. 10.5.2. Using the Dataset.GetXML Method
      3. 10.5.3. Using the DBMS_XMLGEN.GETXML Stored Procedure
    6. 10.6. Manipulating Relational Data as XML
      1. 10.6.1. Inserting Relational Data Using XML
      2. 10.6.2. Updating Relational Data Using XML
      3. 10.6.3. Deleting Relational Data Using XML
    7. 10.7. Using XQuery to Query Data
    8. 10.8. Summary
  15. 11. ODP.NET Security Features
    1. 11.1. Securing Your .NET Applications
    2. 11.2. Authenticating Data
      1. 11.2.1. Implementing Username/Password Authentication
      2. 11.2.2. Implementing Proxy Authentication
      3. 11.2.3. Implementing ClientId-Based Username/Password Authentication
      4. 11.2.4. Implementing Windows Authentication
    3. 11.3. Understanding Code Access Security
      1. 11.3.1. Using Code Groups
      2. 11.3.2. Using Permission Sets
      3. 11.3.3. Resolving Permissions in .NET
      4. 11.3.4. Seeing CAS in Action
    4. 11.4. Configuring CAS Policies
      1. 11.4.1. Configuring CAS Policies via the GUI
      2. 11.4.2. Configuring CAS Policies Programmatically
    5. 11.5. Requesting Permissions
      1. 11.5.1. Requesting Permissions Declaratively
      2. 11.5.2. Requesting Permissions Imperatively
    6. 11.6. Ensuring That an Assembly Can Never Access Oracle
      1. 11.6.1. Refusing Permissions Declaratively at the Assembly Level
      2. 11.6.2. Denying Permissions Declaratively at the Method Level
      3. 11.6.3. Denying Permissions Imperatively at the Method Level
    7. 11.7. Using CAS with ASP.NET Applications
    8. 11.8. Implementing Best Practices
      1. 11.8.1. Preventing SQL Injection Attacks
      2. 11.8.2. Preventing Nonpersistent Cross-Site Scripting Attacks
    9. 11.9. Summary
  16. 12. ODP.NET Performance
    1. 12.1. Measuring Performance
      1. 12.1.1. Enabling the Performance Counters
      2. 12.1.2. Measuring Performance Programmatically
    2. 12.2. Speeding Up Connections with Connection Pooling
    3. 12.3. Performing Faster Floating Point Arithmetic
    4. 12.4. Executing Statements Faster
      1. 12.4.1. Batching Your SQL Statements Together For Execution
      2. 12.4.2. Using Statement Caching
      3. 12.4.3. REF Cursors and Multiple Active Resultsets (MARs)
    5. 12.5. Passing Parameters More Efficiently
      1. 12.5.1. Using Bind Arrays to Pass Parameters in Bulk
      2. 12.5.2. Using PL/SQL Associative Arrays
    6. 12.6. Managing LOBs More Efficiently
      1. 12.6.1. Enabling the LOB Cache
      2. 12.6.2. Setting the InitialLOBFetchSize Property
    7. 12.7. Retrieving Data More Efficiently
      1. 12.7.1. Changing the FetchSize Property
      2. 12.7.2. Using the Client Result Cache
    8. 12.8. Importing Data More Efficiently
    9. 12.9. Applying Optimization Best Practices
      1. 12.9.1. Using Stored Procedures Whenever Possible
      2. 12.9.2. Using the Right Data Access Object
    10. 12.10. Summary
  17. 13. Design Patterns and Considerations in Using ODP.NET
    1. 13.1. Programming to an Interface Instead of an Implementation
    2. 13.2. Using the Data Access Object
    3. 13.3. Using Microsoft's Enterprise Library
    4. 13.4. Creating Oracle.NET
      1. 13.4.1. Creating the ODP.NET DAO Class
      2. 13.4.2. Modifying the DBProviderMapping Class
      3. 13.4.3. Modifying the DatabaseConfigurationView Class
      4. 13.4.4. Compiling the New DAAB
    5. 13.5. Using the ODP.NET DAO
      1. 13.5.1. Editing the Application Configuration File
      2. 13.5.2. Accessing Data via the DAAB
        1. 13.5.2.1. Reading Multiple Rows into a Dataset
        2. 13.5.2.2. Updating Multiple Rows from a Dataset
        3. 13.5.2.3. Reading Data Using a DataReader
    6. 13.6. Considering Best Practices
      1. 13.6.1. Planning for Multiple Data Sources
      2. 13.6.2. Keeping Provider-Specific Code Within the Data Tier
      3. 13.6.3. Outputting Business Objects, Not Datasets
      4. 13.6.4. Deciding How to Map Data Source Structures to Business Objects
      5. 13.6.5. Deciding How to Manage Data Source Settings
    7. 13.7. Summary
  18. 14. ODT.NET Tool Basics
    1. 14.1. Installing ODT.NET
    2. 14.2. Managing the Database Schema
    3. 14.3. Managing Users, Roles, and Object Privileges
    4. 14.4. Editing and Debugging PL/SQL Code
      1. 14.4.1. Creating a PL/SQL Procedure
      2. 14.4.2. Creating a PL/SQL Package
      3. 14.4.3. Debugging PL/SQL stored procedures
    5. 14.5. Managing Oracle SQL Scripts
    6. 14.6. Managing Advanced Queues
    7. 14.7. Importing Tables and Data from External Data Sources
    8. 14.8. Summary
  19. 15. Building Data-Driven Applications with ODT.NET
    1. 15.1. Designing Queries Visually
    2. 15.2. Generating Strongly Typed DataSet Objects
    3. 15.3. Generating UDT Classes
      1. 15.3.1. Designing a UDT Object Visually
      2. 15.3.2. Creating the UDT Object Table Visually
      3. 15.3.3. Creating the OVERSEAS_JOBS Table
      4. 15.3.4. Generating the UDT Classes
      5. 15.3.5. Using the UDT in Your Project
    4. 15.4. Generating ASP.NET Code
    5. 15.5. Summary