Creating Dynamic SelectParameters in my Code instead of Markup for SqlDataSource?

Question

I can properly parameterize my SQL query using IN Clause using a for loop. I have trouble properly implementing the same query in my SQLDataSource. I have to literally declare the SelectParameters in my SQLDataSource markup in order for it to work properly. That limits my reference to the number of parameters that I declare. Is there a way for me to set the SelectParameters dynamically?


for (int i = 0; i < paramNamesTwo.Length; i++)
{
   var residentID = paramNamesTwo[i];
   var test2 = residentIDs[i];
//This line declares the markup I had for only 2
   SqlDataSource1.SelectParameters[residentID].DefaultValue = test2;
}

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BANKMDFConnectionString %>"
    SelectCommand="********">
    <SelectParameters>
        <asp:SessionParameter Name="RTResidentID" SessionField="RTResidentID" Type="String" />
//I declared it manually here twice, so I am only limited to 2 selections
        <asp:SessionParameter Name="RTResidentID0" SessionField="RTResidentID0" Type="String" />
        <asp:SessionParameter Name="RTResidentID1" SessionField="RTResidentID1" 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" />
    </SelectParameters>
</asp:SqlDataSource>

Didn´t you already asked the same thing here? stackoverflow.com/questions/74868930/… — Jens K. Süßmeyer
– Jens K. Süßmeyer, Commented Dec 21, 2022 at 7:52

T McKeown · Accepted Answer · 2022-12-21 18:14:42Z

Why use the SQLDataSource? Why not code it using ADO.NET? This seems limiting.

You should create a SQL server datatype that is a DataTable, then you can pass a data table as a parameter and treat as another table. Your question is based on ASP markup, this would require a bit more code-behind, but is much more capable and easier to code the SQL.

Create a data table type:

CREATE TYPE [dbo].[LookupTableType] AS TABLE(
[Key] [int] NOT NULL,
[Desc] [nvarchar](20) NULL

)

Now you can specify this type in a stored procedure, if you want to run adhoc that is fine, but you will likely need to create the type, use it and then drop the type all in one operation, using a stored procedure is easier.

CREATE PROCEDURE [dbo].[Lookups_Test](
@Lookups        [dbo].[LookupTableType] READONLY    
)
AS

SELECT * FROM @Lookups
RETURN

In C# you would need specify the custom type parameter, and then you could send a System.Data.DataTable with matching column definition to the SQL server to process.

SqlParameter sqlParam = cmd.Parameters.AddWithValue("@Lookups", myTable);
sqlParam.SqlDbType = SqlDbType.Structured;

Now you have the ability to simply JOIN on the @Lookups table in your sproc or adhoc SQL.

Collectives™ on Stack Overflow

Creating Dynamic SelectParameters in my Code instead of Markup for SqlDataSource?

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