SQL query working on DB but not with SQLCommand c#

Question

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.

user3682091 · Accepted Answer · 2017-02-22 02:47:23Z

1

Try to replace '%'+@2+'%' with @2

And then try this:

 command.Parameters.Add(new SqlParameter("2", (searchstring == null) ? DBNull.Value : (object)("%"+searchstring.ToString()+"%")));

answered Feb 22, 2017 at 2:47

user3682091

7551 gold badge7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Exprove Over a year ago

For some reason it's not working. If I try that query with something hardcoded in sql it works... But in sqlcommand it's hard to get that working.

Collectives™ on Stack Overflow

SQL query working on DB but not with SQLCommand c#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related