Install Microsoft Visual Studio 2010

Install Oracle Database 9.2 or later or Oracle Database XE

Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.2.0.1.2 or later from OTN

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

From the Project types list, select Visual C#: 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.

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

From Project menu, select Add Reference...

Scroll down the list of Component Names and select Oracle.DataAccess. Click OK.

From View menu, select Toolbox. The toolbox appears. Expand Common Controls.

Drag the Button control to the Form1 canvas.

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

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

Double-click button1 to open the code window. Note, Form1.cs code window opens.

Add the following C# using statements before the Public Class declaration.

using Oracle.DataAccess.Client; // ODP.NET Oracle managed provider

using Oracle.DataAccess.Types;

Add the following C# code in between the private void button1_Click(object sender, EventArgs e)
{ and } statements.

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();

Select Build > Build OraWinApp.

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

Select Debug > Start Without Debugging.

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.

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

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

Drag a Label control on Form1 canvas.

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

In the Properties sheet, change the Text property to Enter Department ID:

Select the Form1.cs tab.

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:

OracleParameter parm = new OracleParameter();

parm.OracleDbType = OracleDbType.Decimal;

parm.Value = textBox1.Text;

Add the cmd.Parameters statement right after the cmd.Connection statement:

cmd.Parameters.Add(parm);

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

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

Select Build > Rebuild OraWinApp.

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

Select Debug > Start Without Debugging.

Enter 20 for the Department ID and click button1.

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

Enter 300 for the Department ID and click button1.

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.

In Form1.cs window, 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";
};

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

Select Debug > Start Without Debugging.

Enter 300 again for the Department ID and click button1.

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

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

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

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

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";

Also change the following lines of 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));
}

Select Build > Rebuild OraWinApp.

Select Debug > Start Without Debugging.

Click button1.

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