Select Start > Programs > Microsoft Visual Studio 2005> Microsoft Visual Studio 2005.

Select View > Oracle Explorer.

Click the + icon to add a connection.

Enter your Data source name. Enter hr for the User name and Password, click Save password, select Default as Role, and click Test connection.

Test connection was successful. Click OK.

Click OK.

Click the + in front of Data Connections in the Oracle Explorer view. The hr.ORCL connection has been created.

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

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

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

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

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

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

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.

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.

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

Click Save. Note: the Preview SQL is no longer available since the statement has executed.

The Output window is displayed. The DEPENDENTS table has been created. Close the Output window.

Click the + in front of DEPENDENTS table in the Oracle Explorer view. You can view the columns that have been created in this topic. Now you are ready to create the index.

Click the Indexes tab.

Click Add.

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

Select DEPENDENTID for the Key and then click Save.

The Output window is displayed. The DEPENDENTS_INDX index has been created. Close the Output window.

Click Constraints tab.

Click Add.

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.

Enter DEPENDENTS_PK for the Name, select Primary Key for the type, and click Add in the Primary key columns area.

Select DEPENDENTID for the primary key column. Then click Save. Close the Output window.

Click the + in front of Indexes and Constraints in the Oracle Explorer view. You can see the table, its indexes and constraints.

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

Enter at least 4 lines of data as shown in the table below. Make sure you enter a valid Employee ID. Then click Save.

Click Start Page tab.

Click Project... adjacent to Create: in the Recent Projects area.

Select the Project Type Visual C#, 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.

Select the DEPENDENTS table in the Oracle Explorer view and drag and drop it into Form1.

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

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.

The form will appear with the code that was generated.

Select InitializeComponent () from the drop-down menu.

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.

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

Right-click dependentsOracleDataAdapter1 and select Generate Dataset...

Dependents11 is displayed.

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

Select DataGridView from the list of Windows Forms and drag and drop it on your Form1. You may see a pop up window asking you to set the data source property. If you do, you can skip ahead to step 17, otherwise follow the instructions below to open this window.

Close the Toolbox view.

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.

Scroll down to the Data>DataSource section in the Properties window and click (none).

Pull down the DataSource drop-down menu and click the + in front of Other Data Sources.

Click the + in front of Project Data Sources.

Click the + in front of Dependents1.

Click Dependents and click the Save icon.

Expand Form 1 and DatagridView as shown in the screenshot. Double-click Datagridview to view the form with code that was generated.

Select DataGridView1 from the list of Members.

Select Form1() from the drop-down menu.

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);

Select Build > Build Solution.

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

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

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

Accept the default Package name and click Add under Methods.

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

Enter MAXROWS for the Name and click Add.

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

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

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

Review the SQL and click OK.

Click OK to create the Package. Close the Output window.

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;

Replace the line NULL; with the following:

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

Right-click the window tab and select Save. Close the Output window.

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

Enter 999 for the MAXROWS parameter value and click OK.

Select the value for EMPLOYEESCUR.

The details for the EMPLOYEESCUR are shown. Select the value for the DEPENDENTSCUR.

The DEPENDENTSCUR details are shown. Close the Output window.

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

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

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

The results are shown in text format.

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.

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