Book Image

ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET

Book Image

ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET

Overview of this book

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.
Table of Contents (14 chapters)
ODP.NET Developer's Guide
Credits
About the Authors
About the Reviewer
Preface

Handling Oracle Errors and Exceptions


In all of the previous examples, we simply used only the Exception class, which is the ancestral error handling class in .NET. ODP.NET also includes its own exception class OracleException, to deal with errors (received from Oracle database) in detail.

Displaying a Single or First Error

The following code gives you the error details when we try to execute the INSERT statement (which is wrong):

Private Sub btnSingleError_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnSingleError.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
User Id=scott;Password=tiger")
Try
Dim SQL As String
'build the INSERT statement
Dim sb As New System.Text.StringBuilder
sb.Append(" INSERT INTO emp2")
sb.Append(" (empno, ename, sal, deptno)")
sb.Append(" VALUES")
sb.Append(" ({0},'{1}',{2},{3})")
SQL = String.Format(sb.ToString, 1001,
"Jagadish", 1300, 20)
'create command object
Dim cmd As New OracleCommand(SQL,...