How to create a SQL query in vb.net with 'select ...from ..where id in (list)' syntax

Question

at the first glance my problem looks simple, still I can't tackle it. I have to do some corrections to an ASP.NET program a document mangement system, with its backendcode written in vb. The user in my specific case can choose several documents from a list, which can then be processed on a different webpage. So the ids of those documents are put into a sessionvariable and on the new page a query concerning only those ids is done. The implementation is quite horrid:

 For Each Doks As String In Session.Item("choosenDocuments")
   strSQL = "SELECT [...] FROM Dokument where DokumentID = " & Doks
   cmd = New SqlCommand(strSQL, conDatabase)
   dr = cmd.ExecuteReader
   dr.Read()
   [...]
   dr.Close()
 Next

I thought of using a parameterized statement, to create something like:

SELECT * FROM DOCUMENT WHERE DokumentID in (5,78,456)

But I fail to see, how to achive this programatically. Is is what i came up with so far:

Private Sub display(ByVal argument As IEnumerable(Of Integer))
  Try
    Dim result As New DataTable()
      Using con As SqlConnection = getCon() 
        Dim querystring As String = "select * from Dokuments where " &
            "GPDokumenteID in @id"
          Using command As New SqlCommand(querystring, con)
            command.Parameters.Add("@id", SqlDbType.Structured)
            command.Parameters("@id").Value = argument
            Using adapter As New SqlDataAdapter
              adapter.SelectCommand = command
              adapter.Fill(result)
            End Using
          End Using
       End Using
       For Each dr As DataRow In result.Rows
         Console.WriteLine(dr(0).ToString() & "  " & dr(1).ToString())
       Next
    Catch ex As Exception
        Console.WriteLine("BOOM: " & ex.Message)
    End Try
End Sub

This code fails at runtime, complaining that integer arrays cant be cast in IEnumerable.

I have found some sources like this or this but I still fail to construct a simple query example from that. Is my thinking to simple? Since there is such an elegant way to fill a SQL Query from a list I thought there would be a way to use this programatically.

Kind regards

Lorgarn

score 1 · Accepted Answer · answered Jan 20 '14 at 14:46

1

You could do it with a table-value parameter or by creating a temporary table, but concatenating SQL is easier:

    Dim querystring As String = "select * from Dokuments where " &
        "GPDokumenteID in ({0})"

    querystring = string.Format(querystring, String.Join(",",argument)

    Using command As New SqlCommand(querystring, con)
        Using adapter As New SqlDataAdapter
          adapter.SelectCommand = command
          adapter.Fill(result)
        End Using
    End Using

Note - this is safe from SQL injection since you're only using integers - if you were using strings (in which malicious SQL code could be injected) then I would recommend biting the bullet and using table-valued parameters.

answered Jan 20 '14 at 14:46

D Stanley

149,601
11
178
240

Thanks for this approach. Just out of curiosity and to have a global solution (just in case I have to to this with a String array): How would your table-value parameter solution look like? – Lorgarn Jan 20 '14 at 15:02
@Lorgarn you'd need to create a structured type on the server side, create a stored procedure (you can't do it with raw SQL), then set the value of the parameter to a `DataTable` on the VB side. – D Stanley Jan 20 '14 at 15:05
Many thanks. So I will keep that in mind and refer to your first solution in this case. – Lorgarn Jan 20 '14 at 15:17

How to create a SQL query in vb.net with 'select ...from ..where id in (list)' syntax

1 Answers1