Getting Started with Oracle Data Provider for .NET (VB.NET Version)

Purpose

This tutorial describes how you can build a .NET application that uses the Oracle Data Provider for .NET to access data in an Oracle Database.

Time to Complete

Approximately 30 minutes

Introduction

Oracle Data Provider for .NET (ODP.NET) is Oracle's high performance ADO.NET compliant data provider that exposes a complete set of Oracle specific data access features and tuning options including support for Real Application Clusters, XML DB, and advanced security. It is available for free download from the Oracle Technology Network website.

This tutorial will guide you through building a basic .NET appliction that retrieves data from the Oracle database. You will learn how ODP.NET interacts with standard .NET controls. Then, you will learn how to handle errors with ODP.NET. Lastly, you will modify the application to return multiple columns of data.

Prerequisites

Before starting this tutorial, you should:

1. Install Microsoft Visual Studio 2013 or later.

2. Install Oracle Database 12c or later.

3. Install Oracle Data Access Components (ODAC) 12.1.0.1.2 or later from OTN. The ODAC download includes Oracle Developer Tools for Visual Studio and ODP.NET that will be used in this lab.

To create a new project in Visual Studio, perform the following steps:

1. From your Windows Program menu, start Visual Studio. From the File menu, select New > Project....

   

2. From the Project types list, select Visual Basic: Windows. Select the Template Windows Form Application. In the Name field, enter OraWinApp. In the Location field, enter the directory where you want to save the files. (Note: if the directory does not exist, it is created). Click OK

   

3. The project has been created. From View menu, select Solution Explorer to open the Solution Explorer if not already open.

   

Because your project needs access to an Oracle database, it is necessary to add a reference to the DLL containing the data provider. Perform the following steps:

1. From Project menu, select Add Reference...

   

2. Select Extensions menu on the side bar. Scroll down the list of extensions and select Oracle.ManagedDataAccess and click OK.

   

To retrieve data from the database, you can add some controls to your form, then data access logic behind them. Perform the following steps:

1. From View menu, select Toolbox. The toolbox appears.

   

   Expand Common Controls.

   

   Note: Before selecting the Toolbox, you should have the form open.

2. Drag the Button control to the Form1 canvas.

   

   The control is shown on the canvas. You also need to create a Label.

3. From the Toolbox, drag the Label control to the Form1 canvas.

   

4. Double-click Button1 to open the code window.

   

   Note: Form1.vb code window opens.

   

5. Add the following VB.NET Imports statements before the Public Class declaration.

   Imports System.Data
   Imports Oracle.ManagedDataAccess.Client ' ODP.NET, Managed Driver
   Imports Oracle.ManagedDataAccess.Types

   

6. Add the following VB.NET code between the Private Sub and End Sub statements. This code will connect to the HR schema, then query the DEPARTMENTS table for the department name with the ID of 10.

   
   Dim oradb As String = "Data Source=orcl;User Id=hr;Password=hr;"
   Dim conn As New OracleConnection(oradb) 
   conn.Open()
   Dim cmd As New OracleCommand
   cmd.Connection = conn
   cmd.CommandText = "select department_name from departments where department_id = 10"
   cmd.CommandType = CommandType.Text
   Dim dr As OracleDataReader = cmd.ExecuteReader()
   dr.Read()
   Label1.Text = dr.Item("department_name")
   conn.Dispose()
   

   

7. Select Build > Build OraWinApp.

   

   In the output window, ensure you did not get any errors. Now you can run the form.

8. Select Debug > Start Without Debugging.

   

9. The form appears. Click button1.

   

   The result is shown in the label. Since the query in the code was to show the department name for department ID of 10, the result is the Administration department. Close Form1.

   

Instead of running the hard coded query, you can add a text box control to accept user input for the department ID. Perform the following steps:

1. Select the Form1.vb [Design] tab. From the Toolbox, drag the TextBox control to Form1 canvas.

   

   The text box is shown. You can drag it to the desired location.

   

2. Drag another Label control onto Form1 canvas above the text box.

   

