为IN子句参数化查询创建SqlDataSource SelectParameters

Question

我试图为我的IN子句参数化查询创建SqlDataSourceSelectParameters。我可以成功地参数化。我想知道是否有什么方法可以把它做好?

rptvwTransactionHistoryWoTot.LocalReport.DataSources.Clear();
ReportDataSource rds = new ReportDataSource();
DataSet dsReport = new StatementProcess();
string strSQL = "****  Where (RT.ResidentID IN (@RTResidentID0, @RTResidentID1)) 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";
using (SqlConnection conn = new SqlConnection(DBConnection.connectionString()))
{
conn.Open();
var sqlString = string.Format(strSQL, inClause);
using (SqlCommand cmd = new SqlCommand(sqlString, conn))
{
lstResidents.Items[intCnt].Value.ToString()); //ResidentID
for (int i = 0; i < paramNames.Length; i++)
{
cmd.Parameters.AddWithValue(paramNames[i], residentIDs[i]);
}
cmd.Parameters.AddWithValue("@RTTrustFundAcctID", grdvwTrustFundAccount.Rows[intCnt].Cells[2].Text); //TrustFundAccountID
cmd.Parameters.AddWithValue("@RTFacilityID", Session["FacilityID"].ToString()); //RTFacilityID
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";
// for (int i = 0; i < paramNames.Length; i++)
// {
//    var residentID = paramNames[i];
//    var test2 = residentIDs[i];
//    SqlDataSource1.SelectParameters[residentID].DefaultValue = test2;
// }
SqlDataSource1.SelectParameters["RTResidentID"].DefaultValue = lstResidents.Items[intCnt].Value.ToString();
SqlDataSource1.SelectParameters["RTFacilityID"].DefaultValue = Session["FacilityID"].ToString();
SqlDataSource1.SelectParameters["RTTrustFundAcctID"].DefaultValue = grdvwTrustFundAccount.Rows[intCnt].Cells[2].Text;
SqlDataSource1.SelectParameters["txtToDate"].DefaultValue = txtToDate.Text;
SqlDataSource1.SelectParameters["txtFromDate"].DefaultValue = txtFromDate.Text;
SqlDataSource1.SelectCommand = sqlString;
SqlDataSource1.DataBind();
}
conn.Close();
}

这是我的SelectParameters在我的标记

<SelectParameters>
<asp:SessionParameter Name="RTResidentID" SessionField="RTResidentID" Type="String" />
<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="RTResidentIDList" Type="String" />
</SelectParameters>

SqlDataSource1.SelectCommand = sqlString;中的sqlString:

****  WHERE (RT.ResidentID IN (@RTResidentID0, @RTResidentID1)) 
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 

我已经尝试创建另一个for循环并分配它，但我不知道如何在我的标记中声明参数。我得到一个错误

对象引用未设置为对象的实例

// for (int i = 0; i < paramNames.Length; i++)
// {
//    var residentID = paramNames[i]; 
//    var test2 = residentIDs[i];
//    SqlDataSource1.SelectParameters[residentID].DefaultValue = test2;
// }

我还尝试声明一个ParameterList并在那里输入我所有的include，但这也不起作用。

SqlDataSource1.SelectParameters["RTResidentIDList"].DefaultValue = inclause
// IN clause would be return something like "RTResidentID0, RTResidentID1..."
<asp:Parameter Name="RTResidentIDList" Type="String" />

Answer 1

您可以将它们作为字面量(要小心，容易出现sqlinject)添加到那里，或者可以考虑传递TVP。

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters passing-a-table-valued-parameter-to-a-parameterized-sql-statement