| Oracle® Database Express Edition 2 Day Plus .NET Developer Guide 10g Release 2 (10.2) Part Number B25312-01 |
|
|
View PDF |
| Oracle® Database Express Edition 2 Day Plus .NET Developer Guide 10g Release 2 (10.2) Part Number B25312-01 |
|
|
View PDF |
This chapter discusses how to use and deploy .NET stored procedures in your application. You can use custom stored procedures in your ODP.NET code in the same manner as any other stored procedure.
This chapter contains the following sections:
To use .NET Stored Procedures, you must first start the XE Common Language Runtime agent, represented by the OracleXEClrAgent service. This service may not start by default. Note that it is located on the Oracle XE database server, not on the client.
From the Start menu, select All Programs, then select Administrative Tools, and finally, select Services.
In the Services window, click the Extended tab.
Scroll down the list of Services, and select OracleXEClrAgent.
Click the Start hyperlink.
The Service Control window shows that the OracleXEClrAgent is starting.
When the Service Control window closes, note that the status of the OracleXEClrAgent is changed to Started.
Follow these steps to start a new Oracle project in Visual Studio .NET 2003:
On the left side of the New Project dialog box under Project Types, select Visual C# Projects.
On the right side of the New Project dialog box under Templates, select Oracle Project.
For Name, enter HR_DeployStored_CS.
For Location, enter C:\HR\Visual Studio Projects.
Click OK.
If you wish to create project in Visual Basic, under Project Types select Visual Basic Projects instead, and enter HR_DeployStored_VB under Name.
In Oracle Explorer, right-click Data Connections. From the menu, select Add Connection.
In the Add Connection window, enter the following information:
Data source name: Use the Local Database if you are connecting to a database on the same machine. Otherwise, use the alias of the remote database instance.
Select the Use a specific user name and password option.
For Password, enter hr, or the password that was set when unlocking and setting up the hr account.
To save the password for future sessions, check the Save password box. While it is not advisable to save connection password within your application in clear text for security reasons, we will do it in this demonstration.
Ensure that the Role is set to Default. This refers to the default roles that have been granted to the user hr.
The Connection name should be generated automatically from the Data source name and the User name values.
Click the Apply Filters tab and check that the HR schema is in the Displayed schemas column. Only the schema objects (tables, views, and so on) from the schemas selected in the Apply Filters tab are displayed when you expand the schema category nodes in the data connection.
Click the Connection Details tab, and click Test connection.
The test should succeed. Click OK.
If the test fails, it may be due to one or more of the following issues that you must address before proceeding with further steps:
The database is not started.
The database connectivity is not properly configured.
You do not have the correct user name, password, and role.
Click OK to close the Add Connection window.
The Oracle Explorer window should now contain the hr.(Local Database) connection.
Select Class1.cs tab in your project.
Paste the getDepartmentno() method into the Class1 declaration, as shown in Example 7-1 and Example 7-2.
Example 7-1 Adding getDepartmentno() Method Code: 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;
}
Example 7-2 Adding getDepartmentno() Method Code: VB
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
getDepartmentno = department_id
End Function
Using the Ctrl+S keyboard shortcut, save Class1.
From the Build menu, select Build Solution.
An Output window shows that the build was successful. Close the Output window.
Follow these steps to deploy a .NET Stored Procedure:
From the Build menu, select Deploy Solution.
In the Oracle Deployment Wizard for .NET window, click Next.
On the Configure Your Connection window, click New Connection.
You must now establish a connection with SYSDBA privileges.
To use the Oracle Application Express to set the sys account password, see Chapter 6, "Managing Users and Security" in the Oracle Database Express Edition 2 Day DBA.
Click OK to close the test result window.
In the Oracle Deployment Wizard for .NET window, click Next.
On the Specify your deployment option window, ensure that Copy assembly and generate stored procedures is selected, and click Next.
On the Specify an assembly and library name window, accept the defaults and click Next.
On the Specify copy options window, accept the defaults and click Next.
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 drop-down list.
Click Next.
On the Summary window, click Finish.
Follow these steps to run the .NET Stored procedure you created in Section "Deploying .NET Stored Functions and Procedures":
In Oracle Explorer, expand the hr.(LocalDatabase) connection. Expand Functions. Right-click GETDEPARTMENTNO and select Run.
In the Run Function window, enter a Value of 100 for EMPLOYEE_ID.
Click OK.
Note that the return value for department is 90.
