Skip Headers
Oracle® Database 2 Day + .NET Developer's Guide
11g Release 2 (11.2)

Part Number E10767-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Building a Simple .NET Application Using ODP.NET

This chapter contains:

Creating a New Project

Visual Studio groups all development code that you create into containers known as projects. Simpler projects often contain only one file. In this section, you will learn how to create a new development project.

The application you build in this chapter serves as a starting point for work in subsequent chapters, so it is important to follow the order of this guide.

NOTE: When necessary, instructions specify Visual C# or Visual Basic.

To start a new project:

  1. Start Visual Studio.

    Open the Start menu, select All Programs, and then select Microsoft Visual Studio 2008.

    Description of connect00.gif follows
    Description of the illustration connect00.gif

    The Microsoft Visual Studio IDE environment appears.

  2. In the Start Page, under the Recent Projects heading, click Create: Project.

    Alternatively, from the File menu, select New, and then select Project.

    A New Project dialog box appears.

  3. In Project Types, select the type of project you are creating:

    Visual C#:

    Visual C#: Windows

    Visual Basic:

    Other Languages: Visual Basic: Windows

  4. In Templates, select Windows Forms Application.

  5. In the Name field, enter the appropriate name.

    Visual C#:

    HR_Connect_CS

    Visual Basic:

    HR_Connect_VB

    The abbreviation CS indicates C# projects and VB indicates Visual Basic projects.

  6. In Location, enter the directory where you want to save the files.

    For this guide, enter this directory C:\HR_Projects.

  7. In Solution Name, the appropriate name, HR_Connect_CS or HR_Connect_VB should appear.

    A solution can contain several projects; when it contains only one project, you can use the same name for both.

  8. Check Create directory for solution.

  9. Click OK.

  10. Description of connect01.gif follows
    Description of the illustration connect01.gif

    The project is created.

    The main window now displays a new title, either HR_Connect_CS - Microsoft Visual Studio or HR_Connect_CS - Microsoft Visual Studio, depending on the language, and contains Form1 shown below.

    It is important to remember that many projects automatically name the first form Form1. This is the name of the form control. Do not confuse this with the actual name given to the code file, which is typically Form1.cs or Form1.vb.

    Both Form1 and Form1.xx can be renamed. For the purposes of this guide, we will rename Form1.xx several times.

    Description of connect2a.gif follows
    Description of the illustration connect2a.gif

Adding a Reference

This section shows you how to add a reference to the Oracle.DataAccess.dll file, which contains the data provider, Oracle Data Provider for .NET.

To add a reference:

  1. From the Project menu, select Add Reference.

    Description of reference01.gif follows
    Description of the illustration reference01.gif

    The Add Reference windows appears.

  2. In the Add Reference window, under the .NET tab, select Oracle.DataAccess. Click OK.

    Description of reference02.gif follows
    Description of the illustration reference02.gif

    Note that the new reference appears in the Solution Explorer.

    Description of reference03.gif follows
    Description of the illustration reference03.gif

Adding Namespace Directives

You can add Oracle namespace directives that allow you to indicate an assembly's namespaces within the module. To do this, add C# using statements or Visual Basic Imports statements, at or near the top of a code file.

Note:

Adding a reference makes the namespace available within the application. Adding a namespace directive within the application code makes the namespace more visible and allows for additional scoping.

To add Oracle namespace directives:

  1. With Form1 active, from the View menu select Code.

    Alternatively, you can use the F7 keyboard shortcut.

    Description of connect04.gif follows
    Description of the illustration connect04.gif

  2. Add the following statements to the list of declarations depending on the language you are using.

  3. Save the changes by selecting Save from the File menu, or using the Ctrl+S keyboard shortcut.

Designing the User Interface

You can create a user interface by adding the toolbox controls to the design form. This interface accepts connection information from the user.

