CODEWITHSUNDEEP
c#sqlssis
user1429595
user1429595

Reputation: 2735

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

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

Upvotes: 0

Views: 1828

Answers (3)

Nikola Bogdanović
Nikola Bogdanović

Reputation: 3213

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.

Upvotes: -2

Prakhar Mehta
Prakhar Mehta

Reputation: 15

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

Upvotes: 0

Thousand
Thousand

Reputation: 6638

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

Upvotes: 7

Related Questions