Reputation: 25
I want to retrieve the value of a certain column that contain an Oracle db generated sys_guid() within a transaction.
public string GetGUID(OracleConnection connection)
{
string guid = "";
connection.Open();
OracleCommand cmd = new OracleCommand();
OracleTransaction transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = transaction;
cmd.Connection = connection;
try
{
cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO CUSTOMERS (NAME,DETAILS) VALUES (:nm, :dts)";
cmd.Parameters.Add("nm", OracleDbType.VarChar);
cmd.Parameters["nm"].Value = name;
cmd.Parameters.Add("dts", OracleDbType.VarChar);
cmd.Parameters["dts"].Value = details;
cmd.Prepare();
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Console.WriteLine(e.Message);
}
return guid;
}
Table CUSTOMERS have a column ORDER that creates a guid with SYS_GUID(), how can I retrieve that guid?
Upvotes: 1
Views: 741
Reputation: 186718
try returning .. into
clause e.g.
...
using (OracleCommand cmd = connection.CreateCommand()) {
cmd.CommandText =
@"INSERT INTO CUSTOMERS (
NAME,
DETAILS)
VALUES (
:nm,
:dts)
RETURNING
""ORDER"" INTO :orderid";
cmd.Parameters.Add("nm", OracleDbType.VarChar);
cmd.Parameters["nm"].Value = name;
cmd.Parameters.Add("dts", OracleDbType.VarChar);
cmd.Parameters["dts"].Value = details;
cmd.Parameters.Add("orderid", OracleDbType.VarChar);
cmd.Parameters["orderid"].Direction = ParameterDirection.Output;
try {
cmd.ExecuteNonQuery(); // Just execute, nothing to read
transaction.Commit();
guid = Convert.ToString(cmd.Parameters["orderid"].Value);
}
catch (Exception e) { //TODO: put more specific exception type
transaction.Rollback();
Console.WriteLine(e.Message);
}
return guid;
}
...
Upvotes: 2