If you're not
already a subscriber
to our newsletter you're missing out on a myriad
of tips and tricks you need to become a better, faster, smarter developer. Subscribe now and ignite your
career.
Click
the “OK”
button to add it as a reference to the project and the
Oracle.DataAccess icon should be displayed under the References
tree
Next add a button and label control
from
the Toolbox to the top half of the Windows Form
Double click on the “button1” icon on the form
so you can add code to invoke the sample HR
database in the button click event handler.
Add the
following lines of code to the
event handler (entitled
button1_Click)
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();
We also
need to add the following declaration to the
set of using
declarations at the beginning of the form
module:
using
Oracle.DataAccess.Client;
Run the
form to test it and you should see something like the following
At the
moment the department id is hard coded in the query, which means we'll
always display the name of department 10.
To allow the user to enter the department id we
need to add a text box. To do this, click on the “Toolbox” and add
another label control and a
text box control to your form. Name the label “Please
enter a Department ID:”. Your form should resemble the
following:
We also
need to change the select statement to use the department id
entered by the user as follows:
command.CommandText
=
"select
department_name from departments where department_id = "
+
textBox1.Text;
Now we can
run our application and
test it by entering a value of “10” for the department id. The result
should look like this:
The next
thing to do is to add some error
handling in case a non existent department id is entered. To do this we
need to check the result of the data read operation
on the database. If nothing is returned we display a
user-friendly error message,
otherwise we display the department name, like so:
if
(dataReader.Read()) { label1.Text
= dataReader["department_name"].ToString(); }
else
{ label1.Text
= "department id not found";
}
Now
if we re-run the application and enter a value of
“500” we should see something like this:
We could
make this application much more
useful by retrieving multiple rows and colums but we'll leave that for
the advanced tutorial!
One thing we probably should do though is to
improve the error handling to capture exceptions and
other errors.
This is done by using the try-catch-finally
block. Modifying the event handler to do this should give us something
like the following:
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();
if
(dataReader.Read()) { label1.Text
= dataReader["department_name"].ToString(); }
else
{ label1.Text
= "department id not found";
}
}
catch
(OracleException oe)
//Catch database specific errors
{
if
(12545 == oe.Number)
{
MessageBox.Show("The database is not available.");
}
else
if (12514 == oe.Number)
{
MessageBox.Show("Unable
to connect to the database");
}
else
{
MessageBox.Show("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();
}
}
}
Now let's test this new code by stopping the
database
and re-running our application. We should see the following
message displayed:
