Developing and Deploying a .NET Stored Function
Developing and Deploying a .NET Stored Function
This tutorial describes how to deploy a .NET stored function
using Oracle Developer Tools for Visual Studio.
Approximately 30 minutes
This tutorial covers the following topics:
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.
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
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
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
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
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
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
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.
|
Bookmark
