如何为IN子句参数化查询配置SqlDataSource SelectParameters

问题核心 #

你尝试两种方式实现IN子句参数化均失败：

• 动态生成@RTResidentID0、@RTResidentID1这类参数时，因标记未声明且未在代码中添加到SqlDataSource的参数集合，导致访问SqlDataSource1.SelectParameters[residentID]出现对象引用未设置为对象的实例错误。
• 尝试传入逗号分隔的字符串作为RTResidentIDList参数，SQL会将整个字符串视为单一值，无法正确匹配IN子句的多值需求。

方案一：动态生成参数占位符并添加到SelectParameters #

适合小规模多值场景，步骤如下：

1. 调整标记中的SelectParameters
   移除未生效的RTResidentIDList，保留固定参数：

<SelectParameters>
    <asp:SessionParameter Name="RTFacilityID" SessionField="RTFacilityID" Type="String" />
    <asp:SessionParameter Name="RTTrustFundAcctID" SessionField="RTTrustFundAcctID" Type="String" />
    <asp:Parameter Name="txtToDate" Type="String" />
    <asp:Parameter Name="txtFromDate" Type="String" />
</SelectParameters>

2. 后台代码动态生成SQL和参数
   先生成对应数量的参数占位符，再将每个ResidentID作为参数添加到SqlDataSource的SelectParameters集合：

rptvwTransactionHistoryWoTot.LocalReport.DataSources.Clear();
ReportDataSource rds = new ReportDataSource();
DataSet dsReport = new StatementProcess();

// 假设residentIDs是存储目标ResidentID的数组/列表
List<string> paramNames = new List<string>();
for (int i = 0; i < residentIDs.Length; i++)
{
    paramNames.Add($"@RTResidentID{i}");
}
// 生成IN子句的占位符部分
string inClausePlaceholders = string.Join(", ", paramNames);

// 拼接完整SQL
string strSQL = $"****  Where (RT.ResidentID IN ({inClausePlaceholders})) And (RT.TrustFundAcctID = @RTTrustFundAcctID)  And RT.TransactionDate>= @txtFromDate And RT.TransactionDate<= @txtToDate And RT.DepositWithdrawalCode IN ('D','W') And RT.FacilityID = @RTFacilityID  Order by R.[LastName],RT.ResidentID";

// 清空SqlDataSource原有参数并添加动态参数
SqlDataSource1.SelectParameters.Clear();
for (int i = 0; i < residentIDs.Length; i++)
{
    SqlDataSource1.SelectParameters.Add(paramNames[i], DbType.String, residentIDs[i]);
}

// 添加固定参数
SqlDataSource1.SelectParameters.Add("@RTTrustFundAcctID", DbType.String, grdvwTrustFundAccount.Rows[intCnt].Cells[2].Text);
SqlDataSource1.SelectParameters.Add("@RTFacilityID", DbType.String, Session["FacilityID"].ToString());
SqlDataSource1.SelectParameters.Add("@txtFromDate", DbType.String, txtFromDate.Text);
SqlDataSource1.SelectParameters.Add("@txtToDate", DbType.String, txtToDate.Text);

// 设置SelectCommand并绑定数据
SqlDataSource1.SelectCommand = strSQL;
SqlDataSource1.DataBind();

// 后续报表绑定逻辑
using (SqlConnection conn = new SqlConnection(DBConnection.connectionString()))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(strSQL, conn))
    {
        // 给SqlCommand添加参数
        for (int i = 0; i < paramNames.Count; i++)
        {
            cmd.Parameters.AddWithValue(paramNames[i], residentIDs[i]);
        }
        cmd.Parameters.AddWithValue("@RTTrustFundAcctID", grdvwTrustFundAccount.Rows[intCnt].Cells[2].Text);
        cmd.Parameters.AddWithValue("@RTFacilityID", Session["FacilityID"].ToString());
        cmd.Parameters.AddWithValue("@txtFromDate", txtFromDate.Text);
        cmd.Parameters.AddWithValue("@txtToDate", txtToDate.Text);

        SqlDataAdapter adpReport = new SqlDataAdapter(cmd);
        dsReport = new DataSet("TransactionHistory_ResidentTransactions");
        adpReport.Fill(dsReport, "TransactionHistory_ResidentTransactions");

        rds.Name = "TransactionHistory_ResidentTransactions";
        rds.Value = dsReport.Tables[0];
        rptvwTransactionHistoryWoTot.LocalReport.DataSources.Add(rds);
        rptvwTransactionHistoryWoTot.LocalReport.DataSources[0].DataSourceId = "SqlDataSource1";
    }
    conn.Close();
}

方案二：使用表值参数（推荐，适合大量多值场景） #

更优雅的实现方式，避免生成大量动态参数：

1. 在SQL Server中创建用户定义表类型

