2 Days Plus Dot Net Developer Guide
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View 2 Days Plus Dot Net Developer Guide as PDF for free.
More details
- Words: 13,383
- Pages: 120
Oracle® Database Express Edition 2 Day Plus .NET Developer Guide 10g Release 2 (10.2) B25312-01
October 2005
Beta Draft
Oracle Database Express Edition 2 Day Plus .NET Developer Guide, 10g Release 2 (10.2) B25312-01 Copyright © 2005, Oracle. All rights reserved. Primary Author:
Roza Leyderman
Contributing Authors:
John Paul Cook, Mark Williams
Contributors: Alex Keh, Christian Shay The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited. The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose. If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software—Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065 The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs. Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party. Alpha and Beta Draft documentation are considered to be in prerelease status. This documentation is intended for demonstration and preliminary use only. We expect that you may encounter some errors, ranging from typographical errors to data inaccuracies. This documentation is subject to change without notice, and it may not be specific to the hardware on which you are using the software. Please be advised that prerelease documentation in not warranted in any manner, for any purpose, and we will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.
Contents List of Examples
Preface ................................................................................................................................................................. ix Audience....................................................................................................................................................... Documentation Accessibility ..................................................................................................................... Related Documents ..................................................................................................................................... Conventions .................................................................................................................................................
1
Introduction What is Microsoft .NET Framework..................................................................................................... Introduction to Oracle Data Provider for .NET .................................................................................. Introduction to Oracle Developer Tools for Visual Studio .NET.................................................... Introduction to .NET Stored Procedures..............................................................................................
2
ix ix x x
1-1 1-2 1-2 1-2
Installing Oracle Developer Tools What You Need ......................................................................................................................................... 2-1 Installing Oracle Developer Tools ........................................................................................................ 2-1 Testing the Installation............................................................................................................................ 2-4
3
Using Oracle Data Provider for .NET Starting a New Project ............................................................................................................................. 3-1 Adding a Reference............................................................................................................................ 3-3 Initial Programmatic Statements...................................................................................................... 3-6 Using the Connection Object ................................................................................................................. 3-8 Using the Command Object................................................................................................................ 3-11 Retrieving Data: a Simple Query ....................................................................................................... 3-13 Error Handling ....................................................................................................................................... 3-14 Retrieving Data: Bind Variables......................................................................................................... 3-16 Retrieving Data: Multiple Values ...................................................................................................... 3-19 Using the DataSet Class with Oracle Data Provider for .NET ..................................................... 3-20 Inserting, Deleting and Updating Data ............................................................................................ 3-28 Closing the Database Connection...................................................................................................... 3-30
4
Using Oracle Developer Tools for Visual Studio .NET Connecting to the Oracle Database....................................................................................................... 4-1
Beta Draft
iii
Building a New Table .............................................................................................................................. 4-4 Creating a Table and Its Columns ................................................................................................... 4-4 Creating an Index ............................................................................................................................ 4-12 Adding Constraints......................................................................................................................... 4-16 Adding Data to a Table .................................................................................................................. 4-20 Generating Code Automatically......................................................................................................... 4-21 Enabling Database Updates ................................................................................................................ 4-33 Disconnecting from the Oracle Database ......................................................................................... 4-37 Reconnecting to the Oracle Database................................................................................................ 4-38
5
Using PL/SQL Stored Procedures and REF Cursors Introduction to Stored Procedures ........................................................................................................ 5-1 Introduction to Ref Cursors.................................................................................................................... 5-1 Introduction to PL/SQL Packages and Package Bodies .................................................................... 5-2 Creating a PL/SQL Stored Procedure that Uses Ref Cursors ........................................................... 5-2 Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET............................ 5-13
6
Using .NET Stored Procedures Creating a New Connection ................................................................................................................... 6-1 Creating .NET Stored Functions and Procedures............................................................................... 6-3 Deploying a .NET Stored Functions and Procedures........................................................................ 6-5 Running .NET Stored Functions and Procedures ........................................................................... 6-12
7
Including Globalization Support Globalization Support............................................................................................................................. 7-1 Client Globalization Settings ............................................................................................................ 7-1
Index
iv
Beta Draft
List of Examples 2–1 2–2 2–3 2–4 3–1 3–2 3–3 3–4 3–5 3–6 3–7 3–8 3–9 3–10 3–11 3–12 3–13 3–14 3–15 3–16 3–17 3–18 3–19 3–20 3–21 3–22 3–23 3–24 3–25 3–26 3–27 3–28 3–29 3–30 3–31 3–32 3–33 3–34 3–35 3–36 3–37 3–38 3–39 3–40 3–41 3–42 3–43 3–44 3–45 3–46 3–47 4–1 4–2 4–3 vi
Starting SQL*Plus and Connecting as System Administrator.............................................. 2-5 Unlocking the User Account ..................................................................................................... 2-5 Resetting the Password for the User Account ........................................................................ 2-5 Testing the Connection .............................................................................................................. 2-5 Easy Connect Naming Method Syntax for Data Source ....................................................... 3-8 Using Oracle Database Alias in Applications; C# .................................................................. 3-9 Using Oracle Database Alias in Applications; VB ................................................................. 3-9 Creating a Connection String; C# .......................................................................................... 3-10 Creating a Connection String; VB.......................................................................................... 3-10 Creating an Empty Connection String and then Assigning its Value; C#....................... 3-10 Creating an Empty Connection String and then Assigning its Value; VB ...................... 3-10 Opening a Connection with Open(); C# ............................................................................... 3-11 Opening a Connection with Open(); VB............................................................................... 3-11 Creating a SQL Statement String; C# .................................................................................... 3-11 Creating a SQL Statement String; VB.................................................................................... 3-11 Using a Command to Query the Database; C#.................................................................... 3-12 Using a Command to Query the Database; VB ................................................................... 3-12 Retrieving a Value; C#............................................................................................................. 3-13 Retrieving a Value; VB ............................................................................................................ 3-13 Converting Data Types Explicitly; C#................................................................................... 3-14 Converting Data Types Explicitly; VB .................................................................................. 3-14 Error Handling with "Try-Catch-Finally" Syntax; C#......................................................... 3-14 Error Handling with "Try-Catch-Finally" Syntax; VB ........................................................ 3-15 Catching Common Error Messages; C#................................................................................ 3-15 Catching Common Error Messages; VB ............................................................................... 3-16 SELECT Statement without Bind Variables ......................................................................... 3-17 SELECT Statement with Bind Variables ............................................................................... 3-17 UPDATE Statement with Bind Variables ............................................................................. 3-17 Using a Bind Variable; #C....................................................................................................... 3-18 Using a Bind Variable; VB ...................................................................................................... 3-18 Querying for a Multiple Column Single Row Result ......................................................... 3-19 Using Column Ordinals and Names from a Query Result; C#......................................... 3-19 Using Column Ordinals and Names from a Query Result; VB ........................................ 3-19 Querying for a Multiple Column Multiple Row Result ..................................................... 3-19 Looping Through a Multi-Row Query Result; C# .............................................................. 3-20 Looping Through a Multi-Row Query Result; VB .............................................................. 3-20 Using DataSet Class: Declaring Variables; C# ..................................................................... 3-20 Using DataSet Class: Declaring Variables; VB..................................................................... 3-20 Using DataSet Class: Assigning Values; C# ......................................................................... 3-20 Using DataSet Class: Assigning Values; VB ........................................................................ 3-21 Opening a Connection and Filling the DataSet; C# ............................................................ 3-21 Opening a Connection and Filling the DataSet; VB............................................................ 3-21 Declaring Windows.Forms Objects Automatically; C#...................................................... 3-26 Declaring Windows.Forms Objects Automatically; VB ..................................................... 3-26 Binding the DataSet to the DataGrid; C#.............................................................................. 3-26 Binding the DataSet to the DataGrid; VB ............................................................................. 3-26 The Save_Click() Method; C#................................................................................................. 3-26 The Save_Click() Method; VB ................................................................................................ 3-26 Closing and Disposing a Connection; C# ............................................................................. 3-30 Closing and Disposing a Connection; VB ............................................................................ 3-30 Closing and Disposing a Connection when Out of Scope; C# .......................................... 3-30 Generated SQL Form of the New Table ............................................................................... 4-11 Creating a Table Index in SQL ............................................................................................... 4-15 Adding Foreign Key and Primary Key Constraints to a Table ......................................... 4-19 Beta Draft
4–4 4–5 4–6 4–7 5–1 5–2 5–3 5–4 5–5 5–6 6–1 6–2 7–1 7–2
Filling Data into the Form; C#................................................................................................ 4-30 Filling Data into the Form; VB ............................................................................................... 4-30 The Save_Click() Method; #C................................................................................................. 4-37 The Save_Click() Method; VB ................................................................................................ 4-37 PL/SQL Code for Package STORED........................................................................................ 5-5 Assigning Reference Cursors .................................................................................................... 5-8 Changing OracleCommand to Use a Stored Procedure; C#.............................................. 5-13 Changing OracleCommand to Use a Stored Procedure; VB ............................................. 5-13 Defining and Binding OracleParameter Objects for Stored Procedure; C#..................... 5-13 Defining and Binding OracleParameter Objects for Stored Procedure; VB .................... 5-14 Adding getDepartments() Method Code; C# ......................................................................... 6-4 Adding getDepartments() Method Code; VB ......................................................................... 6-4 How to Obtain Oracle Globalization Settings; C#.................................................................. 7-1 How to Obtain Oracle Globalization Settings; VB ................................................................. 7-1
Beta Draft
vii
Preface
Audience This document is intended as an introduction to application development with Oracle Database Express Edition in Microsoft .NET. We assume that users of this book have already read the Oracle Database Express Edition 2 Day DBA Guide and the Oracle Database Express Edition 2 Day Developer Guide, are familiar with basics of SQL and PL/SQL, and know how to use Microsost Visual Studio .NET.
Documentation Accessibility Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/ Accessibility of Code Examples in Documentation Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace. Accessibility of Links to External Web Sites in Documentation This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites. TTY Access to Oracle Support Services Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, seven days a week. For TTY support, call 800.446.2398.
Beta Draft
ix
Related Documents For more information, see the following documents in Oracle Database Express Edition documentation set: ■
Oracle Data Provider for .NET Developer's Guide
■
Oracle Database Extensions for .NET Developer's Guide
■
Oracle Database Express Edition 2 Day DBA Guide
■
Oracle Database Express Edition 2 Day Developer Guide
Conventions The following text conventions are used in this document:
x
Convention
Meaning
boldface
Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.
italic
Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.
monospace
Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.
Beta Draft
1 Introduction This chapter provides a brief description of Oracle technologies for the Microsoft .NET Framework. This chapter contains the following sections: ■
What is Microsoft .NET Framework
■
Introduction to Oracle Developer Tools for Visual Studio .NET
■
Introduction to Oracle Data Provider for .NET
■
Introduction to .NET Stored Procedures
This book is conceived as a quick start guide, to describes the key features of Oracle Data Provider for .NET and Oracle Developer Tools for Visual Studio .NET. It leads your through installation and configuration, shows how to build basic applications using Oracle Data Provider for .NET and Oracle Developer Tools for Visual Studio .NET, and how to create and use both PL/SQL and .NET stored procedures. After working through this book, you will be ready to continue with more extensive information available in the Oracle Database documentation library. See Also: ■
Dynamic help available with Oracle Developer Tools for Visual Studio .NET
■
Oracle Data Provider for .NET Developer's Guide
■
Oracle Database Extensions for .NET Developer's Guide
■
Oracle Database Express Edition 2 Day DBA Guide
■
Oracle Database Express Edition 2 Day Developer Guide
What is Microsoft .NET Framework The Microsoft .NET Framework, .NET, is a multi-language environment for building, deploying, and running XML Web services and applications. Its main components are: Common Language Runtime Common Language Runtime, or CLR, is a language-neutral development and run-time environment that provides services that help manage running applications Framework Class Libraries The Framework Class Libraries, or FCL, provide a consistent, object-oriented library of prepackaged functionality. Beta Draft
Introduction 1-1
Introduction to Oracle Data Provider for .NET
Introduction to Oracle Data Provider for .NET Oracle Data Provider for .NET (ODP.NET) provides data access from .NET client applications to Oracle databases. ODP.NET data access is fast, efficient, and includes access XML DB, .NET, security, Real Application Clusters (RAC) and other Oracle Database features. Oracle, Microsoft, and third-party vendors all offer data providers optimized for Oracle. Some third-party data providers support older versions of Oracle or do not require the installation of Oracle client software.
Introduction to Oracle Developer Tools for Visual Studio .NET Oracle Developer Tools for Visual Studio .NET is an add-in that provides graphical user interface access to Oracle functionality, and improves development productivity and ease of use. Oracle Developer Tools support the programming and implementation of .NET stored procedures using Visual Basic for .NET, C#, and other .NET languages. Oracle Developer Tools include the Oracle Explorer for browsing the Oracle schema, designers and wizards to create and alter schema objects, and the ability to drag and drop schema objects onto a .NET form to automatically generate code. Additional features include a PL/SQL editor with integrated context-sensitive online help, and an Oracle Data Window for performing routine database tasks like inserting and updating data or testing stored procedures in the Visual Studio environment. For maximum flexibility, there is also an Oracle Query Window for executing SQL statements or PL/SQL scripts.
Introduction to .NET Stored Procedures Oracle Database Extensions for .NET has the following features: Common Language Runtime Host for Oracle Database The Oracle Database on Windows hosts the Microsoft Common Language Runtime (CLR). The integration of Oracle Database with CLR enables applications to run .NET stored procedures or functions on Oracle Database, on Microsoft Windows 2003, Windows 2000, and Windows XP. Data Access for .NET Classes through Oracle Data Provider for .NET Using Microsoft Visual Studio .NET, you can build .NET procedures or functions into a .NET assembly. .NET stored procedures and functions use ODP.NET to access data. Oracle Deployment Wizard for Visual Studio .NET After building .NET procedures and functions into a .NET assembly, deploy them in Oracle Database using the Oracle Deployment Wizard for .NET, a component of the Oracle Developer Tools for Visual Studio .NET.
1-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
2 Installing Oracle Developer Tools This chapter will lead you through the installation and configuration of Oracle Developer Tools. This chapter contains the following sections: ■
What You Need
■
Installing Oracle Developer Tools
■
Testing the Installation
What You Need Sample Data The sample data used in this book is an integral part of the seed Oracle Database Express Edition, and installs out of the box. The sample data that ships with the product is the HR component of the Sample Schemas. For the HR data model and table descriptions, refer to the Sample Schema documentation at: http://st-doc.us.oracle.com/10/102/server.102/b14198/toc.htm. Oracle Database Express Edition You should install a copy of Oracle Database Express Edition on your computer. Visual Studio .NET 2003 Before proceeding with instructions in this book, you should have a complete installation of the Visual Studio .NET 2003.
Installing Oracle Developer Tools Follow these steps to install Oracle Developer Tools for Visual Studio .NET: 1.
In your internet browser, navigate to the following software download location: http://www.oracle.com/technology/software/tech/dotnet/odtxe_ index.html
Beta Draft
2.
Download the Setup file to your desktop.
3.
Double-click on the Setup icon.
Installing Oracle Developer Tools 2-1
Installing Oracle Developer Tools
The InstallShield Wizard will be launched.
4.
Once the Oracle Developer Tools for Visual Studio .NET InstallShield Wizard window appears, click Next.
2-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Installing Oracle Developer Tools
Beta Draft
5.
On the Destination Folder screen, accept the default installation location and click Next..
6.
On the Ready to Install the Program screen, click Install.
Installing Oracle Developer Tools 2-3
Testing the Installation
Testing the Installation The applications in this book connect to the Oracle Database Express Edition database as the hr user. You may need to unlock the hr account while signed in as a user with administrator privileges. For information on how to unlock the account using the Oracle XE Database interface, refer to Chapter 6, "Managing Users and Security" in the Oracle Database Express Edition 2 Day DBA Guide. The following steps show how to unlock the account and test the installation from SQL*Plus. 1.
From the Start menu on your desktop, select Run...
2.
In the Run window, type cmd and click OK.
3.
In the command window, start SLQ*Plus using the command in Example 2–1.
2-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Testing the Installation
Example 2–1 Starting SQL*Plus and Connecting as System Administrator sqlplus sys\oracle as sysdba 4.
Re-enter the password oracle at the prompt. You should be notified that you are connected to Oracle Database Express Edition.
5.
Unlock the hr user account using the command in Example 2–2.
Example 2–2 Unlocking the User Account alter user hr account unlock identified by hr;
You should be notified that the user is altered. 6.
In case the password for the hr account was changed, re-set it to the default value of hr, using the command shown in Example 2–3.
Example 2–3 Resetting the Password for the User Account alter user hr identified by hr;
You should be notified that the user is altered. 7.
Test the connection using the hr account, as shown in Example 2–3.
Example 2–4 Testing the Connection connect hr/hr
You should be notified that the connection works. 8.
Beta Draft
Your interaction screen for steps 3 through 7 should look something like this:
Installing Oracle Developer Tools 2-5
Testing the Installation
2-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
3 Using Oracle Data Provider for .NET This chapter explains how to use Oracle Data Provider for .NET. This chapter contains the following sections: ■
Starting a New Project
■
Using the Connection Object
■
Using the Command Object
■
Retrieving Data: a Simple Query
■
Error Handling
■
Retrieving Data: Bind Variables
■
Retrieving Data: Multiple Values
■
Using the DataSet Class with Oracle Data Provider for .NET
■
Inserting, Deleting and Updating Data
■
Closing the Database Connection
Starting a New Project Follow these steps to start a project in Visual Studio .NET 2003: 1.
Click the New Project button. Alternatively, from the File menu, select New, and then select Project.
Beta Draft
Using Oracle Data Provider for .NET 3-1
Starting a New Project
A New Project dialog box appears. 2.
On the left side of the New Project dialog box under Project Types, select Visual C# Projects. If you intend to develop in Visual Basic, select Visual Basic Projects instead. On the right side of the New Project dialog box under Templates, select Windows Application. For Name, enter HR_ODP.NET. For Location, enter C:\HR\Visual Studio Projects. Check the Create Directory for Solution box. For New Solution Name, enter HRApplications. A solution can contain several projects; when it contains only one project, you can use the same name for both. Click OK.
3-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Starting a New Project
If you wish to create project in Visual Basic, select the Visual Basic programming language in Project Types instead, and enter HumanResourcesVB under Name.
Adding a Reference 1.
To connect the project to an Oracle database, add a reference to the OracleData Access.dll, which contains the data provider. In the Solution Explorer, select the References node, right click and select Add Reference. Alternatively, select Add Reference from the Project menu.
Beta Draft
Using Oracle Data Provider for .NET 3-3
Starting a New Project
The Add Reference dialog box appears. 2.
Scroll down the list of references, and select Oracle.DataAccess.dll. Click the Select button.
3-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Starting a New Project
Click the OK button to make the Oracle Data Provider for .NET to your project. Note that the Solution Explorer pane now shows Oracle.DataAccess in the References folder.
Beta Draft
Using Oracle Data Provider for .NET 3-5
Starting a New Project
Initial Programmatic Statements 1.
With Form1 active, select Code from the View menu. Alternatively, use the F7 keyboard shortcut.
3-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Starting a New Project
2.
Add the following using statements to your C# project, at the top of the file: using System.Data; using Oracle.DataAccess.Client;
If you are starting a Visual Basic project, add the following Imports statements: Imports System.Data Imports Oracle.DataAccess.Client
Beta Draft
Using Oracle Data Provider for .NET 3-7
Using the Connection Object
Save the changes using either the Save icon near the top of the window, or select Save from the File menu. To change back to design view, select Designer from the View menu. Alternatively, use the Shift+F7 keyboard shortcut.
Using the Connection Object The OracleConnection object specifies the Oracle Database used by the application. 1.
The Easy Connect naming method enables ODP.NET clients to connect to a database without using any configuration files, simply by specifying the data source attribute through syntax shown in Example 3–1:
Example 3–1 Easy Connect Naming Method Syntax for Data Source user id=id;password=psswd;data source=//host:port/service_name
Where: ■ ■
■
id is the user id; we will use hr to access the HR schema. psswd is the password; in this book, we will use hr, the default password for the HR schema. host is the server machine and domain name to which the XE client will make the connection, such as hr-server in hr.us.acme.com in the following example of valid connections.
3-8 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the Connection Object
■ ■
port, if not specified, uses the default value of 1521. service_name, such as XE, is optional with the XE client. If not specified, the EZ Connect Adapter for XE client will connect to the default service on the host, pre-configured as XE in the listener.ora file on the XE server. Note that the default service is a new feature for Oracle Database Express Edition. If you used other Oracle client software, such as Instant Client for Oracle Database Enterprise Edition, you must supply the service name.
Some valid connections include: user id=hr;password=hr;data source=//hr-server:1521/XE user id=hr;password=hr;data source=//hr-server/hr.us.acme.com user id=hr;password=hr;data source=hr-server/hr.us.acme.com 2.
Example 3–2 and Example 3–3 show how to alias the database connection string. The value of the data source parameter would depend on how the user is connecting to the database server. In the following examples, substitute the name of your machine for the hr-server placeholder.
Example 3–2 Using Oracle Database Alias in Applications; C# string oradb = "userid=hr;password=hr;data source=//hr-server:1521/XE"; Example 3–3 Using Oracle Database Alias in Applications; VB Dim oradb As String = "userid=hr;password=hr;data source=//hr-server:1521/XE"
Beta Draft
Using Oracle Data Provider for .NET 3-9
Using the Connection Object
3.
To instantiate a connection object from the connection class, the connection string must be associated with the connection object. Add the code in Example 3–4 or Example 3–5 to your application, inside the method public Form(). The connection string, oradb, is associated with the connection object by being passed as a parameter in the connection object’s constructor.
Example 3–4 Creating a Connection String; C# OracleConnection conn = new OracleConnection(oradb); Example 3–5 Creating a Connection String; VB Dim conn As New OracleConnection(oradb)
Alternatively, you can create an empty connection object and assign the value of the connection string later in the program, as shown in Example 3–6 and Example 3–7. Example 3–6 Creating an Empty Connection String and then Assigning its Value; C# OracleConnection conn = new OracleConnection(); conn.ConnectionString = oradb; Example 3–7 Creating an Empty Connection String and then Assigning its Value; VB Dim conn As New OracleConnection() conn.ConnectionString = oradb
3-10 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the Command Object
4.
Use the Open() method to make the actual connection, as shown in Example 3–8 and Example 3–9.
Example 3–8 Opening a Connection with Open(); C# conn.Open(); Example 3–9 Opening a Connection with Open(); VB conn.Open()
Using the Command Object The OracleCommand object is used to specify the SQL command, a stored procedure, or a table name. It can be instantiated either from an OracleCommand class or an OracleConnection object. The OraleCommand object formulates a database request, sends the request to the database, and returns the result. 1.
Create a string that represents the SQL query, as shown in Example 3–10 and Example 3–11.
Example 3–10
Creating a SQL Statement String; C#
string sql = "select department_name from departments where department_id = 10"; Example 3–11
Creating a SQL Statement String; VB
Dim sql As String = _ "select department_name from departments where department_id = 10"
Beta Draft
Using Oracle Data Provider for .NET 3-11
Using the Command Object
2.
Use the new sql variable to create the OracleCommand object, and to set the its CommandType property.
Example 3–12
Using a Command to Query the Database; C#
OracleCommand cmd = new OracleCommand(sql, conn); cmd.CommandType = CommandType.Text; Example 3–13
Using a Command to Query the Database; VB
Dim cmd As New OracleCommand(sql, conn) cmd.CommandType = CommandType.Text
3-12 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Retrieving Data: a Simple Query
Retrieving Data: a Simple Query To retrieve data from the database, instantiate a DataCommand object and use its ExecuteReader() method to return an OracleDataReader object. 1.
Example 3–14 shows that the C# implementation uses accessor type methods for retrieving data. There are typed accessors for returning .NET native data types, and others for returning native Oracle data types. Zero-based ordinals are passed to the accessors to specify which table column should be returned.
Example 3–14
Retrieving a Value; C#
OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); // retrieve the first column in the select list label1.Text = dr.GetString(0);
Example 3–15 shows VB.NET can access returned data either through a column name, or through a zero-based column ordinal to the Item property. Another option is to use accessor type methods to return column data. Example 3–15
Retrieving a Value; VB
Dim dr As OracleDataReader = cmd.ExecuteReader() dr.Read() Label1.Text = dr.Item("department_name") ' retrieve by column name Label1.Text = dr.Item(0) ' retrieve the first column in the select list
Beta Draft
Using Oracle Data Provider for .NET 3-13
Error Handling
Label1.Text = dr.GetString(0) ' retrieve the first column in the select list
In previous examples, the returned value of department_name is a string used to set the value of the label control's text property, which is also a string. If department_id, which is not a string, had been retrieved instead, there would be a data type mismatch. The .NET runtime attempts to implicitly convert from one data type to another when the source and destination data types don't match. If the data types are incompatible, the implicit conversion fails and throws an exception.
2.
Oracle recommends that you use explicit data type conversions instead of implicit data type conversion at all times, using the OracleDataReader object to retrieve database native data types as either .NET data types or Oracle native data types. Example 3–16 and Example 3–17 show explicit data type conversion. Note that explicit conversion works on both scalar and array values. Example 3–16
Converting Data Types Explicitly; C#
string department_id = dr.GetInt16("department_id").ToString();
Example 3–17
Converting Data Types Explicitly; VB
Label1.Text = CStr(dr.Item("department_id"))
Error Handling 1.
.NET languages use the "Try-Catch-Finally" structured error handling; Example 3–18 and Example 3–19 show simple implementations of the "Try-Catch-Finally" syntax:
Example 3–18
Error Handling with "Try-Catch-Finally" Syntax; C#
OracleConnection conn = new OracleConnection(oradb); try { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "select department_name from departments where department_id = " + textBox1.Text; cmd.CommandType = CommandType.Text; if (dr.Read()) { label1.Text = dr.GetString(0); } } catch (Exception ex) // catches any error { MessageBox.Show(ex.Message.ToString()); } finally {
3-14 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Error Handling
conn.Dispose(); } Example 3–19
Error Handling with "Try-Catch-Finally" Syntax; VB
Dim conn As New OracleConnection(oradb) Try conn.Open() Dim cmd As New OracleCommand cmd.Connection = conn cmd.CommandText = _ "select department_name from departments where department_id = " + _ TextBox1.Text cmd.CommandType = CommandType.Text If dr.Read() Then Label1.Text = dr.Item("dname") ' or use dr.Item(0) End If Catch ex As Exception ' catches any error MessageBox.Show(ex.Message.ToString()) Finally conn.Dispose() End Try 2.
While code used in Example 3–18 and Example 3–18 will capture any errors encountered when attempting to retrieve data from the database, it is not very useful for the end user. As and example, the error caught when the database is unavailable will appear as "ORA-12545: Connect failed because target host or object does not exist".
3.
Oracle recommends that you add another catch statement, as shown in Example 3–20 and Example 3–21, to trap most common database errors and display them in a more user-friendly manner.
Example 3–20
Catching Common Error Messages; C#
catch (OracleException ex) // catches only Oracle errors { switch (ex.Number) { case 1: MessageBox.Show("Error attempting to insert duplicate data."); break; case 12545: MessageBox.Show("The database is unavailable."); break; default: MessageBox.Show("Database error: " + ex.Message.ToString()); break; Beta Draft
Using Oracle Data Provider for .NET 3-15
Retrieving Data: Bind Variables
} } catch (Exception ex) // catches any error { MessageBox.Show(ex.Message.ToString()); } Example 3–21
Catching Common Error Messages; VB
Catch ex As OracleException ' catches only Oracle errors Select Case ex.Number Case 1 MessageBox.Show("Error attempting to insert duplicate data.") Case 12545 MessageBox.Show("The database is unavailable.") Case Else MessageBox.Show("Database error: " + ex.Message.ToString()) End Select Catch ex As Exception ' catches any error MessageBox.Show(ex.Message.ToString())
The Catch statements should be ordered from most specific to most general. If there are no Oracle errors, the first Catch statement branch is skipped. The second Catch statement catches all other errors. After implementing Example 3–20 or Example 3–21, the ORA-12545 error appears as "The database is unavailable.":
4.
The Finally code block is always executed. If the connection object's Close() or Dispose() method calls are in the Finally code block, the database connection will always be closed when after the Try-Catch-Finally is complete. Attempting to close a closed database connection does not cause an error. If the database is unavailable, the database connection is not opened, so the Finally code block attempts to close a connection that does not exist, making these Close() and Dispose() calls irrelevant. However, placing Close() or Dispose() in the Finally code block guarantees that the connection is closed.
Retrieving Data: Bind Variables Bind variables are essentially placeholders in a SQL statement. When a database receives a SQL statement, it checks the shared memory pool to see if the statement already exists and is stored in memory. If the statement exists in memory, Oracle Database can reuse it and skip the task of parsing and optimizing the statement. In using bind variables, you greatly increase the likelihood that SQL statements will be stored in memory, making them easily and quickly available to the next operation that needs them.
3-16 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Retrieving Data: Bind Variables
1.
Example 3–22 shows a typical SELECT statement that does not use bind variables, with the value 10 specified in the WHERE clause of the statement.
Example 3–22
SELECT Statement without Bind Variables
SELECT department_name FROM departments WHERE department_id = 10 2.
Example 3–23 replaces the numerical value with a bind variable :department_ id. The bind variable identifier always begins with a single colon, :, in SQL statements.
Example 3–23
SELECT Statement with Bind Variables
select department_name from departments where department_id = :department_id 3.
Bind variables can also be used with UPDATE, INSERT, and DELETE statements. Example 3–23 shows how to use bind variables in an UPDATE statement; "Inserting, Deleting and Updating Data" on page 3-28 provides more details.
Example 3–24
UPDATE Statement with Bind Variables
UPDATE departments SET department_name WHERE department_id 4.
= :department_name = :department_id
Use the OracleParameter class to represent each bind variable in your .NET code. The OracleParameterCollection class contains the OracleParameter objects associated with the OracleCommand object for each statement. The OracleCommand class passes your SQL statement to the database and returns the results to your application. You can bind variables by position or by name. The OracleCommand property BindByName (which defaults to false) sets the mode. When binding by position mode, you must use the Add() method to add the parameters to the OracleParameterCollection in the same order as they appear in the SQL statement or stored procedure. If you want to use bind by name mode, you may add the parameters to the collection in any order; however, you must set the ParameterName property for the parameter object to the same value as the bind variable identifier in the stored procedure declaration.
5.
In addition to the binding mode (by position or by name), the following properties are typically set for each parameter object: Direction, OracleDbType, Size, and Value. ■
■
■
Direction Bind variables may be used as output, input, or input/output parameters. The Direction property indicates the direction of each parameter. The default value of the Direction property is Input. OracleDbType property indicates whether the parameter is a number, a date, a VARCHAR2, and so on. Size indicates the maximum size of the data that the parameter will hold, for parameters with a variable length data type, like VARCHAR2.
Beta Draft
Using Oracle Data Provider for .NET 3-17
Retrieving Data: Bind Variables
■
6.
Value contains the parameter value either before statement execution (for input parameters), after execution (for output parameters), or both before and after (for input/output parameter).
Example 3–25 and Example 3–26 tie together these concepts and use a bind variable in a SELECT statement. Note that Direction property uses the default value Input, and the Size property is not set. Since the object is an input parameter, you don't need to set the Size property because the data provider can determine the size from the value.
Example 3–25
Using a Bind Variable; #C
static void Main(string[] args) { string oradb = "userid=hr;password=hr;data source=//hr-server:1521/XE"; OracleConnection con = new OracleConnection(oradeb); con.Open(); StringBuilder sbSQL = new StringBuilder(); sbSQL.Append("select department_name "); sbSQL.Append("from departments "); sbSQL.Append("where department_id = :department_id"); OracleCommand cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = sbSQL.ToString();
// creates command
OracleParameter p_department_id = new OracleParameter(); // creates parameter p_department_id.OracleDbType = OracleDbType.Decimal; // sets data type p_department_id.Value = 10; // sets value cmd.Parameters.Add(p_department_id);
// adds to collection
OracleDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { Console.WriteLine("Department Name: {0}", dr.GetOracleString(0)); } dr.Dispose(); p_department_id.Dispose(); cmd.Dispose(); con.Dispose(); } Example 3–26
Using a Bind Variable; VB
Sub Main() Dim constr As String = "userid=hr;password=hr;data source=//hr-server:1521/XE" Dim con As OracleConnection = New OracleConnection(constr) con.Open() Dim sbSQL As StringBuilder = New StringBuilder sbSQL.Append("select department_name ") sbSQL.Append("from departments ") sbSQL.Append("where department_id = :department_id")
3-18 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Retrieving Data: Multiple Values
Dim cmd As OracleCommand = New OracleCommand ' creates command cmd.Connection = con cmd.CommandText = sbSQL.ToString() Dim p_department_id As OracleParameter = New OracleParameter ' creates parameter p_department_id.OracleDbType = OracleDbType.Decimal ' sets data type p_department_id.Value = 10 'sets value cmd.Parameters.Add(p_department_id) ' adds to collection Dim dr As OracleDataReader = cmd.ExecuteReader() If (dr.Read()) Then Console.WriteLine("Country Name: {0}", dr.GetOracleString(0)) End If dr.Dispose() p_department_id.Dispose() cmd.Dispose() con.Dispose() End Sub
Your output should look like this: Department Name: Administration
Retrieving Data: Multiple Values 1.
A DataReader object can retrieve values for multiple columns and multiple rows. Consider a multiple column, single row query in Example 3–27:
Example 3–27
Querying for a Multiple Column Single Row Result
SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id = 10 2.
Use either zero-based ordinals or column names to obtain the values of the columns, as shown in Example 3–28 and Example 3–29.
Example 3–28
Using Column Ordinals and Names from a Query Result; C#
Label1.Text = "The " + dr.GetString(1) + " department is at location # " + dr.GetString(3); Example 3–29
Using Column Ordinals and Names from a Query Result; VB
Label1.Text = "The " + dr.Item(1) + " department is at location # " + _ dr.Item(3); 3.
A query can also return multiple rows, as in:
Example 3–30
Querying for a Multiple Column Multiple Row Result
SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id > 100 4.
A looping construct is needed to process multiple rows from the DataReader object. Also, a control that can display multiple rows is very useful. Because a
Beta Draft
Using Oracle Data Provider for .NET 3-19
Using the DataSet Class with Oracle Data Provider for .NET
OracleDataReader is a forward-only, read-only cursor, it cannot be bound to an updatable or fully scrollable control such as Windows Forms DataGrid control. A DataReader is, however, compatible with a ListBox control, as shown in Example 3–31 and Example 3–32. Example 3–31
Looping Through a Multi-Row Query Result; C#
while (dr.Read()) { listBox1.Items.Add("The " + dr.GetString(1) + " department is at location # " + dr.GetString(3); } Example 3–32
Looping Through a Multi-Row Query Result; VB
While (dr.Read()) ListBox1.Items.Add("The " + dr.Item(1) + " department is at location # " + _ dr.Item(3) End While
Using the DataSet Class with Oracle Data Provider for .NET The DataSet class encapsulates a memory-resident representation of data that provides a consistent relational programming model for multiple data sources. It consists of one or more tables that store relational or XML data. Unlike OracleDataReader, a DataSet is updateable and backwards scrollable. Let us return to the project started in "Starting a New Project" on page 3-1. 1.
If you are not in code view for Form1, use the F7 keyboard shortcut. In the code view, insert the following instantiations within the Form1 class declaration, as shown in Example 3–33 and Example 3–34:
Example 3–33 private private private private private
OracleConnection conn; OracleCommand cmd; OracleDataAdapter da; OracleCommandBuilder cb; DataSet ds;
Example 3–34 Private Private Private Private Private 2.
Using DataSet Class: Declaring Variables; C#
Using DataSet Class: Declaring Variables; VB
conn As OracleConnection cmd As OracleCommand da As OracleDataAdapter cb As OracleCommandBuilder ds As DataSet
Within method Form1(), assign values to these variables, as shown in Example 3–35 and Example 3–36.
Example 3–35
Using DataSet Class: Assigning Values; C#
conn = new OracleConnection(oradb); cmd = new OracleCommand("SELECT department_id, department_name, manager_id," + " location_id FROM departments WHERE department_id > 100", conn); da = new OracleDataAdapter(cmd); cb = new OracleCommandBuilder(da); ds = new DataSet(); 3-20 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the DataSet Class with Oracle Data Provider for .NET
Example 3–36
Using DataSet Class: Assigning Values; VB
Me.conn = New OracleConnection(oradb) Me.cmd = New OracleCommand("SELECT department_id, department_name," & _ " manager_id, location_id FROM departments WHERE department_id > 100", conn) Me.da = New OracleDataAdapter(cmd) Me.cb = New OracleCommandBuilder(da) Me.ds = New DataSet 3.
You must now open the connection, and fill the DataSet object with the result of the query in the OracleCommand, as shown in Example 3–37 and Example 3–38.
Example 3–37
Opening a Connection and Filling the DataSet; C#
conn.Open(); da.Fill(ds); Example 3–38
Opening a Connection and Filling the DataSet; VB
Me.conn.Open() Me.da.Fill(ds) 4.
Switch to design view (Shift+F7 keyboard shortcut), and from the View menu, select Toolbox.
A Toolbox window will appear. 5.
From the Toolbox, under Windows Forms, select a Data Grid and drag it onto Form1.
Beta Draft
Using Oracle Data Provider for .NET 3-21
Using the DataSet Class with Oracle Data Provider for .NET
A data grid will appear on top of the form. Expand the data grid to fill most of our form. 6.
From the Toolbox, under Windows Forms, drag and drop a Button onto Form1, below the data grid.
7.
Right-click on the new button, and select Properties.
3-22 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the DataSet Class with Oracle Data Provider for .NET
A Properties window will appear. 8.
In the Properties window, under Appearance, Text, enter Save. Under Design, (Name), enter Save. Click on the lightning icon (events) at the top of the Properties window.
Beta Draft
Using Oracle Data Provider for .NET 3-23
Using the DataSet Class with Oracle Data Provider for .NET
9.
Click the mouse on the highlighted Click event.
3-24 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the DataSet Class with Oracle Data Provider for .NET
10. Close the Properties window. Your form should now look something like the
following image.
Beta Draft
Using Oracle Data Provider for .NET 3-25
Using the DataSet Class with Oracle Data Provider for .NET
11. Switch to code view (F7 keyboard shortcut).
You will notice that there are two more object declarations in you Form1 class: a DataGrid and a Button, as shown in Example 3–39 and Example 3–39. Example 3–39
Declaring Windows.Forms Objects Automatically; C#
private System.Windows.Forms.DataGrid dataGrid1; private System.Windows.Forms.Button Save; Example 3–40
Declaring Windows.Forms Objects Automatically; VB
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid Friend WithEvents Save As System.Windows.Forms.Button
There is also automatically generated code that defines them. Do not alter it. 12. You must now make a bind between the new DataGrid and the DataSet.
At the end of your Form1() method declaration, add the binding as shown in Example 3–41 and Example 3–42. Example 3–41
Binding the DataSet to the DataGrid; C#
dataGrid1.DataSource = ds.Tables[0]; Example 3–42
Binding the DataSet to the DataGrid; VB
Me.dataGrid1.DataSource = ds.Tables[0] 13. Your code also contains a new method that has been automatically generated for
you. Insert a new line into the body of the Save_Click() method, as shown in Example 3–43 and Example 3–44. Example 3–43
The Save_Click() Method; C#
private void Save_Click(object sender, System.EventArgs e) { da.Update(ds.Tables[0]); } Example 3–44
The Save_Click() Method; VB
Private Sub Save_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Save.Click da.Update(ds.Tables(0)) End Sub 14. Save Form1 using Ctr+S keyboard shortcut. 15. From the Build menu, select Build Solution, or use the Ctr+Shift+B keyboard
shortcut.
3-26 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the DataSet Class with Oracle Data Provider for .NET
You should see an Output window.
16. From the Debug menu, select Start to run your application. Alternatively, use the
F5 keyboard shortcut.
Your application run-time window, titled Form1, will appear.
Beta Draft
Using Oracle Data Provider for .NET 3-27
Inserting, Deleting and Updating Data
Inserting, Deleting and Updating Data 1.
Scroll down to the bottom of the data grid, and enter a new record at the * prompt as follows: ■
For DEPARTMENT_ID, enter 280
■
For DEPARTMENT_NAME, enter Community Outreach
■
Leave MANAGER_ID as null
■
For LOCATION_ID, enter 1700
Click the Save button.
2.
Close the application, and start it again using the F5 keyboard shortcut.
3.
Scroll down to the bottom of the list, and note that the new record is now part of the DEPARTMENTS table. Change the name of the department to Community Volunteers, and click the Save button.
3-28 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Inserting, Deleting and Updating Data
4.
Close the application, and start it again using the F5 keyboard shortcut.
5.
Scroll down to the bottom of the list, and note that the DEPARTMENT_NAME has changed. Select the entire record you just changed (click the cursor icon before it), and delete it using the Delete key. Click the Save button.
6.
Close the application, and start it again using the F5 keyboard shortcut.
7.
Scroll down to the bottom of the list, and note that the new record is no longer part of the DEPARTMENTS table.
8.
Close the Form1 application.
Beta Draft
Using Oracle Data Provider for .NET 3-29
Closing the Database Connection
Closing the Database Connection 1.
A connection object's Close() and Dispose() methods close the connection to the database. If a connection is not already closed, the Dispose() method will close it. Example 3–45 and Example 3–46 show the use of these methods.
Example 3–45
Closing and Disposing a Connection; C#
conn.Close(); conn.Dispose();
Example 3–46
Closing and Disposing a Connection; VB
conn.Close() conn.Dispose() 2.
C# has an alternative syntax that disposes of a connection when it goes out of scope, through the using keyword, as shown in Example 3–47.
Example 3–47
Closing and Disposing a Connection when Out of Scope; C#
using (OracleConnection conn = new OracleConnection(oradb)) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "select department_name from departments where department_id = 10"; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); label1.Text = dr.GetString(0); }
3-30 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
4 Using Oracle Developer Tools for Visual Studio .NET This chapter explains how to use Oracle Developer Tools. This chapter contains the following sections: ■
Connecting to the Oracle Database
■
Building a New Table
■
Generating Code Automatically
■
Enabling Database Updates
■
Disconnecting from the Oracle Database
■
Reconnecting to the Oracle Database
Begin by starting a new project, as described in Chapter 3, "Using Oracle Data Provider for .NET", section "Starting a New Project" on page 3-1.
Connecting to the Oracle Database To connect to an Oracle Database from Visual Studio .NET, follow these steps: 1.
From the View menu, select Oracle Explorer.
2.
In Oracle Explorer, right-click the Data Connections node and from the menu, select Add Connection. Alternatively, click the + icon (Add Connection) at the top of the Oracle Explorer pane, or select Connect to Oracle Database from the Tools menu.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-1
Connecting to the Oracle Database
The application opens an Add Connection window. 3.
In the Add Connection window, enter the following information: Data source name: Select the Oracle database to use as the data source; here, we are using //hr-server:1521/XE. If the database is remote, use the EZConnect naming string to connect to the database. Select the Use a specific user name and password option if you want to connect to the Oracle Database as the currently logged on Windows user. For User name, enter hr. For Password, enter the password that was set for the hr user during the configuration phase of the Oracle Database installation. If you wish to save the password for future sessions after you exit the current session, check the Save password box. Ensure that the Role is set to Default. This refers to the default roles that have been granted to the user hr. The Connection name should be generated automatically from the Data source name and the User name values.
4-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Connecting to the Oracle Database
Under the Apply Filters tab, check that the HR schema is in the Displayed schemas column. Only the schema objects (tables, views, and so on) from the schemas selected in the Apply Filters tab are displayed when you expand the schema category nodes in the data connection.
Return to the Connection Details tab, and click Test connection. The test should succeed. Click OK.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-3
Building a New Table
If the test fails, it may be due to one or more of the following issues that you must address before proceeding with further steps: ■
The database is not started.
■
The database connectivity is not properly configured. Check that:
■
4.
–
The tsname.ora file exists and contains the correct settings.
–
The TNS_ADMIN environment variable is properly set.
–
NAMES.DIRECTORY_PATH is properly set in the sqlnet.ora file.
You do not have the correct user name, password, and role.
The Oracle Explorer pane should now contain the hr.(Local Database) connection. Expand the connection to show the contents of the hr schema. You should see Tables, Views, Procedures, Functions, and so on.
Building a New Table This section will describe how to create a new table, its columns, indexes, and constraints.
Creating a Table and Its Columns 1.
In Oracle Explorer, right-click on Tables and select New Relational Table...
4-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
A table design window appears. 2.
In design view, enter DEPENDENTS for Table name, and click Add in the Columns tab.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-5
Building a New Table
Note that a default column description appears in the Columns tab. 3.
Under Column Properties, enter Name LASTNAME, Data Type VARCHAR2, and Size 30. Leave all other properties at their default values. Click Add.
Note that column LASTNAME is added to the table. 4.
Under Column Properties, enter Name FIRSTNAME, Data Type VARCHAR2, and Size 30. Leave all other properties at their default values. Click Add.
4-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
Note that column FIRSTNAME is added to the table. 5.
Under Column Properties, enter Name BIRTHDATE, Data Type DATE, and leave all other properties at their default values. Click Add.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-7
Building a New Table
Note that column BIRTHDATE is added to the table. 6.
Under Column Properties, enter Name RELATIONSHIP, Data Type VARCHAR2, and Size 20. Leave all other properties at their default values. Click Add.
4-8 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
Note that column RELATIONSHIP is added to the table. 7.
Under Column Properties, enter Name EMPLOYEEID, Data Type NUMBER, deselect the Allow null check box, enter Precision 6 and Scale 0. Click Add.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-9
Building a New Table
Note that column EMPLOYEEID is added to the table. 8.
Under Column Properties, enter Name DEPENDENTID, Data Type NUMBER, deselect the Allow null check box, enter Precision 4 and Scale 0. Click Preview SQL.
4-10 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
A Preview SQL window appears. 9.
The content of the Preview SQL window is show in Example 4–1:
Example 4–1 Generated SQL Form of the New Table CREATE TABLE "HR"."DEPENDENTS" ("LASTNAME" VARCHAR2(30) NULL,"FIRSTNAME" VARCHAR2(30) NULL,"BIRTHDATE" DATE NULL,"RELATIONSHIP" VARCHAR2(20) NULL,"EMPLOYEEID" NUMBER(6,0) NOT NULL,"DEPENDENTID" NUMBER(4,0) NOT NULL);
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-11
Building a New Table
Click OK to close the Preview SQL window. The design view appears. 10. In the design view, click Save.
Creating an Index 1.
Now you must create an index for the DEPENDENTS table. In the design view, click the Indexes tab.
2.
Click Add.
4-12 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
Note that the Index Property area is activated. 3.
Under Index properties, enter Name DEPENDENTS_INDEX, and leave all other properties in their default state.
4.
In the Index properties area, click Add.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-13
Building a New Table
5.
Select DEPENDENTID from the drop-down list under Key.
6.
Click Preview SQL.
4-14 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
A Preview SQL window appears. 7.
The code displayed in the Preview SQL window is shown in Example 4–2.
Example 4–2 Creating a Table Index in SQL CREATE
INDEX "HR"."DEPENDENTS_INDEX" ON "HR"."DEPENDENTS" ("DEPENDENTID"
);
Click OK to close the Preview SQL window.
The design view appears. Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-15
Building a New Table
8.
In the design view, click Save.
Adding Constraints 1.
Now you must create a foreign key to the EMPLOYEES table. Click the Constraints tab. Note that depending on your configuration, there are may already be default constraints in the list.
2.
Click Add.
4-16 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
Note that the Constraint Properties area is now activated. 3.
Under Constraint Properties, enter Name EMPLOYEES_FK, select Type Foreign Key from the drop-down list, select Table EMPLOYEES and Constraint EMP_EMP_ ID_PK. Under Associations, select EMPLOYEE_ID as Referenced Column and EMPLOYEEID as Local Column. Leave all other properties at their default values.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-17
Building a New Table
Note that EMPLOYEES_FK is now in list of constraints. 4.
Now you must add the primary key for the new table, DEPENDENTS. Click Add. Under Constraint Properties, enter Name DEPENDENTS_PK, select Type Primary Key from the drop-down list. Under Primary Key Columns, select DEPENDENTID. Leave all other properties at their default values.
4-18 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
5.
Click Preview SQL. A Preview SQL window appears.
6.
The SQL code generated for constraints on table DEPENDENTS is shown in Example 4–3. Note that addition of both constraints is an ALTER TABLE command; this is because the constraints change alter the definitions of columns DEPENDENTID and EMPLOYEEID.
Example 4–3 Adding Foreign Key and Primary Key Constraints to a Table ALTER TABLE "HR"."DEPENDENTS" ADD ( CONSTRAINT "EMPLOYEES_FK" FOREIGN KEY ("EMPLOYEEID") REFERENCES "HR"."EMPLOYEES" (EMPLOYEE_ID) ENABLE VALIDATE ); ALTER TABLE "HR"."DEPENDENTS" ADD ( CONSTRAINT "DEPENDENTS_PK" PRIMARY KEY ("DEPENDENTID") ENABLE VALIDATE );
Click OK to close the Preview SQL window. 7.
In design view, click Save. Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-19
Building a New Table
Notice that if you expand the DEPENDENTS table in the Oracle Explorer, all the columns, constraints and indexes of the table are visible.
Adding Data to a Table 1.
You must now add data to the new, but empty, DEPENDENTS table. In Oracle Explorer, right-click the DEPENDENTS table and select Retrieve Data.
A table grid for DEPENDENTS appears in design view.
4-20 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
2.
Enter the following 4 records in the table. Click Save.
Generating Code Automatically To explore the content of table DEPARTMENTS, we will build a form that uses a simple table query. 1.
Start a new project, as outlined in Chapter 3, "Using Oracle Data Provider for .NET", section "Starting a New Project" on page 3-1.
2.
From the View menu, select Oracle Explorer.
3.
In Oracle Explorer, expand Data Connections, expand hr. (Local Database), and finally expand the Tables component. Using your mouse, select the DEPARTMENTS table. Drag and drop the table onto Form1 in the Designer pane.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-21
Generating Code Automatically
A Microsoft Development Environment pop-up window will ask if you wish to save the connection password in the generated code. Click Yes.
You will notice that this action creates an instance of an OracleConnection class, departmentsOracleConnection1, visible below the Designer pane. An instances of the OracleDataAdapter class is also created, departmentsOracleDataAdapter1.
4-22 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
Both these objects represent automatically generated code in the code that is behind Form1. 4.
Right-click on the departmentsOracleDataAdapter1, and select Generate DataSet...
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-23
Generating Code Automatically
departments11 is now added to your Designer pane.
5.
Add a DataGrid object to the form. Select Toolbox from the View menu.
4-24 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
A Toolbox pane will appear. 6.
From the Toolbox, under Window Forms, select DataGrid and drag and drop in onto Form1.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-25
Generating Code Automatically
At this point, you should enlarge both Form1 and the DataGrid.
4-26 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
7.
Set the Data Source of the DataGrid. Select Properties Window from the View menu.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-27
Generating Code Automatically
The Properties pane appears. 8.
In the Properties pane, under Data, select departments11.Departments from the drop-down list that represents DataSource options.
4-28 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
Close the Properties pane. 9.
The DataGrid now contains the column headings from the table DEPARTMENTS.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-29
Generating Code Automatically
10. Switch to code view by double-clicking on the newly created objects, by selecting
Code from the View menu, or by using the F7 keyboard shortcut. 11. Immediately after the InitializeComponent(); command, type the code in
Example 4–4 or Example 4–5. Example 4–4 Filling Data into the Form; C# this.departmentsOracleDataAdapter1.Fill(this.departments11.Departments); Example 4–5 Filling Data into the Form; VB Me.departmentsOracleDataAdapter1.Fill(Me.departments11.Departments)
4-30 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
12. From the Build menu, select Build Solution. Alternatively, you can use the
Ctrl+Shift+B keyboard shortcut.
Check the output to ensure that there were no problems with the build. Double-click the Output tab in the lower left corner of the window.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-31
Generating Code Automatically
Close the Output window. 13. Select Start from the Debug menu, or simply use the F5 keyboard shortcut. You
application will run, and a Form1 with data appears.
You may need to increase the width of the columns to see all the data. 14. You can navigate across Form1 by clicking the mouse down the data set; the
current record will be marked by the cursor. You can also sort the records, either in ascending or descending order, on any of the columns, by clicking on the column heading (notice the direction indicator in the DEPARTMENT_NAME column).
4-32 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Enabling Database Updates
15. Close Form1.
Enabling Database Updates 1.
Select Toolbox from the View menu. Under Window Forms, select Button and drag and drop it onto Form1, below the DataGrid.
A button called button1 will appear on the form.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-33
Enabling Database Updates
2.
Right-click on the button, and select Properties.
The Properties pane will appear. 3.
In the Properties pane, under Appearance, change the value of the text property to Save. While not strictly necessary, you should also change the name under Design to Save. This will make it easier to understand and maintain the code of the application. Click on the lightning icon (events) at the top of the Properties window.
4-34 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Enabling Database Updates
4.
Click the mouse on the highlighted Click event.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-35
Enabling Database Updates
5.
Close the Properties window. Your form should now have a Save button below the data grid.
6.
Double-click on the Save button.
4-36 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Disconnecting from the Oracle Database
The code view appears, with focus on the new and empty Save_Click() method. 7.
Change the code of the Save_Click() method to bind the table update event to the button, as shown in Example 4–6.
Example 4–6 The Save_Click() Method; #C private void Save_Click(object sender, System.EventArgs e) { departmentsOracleDataAdapter1.Update(departments11); } Example 4–7 The Save_Click() Method; VB Private Sub Save_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Save.Click departmentsOracleDataAdapter1.Update(departments11) End Sub 8.
At this point, you can re-build and run your Form1 application. Use the Ctrl+Shift+B keyboard shortcut to build, and F5 keyboard shortcut to start. Your Form1 application window should appear.
9.
To use this form, follow the instructions in Chapter 3, section "Inserting, Deleting and Updating Data" on page 3-28.
Disconnecting from the Oracle Database 1.
In Oracle Explorer, expand the Data Connections node.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-37
Reconnecting to the Oracle Database
Right-click the Data Connection that represents the hr database connection. Select Close from the drop-down menu. The data connection will now close, as indicated by the red x.
Reconnecting to the Oracle Database 1.
In Oracle Explorer, expand Data Connections, and right-click on the closed connection, hr. Select Open from the drop-down menu. The hr connection will be re-opened, and the node will expand to show the components of the HR schema.
4-38 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Reconnecting to the Oracle Database
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-39
Reconnecting to the Oracle Database
4-40 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
5 Using PL/SQL Stored Procedures and REF Cursors This chapter contains the following sections: ■
Introduction to Stored Procedures
■
Introduction to Ref Cursors
■
Introduction to PL/SQL Packages and Package Bodies
■
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
Introduction to Stored Procedures A stored procedure is a named set of PL/SQL statements designed to perform an action. Unlike functions, stored procedures do not return values, but they can accept values as input.
Introduction to Ref Cursors Ref cursors are one of the most powerful, flexible, and scalable methods for returning query results from an Oracle Database to a client application. A ref cursor is a PL/SQL datatype whose value is an address that represents the memory location of a query work area on the database server. A query work area can be thought of as the result set, or a row set, on the server; it is the location where the results of a query are stored in server memory. In essence, a ref cursor is a handle to a result set on the server. A ref cursor is represented through the OracleRefCursor class. Ref cursors have the following characteristics: 1.
A ref cursor refers to server memory. The memory address represented by a ref cursor "lives" on the database server, not on the client machine. Therefore, the client’s connection to the database must be in place during the lifetime of the ref cursor. If the underlying connection to the database is closed, the ref cursor will become inaccessible to the client.
2.
A ref cursor involves an additional database round trip. Because a ref cursor is a pointer to memory on the server that is returned to the client, the actual data contained in the ref cursor is not initially returned to the client. The client must request the data contained in the ref cursor after it has opened the ref cursor. Note that data will not be retrieved until the user attempts to read it.
3.
A ref cursor is not updatable. The result set represented by the ref cursor is read-only. You cannot update the database by using a ref cursor.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-1
Introduction to PL/SQL Packages and Package Bodies
4.
A ref cursor is not backward-scrollable. The data represented by the ref cursor is accessed in a forward-only, serial manner. You cannot position a record pointer inside the ref cursor to point to random records in the result set.
5.
A ref cursor is a PL/SQL datatype. You create and return a ref cursor inside a PL/SQL code block.
6.
A ref cursor can be either weak- or strong-typed. A strong-typed ref cursor has a return type defined when the ref cursor itself is declared in PL/SQL, and can only refer to the same type or structure with which it is initially declared. A weak-typed ref cursor has no return type defined, and can refer to a query work area of any type. A strong-typed ref cursor is specific, while a weak-typed ref cursor is generic. This section will discuss the use of weak-typed ref cursors.
Introduction to PL/SQL Packages and Package Bodies A PL/SQL package stores related items as a single logical entity. A package is composed of two distinct pieces: ■
■
The package specification defines what is contained in the package; it is analogous to a header file in a language such as C++. The specification defines all public items. The specification is the published interface to a package. The package body contains the code for the procedures and functions defined in the specification, and the code for private procedures and functions that are not declared in the specification. This private code is only "visible" within the package body.
The package specification and body are stored as separate objects in the data dictionary and can be seen in the user_source view. The specification is stored as the PACKAGE type, and the body is stored as the PACKAGE BODY type. While it is possible to have a specification without a body, as when declaring a set of public constants, it is not possible to have a body with no specification.
Creating a PL/SQL Stored Procedure that Uses Ref Cursors 1.
From the Oracle Explorer window, right-click on Packages and select New Package.
The New Package window appears.
5-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
2.
In the New Package window, change the Package Name to STORED.
3.
Click Add.
The Add Method window appears. 4.
In the Add Method window, enter Method Name GETCURSORS, and change Method Type to Procedure. Click Add.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-3
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
5.
In the Add Method window, under Parameter Details, enter DEPARTMENTID for Name, IN for Direction, and NUMBER for Data Type. Click Add.
6.
Enter a second parameter under Parameter Details, with EMPLOYEESCUR for Name, OUT for Direction, and SYS_REFCURSOR for Data Type. Click Add.
7.
Enter a third parameter under Parameter Details, with DEPENDENTSCUR for Name, OUT for Direction, and SYS_REFCURSOR for Data Type. Click OK. The New Package window will appear.
8.
Click Preview SQL >> to see the SQL code created.
5-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
A Preview SQL window will appear. 9.
The Preview SQL window will contain the following code, as show in Example 5–1:
Example 5–1 PL/SQL Code for Package STORED CREATE PACKAGE "HR"."STORED" IS -- Declare types, variables, constants, exceptions, cursors, -- and subprograms that can be referenced from outside the package. -- Example -- TYPE tableType IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER; -- PROCEDURE CalculateSalary(rate IN NUMBER, hoursWorked IN NUMBER, salary OUT NUMBER); PROCEDURE "GETCURSORS" ( "DEPARTMENTID" IN NUMBER, "EMPLOYEESCUR" OUT SYS_REFCURSOR, "DEPENDENTSCUR" OUT SYS_REFCURSOR); END "STORED"; CREATE PACKAGE BODY "HR"."STORED" IS -- Implement subprograms, initialize variables declared in package specification. -- Example -- PROCEDURE CalculateSalary(rate IN NUMBER, hoursWorked IN NUMBER, salary OUT NUMBER) IS -- BEGIN
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-5
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
-salary := rate*hoursWorked; -- END; -- Make private declarations of types and items, that are not accessible outside the package PROCEDURE "GETCURSORS" ( "DEPARTMENTID" IN NUMBER, "EMPLOYEESCUR" OUT SYS_REFCURSOR, "DEPENDENTSCUR" OUT SYS_REFCURSOR) IS -- Declare constants and variables in this section.
Click OK to close the window. The New Package window appears. 10. In the New Package window, click OK.
Note that a new package, STORED, now appears in Oracle Explorer. 11. In Oracle Explorer, right-click on package STORED, and select Edit Package
Body...
5-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
The code for procedure STORED is displayed. 12. Scroll down to find the following line of code: NULL;
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-7
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
13. Replace NULL; with code in Example 5–2. Example 5–2 Assigning Reference Cursors OPEN EMPLOYEESCUR FOR SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=DEPARTMENTID; OPEN DEPENDENTSCUR FOR SELECT * FROM DEPENDENTS;
5-8 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
14. Right-click on the window tab and select Save. Alternatively, use the Ctrl+S
keyboard shortcut.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-9
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
15. To run the stored procedure, in Oracle Explorer, expand package STORED.
Right-click on GETCURSORS method, and select Run.
5-10 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
A Run Procedure window appears. 16. In the Run Procedure window, enter a Value of 60 for DEPARTMENTID. Click OK.
In the design view, the following message appears: Procedure was run successfully.
The Output window should also appear:
In the design view, the two new parameters appear: EMPLOYEESCUR and DEPENDENTSCUR. 17. In the design view, select the value for EMPLOYEESCUR.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors 5-11
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
The a Procedure Details area appears, showing the result of the EMPLOYEESCUR. 18. In the design view, select the value for DEPENDENTSCUR.
5-12 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
The a Procedure Details area appears, showing the result of the DEPENDENTSCUR.
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET 1.
Open the form you previously created in Chapter 3, "Using Oracle Data Provider for .NET". Switch to code view using the F7 keyboard shortcut.
2.
Replace the command assignment line, starting with cmd = New oracleCommand... with code in Example 5–3 or Example 5–3.
Example 5–3 Changing OracleCommand to Use a Stored Procedure; C# cmd = new OracleCommand("STORED.GETCURSORS", conn); cmd.CommandType = CommandType.StoredProcedure; Example 5–4 Changing OracleCommand to Use a Stored Procedure; VB cmd = New OracleCommand("STORED.GETCURSORS", conn) cmd.CommandType = CommandType.StoredProcedure 3.
Add definitions and bindings for the three parameters of the GETCURSORS stored procedure as OracleParameter objects, calling them dept_id, emp_cur and dnt_cur.
Example 5–5 Defining and Binding OracleParameter Objects for Stored Procedure; C# OracleParameter dept_id = new OracleParameter(); dept_id.OracleDbType = OracleDbType.Decimal; dept_id.Direction = ParameterDirection.Input; dept_id.Value = 60;
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors 5-13
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
cmd.Parameters.Add(dept_id); OracleParameter emp_cur = new OracleParameter(); emp_cur.OracleDbType = OracleDbType.RefCursor; emp_cur.Direction = ParameterDirection.Output; cmd.Parameters.Add(emp_cur); OracleParameter dnt_cur = new OracleParameter(); dnt_cur.OracleDbType = OracleDbType.RefCursor; dnt_cur.Direction = ParameterDirection.Output; cmd.Parameters.Add(dnt_cur); Example 5–6 Defining and Binding OracleParameter Objects for Stored Procedure; VB Dim dept_id As OracleParameter = New OracleParameter dept_id.OracleDbType = OracleDbType.Decimal dept_id.Direction = ParameterDirection.Input dept_id.Value = 60 cmd.Parameters.Add(dept_id) Dim emp_cur As OracleParameter = New OracleParameter emp_cur.OracleDbType = OracleDbType.RefCursor emp_cur.Direction = ParameterDirection.Output cmd.Parameters.Add(emp_cur) Dim dnt_cur As OracleParameter = New OracleParameter dnt_cur.OracleDbType = OracleDbType.RefCursor dnt_cur.Direction = ParameterDirection.Output cmd.Parameters.Add(dnt_cur) 4.
Re-compile Form1 using the Ctrl+Shift+B keyboard shortcut, and start the application using the F7 keyboard shortcut. A Form1 window will appear.
5.
Scroll horizontally to see that the last column, DEPARTMENT_ID, is equal to 60. Note that the DataGrid object contains the first result set from the stored procedure, which matches the query of the EMPLOYEES table. To view the results of the DEPENDENTS table, fill the DataGrid with results from dnt_cur.
5-14 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
You may remember that 60 is the value of the DEPARTMENTID parameter. 6.
Close the Form1 window.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors 5-15
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
5-16 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
6 Using .NET Stored Procedures This chapter discusses how to use and deploy .NET stored procedures in your application. This chapter contains the following sections: ■
Creating a New Connection
■
Creating .NET Stored Functions and Procedures
■
Deploying a .NET Stored Functions and Procedures
■
Running .NET Stored Functions and Procedures
Creating a New Connection 1.
Start Microsoft Visual Studio for .NET.
2.
From the View menu, select Oracle Explorer.
Beta Draft
Using .NET Stored Procedures
6-1
Creating a New Connection
3.
In Oracle Explorer, right-click the Data Connections node and from the menu, select Add Connection. Alternatively, you can click the + icon (Add Connection) in the Oracle Explorer pane.
4.
Enter ORCL for Data source name. Enter hr for the User name and hr for Password. Click Test Connection.
5.
Provided the connection was successful, dismiss the test result window by clicking OK.
6.
Click OK to close the Add Connection window.
6-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating .NET Stored Functions and Procedures
Creating .NET Stored Functions and Procedures 1.
Click the New Project button. Alternatively, from the File menu, select New, and then select Project.
A New Project dialog box appears. 2.
On the left side of the New Project dialog box under Project Types, select the C# programming language. If you intend to develop in Visual Basic, select it instead. On the right side of the New Project dialog box under Templates, select Oracle Project. Under Name, enter HumanResourcesStored. Check the Create Directory for Solution box. Click OK.
Beta Draft
Using .NET Stored Procedures
6-3
Creating .NET Stored Functions and Procedures
3.
Paste code in Example 6–1 or Example 6–2 into the Class1 object.
Example 6–1 Adding getDepartments() Method Code; C# public static int getDepartmentno(int employee_id) { int department_id = 0; // Get a connection to the db OracleConnection con = new OracleConnection(); con.ConnectionString = "context connection=true"; con.Open(); // Create and execute a command OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "select department_id from employees where employee_id = :1"; cmd.Parameters.Add(":1", OracleDbType.Int32, employee_id, ParameterDirection.Input); OracleDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) department_id=rdr.GetInt32(0); rdr.Close(); cmd.Dispose(); // Return the employee's department number return department_id; Example 6–2 Adding getDepartments() Method Code; VB Public Shared Function getDepartmentno(ByVal employee_id As Integer) As Integer Dim department_id As Integer = 0 ' Get a connection to the db Dim con As OracleConnection = New OracleConnection con.ConnectionString = "context connection=true" con.Open()
6-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Deploying a .NET Stored Functions and Procedures
' Create and execute a command Dim cmd As OracleCommand = con.CreateCommand() cmd.CommandText = "select department_id from employees where employee_id = :1" cmd.Parameters.Add(":1", OracleDbType.Int32, employee_id, ParameterDirection.Input) Dim rdr As OracleDataReader = cmd.ExecuteReader() While rdr.Read() department_id = rdr.GetInt32(0) rdr.Close() cmd.Dispose() ' Return the employee's department number getDepartmentno = department_id End While End Function 4.
Using the Ctrl+S keyboard shortcut, save Class1.
5.
From the Build menu, select Build Solution, or use the Ctrl+Shift+B keyboard shortcut.
Deploying a .NET Stored Functions and Procedures 1.
From the Build Menu, select Deploy Solution.
Beta Draft
Using .NET Stored Procedures
6-5
Deploying a .NET Stored Functions and Procedures
The window shows how to deploy a solution. *********************************************************************************************** An Oracle Deployment Wizard for .NET appears. 2.
Click Next.
3.
On the Configure Your Connection screen, click New Connection to establish a connection with SYSDBA privileges.
6-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Deploying a .NET Stored Functions and Procedures
4.
Select ORCL as the Source Database, enter sys for the Username and system password for the Password. Click Test Connection.
The test result window appears. 5.
Provided the connection was successful, dismiss the test result window by clicking OK.
Beta Draft
Using .NET Stored Procedures
6-7
Deploying a .NET Stored Functions and Procedures
6.
Click OK to save the connection and dismiss the Add Connection window.
7.
Click Next in the Oracle Deployment Wizard for .NET.
6-8 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Deploying a .NET Stored Functions and Procedures
8.
Ensure that Copy Assembly and Generate New Procedures is selected, and click Next.
9.
On the Specify the assembly and library name screen, accept the defaults and click Next.
Beta Draft
Using .NET Stored Procedures
6-9
Deploying a .NET Stored Functions and Procedures
10. On the Specify copy options screen, accept the defaults and click Next.
11. On the Specify methods and security details screen, under Available methods:,
expand HumanResourcesStored, then expand Class1, and select the getDepartmentno() method. Under Method Details, select HR from the Schema drop-down list. 6-10 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Deploying a .NET Stored Functions and Procedures
Click Next.
12. On the Summary screen, click Finish.
13. When your procedure deploys successfully, click OK.
Beta Draft
Using .NET Stored Procedures 6-11
Running .NET Stored Functions and Procedures
Running .NET Stored Functions and Procedures 1.
In Oracle Explorer, expand hr.ORCLE. Expand Functions. Right-click GETDEPARTMENTNO and select Run.
The Run Function window will appear. 2.
In the Run Function window, enter a Value of 100 for EMPLOYEE_ID. Click OK.
3.
Note that the return value for department is 90.
6-12 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Running .NET Stored Functions and Procedures
Beta Draft
Using .NET Stored Procedures 6-13
Running .NET Stored Functions and Procedures
6-14 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
7 Including Globalization Support This chapter discusses globalization support available in Oracle Data Provider for .NET. This chapter contains the following section: ■
Globalization Support
Globalization Support Oracle Data Provider for .NET globalization support enables applications to manipulate culture-sensitive data, such as ensuring proper string format, date, time, monetary, numeric, sort order, and calendar conventions, all depending on the Oracle client globalization settings.
Client Globalization Settings Client globalization settings derive from the Oracle globalization setting, NLS_LANG, in the Windows registry of the local computer. The client globalization parameter settings are read-only and remain constant throughout the lifetime of the application. Example 7–1 and Example 7–2 illustrate how these settings can be obtained by calling the OracleGlobalization.GetClientInfo() static method. The properties of the OracleGlobalization object provide the Oracle globalization value settings. Example 7–1 How to Obtain Oracle Globalization Settings; C# using System; using Oracle.DataAccess.Client; class ClientGlobalizationSample { static void Main() { OracleGlobalization ClientGlob = OracleGlobalization.GetClientInfo(); Console.WriteLine("Client machine language: " + ClientGlob.Language); Console.WriteLine("Client characterset: " + ClientGlob.ClientCharacterSet); } } Example 7–2 How to Obtain Oracle Globalization Settings; VB Imports System Imports Oracle.DataAccess.Client Module ClientGlobalizationSample
Beta Draft
Including Globalization Support
7-1
Globalization Support
Sub Main() Dim ClientGlob As OracleGlobalization = OracleGlobalization.GetClientInfo() Console.WriteLine("Client machine language: " + ClientGlob.Language) Console.WriteLine("Client characterset: " + ClientGlob.ClientCharacterSet) End Sub End Module
7-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Index
Beta Draft
Index-1
Index-2
Beta Draft
October 2005
Beta Draft
Oracle Database Express Edition 2 Day Plus .NET Developer Guide, 10g Release 2 (10.2) B25312-01 Copyright © 2005, Oracle. All rights reserved. Primary Author:
Roza Leyderman
Contributing Authors:
John Paul Cook, Mark Williams
Contributors: Alex Keh, Christian Shay The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited. The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose. If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software—Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065 The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs. Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party. Alpha and Beta Draft documentation are considered to be in prerelease status. This documentation is intended for demonstration and preliminary use only. We expect that you may encounter some errors, ranging from typographical errors to data inaccuracies. This documentation is subject to change without notice, and it may not be specific to the hardware on which you are using the software. Please be advised that prerelease documentation in not warranted in any manner, for any purpose, and we will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.
Contents List of Examples
Preface ................................................................................................................................................................. ix Audience....................................................................................................................................................... Documentation Accessibility ..................................................................................................................... Related Documents ..................................................................................................................................... Conventions .................................................................................................................................................
1
Introduction What is Microsoft .NET Framework..................................................................................................... Introduction to Oracle Data Provider for .NET .................................................................................. Introduction to Oracle Developer Tools for Visual Studio .NET.................................................... Introduction to .NET Stored Procedures..............................................................................................
2
ix ix x x
1-1 1-2 1-2 1-2
Installing Oracle Developer Tools What You Need ......................................................................................................................................... 2-1 Installing Oracle Developer Tools ........................................................................................................ 2-1 Testing the Installation............................................................................................................................ 2-4
3
Using Oracle Data Provider for .NET Starting a New Project ............................................................................................................................. 3-1 Adding a Reference............................................................................................................................ 3-3 Initial Programmatic Statements...................................................................................................... 3-6 Using the Connection Object ................................................................................................................. 3-8 Using the Command Object................................................................................................................ 3-11 Retrieving Data: a Simple Query ....................................................................................................... 3-13 Error Handling ....................................................................................................................................... 3-14 Retrieving Data: Bind Variables......................................................................................................... 3-16 Retrieving Data: Multiple Values ...................................................................................................... 3-19 Using the DataSet Class with Oracle Data Provider for .NET ..................................................... 3-20 Inserting, Deleting and Updating Data ............................................................................................ 3-28 Closing the Database Connection...................................................................................................... 3-30
4
Using Oracle Developer Tools for Visual Studio .NET Connecting to the Oracle Database....................................................................................................... 4-1
Beta Draft
iii
Building a New Table .............................................................................................................................. 4-4 Creating a Table and Its Columns ................................................................................................... 4-4 Creating an Index ............................................................................................................................ 4-12 Adding Constraints......................................................................................................................... 4-16 Adding Data to a Table .................................................................................................................. 4-20 Generating Code Automatically......................................................................................................... 4-21 Enabling Database Updates ................................................................................................................ 4-33 Disconnecting from the Oracle Database ......................................................................................... 4-37 Reconnecting to the Oracle Database................................................................................................ 4-38
5
Using PL/SQL Stored Procedures and REF Cursors Introduction to Stored Procedures ........................................................................................................ 5-1 Introduction to Ref Cursors.................................................................................................................... 5-1 Introduction to PL/SQL Packages and Package Bodies .................................................................... 5-2 Creating a PL/SQL Stored Procedure that Uses Ref Cursors ........................................................... 5-2 Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET............................ 5-13
6
Using .NET Stored Procedures Creating a New Connection ................................................................................................................... 6-1 Creating .NET Stored Functions and Procedures............................................................................... 6-3 Deploying a .NET Stored Functions and Procedures........................................................................ 6-5 Running .NET Stored Functions and Procedures ........................................................................... 6-12
7
Including Globalization Support Globalization Support............................................................................................................................. 7-1 Client Globalization Settings ............................................................................................................ 7-1
Index
iv
Beta Draft
List of Examples 2–1 2–2 2–3 2–4 3–1 3–2 3–3 3–4 3–5 3–6 3–7 3–8 3–9 3–10 3–11 3–12 3–13 3–14 3–15 3–16 3–17 3–18 3–19 3–20 3–21 3–22 3–23 3–24 3–25 3–26 3–27 3–28 3–29 3–30 3–31 3–32 3–33 3–34 3–35 3–36 3–37 3–38 3–39 3–40 3–41 3–42 3–43 3–44 3–45 3–46 3–47 4–1 4–2 4–3 vi
Starting SQL*Plus and Connecting as System Administrator.............................................. 2-5 Unlocking the User Account ..................................................................................................... 2-5 Resetting the Password for the User Account ........................................................................ 2-5 Testing the Connection .............................................................................................................. 2-5 Easy Connect Naming Method Syntax for Data Source ....................................................... 3-8 Using Oracle Database Alias in Applications; C# .................................................................. 3-9 Using Oracle Database Alias in Applications; VB ................................................................. 3-9 Creating a Connection String; C# .......................................................................................... 3-10 Creating a Connection String; VB.......................................................................................... 3-10 Creating an Empty Connection String and then Assigning its Value; C#....................... 3-10 Creating an Empty Connection String and then Assigning its Value; VB ...................... 3-10 Opening a Connection with Open(); C# ............................................................................... 3-11 Opening a Connection with Open(); VB............................................................................... 3-11 Creating a SQL Statement String; C# .................................................................................... 3-11 Creating a SQL Statement String; VB.................................................................................... 3-11 Using a Command to Query the Database; C#.................................................................... 3-12 Using a Command to Query the Database; VB ................................................................... 3-12 Retrieving a Value; C#............................................................................................................. 3-13 Retrieving a Value; VB ............................................................................................................ 3-13 Converting Data Types Explicitly; C#................................................................................... 3-14 Converting Data Types Explicitly; VB .................................................................................. 3-14 Error Handling with "Try-Catch-Finally" Syntax; C#......................................................... 3-14 Error Handling with "Try-Catch-Finally" Syntax; VB ........................................................ 3-15 Catching Common Error Messages; C#................................................................................ 3-15 Catching Common Error Messages; VB ............................................................................... 3-16 SELECT Statement without Bind Variables ......................................................................... 3-17 SELECT Statement with Bind Variables ............................................................................... 3-17 UPDATE Statement with Bind Variables ............................................................................. 3-17 Using a Bind Variable; #C....................................................................................................... 3-18 Using a Bind Variable; VB ...................................................................................................... 3-18 Querying for a Multiple Column Single Row Result ......................................................... 3-19 Using Column Ordinals and Names from a Query Result; C#......................................... 3-19 Using Column Ordinals and Names from a Query Result; VB ........................................ 3-19 Querying for a Multiple Column Multiple Row Result ..................................................... 3-19 Looping Through a Multi-Row Query Result; C# .............................................................. 3-20 Looping Through a Multi-Row Query Result; VB .............................................................. 3-20 Using DataSet Class: Declaring Variables; C# ..................................................................... 3-20 Using DataSet Class: Declaring Variables; VB..................................................................... 3-20 Using DataSet Class: Assigning Values; C# ......................................................................... 3-20 Using DataSet Class: Assigning Values; VB ........................................................................ 3-21 Opening a Connection and Filling the DataSet; C# ............................................................ 3-21 Opening a Connection and Filling the DataSet; VB............................................................ 3-21 Declaring Windows.Forms Objects Automatically; C#...................................................... 3-26 Declaring Windows.Forms Objects Automatically; VB ..................................................... 3-26 Binding the DataSet to the DataGrid; C#.............................................................................. 3-26 Binding the DataSet to the DataGrid; VB ............................................................................. 3-26 The Save_Click() Method; C#................................................................................................. 3-26 The Save_Click() Method; VB ................................................................................................ 3-26 Closing and Disposing a Connection; C# ............................................................................. 3-30 Closing and Disposing a Connection; VB ............................................................................ 3-30 Closing and Disposing a Connection when Out of Scope; C# .......................................... 3-30 Generated SQL Form of the New Table ............................................................................... 4-11 Creating a Table Index in SQL ............................................................................................... 4-15 Adding Foreign Key and Primary Key Constraints to a Table ......................................... 4-19 Beta Draft
4–4 4–5 4–6 4–7 5–1 5–2 5–3 5–4 5–5 5–6 6–1 6–2 7–1 7–2
Filling Data into the Form; C#................................................................................................ 4-30 Filling Data into the Form; VB ............................................................................................... 4-30 The Save_Click() Method; #C................................................................................................. 4-37 The Save_Click() Method; VB ................................................................................................ 4-37 PL/SQL Code for Package STORED........................................................................................ 5-5 Assigning Reference Cursors .................................................................................................... 5-8 Changing OracleCommand to Use a Stored Procedure; C#.............................................. 5-13 Changing OracleCommand to Use a Stored Procedure; VB ............................................. 5-13 Defining and Binding OracleParameter Objects for Stored Procedure; C#..................... 5-13 Defining and Binding OracleParameter Objects for Stored Procedure; VB .................... 5-14 Adding getDepartments() Method Code; C# ......................................................................... 6-4 Adding getDepartments() Method Code; VB ......................................................................... 6-4 How to Obtain Oracle Globalization Settings; C#.................................................................. 7-1 How to Obtain Oracle Globalization Settings; VB ................................................................. 7-1
Beta Draft
vii
Preface
Audience This document is intended as an introduction to application development with Oracle Database Express Edition in Microsoft .NET. We assume that users of this book have already read the Oracle Database Express Edition 2 Day DBA Guide and the Oracle Database Express Edition 2 Day Developer Guide, are familiar with basics of SQL and PL/SQL, and know how to use Microsost Visual Studio .NET.
Documentation Accessibility Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/ Accessibility of Code Examples in Documentation Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace. Accessibility of Links to External Web Sites in Documentation This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites. TTY Access to Oracle Support Services Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, seven days a week. For TTY support, call 800.446.2398.
Beta Draft
ix
Related Documents For more information, see the following documents in Oracle Database Express Edition documentation set: ■
Oracle Data Provider for .NET Developer's Guide
■
Oracle Database Extensions for .NET Developer's Guide
■
Oracle Database Express Edition 2 Day DBA Guide
■
Oracle Database Express Edition 2 Day Developer Guide
Conventions The following text conventions are used in this document:
x
Convention
Meaning
boldface
Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.
italic
Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.
monospace
Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.
Beta Draft
1 Introduction This chapter provides a brief description of Oracle technologies for the Microsoft .NET Framework. This chapter contains the following sections: ■
What is Microsoft .NET Framework
■
Introduction to Oracle Developer Tools for Visual Studio .NET
■
Introduction to Oracle Data Provider for .NET
■
Introduction to .NET Stored Procedures
This book is conceived as a quick start guide, to describes the key features of Oracle Data Provider for .NET and Oracle Developer Tools for Visual Studio .NET. It leads your through installation and configuration, shows how to build basic applications using Oracle Data Provider for .NET and Oracle Developer Tools for Visual Studio .NET, and how to create and use both PL/SQL and .NET stored procedures. After working through this book, you will be ready to continue with more extensive information available in the Oracle Database documentation library. See Also: ■
Dynamic help available with Oracle Developer Tools for Visual Studio .NET
■
Oracle Data Provider for .NET Developer's Guide
■
Oracle Database Extensions for .NET Developer's Guide
■
Oracle Database Express Edition 2 Day DBA Guide
■
Oracle Database Express Edition 2 Day Developer Guide
What is Microsoft .NET Framework The Microsoft .NET Framework, .NET, is a multi-language environment for building, deploying, and running XML Web services and applications. Its main components are: Common Language Runtime Common Language Runtime, or CLR, is a language-neutral development and run-time environment that provides services that help manage running applications Framework Class Libraries The Framework Class Libraries, or FCL, provide a consistent, object-oriented library of prepackaged functionality. Beta Draft
Introduction 1-1
Introduction to Oracle Data Provider for .NET
Introduction to Oracle Data Provider for .NET Oracle Data Provider for .NET (ODP.NET) provides data access from .NET client applications to Oracle databases. ODP.NET data access is fast, efficient, and includes access XML DB, .NET, security, Real Application Clusters (RAC) and other Oracle Database features. Oracle, Microsoft, and third-party vendors all offer data providers optimized for Oracle. Some third-party data providers support older versions of Oracle or do not require the installation of Oracle client software.
Introduction to Oracle Developer Tools for Visual Studio .NET Oracle Developer Tools for Visual Studio .NET is an add-in that provides graphical user interface access to Oracle functionality, and improves development productivity and ease of use. Oracle Developer Tools support the programming and implementation of .NET stored procedures using Visual Basic for .NET, C#, and other .NET languages. Oracle Developer Tools include the Oracle Explorer for browsing the Oracle schema, designers and wizards to create and alter schema objects, and the ability to drag and drop schema objects onto a .NET form to automatically generate code. Additional features include a PL/SQL editor with integrated context-sensitive online help, and an Oracle Data Window for performing routine database tasks like inserting and updating data or testing stored procedures in the Visual Studio environment. For maximum flexibility, there is also an Oracle Query Window for executing SQL statements or PL/SQL scripts.
Introduction to .NET Stored Procedures Oracle Database Extensions for .NET has the following features: Common Language Runtime Host for Oracle Database The Oracle Database on Windows hosts the Microsoft Common Language Runtime (CLR). The integration of Oracle Database with CLR enables applications to run .NET stored procedures or functions on Oracle Database, on Microsoft Windows 2003, Windows 2000, and Windows XP. Data Access for .NET Classes through Oracle Data Provider for .NET Using Microsoft Visual Studio .NET, you can build .NET procedures or functions into a .NET assembly. .NET stored procedures and functions use ODP.NET to access data. Oracle Deployment Wizard for Visual Studio .NET After building .NET procedures and functions into a .NET assembly, deploy them in Oracle Database using the Oracle Deployment Wizard for .NET, a component of the Oracle Developer Tools for Visual Studio .NET.
1-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
2 Installing Oracle Developer Tools This chapter will lead you through the installation and configuration of Oracle Developer Tools. This chapter contains the following sections: ■
What You Need
■
Installing Oracle Developer Tools
■
Testing the Installation
What You Need Sample Data The sample data used in this book is an integral part of the seed Oracle Database Express Edition, and installs out of the box. The sample data that ships with the product is the HR component of the Sample Schemas. For the HR data model and table descriptions, refer to the Sample Schema documentation at: http://st-doc.us.oracle.com/10/102/server.102/b14198/toc.htm. Oracle Database Express Edition You should install a copy of Oracle Database Express Edition on your computer. Visual Studio .NET 2003 Before proceeding with instructions in this book, you should have a complete installation of the Visual Studio .NET 2003.
Installing Oracle Developer Tools Follow these steps to install Oracle Developer Tools for Visual Studio .NET: 1.
In your internet browser, navigate to the following software download location: http://www.oracle.com/technology/software/tech/dotnet/odtxe_ index.html
Beta Draft
2.
Download the Setup file to your desktop.
3.
Double-click on the Setup icon.
Installing Oracle Developer Tools 2-1
Installing Oracle Developer Tools
The InstallShield Wizard will be launched.
4.
Once the Oracle Developer Tools for Visual Studio .NET InstallShield Wizard window appears, click Next.
2-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Installing Oracle Developer Tools
Beta Draft
5.
On the Destination Folder screen, accept the default installation location and click Next..
6.
On the Ready to Install the Program screen, click Install.
Installing Oracle Developer Tools 2-3
Testing the Installation
Testing the Installation The applications in this book connect to the Oracle Database Express Edition database as the hr user. You may need to unlock the hr account while signed in as a user with administrator privileges. For information on how to unlock the account using the Oracle XE Database interface, refer to Chapter 6, "Managing Users and Security" in the Oracle Database Express Edition 2 Day DBA Guide. The following steps show how to unlock the account and test the installation from SQL*Plus. 1.
From the Start menu on your desktop, select Run...
2.
In the Run window, type cmd and click OK.
3.
In the command window, start SLQ*Plus using the command in Example 2–1.
2-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Testing the Installation
Example 2–1 Starting SQL*Plus and Connecting as System Administrator sqlplus sys\oracle as sysdba 4.
Re-enter the password oracle at the prompt. You should be notified that you are connected to Oracle Database Express Edition.
5.
Unlock the hr user account using the command in Example 2–2.
Example 2–2 Unlocking the User Account alter user hr account unlock identified by hr;
You should be notified that the user is altered. 6.
In case the password for the hr account was changed, re-set it to the default value of hr, using the command shown in Example 2–3.
Example 2–3 Resetting the Password for the User Account alter user hr identified by hr;
You should be notified that the user is altered. 7.
Test the connection using the hr account, as shown in Example 2–3.
Example 2–4 Testing the Connection connect hr/hr
You should be notified that the connection works. 8.
Beta Draft
Your interaction screen for steps 3 through 7 should look something like this:
Installing Oracle Developer Tools 2-5
Testing the Installation
2-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
3 Using Oracle Data Provider for .NET This chapter explains how to use Oracle Data Provider for .NET. This chapter contains the following sections: ■
Starting a New Project
■
Using the Connection Object
■
Using the Command Object
■
Retrieving Data: a Simple Query
■
Error Handling
■
Retrieving Data: Bind Variables
■
Retrieving Data: Multiple Values
■
Using the DataSet Class with Oracle Data Provider for .NET
■
Inserting, Deleting and Updating Data
■
Closing the Database Connection
Starting a New Project Follow these steps to start a project in Visual Studio .NET 2003: 1.
Click the New Project button. Alternatively, from the File menu, select New, and then select Project.
Beta Draft
Using Oracle Data Provider for .NET 3-1
Starting a New Project
A New Project dialog box appears. 2.
On the left side of the New Project dialog box under Project Types, select Visual C# Projects. If you intend to develop in Visual Basic, select Visual Basic Projects instead. On the right side of the New Project dialog box under Templates, select Windows Application. For Name, enter HR_ODP.NET. For Location, enter C:\HR\Visual Studio Projects. Check the Create Directory for Solution box. For New Solution Name, enter HRApplications. A solution can contain several projects; when it contains only one project, you can use the same name for both. Click OK.
3-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Starting a New Project
If you wish to create project in Visual Basic, select the Visual Basic programming language in Project Types instead, and enter HumanResourcesVB under Name.
Adding a Reference 1.
To connect the project to an Oracle database, add a reference to the OracleData Access.dll, which contains the data provider. In the Solution Explorer, select the References node, right click and select Add Reference. Alternatively, select Add Reference from the Project menu.
Beta Draft
Using Oracle Data Provider for .NET 3-3
Starting a New Project
The Add Reference dialog box appears. 2.
Scroll down the list of references, and select Oracle.DataAccess.dll. Click the Select button.
3-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Starting a New Project
Click the OK button to make the Oracle Data Provider for .NET to your project. Note that the Solution Explorer pane now shows Oracle.DataAccess in the References folder.
Beta Draft
Using Oracle Data Provider for .NET 3-5
Starting a New Project
Initial Programmatic Statements 1.
With Form1 active, select Code from the View menu. Alternatively, use the F7 keyboard shortcut.
3-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Starting a New Project
2.
Add the following using statements to your C# project, at the top of the file: using System.Data; using Oracle.DataAccess.Client;
If you are starting a Visual Basic project, add the following Imports statements: Imports System.Data Imports Oracle.DataAccess.Client
Beta Draft
Using Oracle Data Provider for .NET 3-7
Using the Connection Object
Save the changes using either the Save icon near the top of the window, or select Save from the File menu. To change back to design view, select Designer from the View menu. Alternatively, use the Shift+F7 keyboard shortcut.
Using the Connection Object The OracleConnection object specifies the Oracle Database used by the application. 1.
The Easy Connect naming method enables ODP.NET clients to connect to a database without using any configuration files, simply by specifying the data source attribute through syntax shown in Example 3–1:
Example 3–1 Easy Connect Naming Method Syntax for Data Source user id=id;password=psswd;data source=//host:port/service_name
Where: ■ ■
■
id is the user id; we will use hr to access the HR schema. psswd is the password; in this book, we will use hr, the default password for the HR schema. host is the server machine and domain name to which the XE client will make the connection, such as hr-server in hr.us.acme.com in the following example of valid connections.
3-8 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the Connection Object
■ ■
port, if not specified, uses the default value of 1521. service_name, such as XE, is optional with the XE client. If not specified, the EZ Connect Adapter for XE client will connect to the default service on the host, pre-configured as XE in the listener.ora file on the XE server. Note that the default service is a new feature for Oracle Database Express Edition. If you used other Oracle client software, such as Instant Client for Oracle Database Enterprise Edition, you must supply the service name.
Some valid connections include: user id=hr;password=hr;data source=//hr-server:1521/XE user id=hr;password=hr;data source=//hr-server/hr.us.acme.com user id=hr;password=hr;data source=hr-server/hr.us.acme.com 2.
Example 3–2 and Example 3–3 show how to alias the database connection string. The value of the data source parameter would depend on how the user is connecting to the database server. In the following examples, substitute the name of your machine for the hr-server placeholder.
Example 3–2 Using Oracle Database Alias in Applications; C# string oradb = "userid=hr;password=hr;data source=//hr-server:1521/XE"; Example 3–3 Using Oracle Database Alias in Applications; VB Dim oradb As String = "userid=hr;password=hr;data source=//hr-server:1521/XE"
Beta Draft
Using Oracle Data Provider for .NET 3-9
Using the Connection Object
3.
To instantiate a connection object from the connection class, the connection string must be associated with the connection object. Add the code in Example 3–4 or Example 3–5 to your application, inside the method public Form(). The connection string, oradb, is associated with the connection object by being passed as a parameter in the connection object’s constructor.
Example 3–4 Creating a Connection String; C# OracleConnection conn = new OracleConnection(oradb); Example 3–5 Creating a Connection String; VB Dim conn As New OracleConnection(oradb)
Alternatively, you can create an empty connection object and assign the value of the connection string later in the program, as shown in Example 3–6 and Example 3–7. Example 3–6 Creating an Empty Connection String and then Assigning its Value; C# OracleConnection conn = new OracleConnection(); conn.ConnectionString = oradb; Example 3–7 Creating an Empty Connection String and then Assigning its Value; VB Dim conn As New OracleConnection() conn.ConnectionString = oradb
3-10 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the Command Object
4.
Use the Open() method to make the actual connection, as shown in Example 3–8 and Example 3–9.
Example 3–8 Opening a Connection with Open(); C# conn.Open(); Example 3–9 Opening a Connection with Open(); VB conn.Open()
Using the Command Object The OracleCommand object is used to specify the SQL command, a stored procedure, or a table name. It can be instantiated either from an OracleCommand class or an OracleConnection object. The OraleCommand object formulates a database request, sends the request to the database, and returns the result. 1.
Create a string that represents the SQL query, as shown in Example 3–10 and Example 3–11.
Example 3–10
Creating a SQL Statement String; C#
string sql = "select department_name from departments where department_id = 10"; Example 3–11
Creating a SQL Statement String; VB
Dim sql As String = _ "select department_name from departments where department_id = 10"
Beta Draft
Using Oracle Data Provider for .NET 3-11
Using the Command Object
2.
Use the new sql variable to create the OracleCommand object, and to set the its CommandType property.
Example 3–12
Using a Command to Query the Database; C#
OracleCommand cmd = new OracleCommand(sql, conn); cmd.CommandType = CommandType.Text; Example 3–13
Using a Command to Query the Database; VB
Dim cmd As New OracleCommand(sql, conn) cmd.CommandType = CommandType.Text
3-12 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Retrieving Data: a Simple Query
Retrieving Data: a Simple Query To retrieve data from the database, instantiate a DataCommand object and use its ExecuteReader() method to return an OracleDataReader object. 1.
Example 3–14 shows that the C# implementation uses accessor type methods for retrieving data. There are typed accessors for returning .NET native data types, and others for returning native Oracle data types. Zero-based ordinals are passed to the accessors to specify which table column should be returned.
Example 3–14
Retrieving a Value; C#
OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); // retrieve the first column in the select list label1.Text = dr.GetString(0);
Example 3–15 shows VB.NET can access returned data either through a column name, or through a zero-based column ordinal to the Item property. Another option is to use accessor type methods to return column data. Example 3–15
Retrieving a Value; VB
Dim dr As OracleDataReader = cmd.ExecuteReader() dr.Read() Label1.Text = dr.Item("department_name") ' retrieve by column name Label1.Text = dr.Item(0) ' retrieve the first column in the select list
Beta Draft
Using Oracle Data Provider for .NET 3-13
Error Handling
Label1.Text = dr.GetString(0) ' retrieve the first column in the select list
In previous examples, the returned value of department_name is a string used to set the value of the label control's text property, which is also a string. If department_id, which is not a string, had been retrieved instead, there would be a data type mismatch. The .NET runtime attempts to implicitly convert from one data type to another when the source and destination data types don't match. If the data types are incompatible, the implicit conversion fails and throws an exception.
2.
Oracle recommends that you use explicit data type conversions instead of implicit data type conversion at all times, using the OracleDataReader object to retrieve database native data types as either .NET data types or Oracle native data types. Example 3–16 and Example 3–17 show explicit data type conversion. Note that explicit conversion works on both scalar and array values. Example 3–16
Converting Data Types Explicitly; C#
string department_id = dr.GetInt16("department_id").ToString();
Example 3–17
Converting Data Types Explicitly; VB
Label1.Text = CStr(dr.Item("department_id"))
Error Handling 1.
.NET languages use the "Try-Catch-Finally" structured error handling; Example 3–18 and Example 3–19 show simple implementations of the "Try-Catch-Finally" syntax:
Example 3–18
Error Handling with "Try-Catch-Finally" Syntax; C#
OracleConnection conn = new OracleConnection(oradb); try { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "select department_name from departments where department_id = " + textBox1.Text; cmd.CommandType = CommandType.Text; if (dr.Read()) { label1.Text = dr.GetString(0); } } catch (Exception ex) // catches any error { MessageBox.Show(ex.Message.ToString()); } finally {
3-14 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Error Handling
conn.Dispose(); } Example 3–19
Error Handling with "Try-Catch-Finally" Syntax; VB
Dim conn As New OracleConnection(oradb) Try conn.Open() Dim cmd As New OracleCommand cmd.Connection = conn cmd.CommandText = _ "select department_name from departments where department_id = " + _ TextBox1.Text cmd.CommandType = CommandType.Text If dr.Read() Then Label1.Text = dr.Item("dname") ' or use dr.Item(0) End If Catch ex As Exception ' catches any error MessageBox.Show(ex.Message.ToString()) Finally conn.Dispose() End Try 2.
While code used in Example 3–18 and Example 3–18 will capture any errors encountered when attempting to retrieve data from the database, it is not very useful for the end user. As and example, the error caught when the database is unavailable will appear as "ORA-12545: Connect failed because target host or object does not exist".
3.
Oracle recommends that you add another catch statement, as shown in Example 3–20 and Example 3–21, to trap most common database errors and display them in a more user-friendly manner.
Example 3–20
Catching Common Error Messages; C#
catch (OracleException ex) // catches only Oracle errors { switch (ex.Number) { case 1: MessageBox.Show("Error attempting to insert duplicate data."); break; case 12545: MessageBox.Show("The database is unavailable."); break; default: MessageBox.Show("Database error: " + ex.Message.ToString()); break; Beta Draft
Using Oracle Data Provider for .NET 3-15
Retrieving Data: Bind Variables
} } catch (Exception ex) // catches any error { MessageBox.Show(ex.Message.ToString()); } Example 3–21
Catching Common Error Messages; VB
Catch ex As OracleException ' catches only Oracle errors Select Case ex.Number Case 1 MessageBox.Show("Error attempting to insert duplicate data.") Case 12545 MessageBox.Show("The database is unavailable.") Case Else MessageBox.Show("Database error: " + ex.Message.ToString()) End Select Catch ex As Exception ' catches any error MessageBox.Show(ex.Message.ToString())
The Catch statements should be ordered from most specific to most general. If there are no Oracle errors, the first Catch statement branch is skipped. The second Catch statement catches all other errors. After implementing Example 3–20 or Example 3–21, the ORA-12545 error appears as "The database is unavailable.":
4.
The Finally code block is always executed. If the connection object's Close() or Dispose() method calls are in the Finally code block, the database connection will always be closed when after the Try-Catch-Finally is complete. Attempting to close a closed database connection does not cause an error. If the database is unavailable, the database connection is not opened, so the Finally code block attempts to close a connection that does not exist, making these Close() and Dispose() calls irrelevant. However, placing Close() or Dispose() in the Finally code block guarantees that the connection is closed.
Retrieving Data: Bind Variables Bind variables are essentially placeholders in a SQL statement. When a database receives a SQL statement, it checks the shared memory pool to see if the statement already exists and is stored in memory. If the statement exists in memory, Oracle Database can reuse it and skip the task of parsing and optimizing the statement. In using bind variables, you greatly increase the likelihood that SQL statements will be stored in memory, making them easily and quickly available to the next operation that needs them.
3-16 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Retrieving Data: Bind Variables
1.
Example 3–22 shows a typical SELECT statement that does not use bind variables, with the value 10 specified in the WHERE clause of the statement.
Example 3–22
SELECT Statement without Bind Variables
SELECT department_name FROM departments WHERE department_id = 10 2.
Example 3–23 replaces the numerical value with a bind variable :department_ id. The bind variable identifier always begins with a single colon, :, in SQL statements.
Example 3–23
SELECT Statement with Bind Variables
select department_name from departments where department_id = :department_id 3.
Bind variables can also be used with UPDATE, INSERT, and DELETE statements. Example 3–23 shows how to use bind variables in an UPDATE statement; "Inserting, Deleting and Updating Data" on page 3-28 provides more details.
Example 3–24
UPDATE Statement with Bind Variables
UPDATE departments SET department_name WHERE department_id 4.
= :department_name = :department_id
Use the OracleParameter class to represent each bind variable in your .NET code. The OracleParameterCollection class contains the OracleParameter objects associated with the OracleCommand object for each statement. The OracleCommand class passes your SQL statement to the database and returns the results to your application. You can bind variables by position or by name. The OracleCommand property BindByName (which defaults to false) sets the mode. When binding by position mode, you must use the Add() method to add the parameters to the OracleParameterCollection in the same order as they appear in the SQL statement or stored procedure. If you want to use bind by name mode, you may add the parameters to the collection in any order; however, you must set the ParameterName property for the parameter object to the same value as the bind variable identifier in the stored procedure declaration.
5.
In addition to the binding mode (by position or by name), the following properties are typically set for each parameter object: Direction, OracleDbType, Size, and Value. ■
■
■
Direction Bind variables may be used as output, input, or input/output parameters. The Direction property indicates the direction of each parameter. The default value of the Direction property is Input. OracleDbType property indicates whether the parameter is a number, a date, a VARCHAR2, and so on. Size indicates the maximum size of the data that the parameter will hold, for parameters with a variable length data type, like VARCHAR2.
Beta Draft
Using Oracle Data Provider for .NET 3-17
Retrieving Data: Bind Variables
■
6.
Value contains the parameter value either before statement execution (for input parameters), after execution (for output parameters), or both before and after (for input/output parameter).
Example 3–25 and Example 3–26 tie together these concepts and use a bind variable in a SELECT statement. Note that Direction property uses the default value Input, and the Size property is not set. Since the object is an input parameter, you don't need to set the Size property because the data provider can determine the size from the value.
Example 3–25
Using a Bind Variable; #C
static void Main(string[] args) { string oradb = "userid=hr;password=hr;data source=//hr-server:1521/XE"; OracleConnection con = new OracleConnection(oradeb); con.Open(); StringBuilder sbSQL = new StringBuilder(); sbSQL.Append("select department_name "); sbSQL.Append("from departments "); sbSQL.Append("where department_id = :department_id"); OracleCommand cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = sbSQL.ToString();
// creates command
OracleParameter p_department_id = new OracleParameter(); // creates parameter p_department_id.OracleDbType = OracleDbType.Decimal; // sets data type p_department_id.Value = 10; // sets value cmd.Parameters.Add(p_department_id);
// adds to collection
OracleDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { Console.WriteLine("Department Name: {0}", dr.GetOracleString(0)); } dr.Dispose(); p_department_id.Dispose(); cmd.Dispose(); con.Dispose(); } Example 3–26
Using a Bind Variable; VB
Sub Main() Dim constr As String = "userid=hr;password=hr;data source=//hr-server:1521/XE" Dim con As OracleConnection = New OracleConnection(constr) con.Open() Dim sbSQL As StringBuilder = New StringBuilder sbSQL.Append("select department_name ") sbSQL.Append("from departments ") sbSQL.Append("where department_id = :department_id")
3-18 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Retrieving Data: Multiple Values
Dim cmd As OracleCommand = New OracleCommand ' creates command cmd.Connection = con cmd.CommandText = sbSQL.ToString() Dim p_department_id As OracleParameter = New OracleParameter ' creates parameter p_department_id.OracleDbType = OracleDbType.Decimal ' sets data type p_department_id.Value = 10 'sets value cmd.Parameters.Add(p_department_id) ' adds to collection Dim dr As OracleDataReader = cmd.ExecuteReader() If (dr.Read()) Then Console.WriteLine("Country Name: {0}", dr.GetOracleString(0)) End If dr.Dispose() p_department_id.Dispose() cmd.Dispose() con.Dispose() End Sub
Your output should look like this: Department Name: Administration
Retrieving Data: Multiple Values 1.
A DataReader object can retrieve values for multiple columns and multiple rows. Consider a multiple column, single row query in Example 3–27:
Example 3–27
Querying for a Multiple Column Single Row Result
SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id = 10 2.
Use either zero-based ordinals or column names to obtain the values of the columns, as shown in Example 3–28 and Example 3–29.
Example 3–28
Using Column Ordinals and Names from a Query Result; C#
Label1.Text = "The " + dr.GetString(1) + " department is at location # " + dr.GetString(3); Example 3–29
Using Column Ordinals and Names from a Query Result; VB
Label1.Text = "The " + dr.Item(1) + " department is at location # " + _ dr.Item(3); 3.
A query can also return multiple rows, as in:
Example 3–30
Querying for a Multiple Column Multiple Row Result
SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id > 100 4.
A looping construct is needed to process multiple rows from the DataReader object. Also, a control that can display multiple rows is very useful. Because a
Beta Draft
Using Oracle Data Provider for .NET 3-19
Using the DataSet Class with Oracle Data Provider for .NET
OracleDataReader is a forward-only, read-only cursor, it cannot be bound to an updatable or fully scrollable control such as Windows Forms DataGrid control. A DataReader is, however, compatible with a ListBox control, as shown in Example 3–31 and Example 3–32. Example 3–31
Looping Through a Multi-Row Query Result; C#
while (dr.Read()) { listBox1.Items.Add("The " + dr.GetString(1) + " department is at location # " + dr.GetString(3); } Example 3–32
Looping Through a Multi-Row Query Result; VB
While (dr.Read()) ListBox1.Items.Add("The " + dr.Item(1) + " department is at location # " + _ dr.Item(3) End While
Using the DataSet Class with Oracle Data Provider for .NET The DataSet class encapsulates a memory-resident representation of data that provides a consistent relational programming model for multiple data sources. It consists of one or more tables that store relational or XML data. Unlike OracleDataReader, a DataSet is updateable and backwards scrollable. Let us return to the project started in "Starting a New Project" on page 3-1. 1.
If you are not in code view for Form1, use the F7 keyboard shortcut. In the code view, insert the following instantiations within the Form1 class declaration, as shown in Example 3–33 and Example 3–34:
Example 3–33 private private private private private
OracleConnection conn; OracleCommand cmd; OracleDataAdapter da; OracleCommandBuilder cb; DataSet ds;
Example 3–34 Private Private Private Private Private 2.
Using DataSet Class: Declaring Variables; C#
Using DataSet Class: Declaring Variables; VB
conn As OracleConnection cmd As OracleCommand da As OracleDataAdapter cb As OracleCommandBuilder ds As DataSet
Within method Form1(), assign values to these variables, as shown in Example 3–35 and Example 3–36.
Example 3–35
Using DataSet Class: Assigning Values; C#
conn = new OracleConnection(oradb); cmd = new OracleCommand("SELECT department_id, department_name, manager_id," + " location_id FROM departments WHERE department_id > 100", conn); da = new OracleDataAdapter(cmd); cb = new OracleCommandBuilder(da); ds = new DataSet(); 3-20 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the DataSet Class with Oracle Data Provider for .NET
Example 3–36
Using DataSet Class: Assigning Values; VB
Me.conn = New OracleConnection(oradb) Me.cmd = New OracleCommand("SELECT department_id, department_name," & _ " manager_id, location_id FROM departments WHERE department_id > 100", conn) Me.da = New OracleDataAdapter(cmd) Me.cb = New OracleCommandBuilder(da) Me.ds = New DataSet 3.
You must now open the connection, and fill the DataSet object with the result of the query in the OracleCommand, as shown in Example 3–37 and Example 3–38.
Example 3–37
Opening a Connection and Filling the DataSet; C#
conn.Open(); da.Fill(ds); Example 3–38
Opening a Connection and Filling the DataSet; VB
Me.conn.Open() Me.da.Fill(ds) 4.
Switch to design view (Shift+F7 keyboard shortcut), and from the View menu, select Toolbox.
A Toolbox window will appear. 5.
From the Toolbox, under Windows Forms, select a Data Grid and drag it onto Form1.
Beta Draft
Using Oracle Data Provider for .NET 3-21
Using the DataSet Class with Oracle Data Provider for .NET
A data grid will appear on top of the form. Expand the data grid to fill most of our form. 6.
From the Toolbox, under Windows Forms, drag and drop a Button onto Form1, below the data grid.
7.
Right-click on the new button, and select Properties.
3-22 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the DataSet Class with Oracle Data Provider for .NET
A Properties window will appear. 8.
In the Properties window, under Appearance, Text, enter Save. Under Design, (Name), enter Save. Click on the lightning icon (events) at the top of the Properties window.
Beta Draft
Using Oracle Data Provider for .NET 3-23
Using the DataSet Class with Oracle Data Provider for .NET
9.
Click the mouse on the highlighted Click event.
3-24 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the DataSet Class with Oracle Data Provider for .NET
10. Close the Properties window. Your form should now look something like the
following image.
Beta Draft
Using Oracle Data Provider for .NET 3-25
Using the DataSet Class with Oracle Data Provider for .NET
11. Switch to code view (F7 keyboard shortcut).
You will notice that there are two more object declarations in you Form1 class: a DataGrid and a Button, as shown in Example 3–39 and Example 3–39. Example 3–39
Declaring Windows.Forms Objects Automatically; C#
private System.Windows.Forms.DataGrid dataGrid1; private System.Windows.Forms.Button Save; Example 3–40
Declaring Windows.Forms Objects Automatically; VB
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid Friend WithEvents Save As System.Windows.Forms.Button
There is also automatically generated code that defines them. Do not alter it. 12. You must now make a bind between the new DataGrid and the DataSet.
At the end of your Form1() method declaration, add the binding as shown in Example 3–41 and Example 3–42. Example 3–41
Binding the DataSet to the DataGrid; C#
dataGrid1.DataSource = ds.Tables[0]; Example 3–42
Binding the DataSet to the DataGrid; VB
Me.dataGrid1.DataSource = ds.Tables[0] 13. Your code also contains a new method that has been automatically generated for
you. Insert a new line into the body of the Save_Click() method, as shown in Example 3–43 and Example 3–44. Example 3–43
The Save_Click() Method; C#
private void Save_Click(object sender, System.EventArgs e) { da.Update(ds.Tables[0]); } Example 3–44
The Save_Click() Method; VB
Private Sub Save_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Save.Click da.Update(ds.Tables(0)) End Sub 14. Save Form1 using Ctr+S keyboard shortcut. 15. From the Build menu, select Build Solution, or use the Ctr+Shift+B keyboard
shortcut.
3-26 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Using the DataSet Class with Oracle Data Provider for .NET
You should see an Output window.
16. From the Debug menu, select Start to run your application. Alternatively, use the
F5 keyboard shortcut.
Your application run-time window, titled Form1, will appear.
Beta Draft
Using Oracle Data Provider for .NET 3-27
Inserting, Deleting and Updating Data
Inserting, Deleting and Updating Data 1.
Scroll down to the bottom of the data grid, and enter a new record at the * prompt as follows: ■
For DEPARTMENT_ID, enter 280
■
For DEPARTMENT_NAME, enter Community Outreach
■
Leave MANAGER_ID as null
■
For LOCATION_ID, enter 1700
Click the Save button.
2.
Close the application, and start it again using the F5 keyboard shortcut.
3.
Scroll down to the bottom of the list, and note that the new record is now part of the DEPARTMENTS table. Change the name of the department to Community Volunteers, and click the Save button.
3-28 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Inserting, Deleting and Updating Data
4.
Close the application, and start it again using the F5 keyboard shortcut.
5.
Scroll down to the bottom of the list, and note that the DEPARTMENT_NAME has changed. Select the entire record you just changed (click the cursor icon before it), and delete it using the Delete key. Click the Save button.
6.
Close the application, and start it again using the F5 keyboard shortcut.
7.
Scroll down to the bottom of the list, and note that the new record is no longer part of the DEPARTMENTS table.
8.
Close the Form1 application.
Beta Draft
Using Oracle Data Provider for .NET 3-29
Closing the Database Connection
Closing the Database Connection 1.
A connection object's Close() and Dispose() methods close the connection to the database. If a connection is not already closed, the Dispose() method will close it. Example 3–45 and Example 3–46 show the use of these methods.
Example 3–45
Closing and Disposing a Connection; C#
conn.Close(); conn.Dispose();
Example 3–46
Closing and Disposing a Connection; VB
conn.Close() conn.Dispose() 2.
C# has an alternative syntax that disposes of a connection when it goes out of scope, through the using keyword, as shown in Example 3–47.
Example 3–47
Closing and Disposing a Connection when Out of Scope; C#
using (OracleConnection conn = new OracleConnection(oradb)) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "select department_name from departments where department_id = 10"; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); label1.Text = dr.GetString(0); }
3-30 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
4 Using Oracle Developer Tools for Visual Studio .NET This chapter explains how to use Oracle Developer Tools. This chapter contains the following sections: ■
Connecting to the Oracle Database
■
Building a New Table
■
Generating Code Automatically
■
Enabling Database Updates
■
Disconnecting from the Oracle Database
■
Reconnecting to the Oracle Database
Begin by starting a new project, as described in Chapter 3, "Using Oracle Data Provider for .NET", section "Starting a New Project" on page 3-1.
Connecting to the Oracle Database To connect to an Oracle Database from Visual Studio .NET, follow these steps: 1.
From the View menu, select Oracle Explorer.
2.
In Oracle Explorer, right-click the Data Connections node and from the menu, select Add Connection. Alternatively, click the + icon (Add Connection) at the top of the Oracle Explorer pane, or select Connect to Oracle Database from the Tools menu.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-1
Connecting to the Oracle Database
The application opens an Add Connection window. 3.
In the Add Connection window, enter the following information: Data source name: Select the Oracle database to use as the data source; here, we are using //hr-server:1521/XE. If the database is remote, use the EZConnect naming string to connect to the database. Select the Use a specific user name and password option if you want to connect to the Oracle Database as the currently logged on Windows user. For User name, enter hr. For Password, enter the password that was set for the hr user during the configuration phase of the Oracle Database installation. If you wish to save the password for future sessions after you exit the current session, check the Save password box. Ensure that the Role is set to Default. This refers to the default roles that have been granted to the user hr. The Connection name should be generated automatically from the Data source name and the User name values.
4-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Connecting to the Oracle Database
Under the Apply Filters tab, check that the HR schema is in the Displayed schemas column. Only the schema objects (tables, views, and so on) from the schemas selected in the Apply Filters tab are displayed when you expand the schema category nodes in the data connection.
Return to the Connection Details tab, and click Test connection. The test should succeed. Click OK.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-3
Building a New Table
If the test fails, it may be due to one or more of the following issues that you must address before proceeding with further steps: ■
The database is not started.
■
The database connectivity is not properly configured. Check that:
■
4.
–
The tsname.ora file exists and contains the correct settings.
–
The TNS_ADMIN environment variable is properly set.
–
NAMES.DIRECTORY_PATH is properly set in the sqlnet.ora file.
You do not have the correct user name, password, and role.
The Oracle Explorer pane should now contain the hr.(Local Database) connection. Expand the connection to show the contents of the hr schema. You should see Tables, Views, Procedures, Functions, and so on.
Building a New Table This section will describe how to create a new table, its columns, indexes, and constraints.
Creating a Table and Its Columns 1.
In Oracle Explorer, right-click on Tables and select New Relational Table...
4-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
A table design window appears. 2.
In design view, enter DEPENDENTS for Table name, and click Add in the Columns tab.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-5
Building a New Table
Note that a default column description appears in the Columns tab. 3.
Under Column Properties, enter Name LASTNAME, Data Type VARCHAR2, and Size 30. Leave all other properties at their default values. Click Add.
Note that column LASTNAME is added to the table. 4.
Under Column Properties, enter Name FIRSTNAME, Data Type VARCHAR2, and Size 30. Leave all other properties at their default values. Click Add.
4-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
Note that column FIRSTNAME is added to the table. 5.
Under Column Properties, enter Name BIRTHDATE, Data Type DATE, and leave all other properties at their default values. Click Add.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-7
Building a New Table
Note that column BIRTHDATE is added to the table. 6.
Under Column Properties, enter Name RELATIONSHIP, Data Type VARCHAR2, and Size 20. Leave all other properties at their default values. Click Add.
4-8 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
Note that column RELATIONSHIP is added to the table. 7.
Under Column Properties, enter Name EMPLOYEEID, Data Type NUMBER, deselect the Allow null check box, enter Precision 6 and Scale 0. Click Add.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET
4-9
Building a New Table
Note that column EMPLOYEEID is added to the table. 8.
Under Column Properties, enter Name DEPENDENTID, Data Type NUMBER, deselect the Allow null check box, enter Precision 4 and Scale 0. Click Preview SQL.
4-10 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
A Preview SQL window appears. 9.
The content of the Preview SQL window is show in Example 4–1:
Example 4–1 Generated SQL Form of the New Table CREATE TABLE "HR"."DEPENDENTS" ("LASTNAME" VARCHAR2(30) NULL,"FIRSTNAME" VARCHAR2(30) NULL,"BIRTHDATE" DATE NULL,"RELATIONSHIP" VARCHAR2(20) NULL,"EMPLOYEEID" NUMBER(6,0) NOT NULL,"DEPENDENTID" NUMBER(4,0) NOT NULL);
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-11
Building a New Table
Click OK to close the Preview SQL window. The design view appears. 10. In the design view, click Save.
Creating an Index 1.
Now you must create an index for the DEPENDENTS table. In the design view, click the Indexes tab.
2.
Click Add.
4-12 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
Note that the Index Property area is activated. 3.
Under Index properties, enter Name DEPENDENTS_INDEX, and leave all other properties in their default state.
4.
In the Index properties area, click Add.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-13
Building a New Table
5.
Select DEPENDENTID from the drop-down list under Key.
6.
Click Preview SQL.
4-14 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
A Preview SQL window appears. 7.
The code displayed in the Preview SQL window is shown in Example 4–2.
Example 4–2 Creating a Table Index in SQL CREATE
INDEX "HR"."DEPENDENTS_INDEX" ON "HR"."DEPENDENTS" ("DEPENDENTID"
);
Click OK to close the Preview SQL window.
The design view appears. Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-15
Building a New Table
8.
In the design view, click Save.
Adding Constraints 1.
Now you must create a foreign key to the EMPLOYEES table. Click the Constraints tab. Note that depending on your configuration, there are may already be default constraints in the list.
2.
Click Add.
4-16 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
Note that the Constraint Properties area is now activated. 3.
Under Constraint Properties, enter Name EMPLOYEES_FK, select Type Foreign Key from the drop-down list, select Table EMPLOYEES and Constraint EMP_EMP_ ID_PK. Under Associations, select EMPLOYEE_ID as Referenced Column and EMPLOYEEID as Local Column. Leave all other properties at their default values.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-17
Building a New Table
Note that EMPLOYEES_FK is now in list of constraints. 4.
Now you must add the primary key for the new table, DEPENDENTS. Click Add. Under Constraint Properties, enter Name DEPENDENTS_PK, select Type Primary Key from the drop-down list. Under Primary Key Columns, select DEPENDENTID. Leave all other properties at their default values.
4-18 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Building a New Table
5.
Click Preview SQL. A Preview SQL window appears.
6.
The SQL code generated for constraints on table DEPENDENTS is shown in Example 4–3. Note that addition of both constraints is an ALTER TABLE command; this is because the constraints change alter the definitions of columns DEPENDENTID and EMPLOYEEID.
Example 4–3 Adding Foreign Key and Primary Key Constraints to a Table ALTER TABLE "HR"."DEPENDENTS" ADD ( CONSTRAINT "EMPLOYEES_FK" FOREIGN KEY ("EMPLOYEEID") REFERENCES "HR"."EMPLOYEES" (EMPLOYEE_ID) ENABLE VALIDATE ); ALTER TABLE "HR"."DEPENDENTS" ADD ( CONSTRAINT "DEPENDENTS_PK" PRIMARY KEY ("DEPENDENTID") ENABLE VALIDATE );
Click OK to close the Preview SQL window. 7.
In design view, click Save. Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-19
Building a New Table
Notice that if you expand the DEPENDENTS table in the Oracle Explorer, all the columns, constraints and indexes of the table are visible.
Adding Data to a Table 1.
You must now add data to the new, but empty, DEPENDENTS table. In Oracle Explorer, right-click the DEPENDENTS table and select Retrieve Data.
A table grid for DEPENDENTS appears in design view.
4-20 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
2.
Enter the following 4 records in the table. Click Save.
Generating Code Automatically To explore the content of table DEPARTMENTS, we will build a form that uses a simple table query. 1.
Start a new project, as outlined in Chapter 3, "Using Oracle Data Provider for .NET", section "Starting a New Project" on page 3-1.
2.
From the View menu, select Oracle Explorer.
3.
In Oracle Explorer, expand Data Connections, expand hr. (Local Database), and finally expand the Tables component. Using your mouse, select the DEPARTMENTS table. Drag and drop the table onto Form1 in the Designer pane.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-21
Generating Code Automatically
A Microsoft Development Environment pop-up window will ask if you wish to save the connection password in the generated code. Click Yes.
You will notice that this action creates an instance of an OracleConnection class, departmentsOracleConnection1, visible below the Designer pane. An instances of the OracleDataAdapter class is also created, departmentsOracleDataAdapter1.
4-22 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
Both these objects represent automatically generated code in the code that is behind Form1. 4.
Right-click on the departmentsOracleDataAdapter1, and select Generate DataSet...
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-23
Generating Code Automatically
departments11 is now added to your Designer pane.
5.
Add a DataGrid object to the form. Select Toolbox from the View menu.
4-24 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
A Toolbox pane will appear. 6.
From the Toolbox, under Window Forms, select DataGrid and drag and drop in onto Form1.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-25
Generating Code Automatically
At this point, you should enlarge both Form1 and the DataGrid.
4-26 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
7.
Set the Data Source of the DataGrid. Select Properties Window from the View menu.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-27
Generating Code Automatically
The Properties pane appears. 8.
In the Properties pane, under Data, select departments11.Departments from the drop-down list that represents DataSource options.
4-28 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
Close the Properties pane. 9.
The DataGrid now contains the column headings from the table DEPARTMENTS.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-29
Generating Code Automatically
10. Switch to code view by double-clicking on the newly created objects, by selecting
Code from the View menu, or by using the F7 keyboard shortcut. 11. Immediately after the InitializeComponent(); command, type the code in
Example 4–4 or Example 4–5. Example 4–4 Filling Data into the Form; C# this.departmentsOracleDataAdapter1.Fill(this.departments11.Departments); Example 4–5 Filling Data into the Form; VB Me.departmentsOracleDataAdapter1.Fill(Me.departments11.Departments)
4-30 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Generating Code Automatically
12. From the Build menu, select Build Solution. Alternatively, you can use the
Ctrl+Shift+B keyboard shortcut.
Check the output to ensure that there were no problems with the build. Double-click the Output tab in the lower left corner of the window.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-31
Generating Code Automatically
Close the Output window. 13. Select Start from the Debug menu, or simply use the F5 keyboard shortcut. You
application will run, and a Form1 with data appears.
You may need to increase the width of the columns to see all the data. 14. You can navigate across Form1 by clicking the mouse down the data set; the
current record will be marked by the cursor. You can also sort the records, either in ascending or descending order, on any of the columns, by clicking on the column heading (notice the direction indicator in the DEPARTMENT_NAME column).
4-32 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Enabling Database Updates
15. Close Form1.
Enabling Database Updates 1.
Select Toolbox from the View menu. Under Window Forms, select Button and drag and drop it onto Form1, below the DataGrid.
A button called button1 will appear on the form.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-33
Enabling Database Updates
2.
Right-click on the button, and select Properties.
The Properties pane will appear. 3.
In the Properties pane, under Appearance, change the value of the text property to Save. While not strictly necessary, you should also change the name under Design to Save. This will make it easier to understand and maintain the code of the application. Click on the lightning icon (events) at the top of the Properties window.
4-34 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Enabling Database Updates
4.
Click the mouse on the highlighted Click event.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-35
Enabling Database Updates
5.
Close the Properties window. Your form should now have a Save button below the data grid.
6.
Double-click on the Save button.
4-36 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Disconnecting from the Oracle Database
The code view appears, with focus on the new and empty Save_Click() method. 7.
Change the code of the Save_Click() method to bind the table update event to the button, as shown in Example 4–6.
Example 4–6 The Save_Click() Method; #C private void Save_Click(object sender, System.EventArgs e) { departmentsOracleDataAdapter1.Update(departments11); } Example 4–7 The Save_Click() Method; VB Private Sub Save_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Save.Click departmentsOracleDataAdapter1.Update(departments11) End Sub 8.
At this point, you can re-build and run your Form1 application. Use the Ctrl+Shift+B keyboard shortcut to build, and F5 keyboard shortcut to start. Your Form1 application window should appear.
9.
To use this form, follow the instructions in Chapter 3, section "Inserting, Deleting and Updating Data" on page 3-28.
Disconnecting from the Oracle Database 1.
In Oracle Explorer, expand the Data Connections node.
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-37
Reconnecting to the Oracle Database
Right-click the Data Connection that represents the hr database connection. Select Close from the drop-down menu. The data connection will now close, as indicated by the red x.
Reconnecting to the Oracle Database 1.
In Oracle Explorer, expand Data Connections, and right-click on the closed connection, hr. Select Open from the drop-down menu. The hr connection will be re-opened, and the node will expand to show the components of the HR schema.
4-38 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Reconnecting to the Oracle Database
Beta Draft
Using Oracle Developer Tools for Visual Studio .NET 4-39
Reconnecting to the Oracle Database
4-40 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
5 Using PL/SQL Stored Procedures and REF Cursors This chapter contains the following sections: ■
Introduction to Stored Procedures
■
Introduction to Ref Cursors
■
Introduction to PL/SQL Packages and Package Bodies
■
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
Introduction to Stored Procedures A stored procedure is a named set of PL/SQL statements designed to perform an action. Unlike functions, stored procedures do not return values, but they can accept values as input.
Introduction to Ref Cursors Ref cursors are one of the most powerful, flexible, and scalable methods for returning query results from an Oracle Database to a client application. A ref cursor is a PL/SQL datatype whose value is an address that represents the memory location of a query work area on the database server. A query work area can be thought of as the result set, or a row set, on the server; it is the location where the results of a query are stored in server memory. In essence, a ref cursor is a handle to a result set on the server. A ref cursor is represented through the OracleRefCursor class. Ref cursors have the following characteristics: 1.
A ref cursor refers to server memory. The memory address represented by a ref cursor "lives" on the database server, not on the client machine. Therefore, the client’s connection to the database must be in place during the lifetime of the ref cursor. If the underlying connection to the database is closed, the ref cursor will become inaccessible to the client.
2.
A ref cursor involves an additional database round trip. Because a ref cursor is a pointer to memory on the server that is returned to the client, the actual data contained in the ref cursor is not initially returned to the client. The client must request the data contained in the ref cursor after it has opened the ref cursor. Note that data will not be retrieved until the user attempts to read it.
3.
A ref cursor is not updatable. The result set represented by the ref cursor is read-only. You cannot update the database by using a ref cursor.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-1
Introduction to PL/SQL Packages and Package Bodies
4.
A ref cursor is not backward-scrollable. The data represented by the ref cursor is accessed in a forward-only, serial manner. You cannot position a record pointer inside the ref cursor to point to random records in the result set.
5.
A ref cursor is a PL/SQL datatype. You create and return a ref cursor inside a PL/SQL code block.
6.
A ref cursor can be either weak- or strong-typed. A strong-typed ref cursor has a return type defined when the ref cursor itself is declared in PL/SQL, and can only refer to the same type or structure with which it is initially declared. A weak-typed ref cursor has no return type defined, and can refer to a query work area of any type. A strong-typed ref cursor is specific, while a weak-typed ref cursor is generic. This section will discuss the use of weak-typed ref cursors.
Introduction to PL/SQL Packages and Package Bodies A PL/SQL package stores related items as a single logical entity. A package is composed of two distinct pieces: ■
■
The package specification defines what is contained in the package; it is analogous to a header file in a language such as C++. The specification defines all public items. The specification is the published interface to a package. The package body contains the code for the procedures and functions defined in the specification, and the code for private procedures and functions that are not declared in the specification. This private code is only "visible" within the package body.
The package specification and body are stored as separate objects in the data dictionary and can be seen in the user_source view. The specification is stored as the PACKAGE type, and the body is stored as the PACKAGE BODY type. While it is possible to have a specification without a body, as when declaring a set of public constants, it is not possible to have a body with no specification.
Creating a PL/SQL Stored Procedure that Uses Ref Cursors 1.
From the Oracle Explorer window, right-click on Packages and select New Package.
The New Package window appears.
5-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
2.
In the New Package window, change the Package Name to STORED.
3.
Click Add.
The Add Method window appears. 4.
In the Add Method window, enter Method Name GETCURSORS, and change Method Type to Procedure. Click Add.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-3
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
5.
In the Add Method window, under Parameter Details, enter DEPARTMENTID for Name, IN for Direction, and NUMBER for Data Type. Click Add.
6.
Enter a second parameter under Parameter Details, with EMPLOYEESCUR for Name, OUT for Direction, and SYS_REFCURSOR for Data Type. Click Add.
7.
Enter a third parameter under Parameter Details, with DEPENDENTSCUR for Name, OUT for Direction, and SYS_REFCURSOR for Data Type. Click OK. The New Package window will appear.
8.
Click Preview SQL >> to see the SQL code created.
5-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
A Preview SQL window will appear. 9.
The Preview SQL window will contain the following code, as show in Example 5–1:
Example 5–1 PL/SQL Code for Package STORED CREATE PACKAGE "HR"."STORED" IS -- Declare types, variables, constants, exceptions, cursors, -- and subprograms that can be referenced from outside the package. -- Example -- TYPE tableType IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER; -- PROCEDURE CalculateSalary(rate IN NUMBER, hoursWorked IN NUMBER, salary OUT NUMBER); PROCEDURE "GETCURSORS" ( "DEPARTMENTID" IN NUMBER, "EMPLOYEESCUR" OUT SYS_REFCURSOR, "DEPENDENTSCUR" OUT SYS_REFCURSOR); END "STORED"; CREATE PACKAGE BODY "HR"."STORED" IS -- Implement subprograms, initialize variables declared in package specification. -- Example -- PROCEDURE CalculateSalary(rate IN NUMBER, hoursWorked IN NUMBER, salary OUT NUMBER) IS -- BEGIN
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-5
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
-salary := rate*hoursWorked; -- END; -- Make private declarations of types and items, that are not accessible outside the package PROCEDURE "GETCURSORS" ( "DEPARTMENTID" IN NUMBER, "EMPLOYEESCUR" OUT SYS_REFCURSOR, "DEPENDENTSCUR" OUT SYS_REFCURSOR) IS -- Declare constants and variables in this section.
Click OK to close the window. The New Package window appears. 10. In the New Package window, click OK.
Note that a new package, STORED, now appears in Oracle Explorer. 11. In Oracle Explorer, right-click on package STORED, and select Edit Package
Body...
5-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
The code for procedure STORED is displayed. 12. Scroll down to find the following line of code: NULL;
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-7
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
13. Replace NULL; with code in Example 5–2. Example 5–2 Assigning Reference Cursors OPEN EMPLOYEESCUR FOR SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=DEPARTMENTID; OPEN DEPENDENTSCUR FOR SELECT * FROM DEPENDENTS;
5-8 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
14. Right-click on the window tab and select Save. Alternatively, use the Ctrl+S
keyboard shortcut.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors
5-9
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
15. To run the stored procedure, in Oracle Explorer, expand package STORED.
Right-click on GETCURSORS method, and select Run.
5-10 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
A Run Procedure window appears. 16. In the Run Procedure window, enter a Value of 60 for DEPARTMENTID. Click OK.
In the design view, the following message appears: Procedure
The Output window should also appear:
In the design view, the two new parameters appear: EMPLOYEESCUR and DEPENDENTSCUR. 17. In the design view, select the value for EMPLOYEESCUR.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors 5-11
Creating a PL/SQL Stored Procedure that Uses Ref Cursors
The a Procedure Details area appears, showing the result of the EMPLOYEESCUR. 18. In the design view, select the value for DEPENDENTSCUR.
5-12 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
The a Procedure Details area appears, showing the result of the DEPENDENTSCUR.
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET 1.
Open the form you previously created in Chapter 3, "Using Oracle Data Provider for .NET". Switch to code view using the F7 keyboard shortcut.
2.
Replace the command assignment line, starting with cmd = New oracleCommand... with code in Example 5–3 or Example 5–3.
Example 5–3 Changing OracleCommand to Use a Stored Procedure; C# cmd = new OracleCommand("STORED.GETCURSORS", conn); cmd.CommandType = CommandType.StoredProcedure; Example 5–4 Changing OracleCommand to Use a Stored Procedure; VB cmd = New OracleCommand("STORED.GETCURSORS", conn) cmd.CommandType = CommandType.StoredProcedure 3.
Add definitions and bindings for the three parameters of the GETCURSORS stored procedure as OracleParameter objects, calling them dept_id, emp_cur and dnt_cur.
Example 5–5 Defining and Binding OracleParameter Objects for Stored Procedure; C# OracleParameter dept_id = new OracleParameter(); dept_id.OracleDbType = OracleDbType.Decimal; dept_id.Direction = ParameterDirection.Input; dept_id.Value = 60;
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors 5-13
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
cmd.Parameters.Add(dept_id); OracleParameter emp_cur = new OracleParameter(); emp_cur.OracleDbType = OracleDbType.RefCursor; emp_cur.Direction = ParameterDirection.Output; cmd.Parameters.Add(emp_cur); OracleParameter dnt_cur = new OracleParameter(); dnt_cur.OracleDbType = OracleDbType.RefCursor; dnt_cur.Direction = ParameterDirection.Output; cmd.Parameters.Add(dnt_cur); Example 5–6 Defining and Binding OracleParameter Objects for Stored Procedure; VB Dim dept_id As OracleParameter = New OracleParameter dept_id.OracleDbType = OracleDbType.Decimal dept_id.Direction = ParameterDirection.Input dept_id.Value = 60 cmd.Parameters.Add(dept_id) Dim emp_cur As OracleParameter = New OracleParameter emp_cur.OracleDbType = OracleDbType.RefCursor emp_cur.Direction = ParameterDirection.Output cmd.Parameters.Add(emp_cur) Dim dnt_cur As OracleParameter = New OracleParameter dnt_cur.OracleDbType = OracleDbType.RefCursor dnt_cur.Direction = ParameterDirection.Output cmd.Parameters.Add(dnt_cur) 4.
Re-compile Form1 using the Ctrl+Shift+B keyboard shortcut, and start the application using the F7 keyboard shortcut. A Form1 window will appear.
5.
Scroll horizontally to see that the last column, DEPARTMENT_ID, is equal to 60. Note that the DataGrid object contains the first result set from the stored procedure, which matches the query of the EMPLOYEES table. To view the results of the DEPENDENTS table, fill the DataGrid with results from dnt_cur.
5-14 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
You may remember that 60 is the value of the DEPARTMENTID parameter. 6.
Close the Form1 window.
Beta Draft
Using PL/SQL Stored Procedures and REF Cursors 5-15
Running a PL/SQL Stored Procedure Using Oracle Data Provider for .NET
5-16 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
6 Using .NET Stored Procedures This chapter discusses how to use and deploy .NET stored procedures in your application. This chapter contains the following sections: ■
Creating a New Connection
■
Creating .NET Stored Functions and Procedures
■
Deploying a .NET Stored Functions and Procedures
■
Running .NET Stored Functions and Procedures
Creating a New Connection 1.
Start Microsoft Visual Studio for .NET.
2.
From the View menu, select Oracle Explorer.
Beta Draft
Using .NET Stored Procedures
6-1
Creating a New Connection
3.
In Oracle Explorer, right-click the Data Connections node and from the menu, select Add Connection. Alternatively, you can click the + icon (Add Connection) in the Oracle Explorer pane.
4.
Enter ORCL for Data source name. Enter hr for the User name and hr for Password. Click Test Connection.
5.
Provided the connection was successful, dismiss the test result window by clicking OK.
6.
Click OK to close the Add Connection window.
6-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Creating .NET Stored Functions and Procedures
Creating .NET Stored Functions and Procedures 1.
Click the New Project button. Alternatively, from the File menu, select New, and then select Project.
A New Project dialog box appears. 2.
On the left side of the New Project dialog box under Project Types, select the C# programming language. If you intend to develop in Visual Basic, select it instead. On the right side of the New Project dialog box under Templates, select Oracle Project. Under Name, enter HumanResourcesStored. Check the Create Directory for Solution box. Click OK.
Beta Draft
Using .NET Stored Procedures
6-3
Creating .NET Stored Functions and Procedures
3.
Paste code in Example 6–1 or Example 6–2 into the Class1 object.
Example 6–1 Adding getDepartments() Method Code; C# public static int getDepartmentno(int employee_id) { int department_id = 0; // Get a connection to the db OracleConnection con = new OracleConnection(); con.ConnectionString = "context connection=true"; con.Open(); // Create and execute a command OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "select department_id from employees where employee_id = :1"; cmd.Parameters.Add(":1", OracleDbType.Int32, employee_id, ParameterDirection.Input); OracleDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) department_id=rdr.GetInt32(0); rdr.Close(); cmd.Dispose(); // Return the employee's department number return department_id; Example 6–2 Adding getDepartments() Method Code; VB Public Shared Function getDepartmentno(ByVal employee_id As Integer) As Integer Dim department_id As Integer = 0 ' Get a connection to the db Dim con As OracleConnection = New OracleConnection con.ConnectionString = "context connection=true" con.Open()
6-4 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Deploying a .NET Stored Functions and Procedures
' Create and execute a command Dim cmd As OracleCommand = con.CreateCommand() cmd.CommandText = "select department_id from employees where employee_id = :1" cmd.Parameters.Add(":1", OracleDbType.Int32, employee_id, ParameterDirection.Input) Dim rdr As OracleDataReader = cmd.ExecuteReader() While rdr.Read() department_id = rdr.GetInt32(0) rdr.Close() cmd.Dispose() ' Return the employee's department number getDepartmentno = department_id End While End Function 4.
Using the Ctrl+S keyboard shortcut, save Class1.
5.
From the Build menu, select Build Solution, or use the Ctrl+Shift+B keyboard shortcut.
Deploying a .NET Stored Functions and Procedures 1.
From the Build Menu, select Deploy Solution.
Beta Draft
Using .NET Stored Procedures
6-5
Deploying a .NET Stored Functions and Procedures
The window shows how to deploy a solution. *********************************************************************************************** An Oracle Deployment Wizard for .NET appears. 2.
Click Next.
3.
On the Configure Your Connection screen, click New Connection to establish a connection with SYSDBA privileges.
6-6 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Deploying a .NET Stored Functions and Procedures
4.
Select ORCL as the Source Database, enter sys for the Username and system password for the Password. Click Test Connection.
The test result window appears. 5.
Provided the connection was successful, dismiss the test result window by clicking OK.
Beta Draft
Using .NET Stored Procedures
6-7
Deploying a .NET Stored Functions and Procedures
6.
Click OK to save the connection and dismiss the Add Connection window.
7.
Click Next in the Oracle Deployment Wizard for .NET.
6-8 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Deploying a .NET Stored Functions and Procedures
8.
Ensure that Copy Assembly and Generate New Procedures is selected, and click Next.
9.
On the Specify the assembly and library name screen, accept the defaults and click Next.
Beta Draft
Using .NET Stored Procedures
6-9
Deploying a .NET Stored Functions and Procedures
10. On the Specify copy options screen, accept the defaults and click Next.
11. On the Specify methods and security details screen, under Available methods:,
expand HumanResourcesStored, then expand Class1, and select the getDepartmentno() method. Under Method Details, select HR from the Schema drop-down list. 6-10 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Deploying a .NET Stored Functions and Procedures
Click Next.
12. On the Summary screen, click Finish.
13. When your procedure deploys successfully, click OK.
Beta Draft
Using .NET Stored Procedures 6-11
Running .NET Stored Functions and Procedures
Running .NET Stored Functions and Procedures 1.
In Oracle Explorer, expand hr.ORCLE. Expand Functions. Right-click GETDEPARTMENTNO and select Run.
The Run Function window will appear. 2.
In the Run Function window, enter a Value of 100 for EMPLOYEE_ID. Click OK.
3.
Note that the return value for department is 90.
6-12 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Running .NET Stored Functions and Procedures
Beta Draft
Using .NET Stored Procedures 6-13
Running .NET Stored Functions and Procedures
6-14 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
7 Including Globalization Support This chapter discusses globalization support available in Oracle Data Provider for .NET. This chapter contains the following section: ■
Globalization Support
Globalization Support Oracle Data Provider for .NET globalization support enables applications to manipulate culture-sensitive data, such as ensuring proper string format, date, time, monetary, numeric, sort order, and calendar conventions, all depending on the Oracle client globalization settings.
Client Globalization Settings Client globalization settings derive from the Oracle globalization setting, NLS_LANG, in the Windows registry of the local computer. The client globalization parameter settings are read-only and remain constant throughout the lifetime of the application. Example 7–1 and Example 7–2 illustrate how these settings can be obtained by calling the OracleGlobalization.GetClientInfo() static method. The properties of the OracleGlobalization object provide the Oracle globalization value settings. Example 7–1 How to Obtain Oracle Globalization Settings; C# using System; using Oracle.DataAccess.Client; class ClientGlobalizationSample { static void Main() { OracleGlobalization ClientGlob = OracleGlobalization.GetClientInfo(); Console.WriteLine("Client machine language: " + ClientGlob.Language); Console.WriteLine("Client characterset: " + ClientGlob.ClientCharacterSet); } } Example 7–2 How to Obtain Oracle Globalization Settings; VB Imports System Imports Oracle.DataAccess.Client Module ClientGlobalizationSample
Beta Draft
Including Globalization Support
7-1
Globalization Support
Sub Main() Dim ClientGlob As OracleGlobalization = OracleGlobalization.GetClientInfo() Console.WriteLine("Client machine language: " + ClientGlob.Language) Console.WriteLine("Client characterset: " + ClientGlob.ClientCharacterSet) End Sub End Module
7-2 Oracle Database Express Edition 2 Day Plus .NET Developer Guide
Beta Draft
Index
Beta Draft
Index-1
Index-2
Beta Draft
Related Documents
2 Days Plus Dot Net Developer Guide
December 2019 22
2 Days Developer Guide
December 2019 20
Dot Net Guide
November 2019 13
Dot Net
December 2019 31
Dot Net
November 2019 27
Dot Net
May 2020 23More Documents from ""
Advanced Javascript
December 2019 24
935wp
December 2019 22
2 Days Plus Dot Net Developer Guide
December 2019 22
Getting Started Guide
December 2019 37
Top 10 About Oracle 10g
December 2019 40