8 Developing and Deploying .NET Stored Procedures

This chapter contains:

Overview of .NET Stored Procedures

.NET stored procedures are methods or procedures written in a .NET language which contains SQL or PL/SQL statements.

You can write custom stored procedures and functions using any .NET compliant language, such as C# and VB.NET, and use these .NET stored procedures in the database, in the same manner as other PL/SQL or Java stored procedures. .NET stored procedures can be called from PL/SQL packages, procedures, functions, and triggers; from SQL statements, or from anywhere a PL/SQL procedure or function can be called.

Oracle Database Extensions for .NET (a database option that allows you to write .NET stored procedures) must be installed and configured in the database to run the examples in this chapter.

This chapter discusses how to use and deploy .NET stored procedures in your application.

Starting the Common Language Runtime Service

To use .NET stored procedures, you must first start the common language runtime agent, represented by the OraClrAgent service. This service may not start by default. Note that it is located on the Oracle database, not on the client.

Note:

OraClrAgnt can be accessed through the Services Control Panel, as OracleOracleHomeNameClrAgnt, where OracleHomeName represents your Oracle home.

To start the common language runtime service:

  1. From the Start menu, select All Programs, then select Administrative Tools, and finally, select Services.

  2. In the Services window, click the Extended tab.

    Scroll down the list of Services, and select OracleOracleHomeNameClrAgnt.

  3. Click the Start hyperlink.

    The Service Control window shows that the OracleClrAgent is starting.

  4. When the Service Control window closes, note that the status of the OracleClrAgent is changed to Started.

Creating a Connection as SYSDBA

Next, you must create a database connection as SYSDBA which enables you to deploy your Oracle Project.

Note:

You must have administrative privileges as SYSDBA to perform this task.

Note:

To use the Enterprise Manager to set the sys account password, see About Administrative Accounts and Privileges in the Oracle Database 2 Day DBA.

To create a database connection in ODT:

  1. From the View menu, select Server Explorer.

  2. In Server Explorer, right-click Data Connections.

  3. Select Add Connection.

  4. When the Add Connection window appears, determine if the Data source says Oracle Database (Oracle ODP.NET).

    If it does, skip to Step 6.

    Description of addconnection1a.gif follows
    Description of the illustration addconnection1a.gif

    If Data source does not say Oracle Database (Oracle ODP.NET), select Change.

    The Change Data Source window appears.

    Description of addconnection2a.gif follows
    Description of the illustration addconnection2a.gif

  5. Choose Oracle Database and then select Oracle Data Provider for .NET.

  6. In the Add Connection window, use the following:

  7. In the Add Connection window, click OK

    The Server Explorer window should now contain the SYS.ORCL connection.

Creating an Oracle Project

To use stored procedures in .NET, you must first create a new Oracle Project to hold the stored procedures.

To create a project for .NET stored procedures:

  1. From the File menu, select New, and then select Project.

    A New Project dialog box appears.

  2. In Project Types, select the type of project you are creating:

    • Visual C#:

      Visual C# , then select Database, and under Templates:Oracle Project

      Enter Name: HR_DeployStored_CS.

    • Visual Basic:

      Other Languages, then select Visual Basic and Database, then under Templates: Oracle Project

      Enter Name: HR_DeployStored_VB.

  3. Enter Location: C:\HR_Projects.

  4. Click OK.

    Description of deploy01.gif follows
    Description of the illustration deploy01.gif

Creating .NET Stored Functions and Procedures

You are now ready to create a .NET stored procedure.

To create a .NET stored procedure:

  1. In Solution View, select the Class1.cs or Class1.vb tab in your project.

  2. Add these namespace directives for the specific language, as described in "Adding Namespace Directives".

    Visual C#:

    using Oracle.DataAccess.Client; 
using Oracle.DataAccess.Types;

    

    Visual Basic:
    

    Imports Oracle.DataAccess.Client 
Imports Oracle.DataAccess.Types

    3. 

  3. 

    Add Reference to Oracle.DataAccess.dll as described in "Adding a Reference".
    

    4. 

  4. 

    Copy the getDepartmentno() method into the Class1 declaration, as indicated
    

    Visual C#
    

    
public static int getDepartmentno(int employee_id)
{
  int department_id = 0;

  // Get a connection to the db
  OracleConnection conn = new OracleConnection();
  conn.ConnectionString = "context connection=true";
  conn.Open();

  // Create and execute a command
  OracleCommand cmd = conn.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;
}

    

    Visual Basic:
    

    
