how to pass two variables in sql command in C#?

Question

really a simple question , I have two Variables "one_hour_ago" and "current_time", I need to pass these two variables in my sql command like :

string commandString = "SELECT * from myTable where time between one_hour_ago and current_time";

here is what I have but I get syntax error

string commandString = "SELECT * from myTable where TS between ' and /" + one_hour_ago + "'" + current_time + "/";

Thanks

Look at the SqlParameter class on MSDN and at their use via SqlCommand class — Steve
– Steve, Commented Jun 4, 2012 at 22:16
don't forget to accept an answer if it helped you, as a basic courtesy towards others... — Nikola Bogdanović
– Nikola Bogdanović, Commented Jun 7, 2012 at 1:01

Thousand · Accepted Answer · 2012-06-04 22:29:26Z

7

string sqlString = "SELECT * FROM myTable WHERE time BETWEEN  @before AND @current_time"; 
SqlCommand oCmd = new SqlCommand(sqlString , connString);
oCmd.Parameters.AddWithValue("@before", date_before);
oCmd.Parameters.AddWithValue("@current_time", currentTime);

where date_before and currentTime are the parameters you pass to the method.

this should take care of the sql injection stuff

answered Jun 4, 2012 at 22:29

Thousand

6,6484 gold badges42 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nikola Bogdanović Over a year ago

@user1429595: I too suggest you go with this answer, mine is just a quick and dirty fix... +1

Greg Over a year ago

using Parameters should also help prevent SQL injection (although isn't the be all and end all)

Prakhar Mehta · Accepted Answer · 2012-06-06 13:50:46Z

create procedure Proc_name (@param1 varchar(100), 
@param2 varchar(100), 
@param3 varchar(100), 
@param4 varchar(100)) 
as 
insert into table1 values(@param1, @param2, @param3, @param4)

Then from your code (giving a c# example using ADO.NET)

using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
SqlCommand command = new SqlCommand 
   ("Proc_name", connection); 

command.CommandType = CommandType.StoredProcedure;

// Add the input parameters and set the properties.
SqlParameter parameter1 = new SqlParameter();
parameter.ParameterName = "@Param1";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = param1;

SqlParameter parameter2 = new SqlParameter();
parameter.ParameterName = "@Param2";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = param2;




// Add the parameter to the Parameters collection. 
command.Parameters.Add(parameter1);
command.Parameters.Add(parameter2);


// Open the connection and execute the reader.
connection.Open();
SqlDataReader reader = command.ExecuteNonQuery();

reader.Close();
}

Nikola Bogdanović · Accepted Answer · 2012-06-07 01:08:40Z

-2

string commandString = "SELECT * FROM myTable WHERE time BETWEEN '" + one_hour_ago + "' AND '" + current_time + "'";

EDIT: this is just what OP explicitly asked for, for a better (right) answer take a look at Jane Doe's...

EDIT 2: For all those ignoramuses that downvoted me, "one_hour_ago" and "current_time" are clearly not user entered strings (but his own DateTime vars), and can be in any case made completely foolproof with just a simple TryParse before using them (and that is all that sql parameters do too in that regard, there is no added magic to it). Now, string concatenating a sql cmd is wrong, but I didn't suggest it, I merely just corrected his own approach to it. And I could have warned him about sql injection, but seeing as how he had problems with a simple string operation, I just judged it to be the least of his problems (right now), and assumed that it would only confuse him further to no end.

edited Jun 7, 2012 at 1:08

answered Jun 4, 2012 at 22:20

Nikola Bogdanović

3,2231 gold badge22 silver badges28 bronze badges

6 Comments

Steve Over a year ago

-1 You should never suggest a string concatenation to build a Sql Text.

Joris Over a year ago

Steve might want to elaborate this opens you up to SQL injection attacks: en.wikipedia.org/wiki/SQL_injection

Steve Over a year ago

@Joris, you are right, but this question repeat itself from an age, also the OP should know that using a Parameter he could stop to worry to forget that data type prefix

Nikola Bogdanović Over a year ago

@Steve: why are you trolling on my answer, if you don't like the question, take it up with the OP - I simply provided exactly what was asked for... if you wanted to "help", then next time do something actually useful, as Jane Doe did

Steve Over a year ago

I have all the rights to say that your proposed answer is not good, you could claim the contrary and the OP decides accepting or not while the community upvote or downvote. This is how this site works. Also I have answered to @joris not to you.

|

Collectives™ on Stack Overflow

how to pass two variables in sql command in C#?

3 Answers 3

2 Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related