I am trying to write an SQL statement as string literal with two variables as follows-
String str="abc"; int val=123;
String sql=@"SELECT Column1, Column2
FROM Table
WHERE Column1= '"" + str + ""' AND Column2 > "" + val + ""
ORDER BY Column1";
But those variables are not treated as variables. Any help?
UPDATE:
Added screenshot with order by clause. There are red curly underlines.
Personally I would rewrite the query to use parameterized queries. You will likely have better performance under load and you prevent SQL Injection if the string you are entering comes from user enterable values.
String sql=@"SELECT Column1, Column2
FROM Table
WHERE Column1 = @str AND Column2 > @val
ORDER BY Column1";
using(var connection = new SqlConnection(_connectionString)
using(var command = new SqlCommand(sql, connection)
{
connection.Open();
command.Parameters.Add("@str", SqlDbType.NVarChar, Column1MaxTextLength).Value = str;
command.Parameters.Add("@val", SqlDbType.Int).Value = val;
using(var reader = command.ExecuteReader())
{
//...
}
}
EDIT: To address your screenshot, you need another @ symbol before the last set of quotes.
String sql=@"SELECT Column1, Column2
FROM Table
WHERE Column1= '" + str + "' AND Column2 > " + val + @"
ORDER BY Column1"; // ^-- You are missing this guy
But I still think you should re-write to use parametrized queries.
First of all try using parameterized queries to avoid SQL Injection. You may read more here.
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT Column1, Column2 FROM Table WHERE Column1=@col1 AND Column2 > @col2 ORDER BY Column1, Column2 " , conn))
{
cmd.Parameters.AddWithValue("@col1", str);
cmd.Parameters.AddWithValue("@col2", val);
using(var reader = cmd.ExecuteReader())
{
//...
}
}
}
But to answer your issue:
String str="abc";
int val=123;
String sql=@"SELECT Column1, Column2
FROM Table
WHERE Column1= '" + str + "' AND Column2 > " + val + "";
Or you could use String.Format as suggested by Aron
String str="abc";
int val=123;
String sql=String.Format(@"SELECT Column1, Column2
FROM Table
WHERE Column1= '{0}' AND Column2 > {1}",str,val);
ToString() on val.
Add( instead of AddWithValue that way you can be more explicit on the data type of the declared variable. I have gotten bitten in the past where a unexpected cast causes the database not to use the indexes you expected it to use.
string gets cast to nvarchar and a widening operator is implicitly applied to the the table side of the query plan, resulting in the index being by passed? I am sure that has NEVER happened to me...>_< String str = "abc"; int val = 123;
StringBuilder strbuilder = new StringBuilder();
strbuilder.Append("SELECT Column1, Column2 FROM Table WHERE Column1=");
strbuilder.Append("'" + str + "'");
strbuilder.Append(" AND Column2 >");
strbuilder.Append("'" + val.ToString() + "'");
user string builder may this helps you.
