Write SQL Query in C# code with multiple lines

Question

I'm writing a SQL query in C# code. But it is giving an errors. How can I correct this?

private string sql = "";
sql = (@"insert into employee_master
(
NATIONALITY,RELIGION,BLOODGROUP,
ELECTORATE,PROVINCE,DISTRICT,MOBILE
)
values
(
'"+emp.NATIONALITY+"','"+emp.RELIGION+"','"+emp.BLOODGROUP+"',
'"+emp.ELECTORATE+"','"+emp.PROVINCE+"','"+emp.DISTRICT+"','"+emp.MOBILE+"'
)");

enter image description here

Remove the string concatenation and use parameters. More safe, more clear. By the way, what error? — Steve, Jun 03 '15 at 21:03
error attached. Syntax error. I want to write without using parameters. — Tom, Jun 03 '15 at 21:11
Sorry, but you SHOULD use parameters. Not using them is an option to NOT consider if you really want to write safe and robust code. — Steve, Jun 03 '15 at 21:12

score 9 · Accepted Answer · edited May 23 '17 at 10:34

You should really remove that string concatenation and use a parameterized query. I can only suppose, from your short code, that something is not as you (or the compiler) expect in that string values that you stick together with quotes. For example, if one of your string values contains a single quote, the whole query will fail with a Syntax Error. A parameterized query, instead, removes any problem with quotes and render your query safe from Sql Injections (and it is a lot easier to understand what's going on)

private string sql = "";
sql = @"insert into employee_master
(
    NATIONALITY,RELIGION,BLOODGROUP,
    ELECTORATE,PROVINCE,DISTRICT,MOBILE
)
values
(
    @nat,@rel,@blood,
    @elect,@prov,@district,@mobile
)";

using (SqlConnection con = new SqlConnection(...constring here...))
using (SqlCommand cmd = new SqlCommand(sql, con))
{
    con.Open();
    cmd.Parameters.Add("@nat", SqlDbType.NVarChar).Value = "Nationality";
    ... continue adding other parameters like @rel, @blood etc... and their values
    cmd.ExecuteNonQuery();
}

Said that, after your edit, we can easily see the error pointed by the red squiggles. It is caused by the bad line endings on these lines

....'"+emp.BLOODGROUP+"',
....'"+emp.MOBILE+"'

you should change them to

....'"+emp.BLOODGROUP+"',"
....'"+emp.MOBILE+"'"

to be correct for the compiler. The effect of the verbatim character @ is terminated when you use the + operator to concatenate strings and you need to repeat it again if you want to omit the closing double quote on the lines above. This leads to this terrible and error prone syntax:

....'"+emp.BLOODGROUP+ @"',
....'"+emp.MOBILE+ @"'

But again, this doesn't protect you from malformed input values that could generate an exception or be a catastrophic hacking of your program. So, please do not write queries in this way

Write SQL Query in C# code with multiple lines

1 Answers1