As Published In November/December 2005

DEVELOPER: Windows

Tools for Your .NET Space

By Christian Shay

Use ODT for fast, easy development for Oracle Database on Windows.

You're working on some C# or VB.NET routines that manipulate data you're retrieving from Oracle Database. You need to access the database to edit and test some stored procedures, modify some data in a table, change the structure of a table or view, or perform one of countless other database tasks.

In the past, you've managed this by using a tool such as SQL*Plus or Toad, but you wish there were a toolset that would let you do all of this from directly inside of Microsoft Visual Studio .NET. And you wish this toolset could include graphical designers and wizards that do the grunt work of figuring out the SQL that's needed for a particular database operation. And maybe this toolset could even automatically generate some of the C# or VB.NET data access code, as well.

Your wishes have been granted: With the release of Oracle Developer Tools for Visual Studio .NET (ODT), all of this is now a reality. In this article, I'll give you a quick tour of many of the features that ODT has to offer. I'll also introduce the new .NET stored procedures feature in Oracle Database 10g Release 2 for Windows and show how you can use ODT to deploy a .NET stored procedure into Oracle Database.

Overview

ODT is a tightly integrated add-in for Visual Studio .NET. ODT, first released in June 2005, is free and available for download from the Oracle Technology Network.

Major features of ODT include 

• Oracle Explorer

• Designers and wizards

• Oracle Data Window

• Oracle PL/SQL Editor

• Automatic code generation

• SQL Query Window

• Integrated Online Help

Oracle Explorer: Viewing the Oracle Schema

When you need to find and examine a table, view, or other Oracle database object, the first place to go is to the Oracle Explorer. To launch the Oracle Explorer, from the main menu, choose View->Oracle Explorer . The Oracle Explorer, shown in Figure 1, is a tree control that lets you view the structure of the Oracle schema. You can view Oracle schema objects including tables, views, packages, and stored procedures here. When you select a schema object, the Visual Studio .NET properties pane displays metadata such as column datatypes or stored procedure parameter types.

Figure 1: Oracle Explorer (left) and PL/SQL Editor (right)

A context menu for each schema object offers additional features: You can generate SQL scripts for schema objects, use the provided filters to limit what types of schema objects are shown, and run designers and wizards to alter the schema. For example, while viewing the structure of a table, you can use the context menu to run the Oracle Table Designer to modify the design of the table.

Designers and Wizards: Simplifying Database Operations

ODT offers a variety of graphical designers and wizards to provide step-by-step help to create or alter database objects including tables, views, stored procedures, stored functions, PL/SQL packages, sequences, indexes, constraints, triggers, synonyms, and more.

To launch the Table Designer, right-click the table in the Oracle Explorer, and choose Design . The Table Designer, shown in Figure 2, makes looking up datatypes or memorizing SQL syntax a thing of the past. You simply provide the table name and the names of the columns, choose the appropriate datatype from a drop-down list, and click Save to create or alter the table. The newly created schema object will then immediately appear in the Oracle Explorer tree control.

Figure 2: Table Designer

Oracle Data Window: View and Modify Table Data

To quickly see table or view data, right-click the table or view in the Oracle Explorer and choose Retrieve Data . The Oracle Data window, shown in Figure 3, appears and displays table or view data in an easy-to-read grid and lets you insert, update, or delete data without leaving the Visual Studio .NET development environment. When you modify a row, the cursor moves to a new row, and the data is immediately committed. For large tables you can provide a row number to navigate to a particular row.

Figure 3: Oracle Data Window

PL/SQL Editor

To edit a procedure, function, or, package, right-click the item in the Oracle Explorer and choose Edit . The PL/SQL editor appears, as shown in Figure 4, and provides a tightly integrated development environment: You don't need to leave Visual Studio .NET to develop stored procedures. Standard Microsoft Visual Studio features in the PL/SQL Editor include syntax coloring to improve readability and collapsible regions to hide stored procedures or functions that are part of a large, complex package. This makes it easier to focus on the procedure or function that you're working on. Additionally, as you enter SQL or PL/SQL statements, a drop-down list of tables or columns will pop up to auto-complete the statement.

Figure 4: PL/SQL Editor

To compile the PL/SQL code, right-click the item in the Oracle Explorer and choose Compile . Any errors found when you compile the PL/SQL are listed in the Microsoft Visual Studio .NET task list. Click on the error text to take you to the line of code that failed. Press the context-sensitive Help key to open the Error Messages and Codes manual for the error code in question.

