Why are some of the C# Case statements not working as expected Using Same Code

Question

I am attempting to run a stored procedure using the following C# code

int intHowMany = docType.Count;
        switch(intHowMany)
        {
            case 1:
            string returntype1 = "'" + docType.ElementAt(0) + "'";
            return returntype1;

            case 2:
            string returntype2 = "'" + docType.ElementAt(0) + "',"
                + "'" + docType.ElementAt(1) + "'";
            return returntype2;

            case 3:
            string returntype3 = "'" + docType.ElementAt(0) + "',"
                + "'" + docType.ElementAt(1) + "',"
                + "'" + docType.ElementAt(2) + "'";
            return returntype3;

            case 4:
            string returntype4 = "'"+docType.ElementAt(0)+"',"
                +"'"+ docType.ElementAt(1)+"',"
                +"'"+ docType.ElementAt(2)+"',"
                +"'"+docType.ElementAt(3)+"'";

            return returntype4;

            case 5:
            string returntype5 = "'" + docType.ElementAt(0) + "',"
                + "'" + docType.ElementAt(1)+"',"
                + "'" + docType.ElementAt(2)+"',"
                + "'" + docType.ElementAt(3)+"'"+","
                + "'" + docType.ElementAt(4)+"'";
            return returntype5;

            case 6:
            string returntype6 = "'" + docType.ElementAt(0) + "',"
                + "'" + docType.ElementAt(1) + "',"
                + "'" + docType.ElementAt(2) 
                + "'," + "'" + docType.ElementAt(3) 
                + "'," + "'" + docType.ElementAt(4) 
                + "'," + "'" + docType.ElementAt(5) +"'";
            return returntype6;

            case 7:
            string returntype7 = "'" 
                + docType.ElementAt(0) + "',"
                + "'" + docType.ElementAt(1) + "',"
                + "'" + docType.ElementAt(2) + "'," 
                + "'" + docType.ElementAt(3) + "',"
                + "'" + docType.ElementAt(4) + "',"
                + "'" + docType.ElementAt(5) + "',"
                + "'" + docType.ElementAt(6) + "'";
            return returntype7;
            break;

        }

        return null;
    }

The above code provides the data for a SQL parameter that completes a SQL WHERE CloumnsName IN() statement.

The case statements for 1, 2 and 3 work as expected. However case statements 4 thru 7 fail with the following error message:

Unclosed quotation mark after the character string 'LA)) order by inc_date_recvd desc;'. Incorrect syntax near 'LA)) order by inc_date_recvd desc;'.

Below are the text strings that are being generated by the code:

'CITIZEN CONCERN','CORRESPONDENCE LOG','INTEL','LAWSUIT'
'CITIZEN CONCERN','CORRESPONDENCE LOG','INTEL','LAWSUIT','EMAIL'
'CITIZEN CONCERN','CORRESPONDENCE LOG','INTEL','LAWSUIT','EMAIL','VIDEO'
'CITIZEN CONCERN','CORRESPONDENCE LOG','INTEL','LAWSUIT','EMAIL','VIDEO','WINDOW'

As you can see it appears the 3rd element in the string 'LAWSUIT' has the closing quotation. I have also copied and pasted the above string results directly into a query on the SQL server and get the expected results.

Can anyone see a problem with this code or explain why I can not get it to work.

And that is why you should *not* try to write dynamic SQL. You can generate an `IN ()` clause quite easily with LINQ-to-SQL or LINQ-to-EF, with a `where listOfIds.Contains(item.id)` — Panagiotis Kanavos, Jun 21 '17 at 12:23
Maybe `return "'" + string.Join("',", docType) + "'"` will do the trick for all cases. — Sebastian Schumann, Jun 21 '17 at 12:23
Sounds like the whole thing is getting truncated at a given length. What is the actual resulting SQL query being executed? — David, Jun 21 '17 at 12:24
It's even easier to create the same statement with Dapper `conn.Query("select * from sometable where id in (@ids)",ids)` where `ids` is a list of ids (array, list, whatever) — Panagiotis Kanavos, Jun 21 '17 at 12:25
Probably you should also show the code that uses this returnTypeX. — Steve, Jun 21 '17 at 12:27
And the error message seems to point you to the string used in a WHERE statement just before the ORDER BY — Steve, Jun 21 '17 at 12:28
This could quite easily go wrong when your content contains a `'` so your SQL look like this: `'CITIZEN CONCERN', 'CORRESPONDENCE LOG', 'INTEL', 'LA'WSUIT'` The `'` after `LA` in `LAWSUIT` would cause this. This is apart from the fact that this whole function is bad practice, generating SQL statements like this opens up the danger of `SQL Injection`. Which might end up in a lawsuit. — Bunnynut, Jun 21 '17 at 12:29
Why use that `case` at all? Why `ElementAt()`? Why not retrieve all elements and join them? If `docType` is an IEnumerable with more elements than you need, you can use `docType.Take(docType.Count)`. Or you can just use `String.Format("\"{0}\""), String.Join(",",docType))` — Panagiotis Kanavos, Jun 21 '17 at 12:30
A better option, apart from using Dapper to get it right immediatelly, is to use table valued parameters in your query and join with the table parameter. At least it's safe against SQL Injection. — Panagiotis Kanavos, Jun 21 '17 at 12:33
@Panagiotis, String.Join() would be okay, but I believe you need to pass single quotation marks to SQL Server like this: string inList = "'" + String.Join("','", docType.Take(4)) + "'"; However there might be an invisible special character that breaks the string in SQL Server. Perhaps changing the encoding would help. — derloopkat, Jun 21 '17 at 12:54
@derloopkat `String.Join` is meant to get rid of that unnecessary `switch`. I'd never write such code - why use dynamic queries when there are ORMs and TVPs that can take care of this? If there are a lot of IDs and performance is an issue, why not *store* them in a table and perform a join that takes advantage of indexing and statistics? — Panagiotis Kanavos, Jun 21 '17 at 12:57
@Perry, I also believe there are better approaches, but if you want to use the above code my suggestion is try removing hidden characters and see if it works https://stackoverflow.com/questions/15259275/removing-hidden-characters-from-within-strings — derloopkat, Jun 21 '17 at 13:04
@Panagiotis Thank you very much for your response the information was just great. I have changed my code and have done away with the Switch statements. The code is much cleaner now. As a new programmer I appreciate the feedback. — Perry, Jun 26 '17 at 14:13

score -1 · Answer 1 · answered Jun 26 '17 at 14:13

The code was modified to this:

 private string MakeTheType()
    {
        string inList = string.Empty;
        List<string> docType = new List<string>();
        foreach (RadListBoxItem item in rlbDocuments.Items)
        {
            string value = item.Value;
            if (item.Checked)
            {
                docType.Add(value);

            }
        }

       int intHowMany = docType.Count;
       if (intHowMany == 1)
       {
           inList = "'" + docType.ElementAt(0) + "'";
           return inList;
       }

       else if (intHowMany >= 2)
       {
           inList = "'" + String.Join("','", docType.Take(intHowMany)) + "'";
           return inList;
       }
       else
           return null;

    }

Why are some of the C# Case statements not working as expected Using Same Code

1 Answers1