c# - 选择 2 个日期时间值之间的数据

Question

这是 Visual C# 中的 SSIS 脚本任务的内容

public void Main()
{
    // TODO: Add your code here
    int Var_Flag = (int)Dts.Variables["Var_Flag"].Value;                        // 1 for manual run, 0 for incremental since last run
    DateTime Start_Date = (DateTime)Dts.Variables["Var_StartDate"].Value;       // Start DateTime of the previous run. Start at this date if run is set to manual
    DateTime End_Date = (DateTime)Dts.Variables["Var_EndDate"].Value;           // End DateTime of the previous run. End at this date if run is set to manual

    String SQL = "";

    // If incremental run, our current run needs to start at the end date from the previous run and include everything up to today's date
    // Format the SQL string

    SQL = "SELECT * " + "   from rz_ibsv.dbo.INTGR_INSTLD_BASE_SRVC_CNTRCT_F"+
    // "WHERE " +
    //"(convert(datetime2,UPD_GMT_TS) >=CONVERT(DATETIME,'" + Start_Date + "',131) AND convert(datetime2,UPD_GMT_TS) < CONVERT(DATETIME,'" + End_Date + "',131)) ";
    //"(convert(UPD_GMT_TS >=cast('" + Start_Date + "',datetime) AND UPD_GMT_TS < cast('" + End_Date + "',datetime) "; 
    //"UPD_GMT_TS BETWEEN '" + Start_Date + "' and '" + End_Date + "'";
  "(UPD_GMT_TS >=CONVERT(datetime,'" + Start_Date.ToString() + "') AND UPD_GMT_TS < CONVERT(datetime,'" + End_Date.ToString() + "')) ";

    // Return the SQL string to the global variable

    Dts.Variables["Intervention_Parts_Qry"].Value = SQL;
    Dts.TaskResult = (int)ScriptResults.Success;
}

这段代码的作用是。

1. 需要 2 个日期时间值将其填充到 2 个变量中。
2. 然后这 2 个变量用于创建 sql 语句，该语句在 2 个变量范围之间选择数据。
3. 该sql语句用于执行sql任务来选择所需的输出。

我收到的错误为:

[OLEDB_SRC- COREIB CONTRACT DATA [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Incorrect syntax near '>'.".

需要帮助解决此问题:)

Answer 1

这些情况下的技巧有两个。第一个是不使用 SQL 语句的字符串构建。这就是Parameters是给。作为一个额外的好处，它有助于防范 SQL 注入(inject)攻击。

遇到奇怪的情况时要做的第二件事是显示命令。假设您忠实地表示了 SQL 变量将计算为

的代码

SELECT * from rz_ibsv.dbo.INTGR_INSTLD_BASE_SRVC_CNTRCT_FWHERE (UPD_GMT_TS...

您的表/ View 和 WHERE 子句之间需要有一个空格。

因此

SQL = "SELECT * " 
    + " FROM rz_ibsv.dbo.INTGR_INSTLD_BASE_SRVC_CNTRCT_F AS F 
    + "WHERE " ...

使用 SqlCommand 的更好方法是

    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = "SELECT F.* FROM rz_ibsv.dbo.INTGR_INSTLD_BASE_SRVC_CNTRCT_F AS F WHERE F.UPD_GMT_TS >= @StartDate AND F.UPD_GMT_TS < @EndDate ";
    command.CommandType = CommandType.Text;

    SqlParameter parameter = new SqlParameter();
    parameter.ParameterName = "StartDate";
    parameter.SqlDbType = SqlDbType.DateTime;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = Start_Date;

    command.Parameters.Add(parameter);

    parameter = new SqlParameter();
    parameter.ParameterName = "EndDate";
    parameter.SqlDbType = SqlDbType.DateTime;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = End_Date;

    command.Parameters.Add(parameter);

    connection.Open();
    SqlDataReader reader = command.ExecuteReader();