ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET E-Book
Sunitha Paruchuri
39,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
The Oracle Data Provider for .NET (ODP.NET) features optimized data access to the Oracle database from a .NET environment and allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, XML DB, and advanced security. It can be used from any .NET language, including C# and VB.NET.This book is a practical guide that will give you the in-depth information you need to work with the Oracle 10g v10.2 database from Visual Studio .NET 2005, using the Oracle Developer Tools and ODP.NET. After introducing ODP.NET, we move on to dealing with SQL, PL/SQL, and XML DB using ODP.NET. Next we look at application development with ODP.NET: Web Applications, Web Services, and Mobile Applications. The last chapter covers Oracle Developer Tools for Visual Studio .NET. All the code examples are in Visual Basic.NET 2005.
http://www.packtpub.com/article/ODP-dot-net-oracle-data-provider-table-of-conten - Read the full Table of Contents for ODP.NET Developer's Guide
Chapter-by-Chapter
Chapter 1 introduces the concept of Oracle Database Extensions for .NET and provides information about Oracle Developer tools for Visual Studio.
Chapter 2 introduces the Provider Independent Model in ADO.NET 2.0, and shows how to connect to Oracle databases from .NET, working with .NET data providers, connection pooling, system privileged connection, and single sign-on etc.
Chapter 3 shows you several methods to retrieve data from an Oracle database. You will work with the core ODP.NET classes like OracleCommand, OracleDataReader, OracleDataAdapter, OracleParameter and ADO.NET classes like Dataset, DataTable, and DataRow etc.
Chapter 4 is about inserting, updating, and deleting data in the database. You will also learn about statement caching, array binding, working with offline data, implementing transactions, and handling errors and exceptions encountered during database work.
Chapter 5 deals with working with PL/SQL blocks, PL/SQL stored procedures, and functions. It also teaches you how to execute routines in PL/SQL packages, how to pass and receive arrays from the Oracle database, and working with REF CURSOR using ODP.NET.
Chapter 6 is completely dedicated to dealing with Large objects in Oracle. This chapter illustrates concepts, configurations, and programming for BFILE, BLOB, and CLOB (or NCLOB) in conjunction with ODP.NET.
Chapter 7 gives details about Oracle XML DB, an add-on feature of Oracle database. It provides information about generating XML from existing rows in tables, manipulating rows in a table using XML, and working with native XML in the Oracle database.
Chapter 8 deals with real-time application development scenarios like Oracle database change notifications, Asynchronous Application development, Web Application development using ASP.NET 2.0, Web Reporting (including grouping, sub-totals, charts etc.), Object-Oriented Development with ODP.NET and ASP.NET, XML Web Services development using ODP.NET and Smart Device Application development (for clients like the Pocket PC etc.).
Chapter 9 introduces you to Oracle Developer Tools for Visual Studio 2005. It teaches you to connect to Oracle from the Visual Studio 2005 environment, retrieve Oracle information from Visual Studio, and work with database objects from Visual Studio. It also provides information about how to create and debug PL/SQL stored procedures and .NET CLR Stored Procedures in Oracle.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 230
Veröffentlichungsjahr: 2007
Ähnliche
Table of Contents
ODP.NET Developer's Guide
Jagadish Chatarji Pulakhandam
Sunitha Paruchuri
ODP.NET Developer's Guide
Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET
Copyright © 2007 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, Packt Publishing, nor its dealers or distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: June 2007
Production Reference: 1150607
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-847191-96-0
www.packtpub.com
Cover Image by www.visionwt.com
Credits
Authors
Jagadish Chatarji Pulakhandam
Sunitha Paruchuri
Reviewer
Steven M. Swafford
Development Editor
Douglas Paterson
Assistant Development Editor
Mithil Kulkarni
Technical Editor
Divya Menon
Editorial Manager
Dipali Chittar
Project Manager
Patricia Weir
Project Coordinator
Abhijeet Deobhakta
Indexer
Bhushan Pangaonkar
Proofreader
Chris Smith
Production Coordinator
Manjiri Nadkarni
Cover Designer
Manjiri Nadkarni
About the Authors
Jagadish Chatarji Pulakhandam currently works as a .NET Architect and is responsible for analyzing/designing enterprise-level .NET applications. He has worked with Oracle since database version 7.1 and has been in the IT field for about 12 years. Apart from Oracle and .NET, he has a good knowledge of developing corporate software and web applications, designing and implementing databases, designing and implementing data warehouses, and working with enterprise reporting software. During his free time, he contributes technical articles to OTN (Oracle Technology Network) and to the world of developer communities.
I dedicate this book to my mother Dhana Laxmi. Without her patience, support and encouragement, I would never be to this stage. A special thanks to my uncle Ch. Jagadish Kumar, who is the basis for change in my life. And several thanks to all of my relatives and friends who encouraged and supported me at various milestones in my life.
A final thanks to every member of this book project from PACKT Publishing and a special thanks to Douglas Paterson, who offered me the first chance of writing this first book in my life.
Sunitha Paruchuri has been programming with Microsoft tools and Oracle since 1997. She has developed numerous desktop, web, mobile, and distributed applications using Microsoft .NET and has good experience with other Microsoft products like Microsoft SQL Server, Microsoft Sharepoint Portal Server, etc.
I dedicate this book to my parents Harnadha babu and Aruna Kumari and special thanks to my sister (Bhagya Laxmi), all of my relatives and friends who framed, encouraged and supported me in developing my career.
About the Reviewer
Steven M. Swafford began developing software in 1995 while serving in the United States Air Force (USAF). Upon leaving the USAF he continued developing leading edge solutions in support of the America's war fighters as part of the original USAF enterprise portal development team. His roots are now in central Alabama where he works as a senior software engineer developing Java- and .NET-based applications and web services. Steven credits his wife Su Ok and daughter Sarah for supporting and inspiring his ongoing passion for software development and the resultant challenges of life near the bleeding edge. Steven was honored by the Microsoft Corporation in 2006 as a Microsoft ASP.NET Visual Developer MVP. He would like to thank Tim Stewart and Edward Habal who were his professional mentors and to this day remain close friends. Steven's personal website is located at http://www.radicaldevelopment.net and his blog is located at http://aspadvice.com/blogs/sswafford/.
Preface
Oracle's ODP.NET is a .NET data provider that can connect to and access Oracle databases with tight integrity. It can be used from any .NET language, including C# and VB.NET. This book will show you how ODP.NET is the best choice for connecting .NET applications with Oracle database. We will be dealing with the concepts of ODP.NET and its requirements, working with SQL, PL/SQL, and XML DB using ODP.NET, looking at application development with ODP.NET: Web Applications, Web Services, and Mobile Applications. We will also learn to manipulate Oracle databases from within Visual Studio using Oracle Developer Tools for Visual Studio.
What This Book Covers
Chapter 1 introduces the concept of Oracle Database Extensions for .NET and provides information about Oracle Developer Tools for Visual Studio.
Chapter 2 introduces the Provider-Independent Model in ADO.NET 2.0, and shows how to connect to Oracle databases from .NET, working with .NET data providers, connection pooling, system privileged connection, and single sign-on etc.
Chapter 3 shows you several methods to retrieve data from an Oracle database. You will work with the core ODP.NET classes like OracleCommand, OracleDataReader, OracleDataAdapter, OracleParameter, and ADO.NET classes like DataSet, DataTable, and DataRow etc.
Chapter 4 is about inserting, updating, and deleting data in the database. You will also learn about statement caching, array binding, working with offline data, implementing transactions, and handling errors and exceptions encountered during database work.
Chapter 5 deals with working with PL/SQL blocks, PL/SQL stored procedures, and functions. It also teaches you how to execute routines in PL/SQL packages, how to pass arrays to and receive arrays from the Oracle database, and working with REF CURSOR using ODP.NET.
Chapter 6 is completely dedicated to dealing with large objects in Oracle. This chapter illustrates concepts, configurations, and programming for BFILE, BLOB, and CLOB (or NCLOB) in conjunction with ODP.NET.
Chapter 7 gives details about Oracle XML DB, an add-on feature of Oracle database. It provides information about generating XML from existing rows in tables, manipulating rows in a table using XML, and working with native XML in the Oracle database.
Chapter 8 deals with real-time application development scenarios like Oracle database change notifications, asynchronous application development, web application development using ASP.NET 2.0, web reporting (including grouping, sub-totals, charts, etc.), Object-Oriented development with ODP.NET and ASP.NET, XML web-services development using ODP.NET, and Smart Device Application development (for clients like the Pocket PC).
Chapter 9 introduces you to Oracle Developer Tools for Visual Studio 2005. It teaches you to connect to Oracle from the Visual Studio 2005 environment, retrieve Oracle information from Visual Studio, and work with database objects from Visual Studio. It also provides information about how to create and debug PL/SQL stored procedures and .NET CLR stored procedures in Oracle.
Reader Feedback
Feedback from our readers is always welcome. Let us know what you think about this book, what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply drop an email to <[email protected]>, making sure to mention the book title in the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Customer Support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the Example Code for the Book
Visit http://www.packtpub.com/support, and select this book from the list of titles to download any example code or extra resources for this book. The files available for download will then be displayed.
Note
The downloadable files contain instructions on how to use them.
Errata
Although we have taken every care to ensure the accuracy of our contents, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in text or code—we would be grateful if you would report this to us. By doing this you can save other readers from frustration, and help to improve subsequent versions of this book. If you find any errata, report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the Submit Errata link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata added to the list of existing errata. The existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Questions
You can contact us at <[email protected]> if you are having a problem with some aspect of the book, and we will do our best to address it.
Chapter 1. Introduction to ODP.NET
In the early days of databases, developers used to have knowledge on only one data access technology as they would usually concentrate on a single database. Later, numerous database products advanced quickly, leaving programmers in a confused state when selecting a particular data access methodology. The era of evolving architectures like client/server (two tier), three tier, and multi-tier (which includes web-enabled) has dramatically changed the way of accessing databases. The paradigm got shifted from simple "connection-oriented" applications to connection-less or disconnected (or offline) applications to meet the demands of devices like PDAs/Handhelds, Smart Phones, Pocket PCs etc.
Introduction to ODP.NET
We now have several types of data access methodologies to develop applications. Choosing the best data access methodology is totally dependent on the type of application you are working on.
ADO.NET is a rock-solid technology and a proof of Microsoft's commitment to the UDA (Universal Data Access) strategy. The ADO.NET layer in the .NET architecture internally contains a few .NET data components (or .NET data providers), which can be used to connect to and access any database.
The data access through ADO.NET is shown in the following figure (along with other data access methodologies available prior to .NET):
Even though Microsoft designed its own .NET data providers, it has also opened its doors (specification) to the public and is encouraging other database companies to develop their own .NET data providers. Microsoft made the data access model consistent among all of the .NET data providers and thus any .NET data provider should definitely conform to the standards and architecture of ADO.NET. One of those is Oracle's ODP.NET, a .NET data provider that can connect to and access Oracle databases with tight integrity.
The ODP.NET features optimized data access to the Oracle database from a .NET environment. It is one of the several data access methods to connect to and access Oracle databases. Oracle didn't simply stop giving support to Microsoft platform with only ODP.NET. Instead, it has extended its commitment for Microsoft .NET by adding Oracle database extensions for .NET and Oracle Developer Tools for Visual Studio.
The upcoming sections will give you a solid understanding of ODP.NET along with its features.
Why Use ODP.NET?
Can't we access Oracle without ODP.NET? Yes, we can. It is not compulsory for you to work with ODP.NET. As mentioned in the following section, we can still connect to and access Oracle using other alternative methods. But, in terms of features and performance, ODP .NET is your best choice for connecting .NET applications with Oracle database. Let us see how!
Note
I am limiting the discussion to only .NET applications or clients that are trying to access Oracle databases. I will not be discussing application development prior to .NET.
Oracle Database Access from .NET Applications
There exist four main methodologies to access Oracle database from a .NET application:
Before discussing each of the above methodologies, let us understand their nature from the following figure:
Microsoft's .NET data providers for ODBC and OLEDB are not intentionally developed exclusively for Oracle database. Those are generic .NET data providers mainly targeted for most of the common data sources. If you plan to use either of those two .NET data providers, you are likely to face performance problems.
From the above figure, you can observe that there exists a separate layer for each of those .NET data providers. In other words, ODBC.NET or OLEDB.NET would not directly execute the queries or commands. Those operations would be carried to another intermediate layer (or data access bridge) and further get executed at Oracle database. The existence of this intermediate layer really kills the performance (or response time) of execution. So, if you are trying to access Oracle database from a .NET application, neither of those would be a good choice.
Coming to the next choice, it is somewhat promising. Microsoft contributed a separate .NET Framework data provider (or Microsoft's Data Provider for Oracle) to connect to and access Oracle. It enables data access to Oracle data sources through Oracle client connectivity software without having any intermediate layers. This really improves performance over the previous two choices. Before using this provider in your .NET applications, you should install and configure Oracle client software (version 8.1.7 or later) on the development machine and test it.
The Oracle Data Provider for .NET (ODP.NET) features optimized data access to the Oracle database from any .NET client. It is the best in performance together with great flexibility. It allows developers to take advantage of native Oracle data types (including XML data type), XML DB, binding array parameters, Multiple Active Result Sets (MARS), Real Application Clusters (RAC), advanced security, etc.
What Do We Require to Work with ODP.NET?
As we are trying to develop .NET applications with access to Oracle database, we must have .NET Framework installed on our machine. Any Windows Operating System (preferably Windows Server 2003 or Windows XP Professional) supporting .NET can be used to work with ODP.NET.
At the time of this writing, .NET Framework 3.0 is the latest in market; but Oracle hasn't released ODP.NET compatible with that version yet. Not only that, Visual Studio 2008 (or "Orcas") supporting .NET Framework 3.0/3.5 is still in its beta version. For our purpose .NET Framework 2.0 is the latest in market, and you can download it free from Microsoft's website.
Even though .NET Framework (including SDK and .NET runtime) alone is enough to develop .NET-based applications, it is better to have some GUI-based RAD environment (or IDE) installed, so that we can develop .NET applications in no time. Microsoft Visual Studio 2005 Professional Edition is the preferred GUI to develop .NET 2.0-based applications. If you install Microsoft Visual Studio 2005 Professional Edition, all the necessary components (including .NET Framework SDK and runtime) get automatically installed.
The next is Oracle database. It is preferred to have at least Oracle 8.1 on your machine (or on a separate server). If you want to test with the latest version of Oracle on your own machine, you can download it free from Oracle's website for your development purposes. The lightest Oracle database version available (free) at the time of this writing is Oracle Database 10g Express Edition (or XE). Certain of the features like .NET CLR extensions (for .NET CLR-based stored procedure development) for Oracle are available only from Oracle 10g version 2.0 (Oracle 10.2) onwards. If you want to have distributed transaction support (like COM+ or Enterprise Services, etc.), then you may have install and configure Oracle Services for MTS.
If you install Oracle database version 9i release 2 or later on your own system, no special Oracle client is necessary to work with ODP.NET. If your database is at some other location, then you may have to install and configure Oracle 9i Release 2 or higher client on your machine to work with ODP.NET. Oracle Net Services get automatically installed when Oracle 9i Release 2 or higher client is installed on your machine. This may be required when you try to access an Oracle database on a network.
Another important optional component is Oracle Developer Tools for Visual Studio 2005. This is a wonderful add-in, which gets injected right into Visual Studio 2005. Using this add-in (called Oracle Explorer), you can connect to any Oracle database and work with schema or data without leaving the Visual Studio 2005 environment. It is particularly useful if you are likely to deal with .NET CLR extensions for Oracle. I strongly recommend having it installed on your machine, if you are working with Visual Studio Environment.
If you are developing ASP.NET applications, it is better to have IIS configured on your machine, to test web applications over the network. If you are developing Smart Phone or Pocket PC applications, you may need to install Smart Device Extensions for Visual Studio (which automatically installs .NET Compact Framework for Smart Devices).
Introduction to Oracle Database Extensions for .NET
The Oracle Database Extensions for .NET is a new feature of Oracle Database 10g Release 2 on Windows that makes it easy to develop, deploy, and run stored procedures and functions written in any .NET-compliant language.
Oracle Database Extensions for .NET
Oracle Database Extensions for .NET makes it possible to build and run any .NET-based stored procedures or functions with Oracle Database for Microsoft Windows. This feature is supported only from Oracle 10g version 2 (on Windows) onwards or Oracle 10g Express Edition (or Oracle 10g XE).
How does .NET Work within Oracle Database?
How come Oracle understands .NET? Oracle database doesn't need to understand .NET at all. It simply hosts the Microsoft .NET Common Language Runtime (CLR) in an external process, outside of the Oracle database process, but on the same computer. The integration of Oracle database with the Microsoft Common Language Runtime (CLR) enables applications to run .NET stored procedures or functions on Oracle database without any hurdles.
Application developers can write stored procedures and functions using any .NET-compliant language, such as C# and VB.NET, and use these .NET stored procedures in the database, in the same manner as other PL/SQL or Java stored procedures. .NET stored procedures can be used from PL/SQL packages, procedures, functions, and triggers.
Once the caller (or other PL/SQL stored procedures, packages, etc.) calls any of these .NET routines (stored procedures or functions), they get executed by the Oracle hosted Microsoft CLR and the results are automatically picked up by the Oracle PL/SQL engine. Once the control comes back to PL/SQL engine, it proceeds with the normal and traditional the PL/SQL process flow of execution.
Processing of .NET Stored Procedure with Oracle
To develop .NET CLR-based stored procedures or functions, you may need to have Oracle 10g version 2 or higher (for Windows) or at least Oracle 10g Express Edition together with Oracle Database Extensions for .NET installed. If you use Oracle 10g Express Edition, the extensions get automatically installed. But, if you install Oracle 10g version 2 (for Windows), you may have to go to custom install and select the extensions. Apart from the extensions, you also need to download Oracle Developer Tools for Visual Studio (with appropriate version) to develop and deploy .NET CLR-based routines in Oracle database.
Application developers build .NET stored procedures or functions using any .NET compliant language, such as C# and VB.NET, into a .NET assembly (generally a DLL), typically using Microsoft Visual Studio .NET 2003/2005. Obviously, we use Oracle Data Provider for .NET (ODP.NET) in .NET stored procedures and functions for Oracle data access. After building .NET procedures and functions into a .NET assembly, developers deploy them in Oracle database, using the Oracle Deployment Wizard for .NET, a component of the Oracle Developer Tools for Visual Studio .NET.
Once the .NET stored procedure gets deployed, the PL/SQL wrappers for all of those routines get automatically created within the schema. The user invokes a .NET stored procedure or function through this PL/SQL wrapper (which would be the same as for normal PL/SQL stored procedures or functions). Oracle Deployment Wizard for .NET determines the probable mappings between Oracle data types and .NET data types, which the user can override. The mappings are handled seamlessly by the PL/SQL wrapper.
