Geek Answers Handbook

Enter secure binding to Oracle stored procedure in C #?

We are deploying several C # projects with Oracle databases behind. I would like to implement all the database logic in Oracle stored procedures like this

  • stores all database logic in the database
  • simplifies maintenance when changing database structures
  • makes it easier to use stored procedures in programming languages.

I have a test code where I return rows using SYS_REFCURSOR, and I manually bind the data to the results, since SYS_REFCURSOR can return something, i.e. not safe

My question is: is there a way to determine the correct types in the returned type of the stored procedure and is it correct to bind this type to my C # code type?

eg. my PL / SQL routine looks like this: the return part is not type safe - it could be anything. If I wanted to reuse it from another Oracle package, it would not have proper type checking

PROCEDURE get_risk (p_process_id IN NUMBER, p_risk OUT sys_refcursor);

and my C # code looks something like this. I combined this together from several classes, so hopefully this makes sense. When I retrieve data from a DB call, I manually define data types - I need to know in C # code that Oracle data types

 // setup procedure call _oracleCommand = new OracleCommand("risk_pkg.get_risk", _conn.OracleConnection); _oracleCommand.Parameters.Add(new OracleParameter("p_process_id", OracleDbType.Int64, processId, ParameterDirection.Input)); _oracleCommand.Parameters.Add(new OracleParameter("p_risk", OracleDbType.RefCursor, null, ParameterDirection.Output)); _oracleDataAdapter = new OracleDataAdapter(_oracleCommand); _dataSet = new DataSet(); // call Oracle _oracleDataAdapter.Fill(_dataSet); // extract data - hand coded binding Int64 dbRiskId = (Int64)_dataSet.Tables[0].Rows[0][_dataSet.Tables[0].Columns["risk_id"]]; Int64 dbClientId = (Int64)_dataSet.Tables[0].Rows[0][_dataSet.Tables[0].Columns["client_id"]]; return new Risk(dbRiskId, dbClientId);

This is not necessarily a problem - I just want to know if there is a better way to do this, to make my PL / SQL more obvious in what it returns, and to make my C # code not know Oracle data types - encapsulating me from changes in the database structure data

Decision taken: this seems to be a practical solution. I am still a bit unhappy that my Oracle procedure does not determine the type of return explicitly, but that life

+4
source share
2 answers

(You should send a sample of your test code. Because I'm not sure if I understood your question correctly.)

The return type of Object serves to serve any possible return value. You must convert it manually. But you can create code for conversions. Define a table or file with this meta-information: which stored procedure returns the types and the types of which they should be converted. Use this meta information to create C # code.

We populate RefCursors in a DataTable . The code for assigning table fields to their corresponding member variables is generated from our meta tables.

+2
source

I used T4 text templating for this with SQL server. It works incredibly well.

+1
source

More articles:


All Articles