Passing arrays to SQL statements safely

SQL Parameters only accepts scalar values. What if you wanted to pass an array as an IN criteria?

Here’s a workaround, without using stored procs. Just IFormatter magic.

You will be able to to this:

Dim ids(0 to 3) as integer
ids(0) = 32
ids(1) = 34
ids(2) = 42
ids(3) = 48
statement = SqlFormatter.Format("SELECT * FROM Persons WHERE PersonId in {0:f0}", ids)

Public Class SqlFormatter
    Implements IFormatProvider, ICustomFormatter

    Public Shared Function Format(ByVal formatString, ByVal ParamArray args()) As String
        Return String.Format(New SqlFormatter(), formatString, args)
    End Function

    Public Function GetFormat(ByVal formatType As System.Type) As Object Implements System.IFormatProvider.GetFormat
        If formatType Is GetType(ICustomFormatter) Then
            Return Me
        Else
            Return Nothing
        End If
    End Function

    Public Function Format(ByVal fmt As String, ByVal arg As Object, ByVal formatProvider As System.IFormatProvider) As String Implements System.ICustomFormatter.Format
        Dim thisFmt As String = String.Empty
        If Not String.IsNullOrEmpty(fmt) Then
            thisFmt = CStr(If(fmt.Length > 1, fmt.Substring(0, 1), fmt))
        End If

        If Not TypeOf (arg) Is String AndAlso TypeOf (arg) Is IEnumerable Then
            Dim sb As New List(Of String)
            If fmt.ToUpper.Equals("N") Then fmt = "f"
            For Each obj In arg
                If obj Is Nothing Then
                    sb.Add("Null")
                Else
                    sb.Add(String.Format(Me, "{0:" + fmt + "}", obj))
                End If
            Next
            Return "(" + String.Join(", ", sb) + ")"
        Else
            Dim formatted As String
            Select Case thisFmt.ToUpper()
                Case "C"
                    ' Escape strings
                    formatted = "'" + CType(arg, String).Replace("'", "''") + "'"
                Case "D"
                    ' Date
                    formatted = "'" + String.Format("{0:Y}-{0:M}-{0:D}", arg) + "'"
                Case Else
                    formatted = HandleOtherFormats(fmt, arg)
            End Select
            Return formatted
        End If

    End Function

    Private Function HandleOtherFormats(ByVal fmt As String, ByVal arg As Object) As String
        If TypeOf arg Is IFormattable Then
            Return DirectCast(arg, IFormattable).ToString(fmt, CultureInfo.CurrentCulture)
        ElseIf arg IsNot Nothing Then
            Return arg.ToString()
        Else
            Return String.Empty
        End If
    End Function
End Class