Dapper and Oracle CRUD issues, how to?

c#-4.0 crud dapper

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

Accepted Answer

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");

Popular Answer

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




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why