Dapper and Oracle CRUD issues, how to?

Question

How do i make Dapper.NET to CRUD my Oracle DB?

I have table named: PLAYER_LOG it's identity is done by trigger, here is the sql

SELECT SQ_MASTER_SEQUENCE.NEXTVAL INTO tmpVar FROM dual;
:NEW.ID := tmpVar;

my Model is:

public class PlayerLogStorage : IEntity //-> here is the identity
{       
    public string Cli { get; set; }
    public string PlayerAnswer { get; set; }
    public DateTime InsertDate { get; set; }
}

here is my insert:

 using (IDbConnection ctx = DbConnectionProvider.Instance.Connection)
 {
            ctx.Query<PlayerLogStorage>("INSERT INTO PLAYER_LOG (CLI, ANSWER, INSERT_DATE) VALUES (:Cli, :PlayerAnswer, :InsertDate)", new
            {
                Cli = model.Cli,
                PlayerAnswer = model.PlayerAnswer,
                InsertDate = model.InsertDate
            });
 }

here is the exception:

ORA-01008: not all variables bound

score 17 · Accepted Answer · answered Jun 08 '12 at 17:18

17

I have ran into something a little similar, but using the returning statement. The trick I found was to use the DynamicParameters object. In the system I use, the insert statements have to invoke NextVal on the sequence, it is not in a trigger.

var param = new DynamicParameters();

param.Add(name: "Cli", value: model.Cli, direction: ParameterDirection.Input);
param.Add(name: "PlayerAnswer", value: model.PlayerAnswer, direction: ParameterDirection.Input);
param.Add(name: "InsertDate", value: model.InsertDate, direction: ParameterDirection.Input);
param.Add(name: "Id", dbType: DbType.Int32, direction: ParameterDirection.Output);

using (IDbConnection ctx = DbConnectionProvider.Instance.Connection)
{
    ctx.Execute("INSERT INTO PLAYER_LOG (CLI, ANSWER, INSERT_DATE) VALUES (:Cli, :PlayerAnswer, :InsertDate) returning Id into :Id", paramList);
}

var Id = param.get<int>("Id");

answered Jun 08 '12 at 17:18

bwalk2895

504
5
11

Thanks i will check it out, do you more examples of use with Oracle? – hackp0int Jun 09 '12 at 08:19
I have been experimenting with using Dapper and Oracle together for a while now, and have found ways around a number of quirks...if you have a specific question I am more than happy to answer. – bwalk2895 Jun 13 '12 at 20:29
1

great answer @bwalk2895 :) helpme alot :) i'm currently using dapper with oracle :) – danywalls Jun 11 '13 at 08:29
@bwalk2895 how did you insert string into clob type? I use OracleDynamicParameter and had this problem http://stackoverflow.com/questions/23145558 do you have any idea? – Timeless Aug 07 '14 at 07:06
@Timeless Sorry, I switched jobs over a year ago and haven't used Oracle since. All the code I wrote for getting oracle to work with Dapper.NET is at my old job. One thing I remember is to try to create a proc / package that accepts a varchar and have the that proc / package convert it to a CLOB. – bwalk2895 Aug 08 '14 at 13:19

score 10 · Answer 2 · edited Jul 24 '17 at 16:49

In addition to bwalk2895's answer, you can also pass in your model object to the constructor of DynamicParameters and then you only need to add the output paramaters. Saves a few lines of code, especially for objects with many properties. Example:

var param = new DynamicParameters(model);

param.Add(name: "Id", dbType: DbType.Int32, direction: ParameterDirection.Output);

using (IDbConnection ctx = DbConnectionProvider.Instance.Connection)
{
    ctx.Execute("INSERT INTO PLAYER_LOG (CLI, ANSWER, INSERT_DATE) VALUES (:Cli, :PlayerAnswer, :InsertDate) returning Id into :Id", param);
}

var Id = param.Get<int>("Id");

update: corrected method name

Dapper and Oracle CRUD issues, how to?

2 Answers2

Linked