Problems with Dapper and Oracle CRUD, how?

Question

How to make Dapper.NET for CRUD my Oracle DB?

I have a table with the name: PLAYER_LOG authentication is performed by a trigger, here is sql

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

My model:

 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 

Answer 1

I came across something similar, but with a return statement. The trick I found was to use a DynamicParameters object. On your system, insert statements must call NextVal in the sequence; it is not in the 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"); 

Answer 2

In addition to bwalk2895's answer, you can also pass the model object to the DynamicParameters constructor, and then you only need to add the output parameters. Saves multiple 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: fixed method name