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


Options



Before You Begin

Purpose

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

Time to Complete

Approximately 30 minutes

Background

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 web site.

This tutorial will guide you through building a basic Visual Basic .NET application 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.

What Do You Need?

Before starting this tutorial, you should:
  1. Install Microsoft Visual Studio 2015 or later.
  2. Install Oracle Database 12c or later.
  3. Install Oracle Data Access Components (ODAC) 12c Release 4 or later from OTN. The ODAC download includes Oracle Developer Tools for Visual Studio and ODP.NET that will be used in this lab.

Creating a Project in Visual Studio

In this section, you will create a new Visual Studio Visual Basic (VB) application.

  1. Open Visual Studio. Click File. Select New > Project.

    Getting Started with ODP VB Version
    Description of this image
  2. Select Visual Basic > Windows > Windows Forms Application. Rename the project as VBWinApp. Click OK.

    Getting Started with ODP VB Version
    Description of this image
  3. Observe the screen. The project VBWinApp opens up and Form1.vb displays on screen.

    Getting Started with ODP VB Version
    Description of this image
  4. Select View > Solution Explorer.

    Getting Started with ODP VB Version
    Description of this image

Adding a Reference

In this section, you will add a reference to the assembly for Oracle Data Provider for .NET.

  1. From the Project menu, select Add Reference. A window opens up.

    Getting Started with ODP VB Version
    Description of this image
  2. Select Extensions menu under Assemblies on the side bar. Scroll down the list of extensions and select Oracle.ManagedDataAccess and click OK. This is the ODP.NET, Managed Driver assembly.

    Getting Started with ODP VB Version
    Description of this image

Retrieving Data from the Database

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

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

    Getting Started with ODP VB Version
    Description of this image
  2. Drag the Button control under the Common Controls in the Toolbox, to the Form1 canvas.

    Getting Started with ODP VB Version
    Description of this image
    Note: Before selecting the Toolbox, you should have the form open.
  3. From the Toolbox, drag the Label control to the Form1 canvas.

    Getting Started with ODP VB Version
    Description of this image
  4. Double-click Button1 to open the code window.

    Getting Started with ODP VB Version
    Description of this image
  5. Observe the screen. Form1.vb code window opens.

    Getting Started with ODP VB Version
    Description of this image
  6. Add the following VB.NET Imports statements to the Form1.vb code.

    Imports System.Data
    Imports Oracle.ManagedDataAccess.Client ' ODP.NET, Managed Driver
    Imports Oracle.ManagedDataAccess.Types
    Getting Started with ODP VB Version
    Description of this image
  7. Add the following VB.NET code in the Button1_Click sub procedure. This code will connect to the HR schema, then query the DEPARTMENTS table for the DEPARTMENT_NAME with the DEPARTMENT_ID of 10.

    In this tutorial, the Data Source is the Oracle database alias, ORCL. This alias may be different from your local database server.

    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()
    Getting Started with ODP VB Version
    Description of this image
  8. Select Build > Build OraWinApp.

    Getting Started with ODP VB Version
    Description of this image
    Note: In the output window, ensure you did not get any errors. Now you can run the form.
  9. Select Debug > Start Without Debugging.

    Getting Started with ODP VB Version
    Description of this image
  10. The Form1 form appears. Click Button1.

    Getting Started with ODP VB Version
    Description of this image
  11. Observe the output. Close Form1.

    Getting Started with ODP VB Version
    Description of this image
    Note: Since the query in the code was to show the department name for department ID of 10, the result is the Administration department.

Incorporating Interactive Controls

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

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

    Getting Started with ODP VB Version
    Description of this image
  2. Drag another Label control onto Form1 canvas, above the text box.

    Getting Started with ODP VB Version
    Description of this image
  3. Right-click Label2 and select Properties.

    Getting Started with ODP VB Version
    Description of this image
  4. In the Properties tab, change the Text property to Enter Department ID.

    Getting Started with ODP VB Version
    Description of this image
  5. Select the Form1.vb tab.

    Getting Started with ODP VB Version
    Description of this image
  6. Add the following code right after the conn.Open() statement in the Form1.vb code.

    Dim parm As New OracleParameter
    parm.OracleDbType = OracleDbType.Decimal
    parm.Value = TextBox1.Text
    Getting Started with ODP VB Version
    Description of this image
  7. Add the following code right after the cmd.Connection = conn statement in the Form1.vb code.

    cmd.Parameters.Add(parm)
    Getting Started with ODP VB Version
    Description of this image
  8. Modify the SQL for the cmd.CommandText statement to the following SQL statement.

    cmd.CommandText = "select department_name from departments where department_id = :1"
    Getting Started with ODP VB Version
    Description of this image
    Note: You can 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 reparsing the SQL statement every time the text box value changes.
  9. Select Build > Build VBWinApp.

    Getting Started with ODP VB Version
    Description of this image
    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.

    Getting Started with ODP VB Version
    Description of this image
  11. Enter 20 for the Department Id and click Button1.

    Getting Started with ODP VB Version
    Description of this image
  12. Observe the output. The Label1 changes to Marketing, which is the name of the Department with department ID 20.

    Getting Started with ODP VB Version
    Description of this image
    Note: The value of label1 varies based on the data entered in the Department ID field.
  13. Enter 300 for the Department Id and click Button1.

    Getting Started with ODP VB Version
    Description of this image
  14. You receive an error because there is no department with an ID of 300. Click Quit. You can add error handling to mitigate this error output.

    Getting Started with ODP VB Version
    Description of this image

Adding Error Handling

Error handling is needed to instruct the user why something does not work properly. In this topic, you modify the code to prevent an error when an invalid Department ID is provided by adding 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, refer to the ODP.NET online documentation.

  1. In Form1.vb code, 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


    Getting Started with ODP VB Version
    Description of this image
  2. Select Build > Build VBWinApp.

    Getting Started with ODP VB Version
    Description of this image
    Note: Ensure that there are no errors reported in the output window.
  3. Select Debug > Start Without Debugging.

    Getting Started with ODP VB Version
    Description of this image
  4. Enter 300 for the Department ID and click button1.

    Getting Started with ODP VB Version
    Description of this image
  5. Observe the output. The message Department ID not found is displayed. Close Form1.

    Getting Started with ODP VB Version
    Description of this image

Retrieving Multiple Columns and Rows

In this section, you will retrieve more than one column of information, displaying the data in 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 locations will then be displayed.

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

    Getting Started with ODP VB Version
    Description of this image
  2. The new listbox appears on the form. You can position and re-size the listbox appropriately. Click Form1.vb tab.

    Getting Started with ODP VB Version
    Description of this image
  3. Comment out the cmd.Parameters.Add(parm) statement in the Form1.vb code.

    Getting Started with ODP VB Version
    Description of this image
  4. Modify the SQL for the cmd.CommandText statement to the following SQL.

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


    Getting Started with ODP VB Version
    Description of this image
  5. In Form1.vb code, change the following 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


    Getting Started with ODP VB Version
    Description of this image
  6. Select Build > Build VBWinApp.

    Getting Started with ODP VB Version
    Description of this image
  7. Select Debug > Start Without Debugging.

    Getting Started with ODP VB Version
    Description of this image
  8. Click Button1.

    Getting Started with ODP VB Version
    Description of this image
  9. Observe the output. The list of all the departments and their locations is shown. Close Form1.

    Getting Started with ODP VB Version
    Description of this image

Want to Learn More?