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 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 .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 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 New Project.
|
| 2. |
Select the Project Type Visual C# Projects , 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.
|
| 3. |
Copy and paste the following text into the class object.
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.
|
| 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 Username 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 Procedure has been deployed successfully. Click OK.
|
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.
|
| 2. |
Enter a value of 100 (or any other valid Employee ID) and press enter.
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
