CODEWITHSUNDEEP
c#sql-server-2008
Viktor Vice
Viktor Vice

Reputation: 223

How to use string variable in sql statement

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?

Upvotes: 11

Views: 101292

Answers (4)

Ashim
Ashim

Reputation: 1

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

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1503509

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.

Upvotes: 14

msarchet
msarchet

Reputation: 15242

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.

Upvotes: 27

Ali
Ali

Reputation: 12684

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

Upvotes: 1

Related Questions