How to include variable in a SQL query in C# [duplicate]

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
– Jon Skeet, Commented Jul 11, 2014 at 14:30
The k, c, and s are variables that I want to use inside the sql query. — Si8
– Si8, Commented Jul 11, 2014 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
– gunr2171, Commented Jul 11, 2014 at 14:32
I saw examples for single variable, how do I use multiple variable for parameters? — Si8
– Si8, Commented Jul 11, 2014 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
– Jon Skeet, Commented Jul 11, 2014 at 14:34

Steve · Accepted Answer · 2014-07-11 14:38:05Z

5

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);
.....

edited Jul 11, 2014 at 14:38

answered Jul 11, 2014 at 14:32

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Si8 Over a year ago

I used @ for the rest of the string and it worked, thank you for the solution. I am planning on using parameters now.

Collectives™ on Stack Overflow

How to include variable in a SQL query in C# [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related