String List in SQLcommand Through Parameters in C#

String list in SqlCommand through Parameters in C#

What you are trying to do is possible but not using your current approach. This is a very common problem with all possible solutions prior to SQL Server 2008 having trade offs related to performance, security and memory usage.

This link shows some approaches for SQL Server 2000/2005

SQL Server 2008 supports passing a table value parameter.

I hope this helps.

Pass Liststring Into SQL Parameter

You can parameterize each value in the list in an IN clause:

List<string> names = new List<string> { "john", "brian", "robert" };
string commandText = "DELETE FROM Students WHERE name IN ({0})";
string[] paramNames = names.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (var command = new SqlCommand(string.Format(commandText, inClause), con))
{
    for (int i = 0; i < paramNames.Length; i++)
    {
        command.Parameters.AddWithValue(paramNames[i], names[i]);
    }
    int deleted = command.ExecuteNonQuery();
} 

which is similar to:

"... WHERE Name IN (@tag0,@tag1,@tag2)"

command.Parameters["@tag0"].Value = "john";
command.Parameters["@tag1"].Value = "brian";
command.Parameters["@tag2"].Value = "robert";

Adapted from: https://stackoverflow.com/a/337792/284240

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.

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]));
}

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.

Get Parameters of sqlcommand to string

cmdMarineResort.Parameters is a SqlParameterCollection.

You can iterate through it using a foreach statement:

   foreach(SqlParameter Para in cmdMarineResort.Parameters)
    {
       Console.WriteLine((string)Para.Value); //value of the parameter as an object
    }

Passing Listint as Query Parameter to SQL Server

In order to pass array/list for IN, you have to create one parameter for each value in the list.

try
    {
        SqlCommand _myCommand_4 = _con.CreateCommand();

        List<string> sqlParams = new List<string>();
        int i = 0;
        foreach(var value in _Node_Neighbor){
            var name = "@p"  + i++;
            _myCommand_4.Parameters.Add(name,value);
            sqlParams.Add(name);
        }

        string paramNames = string.Join(",", sqlParams);

        _myCommand_4.CommandText = "SELECT COUNT(*) FROM GraphEdges"
                       " WHERE Source_Node IN (" + paramNames + ") " 
                         " AND Target_Node IN (" + paramNames + ")";

        _Mutual_Links = Convert.ToInt32(_myCommand_4.ExecuteScalar());
    }
    catch(Exception e)
    {
        Console.WriteLine(e.Message);
    }

---

Related Topics