Zehoo / Hong Pro ODP.NET for Oracle Database 11g

1. ed
ISBN: 978-1-4302-2821-9
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 468 Seiten

This book is a comprehensive and easy-to-understand guide for using the Oracle Data Provider (ODP) version 11g on the .NET Framework. It also outlines the core GoF (Gang of Four) design patterns and coding techniques employed to build and deploy high-impact mission-critical applications using advanced Oracle database features through the ODP.NET provider. The book details the features of the ODP.NET provider in two main sections: 'Basic,' covering the basics and mechanisms for data access via ODP.NET; and 'Advanced,' covering advanced Oracle features such as globalization, savepoints, distributed transactions and how to call them via ODP.NET, advanced queueing (AQ), and promotable transactions. It takes you from the ground up through different implementation scenarios via a rich collection of C# code samples. It outlines database security and performance optimization tricks and techniques on ODP.NET that conform to best practices and adaptable design. Different GoF design patterns are highlighted for different types of ODP.NET usage scenarios with consideration of performance and security. It provides a comprehensive guide to the synergistic integration of Oracle and Microsoft technologies such as the Oracle Developer Tools for Visual Studio (11.1.0.7.10). It also details how programmers can make use of ODT to streamline the creation of robust ODP.NET applications from within the Visual Studio environment.

Edmund T. Zehoo is the chief technology officer and co-founder of Arigoo Pte Ltd., an e-forms and workflows solution vendor based in Singapore. He was the lead architect in the four-year design of Arigoo's flagship product from scratch, which is now a mature product in Singapore with a list of multinational corporate customers to its name. He also holds more than eight years of experience in building performance critical .NET e-forms and workflows solutions hosted on top of Oracle databases for large companies and governmental institutions located in Singapore.

Autoren/Hrsg.

Zehoo, Edmund

Hong, Yap Wai

Inhaltsverzeichnis

