在我的 asp.net web-api中,我从下面的代码片段中收到错误为"ORA-00936:缺少表达式"。我尝试了许多解决方案,但没有克服这个错误。我还想知道如何动态绑定多个参数。我正在使用oracle作为我的后端,并使用dapper作为我的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));
}
最后,我解决了我的问题,而代码几乎没有变化。也就是说@Empid被更改为:Empid,因为oracle数据库以这种方式支持动态参数。对于我的第二个问题,即如何处理多个动态参数,我正在使用dappers DynamicParameters类,如下所示,
var parameters = new Dictionary<string, object>();
parameters.Add("ID", empId);
DynamicParameters dbParams = new DynamicParameters();
dbParams.AddDynamicParams(parameters);
我们可以在 dapper 中使用它,如下面的代码片段所示,参数查询是一个示例 sql 查询。
dynamic result = db.Query(query, dbParams);
所以你在这里有一个逻辑问题,这可能是错误
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
因为 actinact=0 并且 cancel_idno 不为 null 被列出两次
注意:当代码格式良好时,这很容易发现。
你说你想要两个参数,然后在新对象中添加
new { EmpId = empId }
成为
new { EmpId = empId,
newparam = newvalue })
,然后在查询中使用@newparam。
文档 -> https://msdn.microsoft.com/en-us/library/bb397696.aspx