StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You will need to add the values in the array one at a time.</p> <pre><code>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); </code></pre> <p><strong>UPDATE:</strong> 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. </p> <pre><code>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(); } } </code></pre> <p>It is called like this...</p> <pre><code>var cmd = new SqlCommand("SELECT * FROM TableA WHERE Age IN ({Age})"); cmd.AddArrayParameters("Age", new int[] { 1, 2, 3 }); </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload