SQL LIKE query to C# code

Question

I need to use the following query in my C# code:

SELECT AVG(Percent) 
From Table1
Where code Like "Sport" and Year Like"2011" and Sitting Like"June";

I did it like this:

"SELECT AVG(Percentage) FROM MasterTable WHERE Code LIKE " + comboBoxSubject.Text +
"AND Year LIKE "+dateTimePicker1 +" AND Sitting LIKE June"

but i get an exception probably because the parameters are extracted from different controls and are not placed in inverted commas.

Can anyone help me ?

ANSWER

That is the query that worked for me:

"SELECT AVG(Percent) FROM MasterTable WHERE Code LIKE '" + comboBoxSubject.Text + "' AND Year LIKE '" + dateTimePicker1.Value.Year + "' AND Sitting LIKE 'June'"

Answer 1

Supposing you use SQLite, because you don't mention any database. This is how you can avoid SQL injection.

var selectCommand = new SQLiteCommand("@SELECT AVG (PERCENT) 
                                       FROM TABLE1
                                       WHERE CODE LIKE @sport AND YEAR LIKE @year AND SITTING LIKE @month");
selectCommand.Parameters.AddWithValue("@sport", sportParameter);
selectCommand.Parameters.AddWithValue("@year", yearParameter);
selectCommand.Parameters.AddWithValue("@month", monthParameter);

Answer 2

There are three problems.

• There's no space after the code value and AND
• There are missing single quotes between values
• The wildcard symbol (%) is missing from the SQL LIKE statements

It depends what kind of project you are working on but often I find it is much easier to spot syntax errors and missing spaces by printing the end query out. For example, below is a console application that does this.

static void Main(string[] args)
{
    const string code = "Sport";
    const string year = "2011";
    Console.WriteLine("SELECT AVG(Percentage) FROM MasterTable WHERE Code LIKE '%" + code + "%' AND Year LIKE '%" + year + "%' AND Sitting LIKE '%June%'");
}