c# - 在 ExecuteStoreQuery 错误消息 : "Value does not fall within the expected range." 中使用 OracleParameter

Question

为了防止SQL注入(inject)。我需要将我的代码从简单的 String 更改为 OracleParameter。此外，我需要绕过 LINQ，因为它在我的 WHERE CLAUSE 中有限制，您可以在下面看到并持续以获得更好的性能。顺便说一下，这段代码在我使用 OracleParameter 之前就可以正常工作。

我收到此错误消息“值不在预期范围内”。我的代码有什么问题或缺失，特别是 OracleParameter 和 ExecuteStoreQuery 部分？

我正在使用以下内容:

1. VS 2010
2. Oracle 11gR2
3. C#
4. EntityFramework 4.3.1

---

代码(.cs)

public List<ProgramAssetCodeViewModel> GetAssetToList(long _channelID, DateTime _startDate, DateTime _endDate, DateTime _startTime, DateTime _endTime, string _programName, bool isActualAiring)
{
    List<ProgramAssetCodeViewModel> assetList = new List<ProgramAssetCodeViewModel>();

    try
    {
        string qry = @"SELECT D.CHANNEL_NAME CHANNEL_NAME, TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') AIRING_DT, A.PROG_NAME, C.PROG_BODY_ASSET_CD, FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR(B.START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) START_TIME, FN_TR_CONVERT_UTC_TIME ('TO_LOCAL',TO_DATE('01/01/2001' || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) END_TIME, ASCII(UPPER(SUBSTR(C.PROG_BODY_ASSET_CD, -1)))-64 PROG_BODY_NO FROM PROGRAM_MSTR A, PROG_SKED_DTL B, PROG_BODY_MSTR C, CHANNEL_MSTR D WHERE B.CHANNEL_ID = D.CHANNEL_ID AND A.PROGRAM_ID = B.PROGRAM_ID AND B.PROG_SKED_ID = C.PROG_SKED_ID(+) AND B.CHANNEL_ID = :CHANNEL_ID AND A.PROG_NAME LIKE ('%' || :PROG_NAME ||'%') AND A.PROG_NAME NOT LIKE 'BS%' AND A.PROG_NAME NOT LIKE 'B/S%' AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') >= TO_DATE(:START_DATE,'MM/DD/YYYY') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR(B.START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) >= TO_DATE ('01/01/2001 ' || :START_TIME, 'MM/DD/YYYY HH24:MI')) AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') <= TO_DATE(:END_DATE,'MM/DD/YYYY') AND ((TO_DATE ('01/01/2001 ' || :START_TIME, 'MM/DD/YYYY HH24:MI') < TO_DATE ('01/01/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/01/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI')) OR (TO_DATE ('01/01/2001 ' || :START_TIME, 'MM/DD/YYYY HH24:MI') >= TO_DATE ('01/01/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/02/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI')))) ORDER BY CHANNEL_NAME, AIRING_DT, START_TIME, END_TIME, PROG_BODY_NO";
        string qryActual = @"SELECT D.CHANNEL_NAME CHANNEL_NAME, TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') AIRING_DT, A.PROG_NAME, C.PROG_BODY_ASSET_CD, FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR(B2.ACTUAL_START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) START_TIME, FN_TR_CONVERT_UTC_TIME ('TO_LOCAL',TO_DATE('01/01/2001' || TO_CHAR( B2.ACTUAL_END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) END_TIME, ASCII(UPPER(SUBSTR(C.PROG_BODY_ASSET_CD, -1)))-64 PROG_BODY_NO FROM PROGRAM_MSTR A, PROG_SKED_DTL B, PROG_SKED_ACTUAL B2, PROG_BODY_MSTR C, CHANNEL_MSTR D WHERE B.CHANNEL_ID = D.CHANNEL_ID AND B.CHANNEL_ID = B2.CHANNEL_ID(+) AND B.PROGRAM_ID = B2.PROGRAM_ID(+) AND B.START_DT = B2.START_DT(+) AND B.START_TIME = B2.START_TIME(+) AND B.PROG_AIR_DT = B2.PROG_AIR_DT(+) AND A.PROGRAM_ID = B.PROGRAM_ID AND B.PROG_SKED_ID = C.PROG_SKED_ID(+) AND B.CHANNEL_ID = :CHANNEL_ID  AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') >= TO_DATE(:START_DATE,'MM/DD/YYYY') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR(B2.ACTUAL_START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) >= TO_DATE ('01/01/2001 ' || :START_TIME, 'MM/DD/YYYY HH24:MI'))  AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') <= TO_DATE(:END_DATE,'MM/DD/YYYY')   AND ((TO_DATE ('01/01/2001 ' || :ACTUAL_START_TIME, 'MM/DD/YYYY HH24:MI') < TO_DATE ('01/01/2001 ' || :ACTUAL_END_TIME, 'MM/DD/YYYY HH24:MI') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR( B2.ACTUAL_END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/01/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI')) OR (TO_DATE ('01/01/2001 ' || :ACTUAL_START_TIME, 'MM/DD/YYYY HH24:MI') >= TO_DATE ('01/01/2001 ' || :ACTUAL_END_TIME, 'MM/DD/YYYY HH24:MI') AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001' || TO_CHAR( B2.ACTUAL_END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/02/2001 ' || :END_TIME, 'MM/DD/YYYY HH24:MI'))))  AND A.PROG_NAME LIKE ('%' || :PROG_NAME ||'%') AND A.PROG_NAME NOT LIKE 'BS%' AND A.PROG_NAME NOT LIKE 'B/S%' ORDER BY CHANNEL_NAME, AIRING_DT, START_TIME, END_TIME, PROG_BODY_NO";

        StringBuilder sb = isActualAiring ? sb = new StringBuilder(qryActual) : sb = new StringBuilder(qry);

        OracleParameter paramChannel = new OracleParameter() { ParameterName = "CHANNEL_ID", Value = _channelID.ToString(), OracleDbType = OracleDbType.Decimal, Size = 15, Direction = System.Data.ParameterDirection.Input };



        //sb.Replace(":CHANNEL_ID", _channelID.ToString());
        sb.Replace(":START_DATE", String.Format("'{0:MM/dd/yyyy}'", _startDate));
        sb.Replace(":END_DATE", String.Format("'{0:MM/dd/yyyy}'", _endDate));
        sb.Replace(":START_TIME", String.Format("'{0:HH:mm}'", _startTime));
        sb.Replace(":END_TIME", String.Format("'{0:HH:mm}'", _endTime));
        sb.Replace(":PROG_NAME", String.Format("'{0}'", _programName));

        using (var ctx = new FITSEntities())
        {
            foreach (var obj in ctx.GetObjectContext().ExecuteStoreQuery<ProgramAssetCodeWrapper>(sb.ToString(), new { paramChannel }))
            {
                assetList.Add(new ProgramAssetCodeViewModel
                {
                    IsChecked = false,
                    ChannelName = obj.CHANNEL_NAME,
                    AirDate = obj.AIRING_DT.Value,
                    ProgramName = obj.PROG_NAME,
                    AssetCode = obj.PROG_BODY_ASSET_CD,
                    StartTime = obj.START_TIME,
                    EndTime = obj.END_TIME,
                    ProgramBodyNo = obj.PROG_BODY_NO.HasValue ? (Int32)obj.PROG_BODY_NO.Value : 0
                });
            }
        }
    }
    catch
    {

    }

    return assetList;
}

