Getting Started with Oracle Data Provider for .NET (C# 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

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 website.

This tutorial will guide you through building a 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.

This tutorial uses ODP.NET, Managed Driver. It is a 100% managed code driver that does not require the traditional unmanaged Oracle Database Client.

What Do You Need?

Before starting this tutorial, you should:

1. Install Microsoft Visual Studio 2015 or later.
2. Have access to 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.

In this section, you will create a new Visual Studio C# application.

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

   

2. Select Visual C# > Windows > Windows Forms Application. Rename the project as OraWinApp. Click OK.

   

3. Observe the screen. The project OraWinApp opens up and Form1.cs displays on screen.

   

4. Select View > Solution Explorer.

   

In this section, you will add reference to the DLL containing the data provider for Oracle Database.

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

   

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.

   

To retrieve data from the database, you can 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.

   

2. Drag the Button control under the Common Controls, to the Form1 canvas.

   

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

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

   

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

   

5. Observe the screen. Form1.cs code window opens.

   

6. Add the following C# using statements to the Form1.cs code.

   using Oracle.ManagedDataAccess.Client; // ODP.NET, Managed Driver
   using Oracle.ManagedDataAccess.Types;

   
   
   

   

7. Add the following C# code in the button1_Click method. This code will connect to the HR schema, then query the DEPARTMENTS table for the department name with the 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.

   string oradb = "Data Source=ORCL;User Id=hr;Password=hr;";
   OracleConnection conn = new OracleConnection(oradb); // C#
   conn.Open();
   OracleCommand cmd = new OracleCommand();
   cmd.Connection = conn;
   cmd.CommandText = "select department_name from departments where department_id = 10"; 
   cmd.CommandType = CommandType.Text;
   OracleDataReader dr = cmd.ExecuteReader();
   dr.Read();
   label1.Text = dr.GetString(0);
   conn.Dispose();

   
   
   

   

8. Select Build > Build OraWinApp.

   

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

9. Select Debug > Start Without Debugging.

   

10. The Form1 form appears. Click button1.

    

11. Observe the output. Close Form1.

    

    Note: Since the query in the code was to show the department name for department ID of 10, the result is the Administration department.

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.cs [Design] tab. From the Toolbox, drag the TextBox control to Form1 canvas.

   

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

   

3. Right-click Label2 and select Properties.

   

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

   

5. Select the Form1.cs tab.

   

6. Add the following code right after the conn.Open(); statement in the Form1.cs code.

   OracleParameter parm = new OracleParameter();
   parm.OracleDbType = OracleDbType.Decimal;
   parm.Value = textBox1.Text;

   
   
   

   

7. Add the following code right after the cmd.Connection = conn; statement in the Form1.cs code.

   cmd.Parameters.Add(parm);

   
   
   

   

8. Modify the code for the cmd.CommandText statement to the following code.

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

   
   
   

   

   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 saving the database from reparsing the same SQL statement every time the text box value changes.

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. Observe the output. The label1 changes to Marketing, which is the name of the Department with department ID 20.

    

    Note: The value of label1 varies based on the data entered in Department ID field.

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

    

14. You receive an error because there is no department with the ID as 300. Click Quit. You can add error handling to mitigate this error output.

    

Error handling is needed here to instruct the user an invalid ID was entered. In this topic, you modify the code to prevent an error when an invalid Department ID is provided. 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, refer to the ODP.NET online documentation.

1. In Form1.cs code, change the following code from:

   dr.Read();
   label1.Text = dr.GetString(0);

   
   to
   

   if (dr.Read())
   {
       label1.Text = dr.GetString(0);
   }
   else
   {
       label1.Text = "Department ID not found";
   };

   
   
   

   

2. Select Build > Rebuild OraWinApp.

   

   Note: Ensure that there are no errors reported in the output window.

3. Select Debug > Start Without Debugging.

   

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

   

5. Observe the output. The message Department ID not found is displayed. Close Form1.

   

In this section, 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.

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

   

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

   

3. Comment the cmd.Parameters.Add(parm); statement in the Form1.cs code.

   

4. Modify the code for the cmd.CommandText statement to the following code.

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

   
   
   

   

5. In Form1.cs code, change the following code from:

   if (dr.Read())
   {
       label1.Text = dr.GetString(0);
   }
   else
   {
       label1.Text = "Department ID not found";
   };

   
   to
   

   while (dr.Read())
   {
   listBox1.Items.Add("The " + dr.GetString(1) +
   " department is in " + dr.GetString(2));
   }

   
   
   

   

6. Select Build > Rebuild OraWinApp.

   

7. Select Debug > Start Without Debugging.

   

8. Click button1.

   

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

   

• OracleException Class in Data Provider for .NET Developer's Guide
• More .NET and Visual Studio 2015 tutorials
• Oracle Learning Library
• Oracle Technology Network