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 2005.
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:
Back to Topic
List
Before you begin creating your application, you open
Visual Studio and examine the interface. Perform the following steps:
| 1. |
Select Start > Programs > Microsoft
Visual Studio 2005 > Microsoft Visual Studio 2005.
|
| 2. |
Select View > Server Explorer.
|
|
3.
|
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 Start Page tab, click Project...
adjacent to Create: in the Recent Projects area.
|
| 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.
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
This tutorial assumes you are using Oracle Database 10g. If you are using Oracle Database 10g, you need to reference ODP.NET 10.2 in your stored function. The Oracle Project will default to using ODP.NET version 11. You need to change the reference to use ODP.NET 10.2. Perform the following steps:
| 1. |
In the Solution Explorer window, expand References.
(Note: If you can not see the Solution Explorer window, select View
> Solution Explorer.)
|
| 2. |
Right-click Oracle.DataAccess and select Remove.
|
|
3.
|
Right-click References and select Add Reference....
|
| 4. |
In the Add Reference window, select the Browse tab.
|
| 5. |
Browse to <oracle home> odp.net > bin > 2.x to select
Oracle.DataAccess.Dll and click OK.
|
Back to Topic
List
In this topic, you deploy a .NET Stored Function into the
Database. Perform the following steps:
| 1. |
Select Build > Deploy Solution. 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.
|
| 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.
|
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
