Building .NET Applications Using Oracle Developer Tools for Visual Studio .NET

Purpose

This tutorial describes how to build .NET applications using Oracle Developer Tools for Visual Studio .NET.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Creating an Oracle Connection
Creating a Table
Viewing Data in a Table
Generating Code Automatically
Using Stored Procedures
Using the Query Window
Summary

Viewing Screenshots

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.

Overview

The new Oracle Developer Tools for Visual Studio .NET is a tightly integrated "Add-in" for Visual Studio .NET that brings the power of the Oracle Database to .NET developers! Designed to be completely intuitive for database developers, your productivity will increase starting on day one. And if you are new to Oracle development, this is an easy way to learn!

Use the Oracle Explorer to browse your Oracle schema, launch one of the many powerful designers and wizards to create and alter schema objects, and drag and drop schema objects onto your form to automatically generate code.

There's also a PL/SQL editor, Adhoc SQL Query Window, and integrated context sensitive online help, including the Oracle SQL and PL/SQL Users Guides. And with the Oracle Data Window you won't have to leave the Visual Studio environment for routine database tasks like inserting and updating Oracle data or testing stored procedures!

This and many other features greatly improve developer productivity and make developing for Oracle on Windows easy to learn!

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Installing Oracle Database 10g on Windows tutorial.

2.

Perform the Installing Oracle Developer Tools for Visual Studio .NET tutorial.

3.

Perform the Configuring the .NET Stored Procedures Environment tutorial.

Back to Topic List

Creating an Oracle Connection

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.

 

Back to Topic List

Creating a Table

Now you will create a new table called DEPENDENTS which has a foreign key with the EMPLOYEES table.

Create the Columns
Create the Index
Create the Foreign Key

Back to Topic List

Create the Columns

To create the columns in the table, perform the following:

1.

Expand hr.ORCL. Right-click Tables and select New Relational Table...

 
2.

Enter DEPENDENTS for Table name and click Add in the Columns section.

 
3.

Enter FIRSTNAME for the Name, select VARCHAR2 for the Data type and enter 30 for the Size. Then click Add.

 
4.

Enter LASTNAME for the Name, select VARCHAR2 for the Data type and enter 30 for the Size. Then click Add.

 
5.

Enter BIRTHDATE for the Name, select DATE for the Data type. Then click Add.

 
6.

Enter RELATIONSHIP for the Name, select VARCHAR2 for the Data type and enter 20 for the Size. Then click Add.

 
7.

Enter EMPLOYEEID for the Name, select NUMBER for the Data type, deselect Allow null and enter 6 for the Precision and 0 for the Scale. Then click Add.

 
8.

Enter DEPENDENTID for the Name, select NUMBER for the Data type, deselect Allow null and enter 6 for the Precision and 0 for the Scale. Then click Preview SQL.

 
9.

Here is the SQL that will execute when the table is created. Click OK.

 
10.

Click Save. Note: the Preview SQL is no longer available since the statement has executed. Now you are ready to create the index.

 

Back to Topic

Create the Index

To create the index for the table, perform the following:

1.

Click Indexes tab.

 
2.

Click Add.

 
3.

Enter DEPENDENTS_INDX for the Name and click Add in the Index Keys area.

 
4.

Select DEPENDENTID for the Key and then click Save. Your index has been created.

 

Back to Topic

Create the Foreign Key

To create the foreign key for the table, perform the following:

1.

Click Constraints tab.

 
2.

Click Add.

 
3.

Enter EMPLOYEES_FK for the Name, select Foreign Key for the type, select EMPLOYEES for the table, and select the constraint EMP_EMP_ID_PK. Select EMPLOYEE_ID as the Referenced Column and EMPLOYEEID as the local column. Then click Add.

 
4.

Enter DEPENDENTS_PK for the Name, select Primary Key for the type, and click Add.

 
5.

Select DEPENDENTID for the primary key column. Then click Save.

 
6.

In the Oracle Explorer view, you can see the table, its index and constraint.

 

Back to Topic

Viewing Data in a Table

You can access the data in a table by performing the following

1.

Right-click DEPENDENTS table and select Retrieve Data...

 
2.

Enter at least 4 lines of data. Make sure you enter a valid Employee ID. Then click Save.

 

Back to Topic List

Generating Code Automatically

In this topic, you will create a project, and generate the code to show the data in the DEPENDENTS table. Perform the following steps:

1.

Click Start Page tab.

 
2.

Click New Project...

 
3.

Select the Project Type Visual C# Projects, select the Template Windows Application, enter obe1 in the name and enter the directory where you want the files stored (Note: if the directory does not exist, it will be created). Then click OK.

 
4.

Select the DEPENDENTS table in the Oracle Explorer window and drag and drop it into the Form1 on the right side.

 
5.

Click Yes to save the connection password in the code that is generated.

 
6.

