Verify you can connect to database which is 8.1.7.4.1 or later

Install Oracle Developer Tools ( http://otn.oracle.com/dotnet ) into a new Oracle Database 10g Oracle Home. You should not install into a Oracle8 or Oracle9 Oracle Home. By default it will choose to create a new Oracle home. If you have previously only used Oracle8 or Oracle9 Oracle homes, you should copy your tnsnames.ora file (..in Oracle_home/network/admin) over to the new Oracle home so that your connection aliases are still available to you.

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

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 and click Test Connection.

Test connection was successful. Click OK.

Click OK.

Your connection has been created. Expand hr.<database>. In the next topic, you will create a new table.

Right-click on 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, and 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 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 and enter 4 for the Precision and 0 for the Scale. Then click Save. 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. Your index has been created.

Click the 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 Save.

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

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

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

From the Start tab, click New Project...

Select the Project Type Visual C# Projects, select the Template Windows Application, enter obe1 in the name and specify a valid location. Then click OK.

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

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 on dependentsOracleDataAdapter1 to view the code.

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

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 the Form1.cs (Design) tab.

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.

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

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

Select DataGrid1 from the list of Members.

Enter the following code after the InitializeComponent(); Then select Build > Build Solution.

DataSet ds= new DataSet();
dependentsOracleDataAdapter1.Fill(ds);
dataGrid1.DataSource = ds.Tables[0];

Note: If you create a Visual Basic Project, the code for the Public Sub New() method, underneath InitializeComponent() will be as follows:

Dim ds As DataSet = New DataSet
dependentsOracleDataAdapter1.Fill(ds)
DataGrid1.DataSource = ds.Tables(0) 

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

Your application runs and a form appears and your data has been populated.

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

Enter MYPACK for the 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.

From the Oracle Explorer window, right click on the MYPACK Package you just created and select Edit Package Body.

Replace the line NULL; with the following, then right click on the window tab and select Save.

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

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

Enter 999 for the MAXROWS parameter value and click OK.

Select the value for EMPLOYEESCUR.

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

The Dependents cursor details are shown.