Pass an array as value in an ado.net DBParameter

For SQL-Server, there are two common approaches for this. The third option to be avoided is to pass in a varchar and concatenate it into a dynamic SQL statement with IN - this is a clear injection attack surface.

Reasonable options:

• pass in a varchar and use a UDF to split the data on a delimiter (like in this question), perhaps comma, pipe, tab, etc. Join to the result:

  SELECT something, anotherthing
  FROM atable a
  INNER JOIN dbo.SplitUDF(@values) udf
          ON udf.Value = a.something
  

• use a table-valued-parameter (SQL2008) and join directly (avoid the UDF)

Have a look at these articles

• MSSQL Limitations and Arrays
• Passing Arrays in SQL Parameters using XML Data Type in SQL Server 2005
• Since there is no Sqlserver array parameter, what’s the best way to proceed?
• Arrays and Lists in SQL Server 2005

This is an example of using the XML type to create a list

--Split
DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX), 
        @delimiter NVARCHAR(5)

SELECT  @data = 'A,B,C',
        @delimiter = ','

SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM    @textXML.nodes('/d') T(split)

This code does the trick. You can create your own BuildQuery(???) function.

    public void RemoveDependencies(int versionID, int[] deps)
    {
        if (versionID <= 0)
            throw new ArgumentException();
        if (deps == null)
            throw new ArgumentNullException();
        if (deps.Length <= 0)
            throw new ArgumentException();

        SqlCommand cmd = new SqlCommand();
        string query = "DELETE FROM Dependencies WHERE version_id = @VersionId AND dep_version_id IN (";
        int n = deps.Length;
        string key;
        for (int i = 0; i < n; i++)
        {
            if (deps[i] <= 0)
                throw new ArgumentException();
            key = String.Format("@dep{0}", i);
            query += key;
            cmd.Parameters.AddWithValue(key, deps[i]);
            if (i < n - 1)
            {
                 query += ", ";
            }
        }
        query += ")";
        cmd.Parameters.AddWithValue("@VersionId", versionID);
        cmd.CommandText = query;
        using (SqlConnection con = GetSqlConnection())
        {
            con.Open();
            cmd.Connection = con;
            if (cmd.ExecuteNonQuery() <= 0)
            {
                throw new ArgumentException("No rows affected! Illegal id.");
            }
        }
    }