Advanced Search

Set and return data from Oracle using C#

/ Published in: C#
Save to your folder(s)
How to set add a record in an Oracle table and return a value in the same query using C#.

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

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.