static void Main(string[] args)
{
// Enter user id, password, and Oracle data source (i.e. net service name, EZ Connect, etc.)
string constr = "user id=<USER ID>;password=<PASSWORD>;data source=<DATA SOURCE>";
string sql1 = "select ref(p) from odp_ref1_sample_person_obj_tab p " +
"where p.name ='John'";
string sql2 = "odp_ref1_sample_upd_contacts";
string sql3 = "select deref(c.contact_ref), c.contact_phone " +
"from odp_ref1_sample_contacts c";
OracleConnection con = null;
OracleCommand cmd = null;
OracleDataReader reader1 = null;
OracleDataReader reader2 = null;
OracleRef refP = null;
try
{
// Establish a connection to Oracle DB.
con = new OracleConnection(constr);
con.Open();
// Retrieve REF from the object table.
cmd = new OracleCommand(sql1, con);
cmd.CommandType = CommandType.Text;
reader1 = cmd.ExecuteReader();
reader1.Read();
refP = reader1.GetOracleRef(0);
Console.WriteLine("HEX value of ref object: {0}", refP.Value);
Console.WriteLine();
// Insert a row into the object-relational table with the
// REF PERSON value just retrieved (i.e. John)
cmd.CommandText = sql2;
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = new OracleParameter();
param1.OracleDbType = OracleDbType.Ref;
param1.Direction = ParameterDirection.Input;
// Note: The UdtTypeName is case-senstive.
param1.UdtTypeName = "ODP_REF1_SAMPLE_PERSON_TYPE";
param1.Value = refP;
cmd.Parameters.Add(param1);
OracleParameter param2 = new OracleParameter();
param2.OracleDbType = OracleDbType.Varchar2;
param2.Direction = ParameterDirection.Input;
param2.Value = "1-800-555-4412";
cmd.Parameters.Add(param2);
cmd.ExecuteNonQuery();
// Retrieve the rows containing the name, John.
cmd.Parameters.Clear();
cmd.CommandText = sql3;
cmd.CommandType = CommandType.Text;
reader2 = cmd.ExecuteReader();
// Fetch each row
int rowCount = 1;
while (reader2.Read())
{
// Fetch the object as a custom type
Person p;
if (reader2.IsDBNull(0))
{
p = Person.Null;
}
else
{
p = (Person)reader2.GetValue(0);
}
string phone = reader2.GetString(1);
Console.WriteLine("Row {0}: {1}, {2}", rowCount++, p, phone);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Clean up
if (reader1 != null)
{
reader1.Dispose();
}
if (reader2 != null)
{
reader2.Dispose();
}
if (refP != null)
{
refP.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (con != null)
{
con.Dispose();
}
}
}
static void Main(string[] args)
{
// Enter user id, password, and Oracle data source (i.e. net service name, EZ Connect, etc.)
string constr = "user id=<USER ID>;password=<PASSWORD>;data source=<DATA SOURCE>";
string sqlInsertRef = "insert into odp_ref2_sample_person_obj_tab values (:1)";
string sqlSelectRef = "select ref(p) from odp_ref2_sample_person_obj_tab p";
string sqlSelectValue = "select value(p) from odp_ref2_sample_person_obj_tab p";
string udtTypeNameP = "ODP_REF2_SAMPLE_PERSON_TYPE";
string objTabNameP = "ODP_REF2_SAMPLE_PERSON_OBJ_TAB";
// Create a new Person object.
Person p = new Person();
p.Name = "John";
p.Address = "Address 1";
p.Age = 20;
// Create a new Student object.
Student s = new Student();
s.Name = "Jim";
s.Address = "Address 2";
s.Age = 25;
s.Major = "Physics";
OracleConnection con = null;
OracleCommand cmd = new OracleCommand();
OracleDataReader reader = null;
OracleTransaction txn = null;
OracleRef refP = null; // person REF
OracleRef refS = null; // student REF
try
{
// Establish a connection to Oracle DB.
con = new OracleConnection(constr);
con.Open();
cmd.Connection = con;
try
{
// Inserting a person and a student instance into the odp_ref2_sample_person_obj_tab
txn = con.BeginTransaction();
cmd.CommandText = sqlInsertRef;
OracleParameter param = new OracleParameter("inParam", OracleDbType.Object, ParameterDirection.Input);
param.UdtTypeName = udtTypeNameP;
cmd.Parameters.Add(param);
// Insert person
param.Value = p;
cmd.ExecuteNonQuery();
// Insert Student
param.Value = s;
cmd.ExecuteNonQuery();
txn.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Exception in inserting into {0}: {1}", objTabNameP, ex.Message);
}
finally
{
if (txn != null)
{
txn.Dispose();
txn = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
}
}
try
{
// Retrieving REF from odp_ref2_sample_person_obj_tab
cmd.CommandText = sqlSelectRef;
reader = cmd.ExecuteReader();
int row = 1;
while (reader.Read())
{
if (row == 1)
{
refP = reader.GetOracleRef(0);
}
else
{
refS = reader.GetOracleRef(0);
}
row++;
}
// Fetch rows from database table.
Person p1 = (Person)refP.GetCustomObject();
Student s1 = (Student)refS.GetCustomObject();
Console.WriteLine("Person: " + p1);
Console.WriteLine("Student: " + s1);
Console.WriteLine();
}
catch (Exception ex)
{
Console.WriteLine("Exception in selecting from {0}: {1}", objTabNameP, ex.Message);
}
finally
{
if (reader != null)
{
reader.Dispose();
reader = null;
}
}
try
{
// Update person object.
txn = con.BeginTransaction();
Person p2 = (Person)refP.GetCustomObject();
// Update person's age using OracleRef.
p2.Age = p2.Age + 1;
refP.Update(p2);
// p2 is updated to the database.
txn.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Exception in updating person in {0}: {1}", objTabNameP, ex.Message);
}
finally
{
if (txn != null)
{
txn.Dispose();
txn = null;
}
}
// Delete student object.
try
{
txn = con.BeginTransaction();
refS.Delete();
txn.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Exception in deleting student from {0}: {1}", objTabNameP, ex.Message);
}
finally
{
if (txn != null)
{
txn.Dispose();
txn = null;
}
}
try
{
// Retrieve rows from the database table.
cmd.CommandText = sqlSelectValue;
cmd.CommandType = CommandType.Text;
reader = cmd.ExecuteReader();
// Fetch each row.
int rowCount = 1;
while (reader.Read())
{
// Fetch the objects as a custom type.
Person p3;
if (reader.IsDBNull(0))
{
p3 = Person.Null;
}
else
{
p3 = (Person)reader.GetValue(0);
}
Console.WriteLine("Row {0}: {1}", rowCount++, p3);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception in selecting from {0}: {1}", objTabNameP, ex.Message);
}
finally
{
if (reader != null)
{
reader.Dispose();
reader = null;
}
}
}
catch (Exception ex)
{
Console.WriteLine("Get exception: " + ex.Message);
}
finally
{
// Clean up
if (refS != null)
{
refS.Dispose();
}
if (refP != null)
{
refP.Dispose();
}
if (reader != null)
{
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (txn != null)
{
txn.Dispose();
}
if (con != null)
{
con.Close();
con.Dispose();
}
}
}