CODEWITHSUNDEEP
c#
s.k.paul
s.k.paul

Reputation: 7291

using the literal '@' with variable within the string

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. enter image description here

Upvotes: 0

Views: 386

Answers (3)

Scott Chamberlain
Scott Chamberlain

Reputation: 127553

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.

Upvotes: 5

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

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

Upvotes: 3

USER87
USER87

Reputation: 557

        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.

Upvotes: 0

Related Questions