simple oracle connection in C# (Oracle 10g & XE)

Got first assignment about database connection from your lecturer??? you’ve come to the right place mate ! I know U’ve been looking for this code , so enjoy it , it’s “trial & error” time !

First, let me tell you about the types of ADO .NET programming, there are two types of it. There are DataSet and OleDbCommand.
The difference is, on DataSet, instances of this class represent in-memory caches of data, so you don’t have to maintain an active connection to modify the contents of a datasource. It is a better way than using OleDbCommand, which in this approach, SQL statements are executed directly on the datasource, very wasteful right?

But, this article are just for dummies =P , so I’m using OleDbCommand on it (for Oracle, it’s named OracleCommand ). OK then, first, you must add a reference to your project. View the solution explorer then right-click at your project’s root, choose add reference. On .NET tab, choose the System.Data.OracleClient. Now, your Oracle-Client is ready for use!

For opening an Oracle connection, you need at least this three namespaces ;

using System;
using System.Data;
using System.Data.OracleClient;

remember that an Oracle-Client must have already installed on your PC. In this example, I used Oracle XE as my Oracle-Client service. All Oracle-Client have the same methods to get connected, you just have to change the SID, pretty simple mate !

Now, it’s time for the best part, the Oracle connection string. Please behave nicely =P

private OracleConnection conn;

public bool OpenConnection(string SID, string user, string password)
{
try
{
//instance new oracle connection
conn = new OracleConnection(“Data Source=”+SID+ “; User Id=”+user+”; Password=”+password+”;”);
//open the connection
conn.Open();
return true;
}
catch (Exception) //this is your first time ! so,learn to use “try catch” as available as possible! it’s very important!
{
return false;
}
}

There are two basic conditions on executing SQL command, first is query command (select * from [table_name]) , second is non-query command (insert,update,delete).
Here are the codes ;

// this is a method for executing query command
public void execQuery(string sql) {

OracleCommand cmd = new OracleCommand(sql);

cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

try
{

OracleDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{

//read the query result. On this example, i put three names of my database fields, there are “NAMA” ,”ALAMAT”,”EMAIL”
Console.WriteLine(Convert.ToString(reader["NAMA"]));
Console.WriteLine(Convert.ToString(reader["ALAMAT"]));
Console.WriteLine(Convert.ToString(reader["EMAIL"]));

}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
}

}

//this is a method for executing non-query command

public bool execNonQuery(string sql) {

OracleCommand command = new OracleCommand(sql);
command.Connection = conn;
try
{
//a non-query command doesn’t need any reader, all you have to do is execute them !
command.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}

}