---

SQL (qry)

SELECT D.CHANNEL_NAME CHANNEL_NAME,
  TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') AIRING_DT,
  A.PROG_NAME,
  C.PROG_BODY_ASSET_CD,
  FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001'
  || TO_CHAR(B.START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) START_TIME,
  FN_TR_CONVERT_UTC_TIME ('TO_LOCAL',TO_DATE('01/01/2001'
  || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) END_TIME,
  ASCII(UPPER(SUBSTR(C.PROG_BODY_ASSET_CD, -1)))-64 PROG_BODY_NO
FROM PROGRAM_MSTR A,
  PROG_SKED_DTL B,
  PROG_BODY_MSTR C,
  CHANNEL_MSTR D
WHERE B.CHANNEL_ID = D.CHANNEL_ID
AND A.PROGRAM_ID   = B.PROGRAM_ID
AND B.PROG_SKED_ID = C.PROG_SKED_ID(+)
AND B.CHANNEL_ID   = :CHANNEL_ID
AND A.PROG_NAME LIKE ('%'
  || :PROG_NAME
  ||'%')
AND A.PROG_NAME NOT LIKE 'BS%'
AND A.PROG_NAME NOT LIKE 'B/S%'
AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') >= TO_DATE(:START_DATE,'MM/DD/YYYY')
AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001'
  || TO_CHAR(B.START_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) >= TO_DATE ('01/01/2001 '
  || :START_TIME, 'MM/DD/YYYY HH24:MI'))
AND (TO_DATE(TO_CHAR(FN_TR_CONVERT_UTC_TIME('TO_LOCAL', B.PROG_AIR_DT),'MM/DD/YYYY'),'MM/DD/YYYY') <= TO_DATE(:END_DATE,'MM/DD/YYYY')
AND ((TO_DATE ('01/01/2001 '
  || :START_TIME, 'MM/DD/YYYY HH24:MI') < TO_DATE ('01/01/2001 '
  || :END_TIME, 'MM/DD/YYYY HH24:MI')
AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001'
  || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/01/2001 '
  || :END_TIME, 'MM/DD/YYYY HH24:MI'))
OR (TO_DATE ('01/01/2001 '
  || :START_TIME, 'MM/DD/YYYY HH24:MI') >= TO_DATE ('01/01/2001 '
  || :END_TIME, 'MM/DD/YYYY HH24:MI')
AND FN_TR_CONVERT_UTC_TIME ('TO_LOCAL', TO_DATE('01/01/2001'
  || TO_CHAR( B.END_TIME,'HH24:MI'),'MM/DD/YYYY HH24:MI')) <= TO_DATE ('01/02/2001 '
  || :END_TIME, 'MM/DD/YYYY HH24:MI'))))
ORDER BY CHANNEL_NAME,
  AIRING_DT,
  START_TIME,
  END_TIME,
  PROG_BODY_NO

Answer 1

我有一个关于 context.ExecuteStoreQuery<TElement> 用法的例子here.

而且看起来第二个参数必须是一个参数数组。

所以这段代码:

ExecuteStoreQuery<ProgramAssetCodeWrapper>(sb.ToString(), new { paramChannel })

必须是:

ExecuteStoreQuery<ProgramAssetCodeWrapper>(sb.ToString(), new OracleParameter[]{ paramChannel })