ODP.NET Developer’s Guide电子书

ODP.NET Developer's Guide

Table of Contents

ODP.NET Developer's Guide

Credits

About the Authors

About the Reviewer

Preface

What This Book Covers

Conventions

Reader Feedback

Customer Support

Downloading the Example Code for the Book

Errata

Questions

1. Introduction to ODP.NET

Introduction to ODP.NET

Why Use ODP.NET?

Oracle Database Access from .NET Applications

What Do We Require to Work with ODP.NET?

Introduction to Oracle Database Extensions for .NET

Oracle Database Extensions for .NET

How does .NET Work within Oracle Database?

Processing of .NET Stored Procedure with Oracle

Introduction to Oracle Developer Tools for Visual Studio

Summary

2. Connecting to Oracle

Provider-Independent Model in ADO.NET 2.0

Listing All Installed .NET Data Providers

Enumerating all Oracle Data Sources Available

Connecting to Oracle Databases from .NET

Connecting Using .NET Data Provider Factory Classes

Connecting Using .NET Data Provider for OLEDB

Connecting Using .NET Data Provider for ODBC

Connecting using Microsoft's .NET Data Provider for Oracle

Connecting Using Oracle Data Provider for .NET (ODP.NET)

Connecting with Connection Pooling

Connecting with System-Level Privileges or DBA Privileges

Dynamic Connecting String Using OracleConnectionStringBuilder and app.config

Embedding a "tnsnames.ora" Entry-like Connection String

Connecting to a Default Oracle Database

Connecting Using Windows Authentication (Single Sign‑On)

Summary

3. Retrieving Data from Oracle Using ODP.NET

Fundamental ODP.NET Classes to Retrieve Data

Retrieving Data Using OracleDataReader

Retrieving a Single Row of Information

Using "Using" for Simplicity

Retrieving Multiple Rows on to the Grid

Pulling Information Using Table Name

Retrieving Typed Data

Retrieving Typed Data Using Ordinals

Retrieving Typed Data Using Column Names

Working with Data Tables and Data Sets

Retrieving Multiple Rows into a DataTable Using OracleDataAdapter

Filling a DataTable Using OracleDataReader

Retrieving a Single Row of Information Using OracleDataAdapter

Working with DataTableReader

Populating a Dataset with a Single Data Table

Populating a Dataset with Multiple Data Tables

Presenting Master-Detail Information Using a Dataset

More About the OracleCommand Object

Retrieving a Single Value from the Database

Handling Nulls when Executing with ExecuteScalar

Handling Nulls when Working with OracleDataReader

Working with Bind Variables together with OracleParameter

Working with OracleDataAdapter together with OracleCommand

Techniques to Improve Performance while Retrieving Data

Summary

4. Manipulating Data in Oracle Using ODP.NET

Executing DML or DDL Statements Using OracleCommand

Using INSERT with OracleCommand

Using UPDATE with OracleCommand

Using DELETE with OracleCommand

Multiple Inserts Using Statement Caching

Multiple Inserts Using Array Binding

Creating an Oracle Table Dynamically Using ODP.NET

Updating Offline Data to the Database Using OracleDataAdapter

Working with OracleCommandBuilder and OracleDataAdapter

Working with Transactions Using ODP.NET

Handling Oracle Errors and Exceptions

Displaying a Single or First Error

Displaying Multiple Errors

Summary

5. Programming ODP.NET with PL/SQL

Working with Anonymous PL/SQL Blocks

Executing Anonymous PL/SQL Blocks

Passing Information to Anonymous PL/SQL Blocks

Retrieving Information from Anonymous Blocks

Working with PL/SQL Stored Procedures and Functions

Executing a PL/SQL Stored Procedure

Passing Parameter Values to a PL/SQL Stored Procedure

Using an Anonymous PL/SQL Block to Execute a PL/SQL Stored Procedure

Retrieving Output Parameters from a PL/SQL Stored Procedure

Passing IN and Getting OUT Simultaneously

