How to Translate a List<String> into a SQLparameter for a SQL in Statement

How do I translate a Liststring into a SqlParameter for a Sql In statement?

You could try something like this:

string sql = "SELECT dscr FROM system_settings WHERE setting IN ({0})";
string[] paramArray = settingList.Select((x, i) => "@settings" + i).ToArray();
cmd.CommandText = string.Format(sql, string.Join(",", paramArray));

for (int i = 0; i < settingList.Count; ++i)
{
    cmd.Parameters.Add(new SqlParameter("@settings" + i, settingList[i]));
}

SqlParameter and IN statement

I use a helper method like this

/// <summary>
/// Adds a sequence of parameters to an existing parameter collection
/// </summary>
/// <typeparam name="T">Type of parameter values</typeparam>
/// <param name="parameters">Existing parameter collection</param>
/// <param name="pattern">Name pattern of parameters. Must be a valid <see langword="int"/> format string</param>
/// <param name="parameterType">Database type of parameters</param>
/// <param name="length">Length of parameter. 0 for numeric parameters</param>
/// <param name="values">Sequence of values</param>
/// <returns>Comma separated string of parameter names</returns>
public static string AddParameters<T>(SqlParameterCollection parameters,
                                      string pattern,
                                      SqlDbType parameterType,
                                      int length,
                                      IEnumerable<T> values) {
    if (parameters == null)
        throw new ArgumentNullException("parameters");
    if (pattern == null)
        throw new ArgumentNullException("pattern");
    if (values == null)
        throw new ArgumentNullException("values");
    if (!pattern.StartsWith("@", StringComparison.CurrentCultureIgnoreCase))
        throw new ArgumentException("Pattern must start with '@'");

    var parameterNames = new List<string>();
    foreach (var item in values) {
        var parameterName = parameterNames.Count.ToString(pattern, CultureInfo.InvariantCulture);
        parameterNames.Add(parameterName);
        parameters.Add(parameterName, parameterType, length).Value = item;
    }

    return string.Join(",", parameterNames.ToArray());
}

It is used like this

string sql = "SELECT col1 " +
             "FROM Table " +
             "WHERE col2 IN ({@Values}) ";
var paramNames = SqlHelper.AddParameters(command.Parameters,
                                         "@Value0",
                                         SqlDbType.Int,
                                         0,
                                         listOfInts);
command.CommandText = sql.Replace("{@Values}", paramNames);
...

(Result is a query like SELECT ... IN (@Value0, @Value1, @Value2))

Use string of numbers as SqlParameter to SQL query resource with IN Operator

Finally , the only way is to convert string to List:

List<string> p_Ids= p_strValores.Split(',').ToList();

Then convert each value in the List to int and add them to an aux List e.g. aux_p_Ids, then use it in the sql query:

UPDATE tbl_Datos SET col_Leyenda = (select col_Id from tbl_Leyenda where col_Leyenda = @p_Leyenda) WHERE col_Id in aux_p_Ids

Pass Array Parameter in SqlCommand

You will need to add the values in the array one at a time.

var parameters = new string[items.Length];
var cmd = new SqlCommand();
for (int i = 0; i < items.Length; i++)
{
    parameters[i] = string.Format("@Age{0}", i);
    cmd.Parameters.AddWithValue(parameters[i], items[i]);
}

cmd.CommandText = string.Format("SELECT * from TableA WHERE Age IN ({0})", string.Join(", ", parameters));
cmd.Connection = new SqlConnection(connStr);

UPDATE: Here is an extended and reusable solution that uses Adam's answer along with his suggested edit. I improved it a bit and made it an extension method to make it even easier to call.

public static class SqlCommandExt
{

    /// <summary>
    /// This will add an array of parameters to a SqlCommand. This is used for an IN statement.
    /// Use the returned value for the IN part of your SQL call. (i.e. SELECT * FROM table WHERE field IN ({paramNameRoot}))
    /// </summary>
    /// <param name="cmd">The SqlCommand object to add parameters to.</param>
    /// <param name="paramNameRoot">What the parameter should be named followed by a unique value for each value. This value surrounded by {} in the CommandText will be replaced.</param>
    /// <param name="values">The array of strings that need to be added as parameters.</param>
    /// <param name="dbType">One of the System.Data.SqlDbType values. If null, determines type based on T.</param>
    /// <param name="size">The maximum size, in bytes, of the data within the column. The default value is inferred from the parameter value.</param>
    public static SqlParameter[] AddArrayParameters<T>(this SqlCommand cmd, string paramNameRoot, IEnumerable<T> values, SqlDbType? dbType = null, int? size = null)
    {
        /* An array cannot be simply added as a parameter to a SqlCommand so we need to loop through things and add it manually. 
         * Each item in the array will end up being it's own SqlParameter so the return value for this must be used as part of the
         * IN statement in the CommandText.
         */
        var parameters = new List<SqlParameter>();
        var parameterNames = new List<string>();
        var paramNbr = 1;
        foreach (var value in values)
        {
            var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
            parameterNames.Add(paramName);
            SqlParameter p = new SqlParameter(paramName, value);
            if (dbType.HasValue)
                p.SqlDbType = dbType.Value;
            if (size.HasValue)
                p.Size = size.Value;
            cmd.Parameters.Add(p);
            parameters.Add(p);
        }

        cmd.CommandText = cmd.CommandText.Replace("{" + paramNameRoot + "}", string.Join(",", parameterNames));

        return parameters.ToArray();
    }

}

It is called like this...

var cmd = new SqlCommand("SELECT * FROM TableA WHERE Age IN ({Age})");
cmd.AddArrayParameters("Age", new int[] { 1, 2, 3 });

Notice the "{Age}" in the sql statement is the same as the parameter name we are sending to AddArrayParameters. AddArrayParameters will replace the value with the correct parameters.

Parameterize an SQL IN clause

Here's a quick-and-dirty technique I have used:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

So here's the C# code:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Two caveats:

• The performance is terrible. LIKE "%...%" queries are not indexed.
• Make sure you don't have any |, blank, or null tags or this won't work

There are other ways to accomplish this that some people may consider cleaner, so please keep reading.

How to pass a list of strings a sql parameter for the stored procedure to delete?

As far as I can see you are passing list of names separated by comma and you want to delete all those names. You need to use IN operator to find all holiday names that should be deleted.

Here is an example how to do it for @SelectedHolidays:

declare @SelectedHolidays nvarchar(500) = 'H1,H2,H3'
declare @SelectedHolidaysXml xml = cast(replace(N'<R><I>' + @SelectedHolidays + N'</I></R>', ',', '</I><I>') as xml)

DELETE FROM [SessionHolidayMapping]
WHERE [HolidayName] in (select x.items.value('(.)[1]', 'NVARCHAR(500)') from @SelectedHolidaysXml.nodes('/R/I') as x(items))
AND [SessionId] = @SessionId

It is ugly, but I don't know of better way to split comma separated values in sql server.

How to do SQL query with string parameter value in C#

don't use quotes in the sql, and don't use @ in the parameter

public IEnumerable<Asiakas> GetByName(string arvo)
        {
            string sql = "SELECT ASNRO, YRITYS, SUKUNIMI, ETUNIMI, LAHIOSOITE, POSTITP, POSTINRO " +
                         "FROM dbo.ASIAKAS " +
                         "WHERE YRITYS LIKE @KIRJAIN " +
                         "ORDER BY ASNRO";
            using (var cmd = Context.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.Parameters.Add(new SqlParameter("KIRJAIN", arvo));
                var tulos = ToList(cmd);
                return tulos;
            }
        }

---

Related Topics