I have a kinda complex query, basically I search the database in most fields for a string or strings. If it's multiple strings, the database field must match all parts of the strings.
This is the base sql that the query is built on:
SELECT wo.ID, {columns} FROM tblWorkOrder wo
LEFT JOIN tblWorkOrderCategory wc
ON wo.CategoryID = wo.ID
LEFT JOIN tblTenant t
ON wo.TenantID = t.ID
LEFT JOIN tblProperty p
ON wo.PropertyID = p.ID
LEFT JOIN tblRentalUnit ru
ON wo.UnitID = ru.ID
Columns is replaced with this list:
"wo.Date", "wo.WorkDesc", "wo.Priority", "wo.WorkDoneBy", "wo.EstimatedCost", "wo.DueDate", "wo.ActualCost", "wo.FinishedDate", "wo.workOrderNum",
"wc.[Description]",
"t.TenantName",
"p.PropertyName",
"ru.UnitNumber"
and this is how I build the query:
String[] parts = txtSearch.Text.Split(' ');
foreach (String column in columnsToSearch) {
String clause = " (";
for (int i = 0; i < parts.Length; i++) {
clause += column + " LIKE '%@param" + i + "%' ";
if (i + 1 != parts.Length) {
clause += "AND ";
}
}
clause = clause.TrimEnd() + ") ";
sql += clause + " OR ";
}
sql = sql.TrimEnd(new char[] { 'O', 'R', ' ' });
using (SqlConnection conn = new SqlConnection(RentalEase.Properties.Settings.Default.RentalEaseConnectionString)) {
SqlCommand command = new SqlCommand(sql, conn);
for (int i = 0; i < parts.Length; i++) {
command.Parameters.Add("@param" + i, SqlDbType.NVarChar).Value = parts[i];
//command.CommandText = command.CommandText.Replace("@param" + i, parts[i]);
}
Only this always returns no rows. However, in the for loop that assigns the parameter values, if I comment out the Parameters.Add line and uncomment the one below it, I wind up with results like I should be seeing. As this is an unsafe way to do it, I'd like to know why using parameters is failing.