Dapper with sql query dynamic parameters
Solution 1
Finally i solved my issue with little change in my code. That is @Empid is changed into :Empid, since oracle database supports dynamic parameters in this way. And for my second question that is how to handle with multiple dynamic parameters i am using dappers DynamicParameters class as shown below,
var parameters = new Dictionary<string, object>();
parameters.Add("ID", empId);
DynamicParameters dbParams = new DynamicParameters();
dbParams.AddDynamicParams(parameters);
And we can use this in dapper as shown in below code snippe, Parameter query is a sample sql query.
dynamic result = db.Query(query, dbParams);
Solution 2
So you have a logic problem here which might be the error
case
when actinact=0 and cancel_idno is not null then 'Cancelled'
when actinact=1 and AUTH_IDNO is null then 'Pending'
when actinact=0 and cancel_idno is not null then 'Rejected'
else 'Authorised'
end leave_Status
because actinact=0 and cancel_idno is not null is listed twice
Note: This is easy to spot when the code is nicely formatted.
You say you want two parameters just add then in the new object
new { EmpId = empId }
becomes
new { EmpId = empId,
newparam = newvalue })
and then use @newparam in your query.
doc -> https://msdn.microsoft.com/en-us/library/bb397696.aspx
![Krishnarjun Banoth](https://i.stack.imgur.com/fev2V.jpg?s=256&g=1)
Comments
-
Krishnarjun Banoth about 2 years
In my asp.net web-api, am getting error as "ORA-00936: missing expression" from below code snippet. I had tried many solutions but i did not overcome this error. And also i want to know how do i bind more than one parameter dynamically. I am using oracle as my back-end and dapper as my ORM.
string empId = json.EMPID; //'15RD005' var sql = @"Select id_no,SNO,REASON,APPLIEDDATE,Case when LEAVE_TYPE = 0 then 'CL' when LEAVE_TYPE = 1 then 'EL' when LEAVE_TYPE = 2 then 'SL' when LEAVE_TYPE = 3 then 'OFF' when LEAVE_TYPE = 4 then 'OD-OFF' when LEAVE_TYPE = 5 then 'LOP' when LEAVE_TYPE = 6 then 'OPTIONAL' end LEAVE_TYPE, to_char(fromdate,'DD-MON-YYYY') f_date, to_char(todate,'DD-MON-YYYY') t_date, Case when fromslot=0 then 'First-Half' when fromslot=1 then 'Second-Half' when fromslot=2 then 'Full-Day' end From_Slot, Case when toslot=0 then 'First-Half' when toslot=1 then 'Second-Half' when toslot=2 then 'Full-Day' end To_Slot, applieddays APP_DAYS, case when actinact=0 and cancel_idno is not null then 'Cancelled' when actinact=1 and AUTH_IDNO is null then 'Pending' when actinact=0 and cancel_idno is not null then 'Rejected' else 'Authorised' end leave_Status from Tleaves where to_char(Todate,'mm-yyyy') >= to_char(sysdate-30,'mm-yyyy') and to_char(todate,'mm-yyyy') <=to_char(sysdate,'mm-yyyy') and to_char(Todate,'yyyy')=to_char(sysdate,'yyyy') and id_no like @EmpId Order by sno"; try { using (OracleConnection db = new OracleConnection(conString)) { db.Open(); var pastLeavesReport = new PastLeavesReportDTO(); //3.Present and last month lev status report List<PastLeavesReportInfoDTO> pastLeavesReportInfo = db.Query<PastLeavesReportInfoDTO>(sql, new { EmpId = empId }).ToList(); pastLeavesReport.EMPID = ""; pastLeavesReport.LEAVES = pastLeavesReportInfo; return Ok( new EmpLeavesActionResponse(ActionStatusCodes.PastLeavesReportDataFound, "", pastLeavesReport)); } } catch (Exception exp) { return Ok( new EmpLeavesActionResponse(ActionStatusCodes.ServerException, exp.Message, null)); }