Creating SqlDataSource SelectParameters for IN Clause Parameterized queries

Question

I am trying to create a SqlDataSource SelectParameters for my IN clause parameterized queries. I can successfully parameterize. I was wondering if there is any way I can do it properly?

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();
}

Here is my SelectParameters in my markup

<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>

The sqlString in SqlDataSource1.SelectCommand = 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

I've tried creating another for loop and assigning it, but I don't know how to declare the parameter in my markup. I get an error

Object reference not set to an instance of an object

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

I also tried declaring a ParameterList and inputting all my includes there, but that also didn't work.

SqlDataSource1.SelectParameters["RTResidentIDList"].DefaultValue = inclause
// IN clause would be return something like "RTResidentID0, RTResidentID1..."

<asp:Parameter Name="RTResidentIDList" Type="String" />

Some recommended reading: AddWithValue is Evil

Thom A
– Thom A ♦

2022-12-20 21:33:23 +00:00
Commented Dec 20, 2022 at 21:33 — Thom A
– Thom A ♦, Commented Dec 20, 2022 at 21:33

Jens K. Süßmeyer · Accepted Answer · 2022-12-20 21:48:27Z

-1

You would either add them as literals (be careful,prone to SQLInjection) in there or maybe think about passing a TVP instead.

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

answered Dec 20, 2022 at 21:48

Jens K. Süßmeyer

715 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating SqlDataSource SelectParameters for IN Clause Parameterized queries

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related