Public Shared Function getDepartmentno(ByVal employee_id As Integer) As Integer
  Dim department_id As Integer = 0 

  ' Get a connection to the db
  Dim conn As OracleConnection = New OracleConnection()
  conn.ConnectionString = "context connection=true"
  conn.Open()

  ' Create and execute a command
  Dim cmd As OracleCommand = conn.CreateCommand()
  cmd.CommandText = "select department_id from employees where employee_id = :1"
  cmd.Parameters.Add(":1", OracleDbType.Int32, employee_id,     ParameterDirection.Input)
  Dim rdr As OracleDataReader = cmd.ExecuteReader()

  While rdr.Read()
    department_id = rdr.GetInt32(0)

  End While

  rdr.Close()
  cmd.Dispose()

  ' Return the employee's department number
  Return department_id

End Function

    
Description of deploy04.gif follows
    
Description of the illustration deploy04.gif
    

    5. 

  5. 

    Save Class1.
    

    6. 

  6. 

    From the Build menu, select Build Solution.
    
Description of deploy05.gif follows
    
Description of the illustration deploy05.gif
    

    7. 

  7. 

    Check that the Output window indicates a successful build and close it.
    

    8. 









Deploying .NET Stored Functions and Procedures


You can now deploy the .NET stored procedure that you created "Creating .NET Stored Functions and Procedures".


To deploy a .NET stored procedure:


    

  1. 

    From the Build menu, select Deploy Solution.
    
Description of deploy06.gif follows
    
Description of the illustration deploy06.gif
    

    

    An Oracle Deployment Wizard for .NET window appears.
    

    2. 

  2. 

    In the Oracle Deployment Wizard for .NET window, click Next.
    
Description of deploy07.gif follows
    
Description of the illustration deploy07.gif
    

    3. 

  3. 

    On the Configure your OracleConnection window, click Next.
    
Description of deploy08.gif follows
    
Description of the illustration deploy08.gif
    

    4. 

  4. 

    On the Specify your deployment option window, ensure that the first option, Copy assembly and generate stored procedures is selected, and click Next.
    
Description of deploy10.gif follows
    
Description of the illustration deploy10.gif
    

    5. 

  5. 

    On the Specify an assembly and library name window, accept the defaults and click Next.
    
Description of deploy11.gif follows
    
Description of the illustration deploy11.gif
    

    6. 

  6. 

    On the Specify copy options window, accept the defaults and click Next.
    

    Visual Basic:
    

    If you are using Visual Basic, the Microsoft.VisualBasic assembly also appears as a referenced assembly.
    
Description of deploy12.gif follows
    
Description of the illustration deploy12.gif
    

    7. 

  7. 

    On the Specify methods and security details window, under Available methods, expand HR_DeployStored_CS or HR_DeployStored_VB, then expand Class1, and select the getDepartmentno() method.
    

    Under Method Details, select HR from the Schema list.
    

    Click Next.
    
Description of deploy13.gif follows
    
Description of the illustration deploy13.gif
    

    8. 

  8. 

    On the Summary window, click Finish.
    
Description of deploy14.gif follows
    
Description of the illustration deploy14.gif
    

    9. 









Running .NET Stored Functions and Procedures


You are now ready to run the .NET stored procedure you deployed earlier.


To run a .NET stored procedure:


    

  1. 

    In Server Explorer, open and expand the HR.ORCL connection. Expand Functions. Right-click GETDEPARTMENTNO and select Run.
    
Description of deploy15.gif follows
    
Description of the illustration deploy15.gif
    

    

    The Run Function window appears.
    

    2. 

  2. 

    In the Run Function window, enter a Value of 100 for EMPLOYEE_ID.
    

    Click OK.
    
Description of deploy16.gif follows
    
Description of the illustration deploy16.gif
    

    3. 

  3. 

    Note that the return value for department is 90, indicating that EMPLOYEE_ID 100 is in department 90.
    
Description of deploy17.gif follows
    
Description of the illustration deploy17.gif
    

    4. 









Running .NET Stored Procedure in a Query Window


You can run the .NET stored procedure that you have just created using the ODT Query Window, in addition to running it from Server Explorer.


    

  1. 

    Open the Server Explorer in the HR.ORCL schema.
    

    2. 

  2. 

    Expand Functions and select GETDEPARTMENTNO.
    

    3. 

  3. 

    Right-click and select Query Window.
    

    4. 

  4. 

    Enter Select getdepartmentno(100) from dual.
    

    5. 

  5. 

    Click Execute from the toolbar.
    
Description of querywindow.gif follows
    
Description of the illustration querywindow.gif
    

    6.