How To Build an WinForms.NET Web Site Application With Oracle

1. Bring up Visual Studio 2008
2. Navigate to the File menu and select "New Project"
   
   
   
   
   
   Or click the New Project button directly under the File option.
   
   
   
   
3. You will see the New Projects dialog box appear. Please select the “Windows Forms Applications” template and name your application “MyFirstOracleWinFormsApp”. Accept the defaults. Then press the “OK” button.
   
   
   
   
4. Now you will add a reference to the Oracle .NET Provider DLL. Highlight the project “MyFirstWinformsOracleApp”. Right click it then select “Add Reference…”. Your screen should look like this:
   
   
   
   
5. When the “Add Reference” dialog box appears, select “Oracle.DataAccess”.
   
   
   
   The reason for this is because the ODP.NET is in the Oracle.DataAccess .NET component.



---

Oracle Tips & Tricks to 
SKYROCKET Your Career!

If you're not already a subscriber to Oracle Secrets Revealed, you're  missing out on a myriad of tips and techniques to help you work better, faster and smarter.  Subscribe now and ignite your career.


---

6. Click the “OK” button to add it as a reference to the project. Your screen should look like this. Do you see the Oracle.DataAccess icon under the References tree?
   
   
   
   
7. Go ahead and add a button and label control from the Toolbox to the top half of the Windows Form. Your screen should look like this:
   
   
   
   
8. You will add C# code to invoke the sample HR database in the button click event handler. The easiest way to do this is to double click the “button1” icon you added before. This will bring up the code behind page.
   
   
   
   
9. Scroll to the top of the page and add a C# "using" declaration to the Oracle Data Access Client. using Oracle.DataAccess.Client; Your screen should look like this:
   
   
   
   
10. Now add the following lines of code to the button click event handler like this:
    
    string oracledb =
    "Data Source=(DESCRIPTION=(ADDRESS_LIST="
    + "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
    + "User Id=HR;Password=HR;";
    
    OracleConnection connection = new OracleConnection(oracledb);
    connection.Open();
    
    OracleCommand command = new OracleCommand();
    command.Connection = connection;
    command.CommandText =
    "select department_name from departments where department_id = 10";
    command.CommandType = CommandType.Text;
    
    OracleDataReader dataReader = command.ExecuteReader();
    dataReader.Read();
    
    label1.Text = dataReader["department_name"].ToString();
    
    dataReader.Dispose();
    command.Dispose();
    connection.Dispose();
    
    
11. Now test the application by running it. The result should look like this:
    
    
    
    
12. You have put the plumbing in place to enable database access to Oracle from a Windows application. It is now time to make the application a little more dynamic by adding a text box to allow a user to enter a department number. Click on the “Toolbox” and place a second label control and a text box control to the form. Make sure you name the label “Please enter a Department ID:”. Your Form should look like this.
    
    
     
    
    
13. Instead of hard coding the department ID like you did before, you will get it from the text box control. Please change the select statement so that it will look like this: command.CommandText =
    "select department_name from departments where department_id = "
    + textBox1.Text;


14. Now run the application and test it by entering a value of “10”. The result should look like this.
    
    
    
    
15. Improve your code to prevent an error if an invalid department id is entered. You can do this by rewriting the line that has dataReader.Read() with this:
    
    if (dataReader.Read()) { label1.Text = dataReader["department_name"].ToString(); } else { label1.Text = "department id not found"; }
    
    
Test your application by entering “500”. You should see this:




16. Having been successful at retrieving a single value from the database it is time to enhance the application to retrieve multiple rows and columns. To do that you will add a list box to the Form like this:
    
    
    
    
    
17. Now you have to get rid of the where clause from the query and add more columns like this command.CommandText =
    "select department_id, department_name, location_id from departments”;
    
    
18. The results of the query will be read in a while loop. Please change your code so that it looks like this:
    
    private void button1_Click(object sender, EventArgs e)
    {
    string oracledb = "Data Source= (DESCRIPTION=(ADDRESS_LIST="
    + (ADDRESS=(PROTOCOL=TCP) (HOST=localhost)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
    + "User Id=HR;Password=HR;"; OracleConnection connection = new OracleConnection(oracledb);
    connection.Open();
    OracleCommand command = new OracleCommand();
    command.Connection = connection;
    command.CommandText =
    "select department_id, department_name, location_id ”
    + “ from departments";
    command.CommandType = CommandType.Text; OracleDataReader dataReader = command.ExecuteReader(); while(dataReader.Read())
    {
    listBox1.Items.Add("The " + dataReader["department_name"].ToString()
    + " department is in " + dataReader["location_id"].ToString()); dataReader.Dispose();
    command.Dispose();
    connection.Dispose();
    }
    
    
19. Rebuild your code and test it. The application will retrieve all the departments in the database. The results should look like this
    
    
    
    
20. I have said very little about error handling in order to keep things simple. Now that you have a basic understanding of accessing an Oracle database from a Windows App it is time to introduce basic error handling using the try-catch-finally block. Please modify your code to look like this:
    
    private void button1_Click(object sender, EventArgs e)
    {
    string oracledb = "Data Source=(DESCRIPTION=(ADDRESS_LIST="
    + "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))"
    + "User Id=HR;Password=HR;";
    
    OracleConnection connection = null;
    OracleCommand command = null;
    OracleDataReader dataReader = null;
    
    try
    {
    connection = new OracleConnection(oracledb);
    connection.Open();
    
    command = new OracleCommand();
    command.Connection = connection;
    command.CommandText =
    "select department_id, department_name, location_id “
    + “from departments";
    command.CommandType = CommandType.Text;
    
    dataReader = command.ExecuteReader();
    
    while (dataReader.Read())
    {
        ListBox1.Items.Add("The " + dataReader["department_name"].ToString()
    + " department is in " + dataReader["location_id"].ToString());
    }
    }
    
    catch (OracleException oe) //This catches Oracle specific errors
    {
    if (1 == oe.Number)
    {
        MessageBox.Show("Attempt to insert duplicate data");
    }
    else if (12545 == oe.Number)
    {
        MessageBox.Show("The Oracle database is not available.");
    }
    else if (12514 == oe.Number)
    {
        MessageBox.Show("Unable to connect to the Oracle database");
    }
    else
    {
        MessageBox.Show("Oracle database error: "+ oe.Message.ToString());
    }
    
    } catch (Exception ex) //while this catches everything else
    {
    MessageBox.Show(ex.Message.ToString());
    }
    
    finally
    {
        if (null != dataReader)
        {
            dataReader.Dispose();
        }
        if (null != command)
        {
            command.Dispose();
        }
        if (null != connection)
        {
            connection.Dispose();
        }
    }
    }
    
    

21. Now Stop the Oracle XE database. Test the error handling capability by running your application. You should see this dialog box appear

    
    
    


22. Congratulations! You just created and ran your first WindForms.NET web site application.