Developing and Deploying a .NET Stored Procedure

Install Microsoft Visual Studio 2010

Install Oracle Database 10.2 or later with "Oracle Database Extensions for .NET" database option installed and configured.

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

Extract this file into your working directory.

Open Visual Studio.

Select View > Server Explorer.

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....

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.

Test connection succeeded. Click OK.

Click OK.

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

From the File menu, select New > Project.

Expand the Database Project type, click Oracle and select the Visual C# Oracle CLR Project template. Enter Project2 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.

Delete public static void StoredProcedure1() and it's curly braces ( {} ) from the code and copy and paste the text in getdepartmentno.txt from the file you downloaded in the working directory.

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;

}

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

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

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

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.

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.

Test connection succeeded. Click OK.

Click OK to save your connection.

Click Next.

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

Accept the defaults and click Next.

Accept the defaults and click Next.

Select HR from the list of Schemas and click Next.

At the Summary window, click Finish.

Your .NET Stored Function has been deployed successfully.

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

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.

The Department id 90 of the Employee 100 was selected.