Friday, April 24, 2009

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());
}
}

1 comment:

  1. cool, useful
    post more c# snippet like this :-)

    ReplyDelete