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?

[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.Sql Types.

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.Sql Types.whateverDbTypeY ouNeed, 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.