book

Pro ODP .NET for Oracle Database 11g

Name: Pro ODP .NET for Oracle Database 11g
Author: Edmund Zehoo
ISBN: 9781430228202

by Edmund Zehoo

April 2010

Intermediate to advanced

468 pages

10h 31m

English

Apress

Read now

Unlock full access

Copyright
About the Author
About the Technical Reviewer
Acknowledgments
1. Introduction to Oracle .NET Connectivity
1.1. Making the Transition from SQL Server to Oracle1.2. Introducing Oracle Connectivity1.3. Accessing Oracle from Unmanaged Code1.3.1. Using OLEDB in Unmanaged Code1.3.1.1. Oracle Provider for OLEDB1.3.1.2. MS OLEDB Provider for Oracle1.3.2. Using ODBC in Unmanaged Code1.3.2.1. Oracle ODBC Driver1.3.2.2. Microsoft ODBC for Oracle1.3.3. Using OO4O in Unmanaged Code1.4. Accessing Oracle from Managed Code1.4.1. Using OLEDB.NET in Managed Code1.4.2. Using ODBC.NET in Managed Code1.4.3. Using the Microsoft .NET Managed Provider for Oracle1.5. Introducing ODP.NET1.5.1. Understanding the ODP.NET Architecture1.5.2. Understanding the ODP.NET Classes1.5.3. Accessing Data using ODP.NET1.5.4. Using ODP.NET in ASP.NET Projects1.5.5. Considering ODP.NET Performance1.6. Introducing the Oracle Suite of Products1.7. Summary
2. ODP.NET: A Functional Overview
2.1. Exploring Oracle Features Accessible in ODP.NET Version 92.1.1. Manipulating XML2.1.2. Manipulating LOBs2.1.3. Using PL/SQL Associative Array Binding2.1.4. Supporting Active Data Objects (ADO.NET) 2.02.2. Accessing Oracle Features from ODP.NET Version 102.2.1. Supporting Oracle Grids2.2.2. Supporting Multiple Oracle Homes2.2.3. Using Floating Point Data Types2.2.4. Using Statement Caching2.2.5. Supporting Command Cancellation and Timeout2.2.6. Retrieving Parameters Programmatically2.2.7. Supporting .NET Stored Procedures2.2.8. Using Client Identifiers2.2.9. Using Database Change Notifications2.2.10. Managing Connection Pools2.2.11. Optimizing Connection Pools for RAC2.2.12. Using a REF Cursor as an IN/OUT Parameter2.2.13. Using 64-bit ODP.NET2.2.14. Controlling the FetchSize Property2.2.15. Configuring ODP.NET2.3. Accessing Oracle Features from ODP.NET Version 112.3.1. Enhancing Performance2.3.2. Deploying ODP.NET Using xcopy2.3.3. Supporting Oracle User Defined Types (UDTs)2.3.4. Performing Bulk Copy Operations2.3.5. Using Windows Authenticated User Connections Pooling2.3.6. Publishing Connection Pool Performance Counters2.3.7. Supporting Self-Tuning for Applications2.3.8. Using Oracle Streaming AQ2.3.9. Supporting Promotable Local Transactions2.3.10. Using ODP.NET Security Enhancements2.3.11. Running Callbacks for HA Event Notifications2.3.12. Starting Up and Shutting Down Databases2.4. Getting Started2.4.1. Installing Oracle Database 11g2.4.2. Installing ODAC.NET2.5. Summary
3. Connecting to Oracle with ODP.NET
3.1. Connecting via TNS3.2. Understanding the TNSNames.ora file3.3. Connecting in Other Ways3.3.1. Connecting Without TNSNames.ora3.3.2. Connecting via EZConnect3.4. Learning the ODP.NET Connection Parameters3.4.1. Connecting with Connection Pooling Activated3.4.2. Connecting via Integrated Windows Authentication3.4.3. Connecting with Special Privileges3.4.4. Using Other Connection String Attributes3.5. Checking Whether ODP.NET Is Installed3.6. Dynamically Building an ODP.NET Connection String3.6.1. Using the OracleConnectionStringBuilder Class3.6.2. Retrieving Available Oracle Data Sources3.7. Understanding Transparent Application Failover3.7.1. Enabling TAF in Your Application3.7.2. Using TAF Callbacks3.8. Summary
4. Retrieving and Manipulating Data with ODP.NET
4.1. Understanding ODP.NET Data Types4.2. Creating a Sample Table4.3. Retrieving Multiple Rows of Data4.4. Retrieving a Single Value4.5. Handling NULL Values in ODP.NET4.6. Retrieving Data into a Dataset4.7. Using Parameterized Queries4.8. Updating Data4.8.1. Executing a Single INSERT, UPDATE, or DELETE Statement4.8.2. Committing Dataset Changes to the Database4.8.3. Generating Command Objects4.9. Handling Master-Detail Relationships4.9.1. Creating a Second Table4.9.2. Retrieving from Multiple Tables4.9.3. Binding a .NET Form to Your Dataset4.9.4. Committing Changes to Multiple Tables4.9.5. Defining Table Relationships and Constraints in a DataSet4.10. Manipulating LOBs and BFILEs4.10.1. Uploading BLOB Data4.10.2. Retrieving BLOB Data4.10.3. Inserting CLOB/NCLOB Data4.10.4. Retrieving CLOB/NCLOB Data4.10.5. Creating BFILE Directory Mappings4.10.6. Inserting BFILE Data4.10.7. Retrieving BFILE Data4.11. Manipulating RAW Data Types4.12. Creating Automatically Incrementing Columns4.13. Executing DDL from ODP.NET4.14. Discovering Schema in ODP.NET4.15. Handling ODP.NET Exceptions4.16. Summary
5. Using PL/SQL and .NET CLR Stored Procedures with ODP.NET
5.1. Understanding the Basics of PL/SQL5.2. Working with Anonymous PL/SQL Blocks5.2.1. Executing an Anonymous PL/SQL Block5.2.2. Passing Data into an Anonymous Block5.2.3. Returning Data from an Anonymous Block5.3. Working with PL/SQL Stored Procedures5.3.1. Executing a PL/SQL Stored Procedure5.3.2. Passing Data into a PL/SQL Stored Procedure5.3.3. Retrieving Data from a PL/SQL Stored Procedure5.4. Executing a PL/SQL Function5.5. Handling Special IN and OUT Data Types5.5.1. Using Associative Arrays5.5.1.1. Passing Associative Arrays to PL/SQL Code5.5.1.2. Retrieving Associative Arrays from PL/SQL Code5.5.2. Using VARRAYs5.5.3. Using Nested Tables5.5.4. Using REF Cursors5.5.4.1. Reading a Result Set from a REF Cursor Using the OracleDataReader5.5.4.2. Reading a Result Set from a REF Cursor Using the OracleDataAdapter5.5.5. Retrieving Multiple Active Result Sets5.5.6. User Defined Types (UDT) / OBJECT Types5.6. Handling Custom-Defined PL/SQL Errors5.7. Creating Your First .NET CLR Stored Procedure5.7.1. Deploying a .NET CLR Stored Procedure5.7.2. Executing the .NET CLR Stored Procedure5.8. Summary
6. ODP.NET Globalization
6.1. Storing and Retrieving Double-Byte Data6.2. Using the OracleGlobalization class6.2.1. Setting Attributes at the Client Level6.2.2. Setting Attributes at the Session Level6.2.3. Setting Attributes at the Thread Level6.3. Changing the Session Language6.4. Formatting Calendar Dates6.4.1. Displaying Various Date Formats and Languages6.4.2. Designating Calendar Systems6.5. Representing Currencies6.6. Formatting Numbers6.7. Dealing with Time Zones6.8. Sorting and Comparing Strings6.9. Applying Country-Based Formatting6.10. Safely Mapping to .NET Data Types6.11. Summary

