Multiple String Switch Statement

[mcfly1204]

I have numerous strings that I would like to run though a switch statement. These strings are terms such as "None" or "Undefined" , that I would like to set to Null. My first thought was to create an array and then use a foreach statement to run each string through the switch statement. The obvious problem here is that you cannot assign to the iteration variables. How can I get around this?

[Mr Gray]

You could make a collection of those string values and iterate through the collection and assign to each of them.

[mcfly1204]

You could make a collection of those string values and iterate through the collection and assign to each of them.

I suppose I do not understand how that solves the problem of assigning to the strings.

[Curtis Rutland]

I have numerous strings that I would like to run though a switch statement. These strings are terms such as "None" or "Undefined" , that I would like to set to Null. My first thought was to create an array and then use a foreach statement to run each string through the switch statement. The obvious problem here is that you cannot assign to the iteration variables. How can I get around this?

Create a list/array but use a for loop instead of a foreach. Then you will be able to assign values to your strings.

for(int i=0; i<stringsArray.Length; i++)
{
  switch (stringsArray[i])
  {
    case "None":
      stringsArray[i] = null;
      break;
  }
}

[mcfly1204]

Thank you for that, but I now have a new issue. Now that I have eliminated unwanted strings and set them to null, I now need an if statement to not add the parameter if the value is null. Such as:

If (strValue1 != null)
{
     OleDBCommand.Parameters.AddWithValue("@value1", strValue1);
}
else
{
     *code to move on to next parameter to add*
}

If the value is null, how would I proceed to the next parameter to add?

[Curtis Rutland]

Thank you for that, but I now have a new issue. Now that I have eliminated unwanted strings and set them to null, I now need an if statement to not add the parameter if the value is null. Such as:

If (strValue1 != null)
{
OleDBCommand.Pa rameters.AddWit hValue("@value1 ", strValue1);
}
else
{
*code to move on to next parameter to add*
}

If the value is null, how would I proceed to the next parameter to add?

Just use the same string array and iterate through it again. Also, set up an array with your parameter names.

for (int i = 0; i < stringsArray.Length; i++)
{
  if (stringsArray[i] != null)
    cmd.Parameters.AddWithValue(paramArray[i], stringsArray[i]);
}
 

Where cmd is your OleDbCommand.

[mcfly1204]

Just use the same string array and iterate through it again. Also, set up an array with your parameter names.

for (int i = 0; i < stringsArray.Length; i++)
{
  if (stringsArray[i] != null)
    cmd.Parameters.AddWithValue(paramArray[i], stringsArray[i]);
}
 

Where cmd is your OleDbCommand.

So instead of adding the parameters, I would take all parameters in question, add them to an array, and then call something such as what you have above and dynamically add those parameters based of the string value? I just would not have thought to add parameters for a query in one method from another parameter, but I suppose if they're in the same class, it shouldn't matter.

[Curtis Rutland]

So instead of adding the parameters, I would take all parameters in question, add them to an array, and then call something such as what you have above and dynamically add those parameters based of the string value? I just would not have thought to add parameters for a query in one method from another parameter, but I suppose if they're in the same class, it shouldn't matter.

Well, that might not work, now that I think about it. I think that you have to add something for each parameter. I don't usually use parameters. I usually dynamically build my sql statement. So what you could do is write your sql statement up to the "where" clause. Then check to see if you have any valid parameters. If you do, add "where " and then iterate through your array of parameter values. If it isn't null, then use

sql += String.Format("value1 = '{0}' and" ,stringsArray[i]);

to the end of your sql statement (assuming you have your sql statement stored in a string called sql. Once you are done, remove the last 4 chars from the string (the last "and ") and you have a full sql statement.

Also, instead of "value1" you could have another string[] paramArray. This could hold all the names of your parameters. So then the statement would be:

for(int i=0; i<stringsArray.Length; i++)
{
  if(stringsArray[i] != null)
    sql += String.Format("{0} = '{1}' AND ",paramArray[i], stringsArray[i]);
}
 

Just remember to remove the last "AND ".
If you don't know what the String.Format() does, look it up. It is very useful.

[mcfly1204]

This would be a lot simpler if I could just insert Null parameters.

[mcfly1204]

Also, instead of "value1" you could have another string[] paramArray. This could hold all the names of your parameters. So then the statement would be:

for(int i=0; i<stringsArray.Length; i++)
{
  if(stringsArray[i] != null)
    sql += String.Format("{0} = '{1}' AND ",paramArray[i], stringsArray[i]);
}
 

Just remember to remove the last "AND ".
If you don't know what the String.Format() does, look it up. It is very useful.

So here you are formatting the first item in the array to equal the next item and then adding the string AND to the end I am inexperience both with building sql statements dynamically as well as not using parameters.