Best practice to pass a list as input in SQL query using vb.net

Question

I am using vb.net with a SQL Server 2012 database.

I want to check the status of a list of files in the databases. The query is simple and something like this.

DECLARE @Filename NVARCHAR(100)

SET @Filename = 'MyFileName.doc'

SELECT DISTINCT 
    Docs.Filename,
    Status.Name as 'Status'
FROM 
    [Documents] AS Docs
INNER JOIN 
    [Status] AS Status ON Status.StatusID = Docs.CurrentStatusID
WHERE 
    Docs.Filename LIKE @Filename

It works fine for one filename and I can launch it easily in vb.net using a sql connection as in this example.

Dim conn As New SqlConnection
If conn.State = ConnectionState.Closed Then
    conn.ConnectionString = PDMConnectionString
End If

Try
    conn.Open()
    Dim sqlquery As String =
        "DECLARE @Filename NVARCHAR(100)
         SELECT DISTINCT 
         Docs.Filename,
         Status.Name as 'Status'

         FROM [Documents] AS Docs
         INNER JOIN [Status] AS Status
         ON Status.StatusID = Docs.CurrentStatusID

         WHERE Docs.Filename LIKE @Filename  "

    Dim data As SqlDataReader
    Dim adapter As New SqlDataAdapter
    Dim parameter As New SqlParameter
    Dim command As SqlCommand = New SqlCommand(sqlquery, conn)
    With command.Parameters
        .Add(New SqlParameter("@filename", "MyFileName.doc"))
    End With
    command.Connection = conn
    adapter.SelectCommand = command
    data = command.ExecuteReader()
    While data.Read
         'do something'
    End While
Catch ex As Exception

End Try

The problem is that I need to find the status of a lot of files and I would like to do it with only one query.

I can do it directly in the query by by changing the last line like this, removing the parameter in vb.net and sending directly the query:

WHERE 
    Docs.Filename IN ('MyFileName.doc', 'MyOtherFileName.doc')

But it implies a lot of string concatenation and I don't really like how the code looks like with that solution.

What is the best practice to use in that type of situation in order to use less string concatenation and to make a code that is easier to manage?

@Magnus, your suggestion for duplication have "wrong" solution. Correct approach will be Table valued parameter — Fabio, Aug 25 '17 at 14:15
Depends on what you mean by "a lot of string concatenation" Linq can do that sort of thing in just a few lines. I'd check if SQL Server will allow arrays as parameters though even as an XML list that you select from in a stored procedure — Ňɏssa Pøngjǣrdenlarp, Aug 26 '17 at 01:56

score 0 · Answer 1 · answered Aug 26 '17 at 12:02

You could use a function to take a comma separated string and return a table...

CREATE FUNCTION [dbo].[FileNames]( @FilenameValues nvarchar(max) )
RETURNS  @Result TABLE( FileName nvarchar(max) )
AS
BEGIN

        -- convert to an xml string
        DECLARE @xml XML 
        SELECT @xml = CAST( '<A>' + REPLACE( @FilenameValues, ',', '</A><A>' ) + '</A>' AS XML )

        -- select rows out of the xml string
        INSERT INTO @Result            
        SELECT DISTINCT LTRIM( RTRIM( t.value( '.', 'nvarchar(max)' ) ) ) AS [FileName]
        FROM @xml.nodes( '/A ') AS x(t)

        RETURN

   END

Then in your SQL either JOIN to it ...

JOIN (
    SELECT * FROM dbo.FileNames( 'MyFileName.doc, MyOtherFileName.doc' )
) FileNames ON FileNames.FileName = Docs.Filename

OR use in a WHERE ...

WHERE Docs.Filename IN( 
    SELECT * FROM dbo.FileNames( 'MyFileName.doc, MyOtherFileName.doc' )
)

Best practice to pass a list as input in SQL query using vb.net

1 Answers1