How to include variable in a SQL query in C#

Question

I am adding a multiline SQL code in my C# code like this:

sqlCode = @"SELECT
    w.objectID,
    w.attr1397 'LastName',
    w.attr1395 'FirstName', 
    w.attr1396 'MiddleName', 
    w.attr1404 'Gender',     
    w.attr1436 'OtherName', 
    convert(varchar,w.attr1402, 101) 'DOB' ,
    w.attr1401 'SSN',   
    d.employmentStatus
FROM [db].[table] w left outer join  
     (
        WHERE w.attr1397 = '" + k + "'
        AND
        w.attr1395 = c
        AND
        w.attr1401 = s
ORDER BY 4, 2, 3";

The k, c, and s are variable from the function that I want to use and this is what happens:

enter image description here

How can I resolve it?

Use a parameterized query. See `SqlCommand.Parameters` for an example. (I'd expect parameterized SQL to be in *any* reasonable introduction to database access from C#.) Your compile-time error is because your second string literal isn't a verbatim string literal, btw. — Jon Skeet, Jul 11 '14 at 14:30
The k, c, and s are variables that I want to use inside the sql query. — Si8, Jul 11 '14 at 14:31
Well, the first problem is while you are using a `@` for the first string, you are not using it for the second (after `k`). — gunr2171, Jul 11 '14 at 14:32
I saw examples for single variable, how do I use multiple variable for parameters? — Si8, Jul 11 '14 at 14:32
@SiKni8: The example in MSDN for `SqlCommand.Parameters` already has multiple parameters. You really need to do some research *before* asking questions. — Jon Skeet, Jul 11 '14 at 14:34

Steve · Accepted Answer · 2014-07-11T14:38:05.287

You should use a parameterized query as first step to make your code safe and sound.
But your error is caused by the interruption of the verbatim string prefixed by @ char

sqlCode = @"SELECT
    w.objectID,
    w.attr1397 'LastName',
    w.attr1395 'FirstName', 
    w.attr1396 'MiddleName', 
    w.attr1404 'Gender',     
    w.attr1436 'OtherName', 
    convert(varchar,w.attr1402, 101) 'DOB' ,
    w.attr1401 'SSN',   
    d.employmentStatus
FROM [db].[table] w left outer join  
     (
        WHERE w.attr1397 = '" + k + "'" + 
        " AND w.attr1395 = " + c +
        " AND w.attr1401 = " + s +
" ORDER BY 4, 2, 3";

As I have said, it is the recommended practice to use parameters instead of string concatenations

sqlCode = @"SELECT
    w.objectID, 
    w.attr1397 'LastName',
    w.attr1395 'FirstName', 
    w.attr1396 'MiddleName', 
    w.attr1404 'Gender',     
    w.attr1436 'OtherName', 
    convert(varchar,w.attr1402, 101) 'DOB' ,
    w.attr1401 'SSN',   
    d.employmentStatus
FROM [db].[table] w left outer join  
     (
        WHERE w.attr1397 = @k 
        AND w.attr1395 = @c
        AND w.attr1401 = @s
ORDER BY 4, 2, 3";

SqlCommand cmd = new SqlCommand(sqlCode, connection);
cmd.Parameters.AddWithValue("@k", k);
.....

I used `@` for the rest of the string and it worked, thank you for the solution. I am planning on using parameters now. — Si8, Jul 11 '14 at 14:34

How to include variable in a SQL query in C#

1 Answers1