CREATE TYPE dbo.ResidentIDList AS TABLE (ResidentID VARCHAR(50)) -- 根据实际字段类型调整

2. 调整标记中的SelectParameters
   添加一个类型为Object的参数用于接收表值：

<SelectParameters>
    <asp:SessionParameter Name="RTFacilityID" SessionField="RTFacilityID" Type="String" />
    <asp:SessionParameter Name="RTTrustFundAcctID" SessionField="RTTrustFundAcctID" Type="String" />
    <asp:Parameter Name="txtToDate" Type="String" />
    <asp:Parameter Name="txtFromDate" Type="String" />
    <asp:Parameter Name="ResidentIDs" Type="Object" />
</SelectParameters>

3. 修改SQL语句
   使用表值参数作为IN子句的数据源：

****  Where (RT.ResidentID IN (SELECT ResidentID FROM @ResidentIDs)) And (RT.TrustFundAcctID = @RTTrustFundAcctID)  And RT.TransactionDate>= @txtFromDate And RT.TransactionDate<= @txtToDate And RT.DepositWithdrawalCode IN ('D','W') And RT.FacilityID = @RTFacilityID  Order by R.[LastName],RT.ResidentID

4. 后台代码填充表值参数

rptvwTransactionHistoryWoTot.LocalReport.DataSources.Clear();
ReportDataSource rds = new ReportDataSource();
DataSet dsReport = new StatementProcess();

// 创建DataTable存储ResidentID
DataTable residentTable = new DataTable();
residentTable.Columns.Add("ResidentID", typeof(string)); // 匹配表类型的字段类型
foreach (string id in residentIDs)
{
    residentTable.Rows.Add(id);
}

// 配置SqlDataSource参数
SqlDataSource1.SelectParameters.Clear();
SqlDataSource1.SelectParameters.Add("@ResidentIDs", DbType.Object, residentTable);
SqlDataSource1.SelectParameters.Add("@RTTrustFundAcctID", DbType.String, grdvwTrustFundAccount.Rows[intCnt].Cells[2].Text);
SqlDataSource1.SelectParameters.Add("@RTFacilityID", DbType.String, Session["FacilityID"].ToString());
SqlDataSource1.SelectParameters.Add("@txtFromDate", DbType.String, txtFromDate.Text);
SqlDataSource1.SelectParameters.Add("@txtToDate", DbType.String, txtToDate.Text);

// 设置SelectCommand并绑定
string strSQL = "****  Where (RT.ResidentID IN (SELECT ResidentID FROM @ResidentIDs)) And (RT.TrustFundAcctID = @RTTrustFundAcctID)  And RT.TransactionDate>= @txtFromDate And RT.TransactionDate<= @txtToDate And RT.DepositWithdrawalCode IN ('D','W') And RT.FacilityID = @RTFacilityID  Order by R.[LastName],RT.ResidentID";
SqlDataSource1.SelectCommand = strSQL;
SqlDataSource1.DataBind();

// 报表绑定逻辑
using (SqlConnection conn = new SqlConnection(DBConnection.connectionString()))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(strSQL, conn))
    {
        // 添加表值参数
        SqlParameter tvpParam = cmd.Parameters.AddWithValue("@ResidentIDs", residentTable);
        tvpParam.SqlDbType = SqlDbType.Structured;
        tvpParam.TypeName = "dbo.ResidentIDList"; // 对应创建的表类型名称

        cmd.Parameters.AddWithValue("@RTTrustFundAcctID", grdvwTrustFundAccount.Rows[intCnt].Cells[2].Text);
        cmd.Parameters.AddWithValue("@RTFacilityID", Session["FacilityID"].ToString());
        cmd.Parameters.AddWithValue("@txtFromDate", txtFromDate.Text);
        cmd.Parameters.AddWithValue("@txtToDate", txtToDate.Text);

        SqlDataAdapter adpReport = new SqlDataAdapter(cmd);
        dsReport = new DataSet("TransactionHistory_ResidentTransactions");
        adpReport.Fill(dsReport, "TransactionHistory_ResidentTransactions");

        rds.Name = "TransactionHistory_ResidentTransactions";
        rds.Value = dsReport.Tables[0];
        rptvwTransactionHistoryWoTot.LocalReport.DataSources.Add(rds);
        rptvwTransactionHistoryWoTot.LocalReport.DataSources[0].DataSourceId = "SqlDataSource1";
    }
    conn.Close();
}

关键注意点 #

• 动态参数方案中，必须先将参数添加到SqlDataSource.SelectParameters集合，才能通过名称访问，否则会出现空引用错误。
• 不要用逗号分隔的字符串作为IN子句参数，SQL会将其视为单个字符串值，无法拆分匹配。
• 表值参数需要提前在SQL Server中创建对应的表类型，且参数类型要设置为Structured。

内容的提问来源于stack exchange，提问作者brianw921