LinkedIn

Thursday, 12 June 2008

Using an Oracle Function in .NET

Hey,

Just blogging this as it was a pain in the bum to get right:

public Acres.DataContracts.BusinessCaseContentType GetFullCaseDataFromClarity( Acres.DataContracts.BusinessCaseContentType contract ) {


string xml_clob = string.Empty;

System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection( ConfigurationManager.ConnectionStrings[ "ClarityConnection" ].ConnectionString );

using ( System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand() ) {

conn.Open();

cmd.Connection = conn;
cmd.CommandText = "Workflow_procedures.webcall_business_case";
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p_retval = new OracleParameter("p_retval", OracleType.Clob);
p_retval.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_retval);

cmd.Parameters.Add( "p_bc_id", OracleType.Int32 ).Value = contract.ClarityBusinessCaseId;

cmd.ExecuteNonQuery();

xml_clob = ( (System.Data.OracleClient.OracleLob)( p_retval.Value ) ).Value.ToString();

contract =
(Acres.DataContracts.BusinessCaseContentType)Acres.Workflow.UtilityCode.Utility.DeserialiseObject
( xml_clob, typeof( Acres.DataContracts.BusinessCaseContentType ) );
}
return contract;
}