To add toolbox controls:

  1. From the View menu, select Designer.

    This opens Form1, in design view, if it is not already open.

    You will toggle between Code and Designer a lot. The keyboard shortcuts are F7 and shift- F7 respectively.

  2. From the View menu, select Toolbox.

  3. In the Toolbox, expand Common Controls.

    Description of control01.gif follows
    Description of the illustration control01.gif

  4. In the Toolbox, select Label, and drag it onto the Form1.

    Description of control02.gif follows
    Description of the illustration control02.gif

  5. On Form1, right-click label1.

    Description of control03.gif follows
    Description of the illustration control03.gif

  6. From the menu, select Properties, if the Properties Window is not already visible.

    The Properties Window appears.

  7. In the Properties Window, change the Text property from label1 to User ID.

    Description of control04.gif follows
    Description of the illustration control04.gif

  8. Repeat steps 4 through 7 twice, placing two more labels on Form 1 and changing their text properties to Password and Data Source.

    Description of control05.gif follows
    Description of the illustration control05.gif

  9. In the Toolbox, select TextBox, and drag it onto the Form1, next to the User ID label.

    Description of control06.gif follows
    Description of the illustration control06.gif

  10. In the Properties Window, change the Name property to userID.

    Description of control07.gif follows
    Description of the illustration control07.gif

  11. Repeat steps 9 and 10 twice, positioning two more text boxes next to the existing labels, and setting the Name property to password and dataSource.

    Description of control08.gif follows
    Description of the illustration control08.gif

  12. Select the text box next to the Password label. In the Properties Window, scroll to the PasswordChar property and set it to an asterisk (*).

    This masks the password during entry.

    Description of control09.gif follows
    Description of the illustration control09.gif

  13. From the Toolbox, select Button and drag it onto Form1.

    In the Properties Window, change the Text property of the button from button1 to Connect, and change the Name property to connect.

    Description of control10.gif follows
    Description of the illustration control10.gif

  14. Save.

  15. Close the Toolbox.

Writing the Connection Code

Now we write the code that takes the information provided to the user interface and connects to the database.

To connect to the database, you must create a connection object.

To write code that connects to the database:

These steps enable your application to connect to the database based on data that the user enters into the Form1 control. See "Compiling and Running the Application".

  1. From the View menu, select Code.

  2. Add the code indicated to instantiate a database connection string.

    Visual C#: Add the class variable conn to the Form1 class right after the public Form1() block with this code.

    private OracleConnection conn = new OracleConnection();
    
    Description of connect1.gif follows
    Description of the illustration connect1.gif

    Visual Basic: Add the conn class variable in the Form1 class declaration, using this code.

    Public Class Form1
       Dim conn As New OracleConnection
    
    Description of connect2.gif follows
    Description of the illustration connect2.gif

  3. Save your changes.

  4. Change to Designer view by clicking on the View menu and selecting Designer.

  5. Double-click the Connect button on Form1 to open the code window to the connect_Click() method.

    Insert the code indicated into the connect_Click() method.

    Visual C#:

    conn.ConnectionString = "User Id=" + userID.Text +
       ";Password=" + password.Text + 
       ";Data Source=" + dataSource.Text + ";";
    conn.Open();
               
    

    Visual Basic:

    conn.ConnectionString = "User Id=" + userID.Text & _
      ";Password=" + password.Text & _
      ";Data Source=" + dataSource.Text
    conn.Open()
    

    Note: Before a connection can be opened, it must be built from user input for the User ID, Password, and Data Source. The Open() method makes the actual connection.

  6. Set the Enabled attribute of the button to false by inserting the indicated code at the end of the connect_Click() method.

    This disables the Connect button, which is a good practice once a connection is successfully made.

    Visual C#:

    connect.Enabled = false;
    
    Description of connect_obj1.gif follows
    Description of the illustration connect_obj1.gif

    Visual Basic:

    connect.Enabled = false
    
    Description of connect_obj2.gif follows
    Description of the illustration connect_obj2.gif

You have now finished writing an application that can connect to the Oracle database. The following sections show how to use it.

Compiling and Running the Application

This section shows how to compile and run the application you created in the previous sections.

To compile and run the application:

  1. From the Build menu, select Build Solution.

    Description of build1.gif follows
    Description of the illustration build1.gif

  2. Ensure that there are no errors reported in the output window, available from the View menu.

    The following graphics shows a typical output result.

    Description of build2.gif follows
    Description of the illustration build2.gif

  3. If there are any errors indicated, from the View menu, select Error List and fix the errors.

  4. From the Debug menu, select Start Without Debugging to run the application.

    Description of build3.gif follows
    Description of the illustration build3.gif

  5. In the Form1 application, enter the User ID, Password, and Data Source.

    Click Connect.

    This is where the application makes use of the tnsnames.ora file. See "Configuring a NET Connect Alias".

    Once the connection is opened, the Connect button is disabled. You have succeeded in implementing a connection to an Oracle Database instance.

    Description of build4.gif follows
    Description of the illustration build4.gif

Error Handling

Applications must be able to handle run-time errors gracefully. For example, if you try to log in using an incorrect password, the application you developed so far cannot establish a connection to the database, and exits with the following unhandled exception error, ORA-1017: invalid username/password, logon denied.

Description of error1.gif follows
Description of the illustration error1.gif