To test complete stored procedures and functions, right-click the item in the Oracle Explorer and choose Run . A dialog box will appear where any required input parameters for the stored procedure or function can be entered. Click OK in the dialog box to execute the code. The results of running the stored procedure—the output parameters—are displayed in a final window, shown in Figure 5. Complex output types, such as REF CURSOR, are shown as links; click these links to open up grids containing the complex data.

Figure 5: Testing PL/SQL

Automatic Code Generation

When you drag and drop a schema object from the Oracle Explorer onto an application's form, code is automatically generated, for SELECT , UPDATE , INSERT , and DELETE operations on that schema object. The code uses the OracleDataAdapter class, which is provided by Oracle Data Provider for .NET (ODP.NET). This class derives from the ADO DataAdapter class. Anyone who has worked with DataAdapter classes in the past, even with other database vendors, will be familiar with this. You can then use the DataAdapter class to connect UI elements (such as a DataGrid element) on the application's form to Oracle Database—with minimal additional coding.

Code Listing 1: getDepartmentno stored function 

using System;
using System.Data;
using Oracle.DataAccess.Client;

namespace Project1
{
    /// <summary>
    /// Summary description for Class1.
    /// </summary>
    public class Class1
    {
        public static int getDepartmentno(int empno)
        {
            int deptno = 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 deptno from emp where empno = :1";
            cmd.Parameters.Add(":1", OracleDbType.Int32, empno, 
            ParameterDirection.Input);
            OracleDataReader rdr = cmd.ExecuteReader();
            while(rdr.Read())
                deptno = rdr.GetInt32(0);
            rdr.Close();
            cmd.Dispose();

            // Return the employee's department number
            return deptno;
        }
   }
 }

You can also generate code for a typed or untyped dataset based on an Oracle table or view. To generate the code, right-click the OracleDataAdapter icon on your form and choose Generate Dataset . You can then bind this dataset as a data source to UI elements or use it in other standard ways throughout Microsoft Visual Studio .NET, such as with the Microsoft Visual Studio XML Schema designer.

Oracle Database Extensions for .NET: Your First .NET Stored Function

With Oracle Database 10g Release 2 on Windows, Oracle now offers Oracle Database Extensions for .NET. This database option lets you deploy stored procedures and functions written in a .NET-managed language into Oracle Database on Windows. This option is not installed by default; you'll need a custom install of the database to enable it. (For more information on setting up your database to run .NET stored procedures, please see the Oracle Database Extensions for .NET Developer's Guide .) This database option is available only on the Windows platform; those requiring a platform-neutral solution should continue using PL/SQL and Java.

Follow these steps to write and deploy your first .NET stored function:

Step 1: Choose File->New->Project-> C# Projects->Oracle Project to open an Oracle Project.
Step 2: Enter the code for the stored function getDepartmentno from Listing 1. Note that data access in this code is provided by ODP.NET. The only difference between the code for this stored function and code that would be used in a client-side application is the use of the connection string attribute context connection=true . This attribute tells ODP.NET to use the connection associated with the caller of this stored function rather than make a new connection.
Step 3: Build the code into an assembly. Choose Build->Build Solution to begin, and complete the assembly as you would any Microsoft Visual Studio .NET assembly.
Step 4: From the Build menu, choose Deploy Solution . This will start the .NET Deployment wizard.
Step 5: In the .NET Deployment wizard, click on New Connection to establish a connection with SYSDBA credentials. SYSDBA credentials are required to deploy .NET stored procedures.
Step 6: In the next wizard screen, choose Copy Assembly and generate stored procedures .
Step 7: In the next screen, when asked for the assembly and library name, accept the default values.
Step 8: In the next screen, for the dependent assemblies as well as deployment location, accept the default values (the default deployment location is ORACLE_HOME\BIN\CLR).
Step 9: In the datatype mapping screen, shown in Figure 6, expand the tree control and check the box next to getDepartmentno . Choose the SCOTT schema to deploy to. Accept the default .NET to Oracle parameter datatype mappings. Choose a security level. You can set the security level of this function to Safe , in which only database access is allowed; External , in which files and network resources can also be accessed; and Unsafe , in which no restrictions are placed on the code.

Figure 6: Oracle Deployment wizard, datatype mapping screen

Step 10: Press Finish to deploy the assembly and register the stored function with Oracle Database. You should now see the GETDEPARTMENTNO function listed under the Functions node of the Oracle Explorer.
Step 11: To test the stored function, right-click it in the Oracle Explorer and choose Run . Provide a valid EMPNO value as the input parameter (for example, "7369"), and examine the output.

Summary

This article presented the major features of Oracle Developer Tools for Visual Studio .NET, which makes .NET development with Oracle Database faster and easier. The article also walked through the steps to develop and deploy a .NET stored function into Oracle Database 10g Release 2 on Microsoft Windows.