Developing and Deploying a .NET Stored Function

Purpose

This tutorial describes how to deploy a .NET stored function using Oracle Developer Tools for Visual Studio.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating an Oracle Connection
 Creating a .NET Stored Function
 Deploying the .NET Stored Function
 Running the .NET Stored Function
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

With Oracle Database 10g Release 2 on Windows, Oracle now offers Oracle Database Extensions for .NET. This database option makes it possible to deploy stored procedures and functions written in a .NET managed language into the Oracle database on Windows.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Microsoft Visual Studio 2008

2.

Install Oracle Database 10.2 or later

3.

Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.1.0.6.20 or later from OTN

4.

Download and unzip the codesnippets.zip file into your working directory.

5.

Install and configure you database to add Oracle Database Extensions for .NET 2.0

Back to Topic List

Creating an Oracle Connection

Before you begin creating your application, you open Visual Studio and examine the interface. Perform the following steps:

1.

Open Visual Studio.

 

2.

Select View > Server Explorer.

 

3.

 

In Server Explorer, if you already have an HR.ORCL connection, you can expand it to connect to the database.

Then enter HR for the password and click OK.

Otherwise, right-click Data Connections and select Add Connection....

 

4.

Enter your Data source name. Enter hr for the User name and Password. Click Save password, select Default as the Role, and click Test Connection.

 

5.

Test connection succeeded. Click OK.

 

6.

Click OK.

 

7.

Your connection has been created. Expand HR.ORCL. Now you can create your Stored Function.

 

Back to Topic List

Creating a .NET Stored Function

In this topic, you create an Oracle project to store the function in the database. Perform the following steps:

1.

From the File menu, select New Project.

 

2.

Expand the Visual C# Project type, click Database and select the Oracle Project template. Enter Project1 in the Name field and select the directory where you want the files stored (Note: if the directory does not exist, it is created). Then click OK.

The Project1 class object is shown.

 

3.

 

Delete public static void StoredProcedure1() and it's curly braces ( {} ) from the code and copy and paste the text located in file getdepartmentno.txt . This file is located in the codesnippets folder on the Desktop.

                               
                                 
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;

}
                              

                                


                            
4.

Right-click the Class1.cs tab and select Save Class1.cs.

 

Back to Topic List

Deploying a .NET Stored Function

In this topic, you deploy a .NET Stored Function into the Database. Perform the following steps:

1.

Select Build > Deploy Project1. This task compiles the code and then launches the .NET deployment wizard.

 

2.

The Oracle Deployment Wizard for .NET appears. Click Next.

 

3.

You need to establish a connection with SYSDBA credentials. Click New Connection.

Note: If you already have a connection to the SYS user, select that connection from the list.

 

4.

Select ORCL as the Data source name, enter sys for the User name and <syspassword> for the Password. Select Save password and click Test Connection.

 

5.

Test connection succeeded. Click OK.

 

6.

Click OK to save your connection.

 

7.

Click Next.

 

8.

Make sure Copy assembly and generate stored procedures is selected and click Next.

 

9.

Accept the defaults and click Next.

 

10.

Accept the defaults and click Next.

 

11.

Expand Project1 > Class1 and select the check box next to Int32 getDepartmentno. Then select the down arrow next to schema.

 

12.

Select HR from the list of Schemas and click Next.

 

13.

At the Summary window, click Finish.

 

14.

Your .NET Stored Function has been deployed successfully.

 

Back to Topic List

Running the .NET Stored Function

In this topic, you execute the .NET Stored Function you just deployed. In this case, the function retrieves the department number for the employee id given. Perform the following steps:

1.

Expand HR.ORCL > Functions. Right-click GETDEPARTMENTNO and select Run. If you do not see GETDEPARTMENTNO, right-click Functions and click Refresh.

 

2.

Enter a value of 100 (or any other valid Employee ID) and click OK.

Note: If you receive an error, stop and start the OracleOraDb10g_home1ClrAgent. Then try again.

 

3.

The Department id 90 of the Employee 100 was selected.

 

Back to Topic List

Summary

In this tutorial, you learned how to create, deploy and run a .NET Stored Function.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document