Click here to Skip to main content
Click here to Skip to main content
Go to top

Simple Introduction to Oracle XE with C#

, 11 Jul 2008
Rate this:
Please Sign up or sign in to vote.
This article is a simple introduction to Oracle XE using C#

The screen prints below are taken from the sample Oracle HR Management project. This project is intended for learning purposes.

Main Screen

Edit Window

Print Preview

Navigation And Functions

Introduction

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 your 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.

Screenshot - img1.jpg

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 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 reference.

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. Whereas 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. However, 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 from 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, however 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 last name
                Console.WriteLine(emp.Rows[i][1].ToString() + &quot;\t\t&quot; + 
						emp.Rows[i][2].ToString());
            }
            ConnectionClass.Terminate();

            Console.Read();
        }
    }

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Syed M Hussain
Web Developer
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
Questiondownloading permission Pinmemberpatiluday0857-Jul-13 21:16 
AnswerRe: downloading permission PinmemberMember 108028587-May-14 18:28 
Questionwhat is the connecction string if i want to connect to remote server? PinmemberMorteza Naeiamabadi27-Jul-12 20:25 
GeneralMy vote of 4 PinmemberMorteza Naeiamabadi27-Jul-12 20:24 
QuestionCould you send me the sample code ? Pinmemberdaiwuju15-Jul-12 23:01 
AnswerRe: Could you send me the sample code ? PinmemberSyed M Hussain16-Jul-12 9:15 
GeneralRe: Could you send me the sample code ? Pinmemberdaiwuju17-Jul-12 23:23 
GeneralMy vote of 5 Pinmemberwelshmike22-Dec-11 11:58 
GeneralThis is pointless [modified] Pinmembercraigg7530-May-11 5:35 
GeneralRe: This is pointless [modified] PinmemberSyed M Hussain30-May-11 9:55 
GeneralRe: This is pointless Pinmembercraigg7530-May-11 13:54 
GeneralODP.NET Pinmembersikas_C++23-Jun-10 18:17 
GeneralRe: ODP.NET PinmemberMorteza Naeiamabadi27-Jul-12 20:28 
QuestionEmployees table PinmemberGen Erike1-Dec-07 6:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 11 Jul 2008
Article Copyright 2007 by Syed M Hussain
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid