I’d like to describe another not-so-obvious Oracle-C# interaction mechanism. It’s returning values from Oracle procedures stored in C# code using SQL returning into statement.
Oracle 12c introduced the feature named „identity columns”. In short, simple words, it allows to forget about manually created sequences and just insert rows with auto-incremented identities.
聽
Having a table with an identity column allowing us to insert rows without worries about the care of custom sequences – incrementing them properly for example. As you can see on the paste below, we have just to provide essential data parameters in order to insert a row. The id will be added automatically by Oracle.聽
聽
Previously, one has to create a custom sequence and query it before inserting a row. Manually, using a trigger, or possibly another way – but it was more difficult than just not caring about IDs. In general – it’s easier not to manage sequences.聽
聽
How to perform insertion and retrieve the ID from the Oracle using C#? Maybe someone could use the Dapper library, but due to reasons that I described here, so far I haven’t been able to do this yet. Fortunately, we still have managed library types – it’s fairly easy to do insertions with them, but it’s not obvious though. Take a look.
Returning into mechanism
聽
I created method InsertParameter which only one responsibility is to write data into Parameters table. From top to bottom:
- Create a connection to the database using a defined connection string.
- Open connection – this is required here. Dapper opens it automatically.
- SqlText is defined. Simple insert into XXX values XXX but extended with RETURNING ID INTO :nextId聽statement – it assigns ID value to the nextId variable聽
- Create command – be sure that properties CommandType聽and BindByName聽are set properly.聽
- Add parameters to the command. Two parameters related to data to be inserted, and one, special – with returned ID. Note that the parameter’s nextId name is not precessed with a semicolon.
- When parameters added, the ExecuteNonQuery method is invoked.
- If everything went OK, the nextId聽parameter contains the ID of the newly inserted row. Now we have to extract this value to C# long variable.
- As we can see, the Value property of the parameter is a raw聽object. First, we check whether it’s null – if so, return聽0. This is because we decided that in our system聽zero聽is treated as invalid ID.聽
- Then we cast to聽OracleDecimal and check again whether it’s null.聽
- If not, final cast to聽Int64 is done and this is our expected value.
That’s it.
If something is misty here or maybe you need more detailed info on this topic, leave a comment please – I will try to help.