1;Contents at a Glance;5
2;Table of Contents;6
3;About the Author;18
4;About the Technical Reviewer;19
5;Acknowledgments;20
6;CHAPTER 1 Introduction to Oracle.NET Connectivity;21
6.1;Making the Transition from SQL Server to Oracle;22
6.2;Introducing Oracle Connectivity;23
6.3;Accessing Oracle from Unmanaged Code;24
6.3.1;Using OLEDB in Unmanaged Code;25
6.3.1.1;Oracle Provider for OLEDB;25
6.3.1.2;MS OLEDB Provider for Oracle;26
6.3.2;Using ODBC in Unmanaged Code;26
6.3.2.1;Oracle ODBC Driver;26
6.3.2.2;Microsoft ODBC for Oracle;27
6.3.3;Using OO4O in Unmanaged Code;27
6.4;Accessing Oracle from Managed Code;28
6.4.1;Using OLEDB.NET in Managed Code;28
6.4.2;Using ODBC.NET in Managed Code;29
6.4.3;Using the Microsoft .NET Managed Provider for Oracle;31
6.5;Introducing ODP.NET;31
6.5.1;Understanding the ODP.NET Architecture;32
6.5.2;Understanding the ODP.NET Architecture;32
6.5.3;Understanding the ODP.NET Classes;32
6.5.4;Accessing Data using ODP.NET;33
6.5.5;Using ODP.NET in ASP.NET Projects;33
6.5.6;Considering ODP.NET Performance;35
6.6;Introducing the Oracle Suite of Products;35
6.7;Summary;37
7;CHAPTER 2 ODP.NET: A Functional Overview;38
7.1;Exploring Oracle Features Accessible in ODP.NET Version 9;38
7.1.1;Manipulating XML;39
7.1.2;Manipulating LOBs;39
7.1.3;Using PL/SQL Associative Array Binding;40
7.1.4;Supporting Active Data Objects (ADO.NET) 2.0;41
7.2;Accessing Oracle Features from ODP.NET Version 10;41
7.2.1;Supporting Oracle Grids;42
7.2.2;Supporting Multiple Oracle Homes;42
7.2.3;Using Floating Point Data Types;43
7.2.4;Using Statement Caching;43
7.2.5;Supporting Command Cancellation and Timeout;44
7.2.6;Retrieving Parameters Programmatically;45
7.2.7;Supporting .NET Stored Procedures;45
7.2.8;Using Client Identifiers;45
7.2.9;Using Database Change Notifications;46
7.2.10;Managing Connection Pools;47
7.2.11;Optimizing Connection Pools for RAC;47
7.2.12;Using a REF Cursor as an IN/OUT Parameter;48
7.2.13;Using 64-bit ODP.NET;48
7.2.14;Controlling the FetchSize Property;48
7.2.15;Configuring ODP.NET;49
7.3;Accessing Oracle Features from ODP.NET Version 11;49
7.3.1;Enhancing Performance;50
7.3.2;Deploying ODP.NET Using xcopy;50
7.3.3;Supporting Oracle User Defined Types (UDTs);50
7.3.4;Performing Bulk Copy Operations;51
7.3.5;Using Windows Authenticated User Connections Pooling;51
7.3.6;Publishing Connection Pool Performance Counters;52
7.3.7;Supporting Self-Tuning for Applications;53
7.3.8;Using Oracle Streaming AQ;53
7.3.9;Supporting Promotable Local Transactions;54
7.3.10;Using ODP.NET Security Enhancements;56
7.3.11;Running Callbacks for HA Event Notifications;57
7.3.12;Starting Up and Shutting Down Databases;57
7.4;Getting Started;58
7.4.1;Installing Oracle Database 11g;58
7.4.2;Installing ODAC.NET;62
7.5;Summary;67
8;CHAPTER 3 Connecting to Oracle with ODP.NET;68
8.1;Connecting via TNS;68
8.2;Understanding the TNSNames.ora file;70
8.3;Connecting in Other Ways;71
8.3.1;Connecting Without TNSNames.ora;71
8.3.2;Connecting via EZConnect;72
8.4;Learning the ODP.NET Connection Parameters;73
8.4.1;Connecting with Connection Pooling Activated;73
8.4.2;Connecting via Integrated Windows Authentication;76
8.4.3;Connecting with Special Privileges;77
8.4.4;Using Other Connection String Attributes;78
8.5;Checking Whether ODP.NET Is Installed;79
8.6;Dynamically Building an ODP.NET Connection String;80
8.6.1;Using the OracleConnectionStringBuilder Class;80
8.6.2;Retrieving Available Oracle Data Sources;81
8.7;Understanding Transparent Application Failover;81
8.7.1;Enabling TAF in Your Application;82
8.7.2;Using TAF Callbacks;84
8.8;Summary;86
9;CHAPTER 4 Retrieving and Manipulating Data with ODP.NET;87
9.1;Understanding ODP.NET Data Types;87
9.2;Creating a Sample Table;91
9.3;Retrieving Multiple Rows of Data;94
9.4;Retrieving a Single Value;96
9.5;Handling NULL Values in ODP.NET;97
9.6;Retrieving Data into a Dataset;98
9.7;Using Parameterized Queries;99
9.8;Updating Data;101
9.8.1;Executing a Single INSERT, UPDATE, or DELETE Statement;102
9.8.2;Committing Dataset Changes to the Database;103
9.8.3;Generating Command Objects;106
9.9;Handling Master-Detail Relationships;108
9.9.1;Creating a Second Table;108
9.9.2;Retrieving from Multiple Tables;109
9.9.3;Binding a .NET Form to Your Dataset;110
9.9.4;Committing Changes to Multiple Tables;112
9.9.5;Defining Table Relationships and Constraints in a DataSet;113
9.10;Manipulating LOBs and BFILEs;115
9.10.1;Uploading BLOB Data;115
9.10.2;Retrieving BLOB Data;117
9.10.3;Inserting CLOB/NCLOB Data;118
9.10.4;Retrieving CLOB/NCLOB Data;119
9.10.5;Creating BFILE Directory Mappings;120
9.10.6;Inserting BFILE Data;121
9.10.7;Retrieving BFILE Data;122
9.11;Manipulating RAW Data Types;123
9.12;Creating Automatically Incrementing Columns;126
9.13;Executing DDL from ODP.NET;127
9.14;Discovering Schema in ODP.NET;128
9.15;Handling ODP.NET Exceptions;131
9.16;Summary;133
10;CHAPTER 5 Using PL/SQL and .NET CLR Stored Procedures with ODP.NET;134
10.1;Understanding the Basics of PL/SQL;134
10.2;Working with Anonymous PL/SQL Blocks;135
10.2.1;Executing an Anonymous PL/SQL Block;135
10.2.2;Passing Data into an Anonymous Block;136
10.2.3;Returning Data from an Anonymous Block;138
10.3;Working with PL/SQL Stored Procedures;139
10.3.1;Executing a PL/SQL Stored Procedure;140
10.3.2;Passing Data into a PL/SQL Stored Procedure;141
10.3.3;Retrieving Data from a PL/SQL Stored Procedure;143
10.4;Executing a PL/SQL Function;144
10.5;Handling Special IN and OUT Data Types;145
10.5.1;Using Associative Arrays;145
10.5.1.1;Passing Associative Arrays to PL/SQL Code;146
10.5.1.2;Retrieving Associative Arrays from PL/SQL Code;148
10.5.2;Using VARRAYs;151
10.5.3;Using Nested Tables;155
10.5.4;Using REF Cursors;157
10.5.4.1;Reading a Result Set from a REF Cursor Using the OracleDataReader;158
10.5.4.2;Reading a Result Set from a REF Cursor Using the OracleDataAdapter;159
10.5.5;Retrieving Multiple Active Result Sets;161
10.5.6;User Defined Types (UDT) / OBJECT Types;163
10.6;Handling Custom-Defined PL/SQL Errors;168
10.7;Creating Your First .NET CLR Stored Procedure;169
10.7.1;Deploying a .NET CLR Stored Procedure;171
10.7.2;Executing the .NET CLR Stored Procedure;174
10.8;Summary;176
11;CHAPTER 6 ODP.NET Globalization;177
11.1;Storing and Retrieving Double-Byte Data;177
11.2;Using the OracleGlobalization class;181
11.2.1;Setting Attributes at the Client Level;181
11.2.2;Setting Attributes at the Session Level;182
11.2.3;Setting Attributes at the Thread Level;183
11.3;Changing the Session Language;183
11.4;Formatting Calendar Dates;184
11.4.1;Displaying Various Date Formats and Languages;185
11.4.2;Designating Calendar Systems;187
11.5;Representing Currencies;188
11.6;Formatting Numbers;192
11.7;Dealing with Time Zones;192
11.8;Sorting and Comparing Strings;194
11.9;Applying Country-Based Formatting;197
11.10;Safely Mapping to .NET Data Types;199
11.11;Summary;201
12;CHAPTER 7 Transactions with ODP.NET;203
12.1;Understanding Transactions and the OracleTransaction Class;203
12.1.1;Executing Your First Transaction;205
12.1.2;Executing Stored Procedures in a Transaction;207
12.2;Performing Partial Rollbacks;208
12.3;Working with Distributed Transactions;210
12.3.1;Creating a Second Database Instance;211
12.3.2;Executing Implicit Distributed Transactions;214
12.3.3;Executing Explicit Distributed Transactions;217
12.3.4;Executing Promotable Transactions;218
12.4;Summary;219
13;CHAPTER 8 Oracle Database Change Notifications with ODP.NET;220
13.1;Understanding Database Change Notification;220
13.2;Registering for Query-Based Change Notifications;221
13.3;Registering for Object-Based Change Notifications;226
13.4;Grouping Multiple Notification Requests;227
13.5;Removing a Registration;228
13.6;Retrieving Change Notification Information;229
13.7;Choosing to Poll;231
13.8;Considering Typical Usage Scenarios;232
13.9;Thinking About Performance;236
13.10;Summary;237
14;CHAPTER 9 Using Oracle Database Streams Advanced Queuing with ODP.NET;238
14.1;Understanding the Basics of AQ;239
14.2;Creating a Single-Consumer Queue;239
14.2.1;Setting Up a Single-Consumer Queue;239
14.2.2;Enqueuing and Dequeuing a Single Message;241
14.2.3;Enqueuing and Dequeuing Multiple Messages;245
14.3;Creating a Multiple-Consumer Queue;247
14.3.1;Defining Recipients at the Queue Level;248
14.3.2;Defining Recipients at the Message Level;252
14.4;Enqueuing and Dequeuing Various Data Types in AQ;253
14.4.1;Using UDT Data Types;253
14.4.2;Using XML Data Types;259
14.5;Waiting for Incoming Messages;261
14.5.1;Dequeuing Messages Synchronously (Blocking);262
14.5.2;Dequeuing Messages Asynchronously (Nonblocking);263
14.6;Understanding the Useful OracleAQMessage Properties;265
14.7;Summary;266
15;CHAPTER 10 Oracle XML Support;267
15.1;Accessing Native XML Data (XMLTYPE);268
15.1.1;Creating an XMLTYPE Column;268
15.1.2;Receiving XMLTYPE Data with XMLReader;269
15.1.3;Receiving XMLTYPE Data with OracleXMLType;271
15.1.4;Receiving XMLTYPE Data as a String;274
15.2;Passing XML Data to and from PL/SQL Stored Procedures;274
15.3;Validating Against XML Schema;276
15.4;Using XSLT to Transform XML Data;280
15.5;Retrieving Relational Data as XML;283
15.5.1;Using the XMLCommandType property;283
15.5.2;Using the Dataset.GetXML Method;286
15.5.3;Using the DBMS_XMLGEN.GETXML Stored Procedure;287
15.6;Manipulating Relational Data as XML;288
15.6.1;Inserting Relational Data Using XML;288
15.6.2;Updating Relational Data Using XML;292
15.6.3;Deleting Relational Data Using XML;294
15.7;Using XQuery to Query Data;296
15.8;Summary;298
16;CHAPTER 11 ODP.NET Security Features;300
16.1;Securing Your .NET Applications;300
16.2;Authenticating Data;302
16.2.1;Implementing Username/Password Authentication;302
16.2.2;Implementing Proxy Authentication;302
16.2.3;Implementing ClientId-Based Username/Password Authentication;303
16.2.4;Implementing Windows Authentication;304
16.3;Understanding Code Access Security;304
16.3.1;Using Code Groups;305
16.3.2;Using Permission Sets;306
16.3.3;Resolving Permissions in .NET;307
16.3.4;Seeing CAS in Action;308
16.4;Configuring CAS Policies;310
16.4.1;Configuring CAS Policies via the GUI;310
16.4.2;Configuring CAS Policies Programmatically;314
16.5;Requesting Permissions;314
16.5.1;Requesting Permissions Declaratively;314
16.5.2;Requesting Permissions Imperatively;317
16.6;Ensuring That an Assembly Can Never Access Oracle;318
16.6.1;Refusing Permissions Declaratively at the Assembly Level;319
16.6.2;Denying Permissions Declaratively at the Method Level;320
16.6.3;Denying Permissions Imperatively at the Method Level;321
16.7;Using CAS with ASP.NET Applications;321
16.8;Implementing Best Practices;326
16.8.1;Preventing SQL Injection Attacks;326
16.8.2;Preventing Nonpersistent Cross-Site Scripting Attacks;327
16.9;Summary;328
17;CHAPTER 12 ODP.NET Performance;329
17.1;Measuring Performance;329
17.1.1;Enabling the Performance Counters;330
17.1.2;Measuring Performance Programmatically;332
17.2;Speeding Up Connections with Connection Pooling;333
17.3;Performing Faster Floating Point Arithmetic;335
17.4;Executing Statements Faster;337
17.4.1;Batching Your SQL Statements Together For Execution;337
17.4.2;Using Statement Caching;340
17.4.3;REF Cursors and Multiple Active Resultsets (MARs);343
17.5;Passing Parameters More Efficiently;343
17.5.1;Using Bind Arrays to Pass Parameters in Bulk;343
17.5.2;Using PL/SQL Associative Arrays;346
17.6;Managing LOBs More Efficiently;349
17.6.1;Enabling the LOB Cache;349
17.6.2;Setting the InitialLOBFetchSize Property;352
17.7;Retrieving Data More Efficiently;353
17.7.1;Changing the FetchSize Property;353
17.7.2;Using the Client Result Cache;355
17.8;Importing Data More Efficiently;358
17.9;Applying Optimization Best Practices;360
17.9.1;Using Stored Procedures Whenever Possible;361
17.9.2;Using the Right Data Access Object;361
17.10;Summary;361
18;CHAPTER 13 Design Patterns and Considerations in Using ODP.NET;362
18.1;Programming to an Interface Instead of an Implementation;363
18.2;Using the Data Access Object;364
18.3;Using Microsoft’s Enterprise Library;365
18.4;Creating Oracle.NET;366
18.4.1;Creating the ODP.NET DAO Class;366
18.4.2;Modifying the DBProviderMapping Class;372
18.4.3;Modifying the DatabaseConfigurationView Class;373
18.4.4;Compiling the New DAAB;375
18.5;Using the ODP.NET DAO;375
18.5.1;Editing the Application Configuration File;376
18.5.2;Accessing Data via the DAAB;377
18.5.2.1;Reading Multiple Rows into a Dataset;378
18.5.2.2;Updating Multiple Rows from a Dataset;379
18.5.2.3;Reading Data Using a DataReader;380
18.6;Considering Best Practices;381
18.6.1;Planning for Multiple Data Sources;381
18.6.2;Keeping Provider-Specific Code Within the Data Tier;381
18.6.3;Outputting Business Objects, Not Datasets;381
18.6.4;Deciding How to Map Data Source Structures to Business Objects;381
18.6.5;Deciding How to Manage Data Source Settings;382
18.7;Summary;382
19;CHAPTER 14 ODT.NET Tool Basics;383
19.1;Installing ODT.NET;383
19.2;Managing the Database Schema;384
19.3;Managing Users, Roles, and Object Privileges;387
19.4;Editing and Debugging PL/SQL Code;390
19.4.1;Creating a PL/SQL Procedure;390
19.4.2;Creating a PL/SQL Package;393
19.4.3;Debugging PL/SQL stored procedures;395
19.5;Managing Oracle SQL Scripts;401
19.6;Managing Advanced Queues;404
19.7;Importing Tables and Data from External Data Sources;406
19.8;Summary;409
20;CHAPTER 15 Building Data-Driven Applications with ODT.NET;410
20.1;Designing Queries Visually;410
20.2;Generating Strongly Typed DataSet Objects;413
20.3;Generating UDT Classes;422
20.3.1;Designing a UDT Object Visually;422
20.3.2;Creating the UDT Object Table Visually;423
20.3.3;Creating the OVERSEAS_JOBS Table;424
20.3.4;Generating the UDT Classes;426
20.3.5;Using the UDT in Your Project;429
20.4;Generating ASP.NET Code;432
20.5;Summary;436
21;Index;437