I am trying to pass array parameter to SQL commnd in C# like below, but it does not work. Does anyone meet it before?
string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)";
SqlConnection sqlCon = new SqlConnection(connectString);
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlCon;
sqlComm.CommandType = System.Data.CommandType.Text;
sqlComm.CommandText = sqlCommand;
sqlComm.CommandTimeout = 300;
sqlComm.Parameters.Add("@Age", SqlDbType.NVarChar);
StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
if (item.Selected)
{
sb.Append(item.Text + ",");
}
}
sqlComm.Parameters["@Age"].Value = sb.ToString().TrimEnd(',');
Here is a minor variant of Brian's answer that someone else may find useful. Takes a List of keys and drops it into the parameter list.
//keyList is a List<string>
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
string sql = "SELECT fieldList FROM dbo.tableName WHERE keyField in (";
int i = 1;
foreach (string key in keyList) {
sql = sql + "@key" + i + ",";
command.Parameters.AddWithValue("@key" + i, key);
i++;
}
sql = sql.TrimEnd(',') + ")";
I wanted to expand on the answer that Brian contributed to make this easily usable in other places.
/// <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 (returnValue))
/// </summary>
/// <param name="sqlCommand">The SqlCommand object to add parameters to.</param>
/// <param name="array">The array of strings that need to be added as parameters.</param>
/// <param name="paramName">What the parameter should be named.</param>
protected string AddArrayParameters(SqlCommand sqlCommand, string[] array, string paramName)
{
/* 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 string[array.Length];
for (int i = 0; i < array.Length; i++)
{
parameters[i] = string.Format("@{0}{1}", paramName, i);
sqlCommand.Parameters.AddWithValue(parameters[i], array[i]);
}
return string.Join(", ", parameters);
}
You can use this new function as follows:
SqlCommand cmd = new SqlCommand();
string ageParameters = AddArrayParameters(cmd, agesArray, "Age");
sql = string.Format("SELECT * FROM TableA WHERE Age IN ({0})", ageParameters);
cmd.CommandText = sql;
public static class Extensions
{
public static void AddArrayParameters<T>(this SqlCommand cmd, string name, IEnumerable<T> values)
{
name = name.StartsWith("@") ? name : "@" + name;
var names = string.Join(", ", values.Select((value, i) => {
var paramName = name + i;
cmd.Parameters.AddWithValue(paramName, value);
return paramName;
}));
cmd.CommandText = cmd.CommandText.Replace(name, names);
}
}
You can then use this extension method as follows:
var ageList = new List<int> { 1, 3, 5, 7, 9, 11 };
var cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE Age IN (@Age)";
cmd.AddArrayParameters("Age", ageList);
Make sure you set the CommandText before calling AddArrayParameters.
Also make sure your parameter name won't partially match anything else in your statement (i.e. @AgeOfChild)
Use .AddWithValue(), So:
sqlComm.Parameters.AddWithValue("@Age", sb.ToString().TrimEnd(','));
Alternatively, you could use this:
sqlComm.Parameters.Add(
new SqlParameter("@Age", sb.ToString().TrimEnd(',')) { SqlDbType = SqlDbType. NVarChar }
);
Your total code sample will look at follows then:
string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)";
SqlConnection sqlCon = new SqlConnection(connectString);
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlCon;
sqlComm.CommandType = System.Data.CommandType.Text;
sqlComm.CommandText = sqlCommand;
sqlComm.CommandTimeout = 300;
StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
if (item.Selected)
{
sb.Append(item.Text + ",");
}
}
sqlComm.Parameters.AddWithValue("@Age", sb.ToString().TrimEnd(','));
// OR
// sqlComm.Parameters.Add(new SqlParameter("@Age", sb.ToString().TrimEnd(',')) { SqlDbType = SqlDbType. NVarChar });
If you are using MS SQL Server 2008 and above you can use table-valued parameters like described here http://www.sommarskog.se/arrays-in-sql-2008.html.
The following command creates a table type for integers:
create type int32_id_list as table (id int not null primary key)
public static SqlCommand AddParameter<T>(this SqlCommand command, string name, IEnumerable<T> ids)
{
var parameter = command.CreateParameter();
parameter.ParameterName = name;
parameter.TypeName = typeof(T).Name.ToLowerInvariant() + "_id_list";
parameter.SqlDbType = SqlDbType.Structured;
parameter.Direction = ParameterDirection.Input;
parameter.Value = CreateIdList(ids);
command.Parameters.Add(parameter);
return command;
}
private static DataTable CreateIdList<T>(IEnumerable<T> ids)
{
var table = new DataTable();
table.Columns.Add("id", typeof (T));
foreach (var id in ids)
{
table.Rows.Add(id);
}
return table;
}
cmd.CommandText = "select * from TableA where Age in (select id from @age)";
cmd.AddParameter("@age", new [] {1,2,3,4,5});
Passing an array of items as a collapsed parameter to the WHERE..IN clause will fail since query will take form of WHERE Age IN ("11, 13, 14, 16").
But you can pass your parameter as an array serialized to XML or JSON:
nodes() method:StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
if (item.Selected)
sb.Append("<age>" + item.Text + "</age>"); // actually it's xml-ish
sqlComm.CommandText = @"SELECT * from TableA WHERE Age IN (
SELECT Tab.col.value('.', 'int') as Age from @Ages.nodes('/age') as Tab(col))";
sqlComm.Parameters.Add("@Ages", SqlDbType.NVarChar);
sqlComm.Parameters["@Ages"].Value = sb.ToString();
OPENXML method:using System.Xml.Linq;
...
XElement xml = new XElement("Ages");
foreach (ListItem item in ddlAge.Items)
if (item.Selected)
xml.Add(new XElement("age", item.Text);
sqlComm.CommandText = @"DECLARE @idoc int;
EXEC sp_xml_preparedocument @idoc OUTPUT, @Ages;
SELECT * from TableA WHERE Age IN (
SELECT Age from OPENXML(@idoc, '/Ages/age') with (Age int 'text()')
EXEC sp_xml_removedocument @idoc";
sqlComm.Parameters.Add("@Ages", SqlDbType.Xml);
sqlComm.Parameters["@Ages"].Value = xml.ToString();
That's a bit more on the SQL side and you need a proper XML (with root).
OPENJSON method (SQL Server 2016+):using Newtonsoft.Json;
...
List<string> ages = new List<string>();
foreach (ListItem item in ddlAge.Items)
if (item.Selected)
ages.Add(item.Text);
sqlComm.CommandText = @"SELECT * from TableA WHERE Age IN (
select value from OPENJSON(@Ages))";
sqlComm.Parameters.Add("@Ages", SqlDbType.NVarChar);
sqlComm.Parameters["@Ages"].Value = JsonConvert.SerializeObject(ages);
Note that for the last method you also need to have Compatibility Level at 130+.
I want to propose another way, how to solve limitation with IN operator.
For example we have following query
select *
from Users U
WHERE U.ID in (@ids)
We want to pass several IDs to filter users. Unfortunately it is not possible to do with C# in easy way. But I have fount workaround for this by using "string_split" function. We need to rewrite a bit our query to following.
declare @ids nvarchar(max) = '1,2,3'
SELECT *
FROM Users as U
CROSS APPLY string_split(@ids, ',') as UIDS
WHERE U.ID = UIDS.value
Now we can easily pass one parameter enumeration of values separated by comma.
Overview: Use the DbType to set the parameter type.
var parameter = new SqlParameter();
parameter.ParameterName = "@UserID";
parameter.DbType = DbType.Int32;
parameter.Value = userID.ToString();
var command = conn.CreateCommand()
command.Parameters.Add(parameter);
var reader = await command.ExecuteReaderAsync()
If you can use a tool like "dapper", this can be simply:
int[] ages = { 20, 21, 22 }; // could be any common list-like type
var rows = connection.Query<YourType>("SELECT * from TableA WHERE Age IN @ages",
new { ages }).ToList();
Dapper will handle unwrapping this to individual parameters for you.
Since there is a method on
SqlCommand.Parameters.AddWithValue(parameterName, value)
it might be more convenient to create a method accepting a parameter (name) to replace and a list of values. It is not on the Parameters level (like AddWithValue) but on command itself so it's better to call it AddParametersWithValues and not just AddWithValues:
query:
SELECT * from TableA WHERE Age IN (@age)
usage:
sqlCommand.AddParametersWithValues("@age", 1, 2, 3);
the extension method:
public static class SqlCommandExtensions
{
public static void AddParametersWithValues<T>(this SqlCommand cmd, string parameterName, params T[] values)
{
var parameterNames = new List<string>();
for(int i = 0; i < values.Count(); i++)
{
var paramName = @"@param" + i;
cmd.Parameters.AddWithValue(paramName, values.ElementAt(i));
parameterNames.Add(paramName);
}
cmd.CommandText = cmd.CommandText.Replace(parameterName, string.Join(",", parameterNames));
}
}
try it like this
StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
if (item.Selected)
{
string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)";
SqlConnection sqlCon = new SqlConnection(connectString);
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlCon;
sqlComm.CommandType = System.Data.CommandType.Text;
sqlComm.CommandText = sqlCommand;
sqlComm.CommandTimeout = 300;
sqlComm.Parameters.Add("@Age", SqlDbType.NVarChar);
sb.Append(item.Text + ",");
sqlComm.Parameters["@Age"].Value = sb.ToString().TrimEnd(',');
}
}
Source: Stackoverflow.com