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?

回答1:

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.

回答2:

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.

回答3:

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

回答4:

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