11

I have a WPF Application in which I am getting

string someone = TextBox.text;

I would like to use this in the following query

query = " Select * From Table Where Title = someone "

How should I go about using the variable someone in the query?

msarchet
  • 15,104
  • 2
  • 43
  • 66
Viktor Vice
  • 223
  • 1
  • 2
  • 13

4 Answers4

27

You can just do this

query = "Select * From Table Where Title = " + someone;

But that is bad and opens you to SQL Injection

You should just use a parameterized query

Something like this should get you started

using (var cn = new SqlClient.SqlConnection(yourConnectionString))
using (var cmd = new SqlClient.SqlCommand())
{
   cn.Open();
   cmd.Connection = cn;
   cmd.CommandType = CommandType.Text;
   cmd.CommandText = "Select * From Table Where Title = @Title";
   cmd.Parameters.Add("@Title", someone);
}

From Jon Skeet's answer since his was more complete than mine

See the docs for SqlCommand.Parameters for more information.

Basically you shouldn't embed your values within the SQL itself for various reasons:

  • It's inelegant to mix code and data
  • It opens you up to SQL injection attacks unless you're very careful about escaping
  • You have to worry about formatting and i18n details for things like numbers, dates and times etc
  • When the query remains the same with only the values changing, the optimizer has less work to do - it can look up the previous optimized query directly as it'll be a perfect match in terms of the SQL.
msarchet
  • 15,104
  • 2
  • 43
  • 66
  • What if I have multiple variable I have to add? – Si8 Jul 11 '14 at 14:30
  • 1
    Use multiple parameters. Would look something like `cmd.CommandText = "Select * From Table Where Tile = @Title, Thing = @Thing` `cmd.Parameters.Add("@Title", someone); cmd.Parameters.Add("@Thing, thing);` – msarchet Jul 14 '14 at 01:30
14

You should use a parameterized SQL query:

query = "SELECT * From TableName WHERE Title = @Title";

command.Parameters.Add("@Title", SqlDbType.VarChar).Value = someone;

See the docs for SqlCommand.Parameters for more information.

Basically you shouldn't embed your values within the SQL itself for various reasons:

  • It's inelegant to mix code and data
  • It opens you up to SQL injection attacks unless you're very careful about escaping
  • You have to worry about formatting and i18n details for things like numbers, dates and times etc
  • When the query remains the same with only the values changing, the optimizer has less work to do - it can look up the previous optimized query directly as it'll be a perfect match in terms of the SQL.
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • 2
    I pulled in the meat of your answer to mine since mine got accepted so at least someone looking at the accepted answer will see the fullest of information. – msarchet Sep 01 '11 at 14:12
1

Easiest is to use a C# Prepared sql. Example on this post. You don't have to worry about escaping the characters in your sql string or anything

Community
  • 1
  • 1
Ali
  • 12,354
  • 9
  • 54
  • 83
0
declare @SqlQuery varchar(2000), @Fromdate varchar(20), @Todate varchar(20)

set @Fromdate='01 jan 2017'
set @Todate='30 mar 2017'


set @SqlQuery='select * from tblEmployee where tblEmployee.JDate between '''+ @Fromdate + ''' and '''+ @Todate+ ''''

print  @SqlQuery
Pang
  • 9,564
  • 146
  • 81
  • 122
Ashim
  • 1
  • 1