Handling User-Defined Application Errors

Executing a PL/SQL User-Defined Function

PL/SQL Packages, Tables, and REF CURSOR

Executing Routines in a PL/SQL Package

Executing a Procedure in a PL/SQL Package

Executing a User-Defined Function in a PL/SQL Package

Passing Arrays to and Receiving Arrays from Oracle Database

Sending an Array to Oracle Database

Receiving an Array from Oracle Database

Working with REF CURSOR Using ODP.NET

Pulling from REF CURSOR Using OracleDataReader

Filling a Dataset from REF CURSOR

Working with Multiple Active Result Sets (MARS)

Summary

6. Dealing with Large Objects (LOBs)

Working with BFILEs

Setting Up the Environment to Work with BFILEs

Adding a New Row Containing BFILE

Updating an Existing BFILE Row

Retrieving BFILE Information from a Database

Retrieving Properties of a BFILE

Working with CLOBs

Inserting Huge Text Information into Oracle Database

Updating CLOB Information Using OracleClob

Retrieving CLOB Information from Oracle Database

Reading a Text File and Uploading as CLOB

Working with BLOBs

Setting Up the Environment to Work with BLOBs

Uploading Images to Oracle Database Using BLOB

Retrieving Images from Oracle Database Using BLOB

Uploading Documents to and Retrieving Documents from Oracle Database

Summary

7. XML and XML DB Development with ODP.NET

A Fast Track on XML with Oracle

Generating XML from Existing Rows in Tables

Generate XML Using ADO.NET DataSet

Generate XML Using ExecuteXMLReader

Generate XML Using DBMS_XMLGEN

Converting Rows to HTML Using XML and XSLT

Manipulating Rows in a Table Using XML

Inserting Rows into Oracle Using XML

Updating Rows into Oracle Using XML

Working with Native XML in Oracle Database

Inserting XML Data into XMLType Using Traditional INSERT

Updating XML Data in XMLType Using Traditional UPDATE

Inserting XML Data Using OracleXmlType

Retrieving and Updating XML Data Using OracleXmlType

Extracting Individual Node Information of an XMLType Value

Summary

8. Application Development Using ODP.NET

Notifying Applications of Database Changes

Catching Notifications

Catching Multiple Notifications

Identifying Rows Modified During Notifications

Developing Long-Running Applications

The Devil of Applications: "Not Responding"

Asynchronous Task with Multi-Threading

Developing Web Applications Using ASP.NET and ODP.NET

Web Development Using Smart Data Binding

Populating an ASP.NET DropDownList Control

Linking an ASP.NET GridView Control with a DropDownList Control

Add, Update, or Delete a Row Using GridView and FormView

Working with Web Controls Manually

Developing Web Reports Using ASP.NET

Creating a Strongly-Typed Dataset Using Designer

Designing and Binding a Report to the Dataset

Grouping and Displaying Sub-Totals

Embedding Charts (Graphs) in Reports

Object-Oriented Development Using ASP.NET and ODP.NET

Developing a Simple Oracle Database Helper Class

Developing a Simple Business Logic Class

Working with ObjectDataSource in an ASP.NET 2.0 Web Form

Developing Web Services Using ODP.NET

Creating the .NET XML Web Service

Consuming the Web Service from ASP.NET

Developing Smart Device Applications

Introducing Microsoft Windows Mobile

Consuming a Web Service from Pocket PC

Summary

9. Introduction to Oracle Developer Tools for Visual Studio 2005

Features of Oracle Developer Tools

Connecting to Oracle from Visual Studio Using Oracle Explorer

Retrieving Oracle Information from Visual Studio Using ODT

Working with Oracle Database Objects from Visual Studio Using ODT

Dealing with Tables, Views, and Sequences Using ODT

Creating Stored Procedures Using ODT

Debugging PL/SQL Stored Procedures from Visual Studio

.NET CLR Stored Procedures in Oracle

Taking Advantage of Automatic .NET Code Generation

Summary

Index