Pro ODP.NET for Oracle Database 11g (eBook)

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.

Contents at a Glance 5
Table of Contents 6
About the Author 18
About the Technical Reviewer 19
Acknowledgments 20
CHAPTER 1 Introduction to Oracle.NET Connectivity 21
Making the Transition from SQL Server to Oracle 22
Introducing Oracle Connectivity 23
Accessing Oracle from Unmanaged Code 24
Using OLEDB in Unmanaged Code 25
Oracle Provider for OLEDB 25
MS OLEDB Provider for Oracle 26
Using ODBC in Unmanaged Code 26
Oracle ODBC Driver 26
Microsoft ODBC for Oracle 27
Using OO4O in Unmanaged Code 27
Accessing Oracle from Managed Code 28
Using OLEDB.NET in Managed Code 28
Using ODBC.NET in Managed Code 29
Using the Microsoft .NET Managed Provider for Oracle 31
Introducing ODP.NET 31
Understanding the ODP.NET Architecture 32
Understanding the ODP.NET Architecture 32
Understanding the ODP.NET Classes 32
Accessing Data using ODP.NET 33
Using ODP.NET in ASP.NET Projects 33
Considering ODP.NET Performance 35
Introducing the Oracle Suite of Products 35
Summary 37
CHAPTER 2 ODP.NET: A Functional Overview 38
Exploring Oracle Features Accessible in ODP.NET Version 9 38
Manipulating XML 39
Manipulating LOBs 39
Using PL/SQL Associative Array Binding 40
Supporting Active Data Objects (ADO.NET) 2.0 41
Accessing Oracle Features from ODP.NET Version 10 41
Supporting Oracle Grids 42
Supporting Multiple Oracle Homes 42
Using Floating Point Data Types 43
Using Statement Caching 43
Supporting Command Cancellation and Timeout 44
Retrieving Parameters Programmatically 45
Supporting .NET Stored Procedures 45
Using Client Identifiers 45
Using Database Change Notifications 46
Managing Connection Pools 47
Optimizing Connection Pools for RAC 47
Using a REF Cursor as an IN/OUT Parameter 48
Using 64-bit ODP.NET 48
Controlling the FetchSize Property 48
Configuring ODP.NET 49
Accessing Oracle Features from ODP.NET Version 11 49
Enhancing Performance 50
Deploying ODP.NET Using xcopy 50
Supporting Oracle User Defined Types (UDTs) 50
Performing Bulk Copy Operations 51
Using Windows Authenticated User Connections Pooling 51
Publishing Connection Pool Performance Counters 52
Supporting Self-Tuning for Applications 53
Using Oracle Streaming AQ 53
Supporting Promotable Local Transactions 54
Using ODP.NET Security Enhancements 56
Running Callbacks for HA Event Notifications 57
Starting Up and Shutting Down Databases 57
Getting Started 58
Installing Oracle Database 11g 58
Installing ODAC.NET 62
Summary 67
CHAPTER 3 Connecting to Oracle with ODP.NET 68
Connecting via TNS 68
Understanding the TNSNames.ora file 70
Connecting in Other Ways 71
Connecting Without TNSNames.ora 71
Connecting via EZConnect 72
Learning the ODP.NET Connection Parameters 73
Connecting with Connection Pooling Activated 73
Connecting via Integrated Windows Authentication 76
Connecting with Special Privileges 77
Using Other Connection String Attributes 78
Checking Whether ODP.NET Is Installed 79
Dynamically Building an ODP.NET Connection String 80
Using the OracleConnectionStringBuilder Class 80
Retrieving Available Oracle Data Sources 81
Understanding Transparent Application Failover 81
Enabling TAF in Your Application 82
Using TAF Callbacks 84
Summary 86
CHAPTER 4 Retrieving and Manipulating Data with ODP.NET 87
Understanding ODP.NET Data Types 87
Creating a Sample Table 91
Retrieving Multiple Rows of Data 94
Retrieving a Single Value 96
Handling NULL Values in ODP.NET 97
Retrieving Data into a Dataset 98
Using Parameterized Queries 99
Updating Data 101
Executing a Single INSERT, UPDATE, or DELETE Statement 102
Committing Dataset Changes to the Database 103
Generating Command Objects 106
Handling Master-Detail Relationships 108
Creating a Second Table 108
Retrieving from Multiple Tables 109
Binding a .NET Form to Your Dataset 110
Committing Changes to Multiple Tables 112
Defining Table Relationships and Constraints in a DataSet 113
Manipulating LOBs and BFILEs 115
Uploading BLOB Data 115
Retrieving BLOB Data 117
Inserting CLOB/NCLOB Data 118
Retrieving CLOB/NCLOB Data 119
Creating BFILE Directory Mappings 120
Inserting BFILE Data 121
Retrieving BFILE Data 122
Manipulating RAW Data Types 123
Creating Automatically Incrementing Columns 126
Executing DDL from ODP.NET 127
Discovering Schema in ODP.NET 128
Handling ODP.NET Exceptions 131
Summary 133
CHAPTER 5 Using PL/SQL and .NET CLR Stored Procedures with ODP.NET 134
Understanding the Basics of PL/SQL 134
Working with Anonymous PL/SQL Blocks 135
Executing an Anonymous PL/SQL Block 135
Passing Data into an Anonymous Block 136
Returning Data from an Anonymous Block 138
Working with PL/SQL Stored Procedures 139
Executing a PL/SQL Stored Procedure 140
Passing Data into a PL/SQL Stored Procedure 141
Retrieving Data from a PL/SQL Stored Procedure 143
Executing a PL/SQL Function 144
Handling Special IN and OUT Data Types 145
Using Associative Arrays 145
Passing Associative Arrays to PL/SQL Code 146
Retrieving Associative Arrays from PL/SQL Code 148
Using VARRAYs 151
Using Nested Tables 155
Using REF Cursors 157
Reading a Result Set from a REF Cursor Using the OracleDataReader 158
Reading a Result Set from a REF Cursor Using the OracleDataAdapter 159
Retrieving Multiple Active Result Sets 161
User Defined Types (UDT) / OBJECT Types 163
Handling Custom-Defined PL/SQL Errors 168
Creating Your First .NET CLR Stored Procedure 169
Deploying a .NET CLR Stored Procedure 171
Executing the .NET CLR Stored Procedure 174
Summary 176
CHAPTER 6 ODP.NET Globalization 177
Storing and Retrieving Double-Byte Data 177
Using the OracleGlobalization class 181
Setting Attributes at the Client Level 181
Setting Attributes at the Session Level 182
Setting Attributes at the Thread Level 183
Changing the Session Language 183
Formatting Calendar Dates 184
Displaying Various Date Formats and Languages 185
Designating Calendar Systems 187
Representing Currencies 188
Formatting Numbers 192
Dealing with Time Zones 192
Sorting and Comparing Strings 194
Applying Country-Based Formatting 197
Safely Mapping to .NET Data Types 199
Summary 201
CHAPTER 7 Transactions with ODP.NET 203
Understanding Transactions and the OracleTransaction Class 203
Executing Your First Transaction 205
Executing Stored Procedures in a Transaction 207
Performing Partial Rollbacks 208
Working with Distributed Transactions 210
Creating a Second Database Instance 211
Executing Implicit Distributed Transactions 214
Executing Explicit Distributed Transactions 217
Executing Promotable Transactions 218
Summary 219
CHAPTER 8 Oracle Database Change Notifications with ODP.NET 220
Understanding Database Change Notification 220
Registering for Query-Based Change Notifications 221
Registering for Object-Based Change Notifications 226
Grouping Multiple Notification Requests 227
Removing a Registration 228
Retrieving Change Notification Information 229
Choosing to Poll 231
Considering Typical Usage Scenarios 232
Thinking About Performance 236
Summary 237
CHAPTER 9 Using Oracle Database Streams Advanced Queuing with ODP.NET 238
Understanding the Basics of AQ 239
Creating a Single-Consumer Queue 239
Setting Up a Single-Consumer Queue 239
Enqueuing and Dequeuing a Single Message 241
Enqueuing and Dequeuing Multiple Messages 245
Creating a Multiple-Consumer Queue 247
Defining Recipients at the Queue Level 248
Defining Recipients at the Message Level 252
Enqueuing and Dequeuing Various Data Types in AQ 253
Using UDT Data Types 253
Using XML Data Types 259
Waiting for Incoming Messages 261
Dequeuing Messages Synchronously (Blocking) 262
Dequeuing Messages Asynchronously (Nonblocking) 263
Understanding the Useful OracleAQMessage Properties 265
Summary 266
CHAPTER 10 Oracle XML Support 267
Accessing Native XML Data (XMLTYPE) 268
Creating an XMLTYPE Column 268
Receiving XMLTYPE Data with XMLReader 269
Receiving XMLTYPE Data with OracleXMLType 271
Receiving XMLTYPE Data as a String 274
Passing XML Data to and from PL/SQL Stored Procedures 274
Validating Against XML Schema 276
Using XSLT to Transform XML Data 280
Retrieving Relational Data as XML 283
Using the XMLCommandType property 283
Using the Dataset.GetXML Method 286
Using the DBMS_XMLGEN.GETXML Stored Procedure 287
Manipulating Relational Data as XML 288
Inserting Relational Data Using XML 288
Updating Relational Data Using XML 292
Deleting Relational Data Using XML 294
Using XQuery to Query Data 296
Summary 298
CHAPTER 11 ODP.NET Security Features 300
Securing Your .NET Applications 300
Authenticating Data 302
Implementing Username/Password Authentication 302
Implementing Proxy Authentication 302
Implementing ClientId-Based Username/Password Authentication 303
Implementing Windows Authentication 304
Understanding Code Access Security 304
Using Code Groups 305
Using Permission Sets 306
Resolving Permissions in .NET 307
Seeing CAS in Action 308
Configuring CAS Policies 310
Configuring CAS Policies via the GUI 310
Configuring CAS Policies Programmatically 314
Requesting Permissions 314
Requesting Permissions Declaratively 314
Requesting Permissions Imperatively 317
Ensuring That an Assembly Can Never Access Oracle 318
Refusing Permissions Declaratively at the Assembly Level 319
Denying Permissions Declaratively at the Method Level 320
Denying Permissions Imperatively at the Method Level 321
Using CAS with ASP.NET Applications 321
Implementing Best Practices 326
Preventing SQL Injection Attacks 326
Preventing Nonpersistent Cross-Site Scripting Attacks 327
Summary 328
CHAPTER 12 ODP.NET Performance 329
Measuring Performance 329
Enabling the Performance Counters 330
Measuring Performance Programmatically 332
Speeding Up Connections with Connection Pooling 333
Performing Faster Floating Point Arithmetic 335
Executing Statements Faster 337
Batching Your SQL Statements Together For Execution 337
Using Statement Caching 340
REF Cursors and Multiple Active Resultsets (MARs) 343
Passing Parameters More Efficiently 343
Using Bind Arrays to Pass Parameters in Bulk 343
Using PL/SQL Associative Arrays 346
Managing LOBs More Efficiently 349
Enabling the LOB Cache 349
Setting the InitialLOBFetchSize Property 352
Retrieving Data More Efficiently 353
Changing the FetchSize Property 353
Using the Client Result Cache 355
Importing Data More Efficiently 358
Applying Optimization Best Practices 360
Using Stored Procedures Whenever Possible 361
Using the Right Data Access Object 361
Summary 361
CHAPTER 13 Design Patterns and Considerations in Using ODP.NET 362
Programming to an Interface Instead of an Implementation 363
Using the Data Access Object 364
Using Microsoft’s Enterprise Library 365
Creating Oracle.NET 366
Creating the ODP.NET DAO Class 366
Modifying the DBProviderMapping Class 372
Modifying the DatabaseConfigurationView Class 373
Compiling the New DAAB 375
Using the ODP.NET DAO 375
Editing the Application Configuration File 376
Accessing Data via the DAAB 377
Reading Multiple Rows into a Dataset 378
Updating Multiple Rows from a Dataset 379
Reading Data Using a DataReader 380
Considering Best Practices 381
Planning for Multiple Data Sources 381
Keeping Provider-Specific Code Within the Data Tier 381
Outputting Business Objects, Not Datasets 381
Deciding How to Map Data Source Structures to Business Objects 381
Deciding How to Manage Data Source Settings 382
Summary 382
CHAPTER 14 ODT.NET Tool Basics 383
Installing ODT.NET 383
Managing the Database Schema 384
Managing Users, Roles, and Object Privileges 387
Editing and Debugging PL/SQL Code 390
Creating a PL/SQL Procedure 390
Creating a PL/SQL Package 393
Debugging PL/SQL stored procedures 395
Managing Oracle SQL Scripts 401
Managing Advanced Queues 404
Importing Tables and Data from External Data Sources 406
Summary 409
CHAPTER 15 Building Data-Driven Applications with ODT.NET 410
Designing Queries Visually 410
Generating Strongly Typed DataSet Objects 413
Generating UDT Classes 422
Designing a UDT Object Visually 422
Creating the UDT Object Table Visually 423
Creating the OVERSEAS_JOBS Table 424
Generating the UDT Classes 426
Using the UDT in Your Project 429
Generating ASP.NET Code 432
Summary 436
Index 437