This tutorial is intended to be a beginner's guide to Oracle XE. Oracle XE is an entry level database from Oracle. It has many of the features of the standard Oracle database yet it is easy to install with very little configuring needed and also easy to administer. There are some limitations as you would expect from a free product. The first is that there is a four gigabyte user data restriction. That is you can only store up to four gigabytes of user data. This does not include table namespaces or database data. The other restriction is that the database will only use one CPU from the host machine even if the host machine has more than one CPU.

Oracle XE

Oracle XE contains a sample account named HR. This account is locked and before you can use it, you need to unlock the account. This is a very simple task, which requires you to login using the system account login details. This is because the sample HR account is locked by default, so you need to login as system, which gives you administrator privileges. Once logged in as system, you can unlock the sample HR account.

If you haven't already done so, download a copy of Oracle XE and install it on your machine. Take note of the password you supply when installing Oracle XE. This password is you system login password.

After installing Oracle XE, your Program Files menu will contain a new entry. Navigate to Oracle Database 10g Express Edition, once the submenus appear you should notice a menu titled 'Go To Database Home Page'. Click on this menu. Your default web browser will load the Oracle Application Express (Oracle APEX). Oracle APEX is a web application which in short is an application to help you manage your database.

To login to the system account, type SYS for the username. Enter the password you supplied when installing Oracle XE. Once logged in, you will be presented with the following screen. See figure 1.1 below.

Figure 1.1

Notice the four main menus. At this point your main task is to unlock the sample HR account. The reason for this is we will be developing a simple C# application which will make use of this account.

To unlock this account click the Administration. From the menu click Database Users then from the submenu click Manage Users. You should now see the sample HR account with a Lock symbol. Click the HR icon. Now from the Manage Database User panel, select Unlock from Account Status. After selecting Unlocked click the Alter User button. You should now see the lock symbol removed from the HR account.

Connecting To Oracle XE Using C#

We will be using the Oracle Data Provider to establish a connection to Oracle XE. ODP.Net is by default supplied with Oracle XE. That is, when you install Oracle XE, you also get ODP.Net. To make sure that you actually do have ODP.Net, you can perform a very simple test.

Run SQL*Plus, this is actually titled 'Run SQL Command Line'. Once the console is loaded, type the following:

connect hr/hr

Above we are simply connecting to the HR account. Using hr as the username and hr as the password. If you receive Connected, this means that ODP.Net is installed.

It's about time we actually started doing some programming. Let's start with some very simple code. The code below in listing 1.1 shows how to establish a connection. Before you can use the code you need to add the Oracle.DataAccess refrence

Listing 1.1

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

    static class ConnectionClass
    {
        private static OracleConnection conn;

        public static void Connection()
        {
                string oradb = "Data Source=XE;User Id=hr;Password=hr;";
                conn = new OracleConnection(oradb);
                conn.Open();
        }
    }

Every time I write a database application, I always like to create a separate Class which consists of the database connection code. Using this approach I can easily reuse my connection code. I also make the Class a static Class. This allows me to use the Class without having to create an instance of the Class.

The above code is fairly easy to understand. We simply create an instance of the OracleConnection Class. The OracleConnection Class takes one argument, which is the connection string. The connection string simply consists of the Data Source name, which is by default XE, the user id, which is the username and the password. Although the above code is all that is needed to establish a connection to the Oracle database, we can add a try, catch block to catch any errors that might occur. The code in Listing 1.1 can be changed to the following code in Listing 1.2 below.

Listing 1.2

        public static string Connection()
        {
            try
            {
                string oradb = "Data Source=XE;User Id=hr;Password=hr;";
                conn = new OracleConnection(oradb);
                conn.Open();
            }
            catch (OracleException e)
            {
                return e.Message;
            }

            return conn.State.ToString();
        }

The first thing to note about this code is that the Connection method will return a string. Where as in Listing 1.1, the Connection method was declared as void which, did not return a value to the calling method. This approach is a very simple approach which allows us to catch any errors that might occur and send them back to the calling method, where you can process the message either by showing the message to the user or based on the error code you can perform another task. If there are no errors, conn.State.ToString() will return the string "Open" indicating that the connection is open.

The HR database contains a table named Employees, this table holds details about each employee such as first name, last name, e-mail and so on. For our next task we will create a GetEmployees() method, which will return a DataTable. The code in listing 1.3 demonstrates how to retrieve all the columns from the Employees table and return the data as a DataTable.

Listing 1.3

using System.Data;

        private static string SQL;
        private static OracleConnection conn;
        private static OracleCommand cmd;
        private static OracleDataAdapter da;
        private static DataSet ds;

    .....................................................

        public static DataTable GetEmployees()
        {
            SQL = "SELECT * FROM Employees";
            cmd = new OracleCommand(SQL, conn);
            cmd.CommandType = CommandType.Text;
            da = new OracleDataAdapter(cmd);
            ds = new DataSet();

            da.Fill(ds);
            return ds.Tables[0];
        }

The first thing to note is that we have added a new directive, this is the System.Data namespace. The GetEmployees() method, creates an instance of the OracleCommand Class. This object is responsible for formulating the request and passing it to the database. It takes an SQL statement and the connection object as arguments. Although it can take just the SQL as an argument and the connection object can be set in the OracleCommand objects property such as cmd.Connection = conn.

Next we create an instance of the OracleDataAdapter. We use the OracleDataAdapter to fill a Dataset, which will be used to return a table form the Dataset. We supply the OracleCommand object as an argument to the OracleDataAdapter. We then use the Fill method of the OracleDataAdapter to fill a DataSet. Finally we return the table using return ds.Tables[0].

Our Connection Class is almost complete, how ever there is one more method we need to implement. This is the Terminate() method. The terminate method will be responsible for closing the database connection. Listing 1.4 below shows the code for the Terminate() Method.

Listing 1.4

        public static void Terminate()
        {
            conn.Close();
        }

Let's take a look at ConnectionClass code. Listing 1.5 below shows the entire code.

Listing 1.5

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Data;


    static class ConnectionClass
    {
        private static string SQL;
        private static OracleConnection conn;
        private static OracleCommand cmd;
        private static OracleDataAdapter da;
        private static DataSet ds;

        public static string Connection()
        {
            try
            {
                string oradb = "Data Source=XE;User Id=hr;Password=hr;";
                conn = new OracleConnection(oradb);
                conn.Open();
            }
            catch (OracleException e)
            {
                return e.Message;
            }

            return conn.State.ToString();
        }

        public static DataTable GetEmployees()
        {
            SQL = "SELECT * FROM Employees";
            cmd = new OracleCommand(SQL, conn);
            cmd.CommandType = CommandType.Text;
            da = new OracleDataAdapter(cmd);
            ds = new DataSet();

            da.Fill(ds);
            return ds.Tables[0];
        }

        public static void Terminate()
        {
            conn.Close();
        }
    }

Finally we need to create a simple program to use our ConnectionClass. Listing 1.6 below shows a complete Program.cs file which uses the ConnectionClass to display employees first name and last name.

Listing 1.6

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;


    class Program
    {
        static void Main(string[] args)
        {
            string strConn = ConnectionClass.Connection();

            DataTable emp = ConnectionClass.GetEmployees();

            for (int i = 0; i < emp.Rows.Count; i++)
            {
                //Print first name and lasy name
                Console.WriteLine(emp.Rows[i][1].ToString() + "\t\t" + emp.Rows[i][2].ToString());
            }
            ConnectionClass.Terminate();

            Console.Read();
        }
    }

You can download the sample HR GUI application, which uses the suppiled HR database or you can download the Sample ConnectionClass progect files if you want to work with the basics.