C# Oracle for Beginners

In the last two days, at work I had a couple of hours to play with Oracle.
I've made a simple class to Wrap the .NET OracleConnection and here there's a simple example with Connection/Disconnection a couple of CREATE TABLE, INSERT a STORED PROCEDURE and a FUNCTION a couple of SELECT to show JOIN and other operations.

OracleDatabase db = new OracleDatabase();
db.Connect("192.168.3.2", "system", "password");

// Create And Populate Tables
db.ExecuteNonQuery("CREATE TABLE tb1 (a NUMBER NOT NULL, b CHAR(20))");
db.ExecuteNonQuery("INSERT INTO tb1 (a, b) VALUES (10, 'Matteo')");
db.ExecuteNonQuery("INSERT INTO tb1 (a, b) VALUES (20, 'Mauro')");

db.ExecuteNonQuery("CREATE TABLE tb2 (a NUMBER NOT NULL, b CHAR(20))");
db.ExecuteNonQuery("INSERT INTO tb2 (a, b) VALUES (10, 'San Francisco')");
db.ExecuteNonQuery("INSERT INTO tb2 (a, b) VALUES (20, 'Cuppertino')");
            
// A Store Procedure cannot Return Value
StringBuilder sp1 = new StringBuilder();
sp1.Append("CREATE OR REPLACE PROCEDURE tb2sp1 ");
sp1.Append("IS ");
sp1.Append("BEGIN ");
sp1.Append("INSERT INTO tb1 (a, b) VALUES (30, 'SP1 TEST'); ");
sp1.Append("END; ");
db.ExecuteNonQuery(sp1.ToString());

// A Function can Return a Value
StringBuilder sp2 = new StringBuilder();
sp2.Append("CREATE OR REPLACE function tb2sp2 (idx NUMBER) ");
sp2.Append(" RETURN NUMBER ");
sp2.Append("IS ");
sp2.Append(" var_count NUMBER; ");
sp2.Append("BEGIN ");
sp2.Append(" SELECT COUNT(*) INTO var_count FROM tb1; ");
sp2.Append(" return (var_count + idx); ");
sp2.Append("END; ");
db.ExecuteNonQuery(sp2.ToString());

List<OracleDatabase.DataRecord> r1 = db.Execute("select * from tb1");
List<OracleDatabase.DataRecord> r2 = db.Execute("select a, b, (a * 2 + 1) from tb1");
List<OracleDatabase.DataRecord> r3 = db.Execute("select tb1.a as ID, tb1.b as NAME, 
                              tb2.b as PLACE from tb1 INNER JOIN tb2 ON tb1.a = tb2.a");
db.ExecuteStoredProcedure("tb2sp1");
List<OracleDatabase.DataRecord> r4 = db.Execute("select * from tb1");
object sp2RetVal = db.ExecuteFunction("tb2sp2(10)");
            
db.ExecuteNonQuery("DROP PROCEDURE tb2sp1");
db.ExecuteNonQuery("DROP FUNCTION tb2sp2");
db.ExecuteNonQuery("DROP TABLE tb2");
db.ExecuteNonQuery("DROP TABLE tb1");

db.Disconnect();

Here you can find the Source Code: Oracle for Beginners Source Code.


The OracleDatabase class that you've seen in the example above, is a simple wrapper class, that you can see below.
As always, we've two methods for Connect and Disconnect from Database Server.

public bool Connect(string sid, string userId, string password) {
    string cs = String.Format("Data Source={0}; User Id={1}; 
	                           Password={2};", sid, userId, password);
    _oracle = new OracleConnection(cs);
    _oracle.Open();
    return (_oracle.State == ConnectionState.Open);
}

public void Disconnect() {
    if (_oracle != null && _oracle.State != ConnectionState.Closed)
        _oracle.Close();
}

Below there're the methods that helps you to retrieve SELECT data, and to run non query like INSERT, UPDATE, DELETE, DROP...

public List<DataRecord> Execute(string query, params object[] args) {
    using (OracleCommand cmd = new OracleCommand(String.Format(query, args), _oracle)) {
        cmd.CommandType = CommandType.Text;
        return (Execute(cmd));
    }
}

private List<DataRecord> Execute(OracleCommand cmd) {
    List<DataRecord> dataRecordList = new List<DataRecord>();
    using (OracleDataReader reader = cmd.ExecuteReader()) {
        while (reader.Read()) {
            DataRecord record = new DataRecord();
            for (int i = 0; i < reader.FieldCount; ++i)
                record.Add(reader.GetName(i), reader[i]);
            dataRecordList.Add(record);
        }
        reader.Close();
    }
    return (dataRecordList);
}

public int ExecuteNonQuery(string query, params object[] args) {
    using (OracleCommand cmd = new OracleCommand(String.Format(query, args), _oracle)) {
        cmd.CommandType = CommandType.Text;
        return(cmd.ExecuteNonQuery());
    }
}

Below two methods to Execute a Function and a Stored Procedure, functions can have a return value.

public object ExecuteFunction(string query, params object[] args) {
    using (OracleCommand cmd = new OracleCommand(String.Format(query, args), _oracle)) {
        cmd.CommandType = CommandType.StoredProcedure;
        OracleParameter retParameter = new OracleParameter();
        retParameter.Direction = ParameterDirection.ReturnValue;
        retParameter.OracleType = OracleType.Number;
        cmd.Parameters.Add(retParameter);
        cmd.ExecuteNonQuery();
        return (retParameter.Value);
    }
}

public int ExecuteStoredProcedure(string query, params object[] args) {
    using (OracleCommand cmd = new OracleCommand(String.Format(query, args), _oracle)) {
        cmd.CommandType = CommandType.StoredProcedure;
        return (cmd.ExecuteNonQuery());
    }
}