Delete records with "in" clause using ado.net

Question

Below is the code, I am trying to execute using ado.net. What am I doing wrong?

    Trying to delete records using ado.net 

    private const string SqlDeleteMultiple = @"        
    DELETE FROM [Board] 
    WHERE 
    [Id] in (@Ids)
    ";

    public void DeleteMultiple(string items)
        {
            ArgumentValidator.ThrowOnNull("item", items);

            /*   var sbItems = new StringBuilder();

               sbItems.Append("'");
               sbItems.Append(items);
               sbItems.Replace(",", "','");
               sbItems.Append("'");*/


            try
            {
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    using (var cmd = new SqlCommand( SqlDeleteMultiple,conn))
                    {
                        cmd.Parameters.Add(new SqlParameter("@Ids", SqlDbType.VarChar) {Value = items});
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception err)
            {
                _logger.Log(LogLevel.Error, err, "Error calling Delete");
                throw err;
            }
        }

Conversion failed when converting the varchar value '9,8' to data type int. If I change SqlDbType to Int.. I get an another error.

Answer 1

your @Ids contains a list of values but you provide only one SqlParameter ... add a SqlParameter for each value in the list and it should work

you could also use a table valued parameter to pass the list to a stored procedure ... but you need to put your delete statement into a stored procedure and create a user defined type on the server side ...