The icons you now see just below your form represent code that has now been automatically generated for you. Specifically an OracleDataAdapter class has been generated which is part of the Oracle Data Provider for .NET. Double-click dependentsOracleDataAdapter1 to view the code.

 
7.

The form will appear with the code that was generated. Expand Windows Form Designer generated code.

 
8.

The OracleDataAdapter is based on the ADO DataAdapter which is designed to make it easy to send data to and from your database and widgets on your form, with a minimal amount of code. The OracleDataAdapter class is provided by the Oracle Data Provider for .NET which was installed when you installed the Oracle Developer Tools. Click Form1.cs (Design) tab.

 
9.

Click anywhere next to the classes so they become deselected (no box around them).

 
10.

Right-click dependentsOracleDataAdapter1 and select Generate Dataset...

Dependents11 is displayed.

 
11.

In order to be able to display the data in the Dependents table in your application, you need to add a DataGrid to the form which is located in the Toolbox. Select View then Toolbox.

 
12.

Select DataGrid from the list of Windows Forms and drag and drop it on your Form1.

 
13.

Enlarge the DataGrid as shown below. Now you need to set the Data Source of the Grid to the Oracle Data Source. Select Properties Window from the View pulldown. This step maybe optional if your properties window is already open.

 
14.

Under Data section for the Data Source property, select dependents11.Dependents.

 
15.

Double-click Datagrid to view the form with code that was generated.

 
16.

Select DataGrid1 from the list of Members.

 
17.

Enter the following code after the InitializeComponent(); 

this.

You will see a list of valid options to select from. Select dependentsOracleData Adapter1. Then type or select the rest of the code as follows:

this.dependentsOracleDataAdapter1.Fill(this.dependents11.Dependents);

 
18.

Select Build > Build Solution .

 
19.

Check your output area to make sure you didn't receive any errors. Then select Debug > Start.

 
20.

Your application runs and a form appears. Your form has been populated with data. Review your data. Then close the window.

 

Back to Topic List

Using Stored Procedures

Now you will create a stored procedure and run it.

Create a Stored Procedure
Edit the Package Body
Run the Stored Procedure

Back to Topic List

Create a Stored Procedure

1.

From the Oracle Explorer window, right-click Packages and select New Package.

 
2.

Accept the default Package name and click Add under Methods.

 
3.

Enter GETCURSORS for the method name, select Procedure for the method type and click Add under Parameters.

 
4.

Enter MAXROWS for the Name and click Add.

 
5.

Enter EMPLOYEESCUR for the Name, select OUT for Direction, and select SYS_REFCURSOR for Data type. Then click Add.

 
6.

Enter DEPENDENTSCUR for the Name, select OUT for the Direction and select SYS_REFCURSOR for the Data type. Then click OK.

 
7.

Click Preview SQL >> to see the SQL that will be executed.

 
8.

Review the SQL and click OK.

 
9.

Click OK to create the Package.

 

Back to Topic

Edit the Package Body

1.

The Package is created and the Package Body is opened. If you do not see the code that is generated for the Package Body, right-click PACKAGE1 Package in the Oracle Explorer and select Edit Package Body. Scroll down to find the line NULL;

 
2.

Replace the line NULL; with the following:

OPEN EMPLOYEESCUR FOR SELECT * FROM EMPLOYEES;
OPEN DEPENDENTSCUR FOR SELECT * FROM DEPENDENTS;

 
3.

Right-click the window tab and select Save.

 

Back to Topic

Run the Stored Procedure

1.

Expand the Package PACKAGE1 in the Oracle Explorer window. Right-click GETCURSORS and select Run.

 
2.

Enter 999 for the MAXROWS parameter value and click OK.

 
3.

Select the value for EMPLOYEESCUR.

 
4.

The details for the Employees cursor are shown. Select the value for the DEPENDENTSCUR.

 
5.

The Dependents cursor details are shown.

 

Back to Topic

Using the Query Window

The query window allows you to run one or queries against data in your database. Perform the following steps:

1.

Right-click your database connection Hr.ORCL and select Query Window.

 
2.

Enter the SQL command SELECT * FROM DEPENDENTS; and click Execute.

 
3.

Your results are shown. To format the results, right-click in the query area, select Set Query Output > Text and click Execute again.

 
4.

You can execute more than one SQL statement at a time. Add the SQL command SELECT * FROM EMPLOYEES; In order for both statements to be executed at the same time, you need to select both statements, then click Execute. Note: if both statements are not selected, only the last SQL statement in the list will be run.

 
5.

Scroll up to the top of your output. You see the data from both queries.

 

Back to Topic List

Summary

In this lesson, you learned how to:

Create a table and view the data
Automatically generate code
Create and run a stored procedure

Back to Topic List

Move your mouse over this icon to hide all screenshot