Developing and Deploying a .NET Stored Function
Building .NET Applications Using Oracle Developer Tools For Visual Studio .NET
Developing and Deploying a .NET Stored Function
This tutorial describes how you can deploy a .NET stored function using Oracle Developer Tools for Visual Studio .NET.
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 begin creating your application, you open Visual Studio and examine the interface. Perform the following steps:
| 1. |
Select Start > Programs > Microsoft Visual Studio .NET > Microsoft Visual Studio .NET.
|
| 2. |
Select View > Oracle Explorer.
|
| 3. |
Click the + icon to add a connection.
|
| 4. |
Enter your Data source name. Enter
hr for the User name and
Password, click Save password, select Default
as Role, and click Test connection.
|
| 5. |
Test connection was successful. 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 tab, click Project...
adjacent to Create: in the Recent Projects area.
|
| 2. |
Select the Project Type Visual C#,
select the Template Oracle Project, enter Project1
in the name, and enter 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 in the codesnippets folder on the Desktop of your computer.
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.
|
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 Source Database,
enter sys for the User name
and <syspassword> for
the Password. Click Save password and click Test
Connection.
|
| 5. |
You connection is successful. 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 on 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, make sure that the OracleOraDb10g_home1ClrAgent is started. 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
Move
your mouse over this icon to hide all screenshots.
|
Bookmark
