How to Parameterized Queries with the SqlDataSource (VB)

Question

This is my code example to run parameterized query in VB.NET:

    Dim sqlconn As New SqlConnection(connectionString)
    sqlconn.Open()

    Dim cmd As New SqlCommand
    cmd.CommandText = "Select * from TAble1 Where SkuCode in (@SKU)"
    cmd.Connection = sqlconn

    Dim parm As New SqlParameter     
    parm.Value ="1" 'This is working
    parm.ParameterName = "@SKU"
    cmd.Parameters.Add(parm)

    Dim ds As New DataSet
    Dim sqlDa As New SqlDataAdapter(cmd)
    sqlDa.Fill(ds)

    Dim dt As DataTable
    dt = ds.Tables(0)

    If dt.Rows.Count > 0 Then
        MsgBox("Done")
    Else
        MsgBox("Not done.")
    End If

If I run this example in VB.NET this returns the result successfully.

But there is an issue while trying to get results with multiple in records... this is not working.

Please check and suggest the change we have to do to run in query with parameters.

'parm.Value = "N'1', N'2'"   'this does not work.
'parm.Value = "'1','2'"   'this does not work.

I have tried these parameter value but it does not work.

Answer 1

SQL parameters are scalar and only accept a single value. You can use the sqldbtype.Structured though it gets a bit complicated.

I've found that if you need to pass in a set of parameters for an IN where the number of parameters is dynamics, the most effective way (unfortunately) is:

1. String interpolation/concatenation without parameters
2. Loop to build out the parameters and add them to your sqlcommand
3. LINQ to build out the parameters and add them to your sqlcommand

I've provided an example of the linq option below.

        Dim sqlParams As Dictionary(Of String, Integer) = integers.ToDictionary(Function(i) $"@ParamValue{i}", Function(i) i)
        Dim ds As New DataSet
        Dim dt As DataTable


        Using db as new SqlConnection(conn)
            conn.open()
            Using cmd As New SqlCommand($"SELECT * FROM Table1 WHERE SkuCode IN (-1, {String.Join(", ", sqlParams.Select(Function(f) f.Key))}", db)
                cmd.Parameters.AddRange(sqlParams.Select(Function(f) New SqlParameter(f.Key, SqlDbType.BigInt).Value = f.Value).ToArray())

                Dim sqlDa As New SqlDataAdapter(cmd)
                sqlDa.Fill(ds)
                dt = ds.Tables(0)
            End Using
        End Using

        MsgBox(If(dt.Rows.Count > 0, "Done", "Not done"))