Pass Array Parameter in SqlCommand

Question

Hi all

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

Best Regadrs,

Answer 1

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

Answer 2

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

Answer 3

sorry how do you add code?

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

Answer 4

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

Answer 5

i see, you want to pass make the parameter = to the whole string from the stringbuilder

Answer 6

try

sqlComm.Parameters["@Age"].Value = sb.ToString().Replace(","," ");

Answer 7

I know this has nothing to do with the question but seriously look into an orm solution like Nhibernate, Linq to SQL, LLBLGen Pro or entity framework.

It will make your life much simpler and more productive.

Just my two cents.