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.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?

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.

[Curtis Rutland]

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.

Not quite. The way string.Format works is that it evaluates "{#}" as one of the parameters following. So:

string temp = "Bob";
string out = String.Format("{0} is his name",temp);
//out == "Bob is his name"
 

What I did in that loop:
lets say I have a sql statement with 4 possible params: nameFirst, nameLast, nameMiddle, and userName. What I might do is:

string[] paramArray = new string[4];
paramArray[0] = "nameFirst";
paramArray[1] = "nameLast";
paramArray[2] = "nameMiddle";
paramArray[3] = "userName";
 

Then, I would get my value strings like you did, and assign them to stringsArray. Then run them through the switch statement and so forth.

Then you could build your sql statement.

string sql = "select * from tableName ";
bool hasParams = false;
for(int i=0; i<paramArray.Length; i++)
{
  if(stringsArray[i] != null)
  {
    hasParams = true;
    break;
  }
}
if(hasParams)
{
  sql += " where ";
  for(int i=0; i<stringsArray.Length; i++)
  {
    if(stringsArray[i] != null)
      sql += String.Format("{0} = '{1}' AND ",paramArray[i], stringsArray[i]);
  }
  sql = sql.Substring(0,sql.Length - 4);
}
 
 

What I did there is check to see if any of the strings aren't null (as per the way you were setting them null in the switch.) Then, if there are any "where" parameters, we finish building the sql statement. What that String.Format would evaluate to is "paramName = 'paramValue' AND "
So, in practice, if nameFirst should have a value of Bob, it will evaluate to:
"nameFirst = 'Bob' AND "
And at the end, we remove the extra "AND " (4 chars off the end.) You have a complete sql statement.

Maybe not the most simple way of doing it, but it works.

[mcfly1204]

So, I see what you are doing in your example by adding a where clause, if a known value exists, but I still do not see how this will work with an insert statement. For example:


INSERT INTO [table] (column1, column2*
VALUES (@parameter1, @parameter2**

cmd.Parameters.AddWithValue("@parameter1", value1);
cmd.Parameters.AddWithValue("@parameter2", value2);
***

Value1 and value2 are known not to be null, but any value after that will need to be checked for nulls and if not null, added to the sql statement. Given I will potentially be adding to the insert statement at *, **, ***, I do not understand how this will work.

[Curtis Rutland]

So, I see what you are doing in your example by adding a where clause, if a known value exists, but I still do not see how this will work with an insert statement. For example:


INSERT INTO [table] (column1, column2*
VALUES (@parameter1, @parameter2**

cmd.Parameters.AddWithValue("@parameter1", value1);
cmd.Parameters.AddWithValue("@parameter2", value2);
***

Value1 and value2 are known not to be null, but any value after that will need to be checked for nulls and if not null, added to the sql statement. Given I will potentially be adding to the insert statement at *, **, ***, I do not understand how this will work.

Ok, I was working on the incorrect assumption on a select statement. What you want to do is perfectly possible with System.Data.SqlTypes.

SqlCommand cmd = new SqlCommand(sqlInsertText,connectionObject);
SqlParameter parameter = new SqlParameter("@param1", System.Data.SqlTypes.SqlString.Null);
cmd.Parameters.Add(parameter);
 

That will insert a null value for that param. Sorry for all the confusion :(

Now if this isn't a varchar or nvarchar field you might have to use System.Data.SqlTypes.whateverDbTypeYouNeed, but this works for an nvarchar.

[mcfly1204]

I suppose I should have been more specific... but I think this may have something to do with why I was having a difficult time grasping this. I appreciate the effort nonetheless.