This is something that might be obvious, but I thought I would mention this because it wasn't obvious to me. As I have
mentioned earlier, I prefer stored procedures, but recently found myself in a scenario where I had to use inline SQL.
I will avoid the "why" discussion and get to the point. I wanted to use the '%partial_term%' wildcard syntax in order
to retrieve partial matches on a search page (so that FName, First_Name, Name, nameF, etc. would all be returned when
inputting "name", for example). To prevent SQL Injection, however, I wasn't about to just use string concatenation for
my search term. Googling for an example using inline SqlParameters with = (EQUALS operand) in the WHERE clause gets
you what you need, but I couldn't find an example with LIKE in the WHERE clause.
Add the % wildcard(s) before / after the partial search string when creating the SqlParameter object. The example
below lives in an object helper class and uses a function in my DAL class (which acts more or less like a wrapper
around the Data Access Application Block for .Net).
string sql = "SELECT <columns> FROM <table>";
if (search_string != string.Empty)
{
sql += " WHERE <column> LIKE @searchstring";
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@searchstring","%" + search_string + "%");
return DAL.GetDataTableWithText(sql,sqlParams);
}
else
{
return DAL.GetDataTableWithText(sql,null);
}