3. The Label is shown. Right-click Label2 and select Properties.

   

4. In the Properties tab, change the Text property to Enter Department ID:

   

5. Select the Form1.vb tab.

   

6. You use a bind variable to include the value of the text box as part of the SELECT statement. Bind variables improve performance by preventing the database from having to reparse the SQL statement every time the text box value changes.

   Add the following code right after the conn.Open() statement:

   Dim parm As New OracleParameter
   parm.OracleDbType = OracleDbType.Decimal
   parm.Value = TextBox1.Text

   

7. Add the below mentioned statement right after the cmd.Connection = conn statement:

   cmd.Parameters.Add(parm)

   

8. Change the code for the cmd.CommandText statement to the following:

   cmd.CommandText = "select department_name from departments where department_id = :1"

   

9. Select Build > Rebuild OraWinApp.

   

   Check in the output window for any errors reported. If no errors or warnings, proceed with the next step.

10. Select Debug > Start Without Debugging.

    

11. Enter 20 for the Department ID and click button1.

    

12. Notice that the label1 changes to the name of the department. This value depends on what is entered in the Department ID field.

    

13. Enter 300 for the Department ID and click button1.

    

14. This time you receive an error because there is no department with the ID of 300. Click Quit. In the next topic you add some error handling so you know exactly why you received the error.

    

Error handling is needed to instruct the user why something doesn't work properly. In this topic, you modify your code to prevent an error when an invalid Department ID is provided. In this tutorial, you add some very simple error handling code. For more robust error handling, Structured Exception Handling should be used. For more information on Structured Exception Handling with ODP.NET please refer to the ODP.NET online documentation. Perform the following steps:

1. In Form1.vb window, change the following code from:

   dr.Read()
   label1.Text = dr.Item("department_name")

   to

   If dr.Read() Then
     Label1.Text = dr.Item("department_name")
    Else
     Label1.Text = "Department ID not found"
    End If
   

   

2. Select Build > Rebuild OraWinApp. Ensure that there are no errors reported in the output window.

   

3. Select Debug > Start Without Debugging.

   

4. Enter 300 again for the Department ID and click button1.

   

5. This time, the message Department ID not found is displayed. Close Form1.

   

In this topic, you will retrieve more than one column of information using a listbox. The new query will be a join of the DEPARTMENTS and LOCATIONS tables. The query will return all the department IDs, the department names, and their locations. The department names and their respective location will then be displayed. Perform the following steps:

1. Click the Form1.vb [Design] tab. From the Toolbox, drag the Listbox control to the Form 1 canvas.

   

2. The new ListBox appears on the form. You can position and re-size the ListBox appropriately. Click Form1.vb tab.

   

3. Delete the cmd.Parameters.Add(parm) statement.

   

4. Change the cmd.CommandText code from:

   cmd.CommandText = "select department_name from departments where department_id = :1"

   to

   cmd.CommandText = "select department_id, department_name, city" +
   " from departments d, locations l" +
   " where d.location_id = l.location_id"

   

5. Also change the following lines of code from:

   If dr.Read() Then
     Label1.Text = dr.Item("department_name")
    Else
     Label1.Text = "Department ID not found"
   End If

   to

   While dr.Read()
    ListBox1.Items.Add("The " + dr.Item(1) +
    " department is in " + dr.Item("city"))
   End While

   

6. Select Build > Rebuild OraWinApp.

   

7. Select Debug > Start Without Debugging.

   

8. Click button1.

   

9. The list of all the departments and their location is shown. Close Form1.

   

In this tutorial, you have learned how to:

• Create a VB.NET ODP.NET project and add a reference
• Retrieve data from the database
• Incorporate interactive controls and error handling
• Retrieve multiple columns and rows with a join

Resources

• Visit the Oracle Technology Network Portal to learn the latest developments in Oracle .NET.
• To learn more about Oracle .NET with Visual Studio refer to additional OBEs in the Oracle Learning Library.

Credits

• Lead Curriculum Developer: Ashwin Agarwal
• Other Contributors: Alex Keh, Christian Shay