7. Transactions with ODP.NET
7.1. Understanding Transactions and the OracleTransaction Class7.1.1. Executing Your First Transaction7.1.2. Executing Stored Procedures in a Transaction7.2. Performing Partial Rollbacks7.3. Working with Distributed Transactions7.3.1. Creating a Second Database Instance7.3.2. Executing Implicit Distributed Transactions7.3.3. Executing Explicit Distributed Transactions7.3.4. Executing Promotable Transactions7.4. Summary
8. Oracle Database Change Notifications with ODP.NET
8.1. Understanding Database Change Notification8.2. Registering for Query-Based Change Notifications8.3. Registering for Object-Based Change Notifications8.4. Grouping Multiple Notification Requests8.5. Removing a Registration8.6. Retrieving Change Notification Information8.7. Choosing to Poll8.8. Considering Typical Usage Scenarios8.9. Thinking About Performance8.10. Summary
9. Using Oracle Database Streams Advanced Queuing with ODP.NET
9.1. Understanding the Basics of AQ9.2. Creating a Single-Consumer Queue9.2.1. Setting Up a Single-Consumer Queue9.2.2. Enqueuing and Dequeuing a Single Message9.2.3. Enqueuing and Dequeuing Multiple Messages9.3. Creating a Multiple-Consumer Queue9.3.1. Defining Recipients at the Queue Level9.3.2. Defining Recipients at the Message Level9.4. Enqueuing and Dequeuing Various Data Types in AQ9.4.1. Using UDT Data Types9.4.2. Using XML Data Types9.5. Waiting for Incoming Messages9.5.1. Dequeuing Messages Synchronously (Blocking)9.5.2. Dequeuing Messages Asynchronously (Nonblocking)9.6. Understanding the Useful OracleAQMessage Properties9.7. Summary
10. Oracle XML Support
10.1. Accessing Native XML Data (XMLTYPE)10.1.1. Creating an XMLTYPE Column10.1.2. Receiving XMLTYPE Data with XMLReader10.1.3. Receiving XMLTYPE Data with OracleXMLType10.1.4. Receiving XMLTYPE Data as a String10.2. Passing XML Data to and from PL/SQL Stored Procedures10.3. Validating Against XML Schema10.4. Using XSLT to Transform XML Data10.5. Retrieving Relational Data as XML10.5.1. Using the XMLCommandType property10.5.2. Using the Dataset.GetXML Method10.5.3. Using the DBMS_XMLGEN.GETXML Stored Procedure10.6. Manipulating Relational Data as XML10.6.1. Inserting Relational Data Using XML10.6.2. Updating Relational Data Using XML10.6.3. Deleting Relational Data Using XML10.7. Using XQuery to Query Data10.8. Summary
11. ODP.NET Security Features
11.1. Securing Your .NET Applications11.2. Authenticating Data11.2.1. Implementing Username/Password Authentication11.2.2. Implementing Proxy Authentication11.2.3. Implementing ClientId-Based Username/Password Authentication11.2.4. Implementing Windows Authentication11.3. Understanding Code Access Security11.3.1. Using Code Groups11.3.2. Using Permission Sets11.3.3. Resolving Permissions in .NET11.3.4. Seeing CAS in Action11.4. Configuring CAS Policies11.4.1. Configuring CAS Policies via the GUI11.4.2. Configuring CAS Policies Programmatically11.5. Requesting Permissions11.5.1. Requesting Permissions Declaratively11.5.2. Requesting Permissions Imperatively11.6. Ensuring That an Assembly Can Never Access Oracle11.6.1. Refusing Permissions Declaratively at the Assembly Level11.6.2. Denying Permissions Declaratively at the Method Level11.6.3. Denying Permissions Imperatively at the Method Level11.7. Using CAS with ASP.NET Applications11.8. Implementing Best Practices11.8.1. Preventing SQL Injection Attacks11.8.2. Preventing Nonpersistent Cross-Site Scripting Attacks11.9. Summary
12. ODP.NET Performance
12.1. Measuring Performance12.1.1. Enabling the Performance Counters12.1.2. Measuring Performance Programmatically12.2. Speeding Up Connections with Connection Pooling12.3. Performing Faster Floating Point Arithmetic12.4. Executing Statements Faster12.4.1. Batching Your SQL Statements Together For Execution12.4.2. Using Statement Caching12.4.3. REF Cursors and Multiple Active Resultsets (MARs)12.5. Passing Parameters More Efficiently12.5.1. Using Bind Arrays to Pass Parameters in Bulk12.5.2. Using PL/SQL Associative Arrays12.6. Managing LOBs More Efficiently12.6.1. Enabling the LOB Cache12.6.2. Setting the InitialLOBFetchSize Property12.7. Retrieving Data More Efficiently12.7.1. Changing the FetchSize Property12.7.2. Using the Client Result Cache12.8. Importing Data More Efficiently12.9. Applying Optimization Best Practices12.9.1. Using Stored Procedures Whenever Possible12.9.2. Using the Right Data Access Object12.10. Summary
13. Design Patterns and Considerations in Using ODP.NET
13.1. Programming to an Interface Instead of an Implementation13.2. Using the Data Access Object13.3. Using Microsoft's Enterprise Library13.4. Creating Oracle.NET13.4.1. Creating the ODP.NET DAO Class13.4.2. Modifying the DBProviderMapping Class13.4.3. Modifying the DatabaseConfigurationView Class13.4.4. Compiling the New DAAB13.5. Using the ODP.NET DAO13.5.1. Editing the Application Configuration File13.5.2. Accessing Data via the DAAB13.5.2.1. Reading Multiple Rows into a Dataset13.5.2.2. Updating Multiple Rows from a Dataset13.5.2.3. Reading Data Using a DataReader13.6. Considering Best Practices13.6.1. Planning for Multiple Data Sources13.6.2. Keeping Provider-Specific Code Within the Data Tier13.6.3. Outputting Business Objects, Not Datasets13.6.4. Deciding How to Map Data Source Structures to Business Objects13.6.5. Deciding How to Manage Data Source Settings13.7. Summary
14. ODT.NET Tool Basics
14.1. Installing ODT.NET14.2. Managing the Database Schema14.3. Managing Users, Roles, and Object Privileges14.4. Editing and Debugging PL/SQL Code14.4.1. Creating a PL/SQL Procedure14.4.2. Creating a PL/SQL Package14.4.3. Debugging PL/SQL stored procedures14.5. Managing Oracle SQL Scripts14.6. Managing Advanced Queues14.7. Importing Tables and Data from External Data Sources14.8. Summary
15. Building Data-Driven Applications with ODT.NET
15.1. Designing Queries Visually15.2. Generating Strongly Typed DataSet Objects15.3. Generating UDT Classes15.3.1. Designing a UDT Object Visually15.3.2. Creating the UDT Object Table Visually15.3.3. Creating the OVERSEAS_JOBS Table15.3.4. Generating the UDT Classes15.3.5. Using the UDT in Your Project15.4. Generating ASP.NET Code15.5. Summary

Content preview from Pro ODP .NET for Oracle Database 11g

Chapter 4. Retrieving and Manipulating Data with ODP.NET

I was once told during a technical workshop filled with geeky programmers that "an enterprise application is only as strong as its data tier." The programmers loved catchy phrases like that. I'm not sure how true that rings, but I can definitely agree with the most of them that if you don't know your database well enough, you're going to build underperforming routines that the remaining 70 percent of your application will unfortunately be based on.

A robust data tier goes a long way, and understanding the features and limits of what you can or cannot do with ODP.NET is important. This chapter aims to lay the basic groundwork of the types of data retrieval and manipulation possible through ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781430228202Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Pro ODP .NET for Oracle Database 11g

by Edmund Zehoo

Chapter 4. Retrieving and Manipulating Data with ODP.NET

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.