Asked  7 Months ago    Answers:  5   Viewed   66 times

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(',');

 Answers

87

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.

Tuesday, June 1, 2021
 
Trott
answered 7 Months ago
75

For Mysql -

Setting array is not possible in Mysql.

Instead of that you can form a query for (?,?,..) in the loop and same way for setting values.

String[] Parameter = { "user1", "Administrator" };
String query = "select * from userinfo where firstname in (";
String temp = "";

for(i = 0; i < Parameter.length; i++) {
  temp += ",?";
}

temp = temp.replaceFirst(",", "");
temp += ")";
query = query + temp;

PreparedStatement pst = conn.prepareStatement(query);

so query becomes

select * from userinfo where firstname in (?,?)

and pass values also using loop.

For Oracle -

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
String[] Parameter = { "user1", "Administrator" };
java.sql.Array sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);
.
.
pstmt.setArray(1, sqlArray);
Tuesday, August 3, 2021
 
Null
answered 4 Months ago
42

This seems to be as close to a work around as I can find. Do the inter-function calls from VBA

If you make something like this

Public Function funBA(n As Variant) As Variant
    funBA = funB(funA(n))
End Function

it seems to work up to n=2^24=2^8^3 (which doesn't look like any data type break point in VBA which is where the hang up is, but that's a pretty big array)

Saturday, August 14, 2021
 
d8aninja
answered 4 Months ago
55

This is because .NET defaults to 'ToEven' rounding, while SQL uses 'AwayFromZero'. See This. These are different rounding methods, they differ in how they treat 5. AwayFromZero rounds it up to the next positive, or down to the next negative number. So, 0.5 becomes 1, -0.5 becomes -1. ToEven rounds to the nearest even number. So 2.5 becomes 2, 3.5 becomes 4 (and likewise for negative numbers). Numbers other than 5 are treated the same, they are rounded to the nearest number. Since 5 is equidistant from two numbers, it's a special case, with different strategies.

ToEven is also known as 'Banking Rules', its the default used in IEEE_754, which is why it's the default in .NET.

Conversely, AwayFromZero is also known as 'Commercial Rounding'. I don't know why it is the default of sql server, probably simply because it's the most widely known and understood method.

Of course, you can always configure what you need:

In C# you can do:

Math.Round(value, MidpointRounding.ToEven)

or

Math.Round(value, MidpointRounding.AwayFromZero)

In SQL you can use ROUND(), FLOOR() and/or CEILING().

Which of the methods is better, depends what you use it for, and what you want. For reasonable collections/distributions, the average of rounded toEven values is the same as it's original values. This is not necessarily the case with AwayFromZero. If you have a collection with many .5 data, rounding AwayFromZero will treat all those values the same, and introduce a bias. The effect is that the average of the rounded values is not the same as the average of the original values. The point of rounding is making a value simpler, while it holds the same meaning. This is no longer the case if the averages don't match; the rounded values have a (slightly?) different meaning then the original values.

Sunday, August 15, 2021
 
RustyFluff
answered 4 Months ago
24

I don't think you can compare text field values, which would explain the error.

Also, the text data type is deprecated in favor of varchar(MAX).

See this

Saturday, August 28, 2021
 
RenegadeAndy
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share