You must reselect Start Without Debugging to try this with a different password.

Error handling manages occurrences of conditions that change the normal flow of program execution. Oracle Data Provider for .NET contains three classes for error handling and support:

Using Try-Catch-Finally Block Structure

.NET languages use Try-Catch-Finally block structure for error handling. With this structure, the Try code is the main code, the goal that the application wants to accomplish. The Catch code catches errors of various types, as shown in the next two section. The Finally block comes last and always executes.

The Finally block frequently contains the Dispose method, which closes and disposes of the connection. Having the Dispose method in the Finally block ensures that the database connection is always closed after the Try-Catch-Finally block completes. Closing database connections after the application no longer requires database access is important for many reasons, especially data security.

Attempting to close a closed database connection does not cause an error. The attempt is irrelevant. Nonetheless, placing Dispose() in the Finally code block guarantees that the connection is closed.

The next section shows how to use Try-Catch-Finally block structure with general errors, and the section after that, with Oracle errors.

Handling General Errors

This section shows how to handle general errors using a Try-Catch-Finally block.

To handle general errors:

  1. Change the code of the connect_Click() method in Form1 by adding an implementation of the Try-Catch-Finally syntax.

    New code is in bold font.

    Visual C#:

    private void connect_Click(object sender, EventArgs e)
    {
      conn.ConnectionString = "Data Source=ORCL;User Id="
         + userID.Text + ";Password=" + password.Text + ";";
      try
      {
        conn.Open();
        connect.Enabled = false;
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message.ToString());
      }
      finally
      {
        conn.Dispose();
      }
    }
    

    Alternatively, you can use C# syntax that disposes of a connection when it goes out of scope, with the using keyword, as follows:

    using (OracleConnection conn = new OracleConnection())
    {
      conn.Open();
      // application code
      ...
    }
    

    Visual Basic:

    Try
      conn.Open()
      connect.Enabled = false
    
    Catch ex As Exception
      MessageBox.Show(ex.Message.ToString())
    
    Finally
      conn.Dispose()
    End Try
    
  2. From the Build menu, select Rebuild Solution.

    Ensure that there are no errors.

  3. From the Debug menu, select Start Without Debugging.

  4. Run the application again, as described in section "Compiling and Running the Application", and attempt to connect using an incorrect password. This time, the application catches the error and displays it in a pop-up window, ORA-1017: invalid username/password; logon denied.

    Description of error2.gif follows
    Description of the illustration error2.gif

Handling Common Oracle Errors

In the completed Try-Catch-Finally block code shown below, the first Catch statement branch is skipped if there are no OracleExceptions. The second Catch statement branch catches all other Exceptions.

The first catch statement contains Case statements, which can be used to trap common database errors and display them in a user-friendly manner.

Note that the second Case statement catches a specific example of OracleException, when the database is not accessible.

To handle specific errors:

  1. Stop the database instance. See Appendix A, "Starting and Stopping an Oracle Database Instance".

  2. Add the Catch OracleException block shown below in bold, before the Catch Exception block previously added in the connect_Click() method.

    Visual C#:

    try
      {
        conn.Open();
        connect.Enabled = false;
      }
    catch (OracleException ex)
    {
      switch (ex.Number)
      {
        case 1:
          MessageBox.Show("Error attempting to insert duplicate data.");
          break;
        case 12560:
          MessageBox.Show("The database is unavailable.");
          break;
        default:
          MessageBox.Show("Database error: " + ex.Message.ToString());
          break;
      }
    }
     catch (Exception ex)
       {
         MessageBox.Show(ex.Message.ToString());
       }
    finally
      {
        conn.Dispose();
      }
    }
    

    Visual Basic:

    Try
      conn.Open()
      connect.Enabled = false
    
    Catch ex As OracleException ' catches only Oracle errors
      Select Case ex.Number
        Case 1
          MessageBox.Show("Error attempting to insert duplicate data.")
        Case 12560
          MessageBox.Show("The database is unavailable.")
        Case Else
          MessageBox.Show("Database error: " + ex.Message.ToString())
      End Select
    
    
    Catch ex As Exception
      MessageBox.Show(ex.Message.ToString())
    
    Finally
      conn.Dispose()
    End Try
    
  3. Compile and run the application again, as described in section "Compiling and Running the Application".

    Note that the ORA-12560 error appears in the pop-up window as The database is unavailable with no error number provided.

    Description of error04.gif follows
    Description of the illustration error04.gif

  4. Restart the database instance. See Appendix A, "Starting and Stopping an Oracle Database Instance".