Posted By

heathbo on 01/29/14


Tagged


Versions (?)

Set and return data from Oracle using C#


 / Published in: C#
 

How to set add a record in an Oracle table and return a value in the same query using C#.

Returning ID INTO : OUTPUTID is referring to returning the value found in the ID field in the SQL table to the OUTPUTID parameter set in the AddStatusCode method.

  1. using Oracle.DataAccess.Client;
  2.  
  3.  
  4. private const string SqlInsertStatusCode = @"INSERT INTO STATUS_CODES(ID, STATUS_CODE, GROUP_CODE, DESCRIPTION, RANK) VALUES(STATUS_CODE_SEQ1.NEXTVAL, :STATUSCODE, :GROUPCODE, :DESCR, :STATUSCODE_RANK)
  5. RETURNING ID INTO :OUTPUT_ID";
  6.  
  7.  
  8.  
  9. public int AddStatusCode(StatusCode statusCode)
  10. {
  11. OracleParameter[] parameters =
  12. {
  13. CreateOracleParameter("OUTPUT_ID", null, OracleDbType.Int32,ParameterDirection.Output),
  14. CreateOracleParameter("DESCR",SafeDbConvert<string>(statusCode.Description),OracleDbType.Char),
  15. CreateOracleParameter("GROUPCODE", SafeDbConvert<string>(statusCode.GroupCode),OracleDbType.Char),
  16. CreateOracleParameter("STATUSCODE_RANK", SafeDbConvert<int>(statusCode.Rank),OracleDbType.Int32),
  17. CreateOracleParameter("STATUSCODE", SafeDbConvert<int>(statusCode.Status_Code), OracleDbType.Int32),
  18. };
  19.  
  20. ExecuteNonQuery(SqlInsertStatusCode, parameters);
  21.  
  22. return int.Parse(parameters[0].Value.ToString());
  23. }

Report this snippet  

You need to login to post a comment.