Pro ODP .NET for Oracle Database 11g

Apress - Pro ODP Dot Net for Oracle Database 11g (2010) (ATTiCA)......Page 1
Books for Professionals......Page 2
Contents at a Glance......Page 6
Contents......Page 7
About the Author......Page 19
About the Technical Reviewer......Page 20
Acknowledgments......Page 21
Introduction to Oracle .NET Connectivity......Page 23
Making the Transition from SQL Server to Oracle......Page 24
Introducing Oracle Connectivity......Page 25
Accessing Oracle from Unmanaged Code......Page 26
Oracle Provider for OLEDB......Page 27
Oracle ODBC Driver......Page 28
Using OO4O in Unmanaged Code......Page 29
Using OLEDB.NET in Managed Code......Page 30
Using ODBC.NET in Managed Code......Page 31
Introducing ODP.NET......Page 33
Understanding the ODP.NET Classes......Page 34
Using ODP.NET in ASP.NET Projects......Page 35
Introducing the Oracle Suite of Products......Page 37
Summary......Page 39
Exploring Oracle Features Accessible in ODP.NET Version 9......Page 41
Manipulating LOBs......Page 42
Using PL/SQL Associative Array Binding......Page 43
Accessing Oracle Features from ODP.NET Version 10......Page 44
Supporting Multiple Oracle Homes......Page 45
Using Statement Caching......Page 46
Supporting Command Cancellation and Timeout......Page 47
Using Client Identifiers......Page 48
Using Database Change Notifications......Page 49
Optimizing Connection Pools for RAC......Page 50
Controlling the FetchSize Property......Page 51
Accessing Oracle Features from ODP.NET Version 11......Page 52
Supporting Oracle User Defined Types (UDTs)......Page 53
Using Windows Authenticated User Connections Pooling......Page 54
Publishing Connection Pool Performance Counters......Page 55
Using Oracle Streaming AQ......Page 56
Supporting Promotable Local Transactions......Page 57
Using ODP.NET Security Enhancements......Page 59
Starting Up and Shutting Down Databases......Page 60
Installing Oracle Database 11g......Page 61
Installing ODAC.NET......Page 65
Summary......Page 70
Connecting via TNS......Page 71
Understanding the TNSNames.ora file......Page 73
Connecting Without TNSNames.ora......Page 74
Connecting via EZConnect......Page 75
Connecting with Connection Pooling Activated......Page 76
Connecting via Integrated Windows Authentication......Page 79
Connecting with Special Privileges......Page 80
Using Other Connection String Attributes......Page 81
Checking Whether ODP.NET Is Installed......Page 82
Using the OracleConnectionStringBuilder Class......Page 83
Understanding Transparent Application Failover......Page 84
Enabling TAF in Your Application......Page 85
Using TAF Callbacks......Page 87
Summary......Page 89
Understanding ODP.NET Data Types......Page 91
Creating a Sample Table......Page 95
Retrieving Multiple Rows of Data......Page 98
Retrieving a Single Value......Page 100
Handling NULL Values in ODP.NET......Page 101
Retrieving Data into a Dataset......Page 102
Using Parameterized Queries......Page 103
Updating Data......Page 105
Executing a Single INSERT, UPDATE, or DELETE Statement......Page 106
Committing Dataset Changes to the Database......Page 107
Generating Command Objects......Page 110
Creating a Second Table......Page 112
Retrieving from Multiple Tables......Page 113
Binding a .NET Form to Your Dataset......Page 114
Committing Changes to Multiple Tables......Page 116
Defining Table Relationships and Constraints in a DataSet......Page 117
Uploading BLOB Data......Page 119
Retrieving BLOB Data......Page 121
Inserting CLOB/NCLOB Data......Page 122
Retrieving CLOB/NCLOB Data......Page 123
Creating BFILE Directory Mappings......Page 124
Inserting BFILE Data......Page 125
Retrieving BFILE Data......Page 126
Manipulating RAW Data Types......Page 127
Creating Automatically Incrementing Columns......Page 130
Executing DDL from ODP.NET......Page 131
Discovering Schema in ODP.NET......Page 132
Handling ODP.NET Exceptions......Page 135
Summary......Page 137
Understanding the Basics of PL/SQL......Page 139
Executing an Anonymous PL/SQL Block......Page 140
Passing Data into an Anonymous Block......Page 141
Returning Data from an Anonymous Block......Page 143
Working with PL/SQL Stored Procedures......Page 144
Executing a PL/SQL Stored Procedure......Page 145
Passing Data into a PL/SQL Stored Procedure......Page 146
Retrieving Data from a PL/SQL Stored Procedure......Page 148
Executing a PL/SQL Function......Page 149
Using Associative Arrays......Page 150
Passing Associative Arrays to PL/SQL Code......Page 151
Retrieving Associative Arrays from PL/SQL Code......Page 153
Using VARRAYs......Page 156
Using Nested Tables......Page 160
Using REF Cursors......Page 162
Reading a Result Set from a REF Cursor Using the OracleDataReader......Page 163
Reading a Result Set from a REF Cursor Using the OracleDataAdapter......Page 164
Retrieving Multiple Active Result Sets......Page 166
User Defined Types (UDT) / OBJECT Types......Page 168
Handling Custom-Defined PL/SQL Errors......Page 173
Creating Your First .NET CLR Stored Procedure......Page 174
Deploying a .NET CLR Stored Procedure......Page 176
Executing the .NET CLR Stored Procedure......Page 179
Summary......Page 181
Storing and Retrieving Double-Byte Data......Page 183
Setting Attributes at the Client Level......Page 187
Setting Attributes at the Session Level......Page 188
Changing the Session Language......Page 189
Formatting Calendar Dates......Page 190
Displaying Various Date Formats and Languages......Page 191
Designating Calendar Systems......Page 193
Representing Currencies......Page 194
Dealing with Time Zones......Page 198
Sorting and Comparing Strings......Page 200
Applying Country-Based Formatting......Page 203
Safely Mapping to .NET Data Types......Page 205
Summary......Page 207
Understanding Transactions and the OracleTransaction Class......Page 209
Executing Your First Transaction......Page 211
Executing Stored Procedures in a Transaction......Page 213
Performing Partial Rollbacks......Page 214
Working with Distributed Transactions......Page 216
Creating a Second Database Instance......Page 217
Executing Implicit Distributed Transactions......Page 220
Executing Explicit Distributed Transactions......Page 223
Executing Promotable Transactions......Page 224
Summary......Page 225
Understanding Database Change Notification......Page 227
Registering for Query-Based Change Notifications......Page 228
Registering for Object-Based Change Notifications......Page 233
Grouping Multiple Notification Requests......Page 234
Removing a Registration......Page 235
Retrieving Change Notification Information......Page 236
Choosing to Poll......Page 238
Considering Typical Usage Scenarios......Page 239
Thinking About Performance......Page 243
Summary......Page 244
Using Oracle Database Streams Advanced Queuing with ODP.NET......Page 245
Setting Up a Single-Consumer Queue......Page 246
Enqueuing and Dequeuing a Single Message......Page 248
Enqueuing and Dequeuing Multiple Messages......Page 252
Creating a Multiple-Consumer Queue......Page 254
Defining Recipients at the Queue Level......Page 255
Defining Recipients at the Message Level......Page 259
Using UDT Data Types......Page 260
Using XML Data Types......Page 266
Waiting for Incoming Messages......Page 268
Dequeuing Messages Synchronously (Blocking)......Page 269
Dequeuing Messages Asynchronously (Nonblocking)......Page 270
Understanding the Useful OracleAQMessage Properties......Page 272
Summary......Page 273
Oracle XML Support......Page 275
Creating an XMLTYPE Column......Page 276
Receiving XMLTYPE Data with XMLReader......Page 277
Receiving XMLTYPE Data with OracleXMLType......Page 279
Passing XML Data to and from PL/SQL Stored Procedures......Page 282
Validating Against XML Schema......Page 284
Using XSLT to Transform XML Data......Page 288
Using the XMLCommandType property......Page 291
Using the Dataset.GetXML Method......Page 294
Using the DBMS_XMLGEN.GETXML Stored Procedure......Page 295
Inserting Relational Data Using XML......Page 296
Updating Relational Data Using XML......Page 300
Deleting Relational Data Using XML......Page 302
Using XQuery to Query Data......Page 304
Summary......Page 306
Securing Your .NET Applications......Page 309
Implementing Proxy Authentication......Page 311
Implementing ClientId-Based Username/Password Authentication......Page 312
Understanding Code Access Security......Page 313
Using Code Groups......Page 314
Using Permission Sets......Page 315
Resolving Permissions in .NET......Page 316
Seeing CAS in Action......Page 317
Configuring CAS Policies via the GUI......Page 319
Requesting Permissions Declaratively......Page 323
Requesting Permissions Imperatively......Page 326
Ensuring That an Assembly Can Never Access Oracle......Page 327
Refusing Permissions Declaratively at the Assembly Level......Page 328
Denying Permissions Declaratively at the Method Level......Page 329
Using CAS with ASP.NET Applications......Page 330
Preventing SQL Injection Attacks......Page 335
Preventing Nonpersistent Cross-Site Scripting Attacks......Page 336
Summary......Page 337
Measuring Performance......Page 339
Enabling the Performance Counters......Page 340
Measuring Performance Programmatically......Page 342
Speeding Up Connections with Connection Pooling......Page 343
Performing Faster Floating Point Arithmetic......Page 345
Batching Your SQL Statements Together For Execution......Page 347
Using Statement Caching......Page 350
Using Bind Arrays to Pass Parameters in Bulk......Page 353
Using PL/SQL Associative Arrays......Page 356
Enabling the LOB Cache......Page 359
Setting the InitialLOBFetchSize Property......Page 362
Changing the FetchSize Property......Page 363
Using the Client Result Cache......Page 365
Importing Data More Efficiently......Page 368
Applying Optimization Best Practices......Page 370
Summary......Page 371
Design Patterns and Considerations in Using ODP.NET......Page 373
Programming to an Interface Instead of an Implementation......Page 374
Using the Data Access Object......Page 375
Using Microsoft’s Enterprise Library......Page 376
Creating the ODP.NET DAO Class......Page 377
Modifying the DBProviderMapping Class......Page 383
Modifying the DatabaseConfigurationView Class......Page 384
Using the ODP.NET DAO......Page 386
Editing the Application Configuration File......Page 387
Accessing Data via the DAAB......Page 388
Reading Multiple Rows into a Dataset......Page 389
Updating Multiple Rows from a Dataset......Page 390
Reading Data Using a DataReader......Page 391
Deciding How to Map Data Source Structures to Business Objects......Page 392
Summary......Page 393
Installing ODT.NET......Page 395
Managing the Database Schema......Page 396
Managing Users, Roles, and Object Privileges......Page 399
Creating a PL/SQL Procedure......Page 402
Creating a PL/SQL Package......Page 405
Debugging PL/SQL stored procedures......Page 407
Managing Oracle SQL Scripts......Page 413
Managing Advanced Queues......Page 416
Importing Tables and Data from External Data Sources......Page 418
Summary......Page 421
Designing Queries Visually......Page 423
Generating Strongly Typed DataSet Objects......Page 426
Designing a UDT Object Visually......Page 435
Creating the UDT Object Table Visually......Page 436
Creating the OVERSEAS_JOBS Table......Page 437
Generating the UDT Classes......Page 439
Using the UDT in Your Project......Page 442
Generating ASP.NET Code......Page 445
Summary......Page 449
A......Page 451
B......Page 452
D......Page 454
E......Page 455
M......Page 456
N......Page 457
O......Page 458
P......Page 461
R......Page 462
S......Page 463
T......Page 464
U......Page 465
W......Page 466