Reputation: 1421
I have a problem here, I got a query that works on SQL Server, but when I try to execute it via SqlCommand
in C#, it doesn't work. Could you try to help me?
public List<Product> GetProductsByFilter(string category, string searchparam, string searchstring, string sortparam, string sort)
{
string stringcommand = "SELECT * FROM Products WHERE (Category = @0 OR @0 IS NULL) AND(@1 = @2 OR @1 Like '%'+@2+'%' OR @1 IS NULL OR @2 IS NULL) ORDER BY Category,";
stringcommand += (string.IsNullOrWhiteSpace(sortparam)) ? "Name" : sortparam;
stringcommand += (string.IsNullOrWhiteSpace(sort)) ? " ASC" : " " + sort;
if (string.IsNullOrWhiteSpace(searchparam) && !string.IsNullOrWhiteSpace(searchstring))
searchparam = "Name";
List<Product> productlist = new List<Product>();
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(stringcommand, conn);
command.Parameters.Add(new SqlParameter("0", (category == null) ? DBNull.Value : (object)category));
command.Parameters.Add(new SqlParameter("1", (searchparam == null) ? DBNull.Value : (object)searchparam));
command.Parameters.Add(new SqlParameter("2", (searchstring == null) ? DBNull.Value : (object)searchstring.ToString()));
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
productlist.Add(new Product()
{
ProductID = (int)reader[0],
Name = (string)reader[1],
Description = (string)reader[2],
Category = (string)reader[3],
ImagePath = (string)reader[4],
UnitsInStock = (int)reader[5],
Price = (decimal)reader[6],
Supplier = GetSupplierDataByID((int)reader[7])
});
}
}
}
return productlist;
}
This function returns null, while in SQL it returns some rows. If I remove the LIKE
clause it works like a charm.
Upvotes: 1
Views: 123
Reputation: 755
Try to replace '%'+@2+'%'
with @2
And then try this:
command.Parameters.Add(new SqlParameter("2", (searchstring == null) ? DBNull.Value : (object)("%"+searchstring.ToString()+"%")));
Upvotes: 1