Npgsql query with search variable parameter returning empty result C# Postgres

Question

I am using Npgsql to query a database table and show the results on my page/view. The code works fine WITHOUT the where clause and parameters as it gets all the rows from the table onto the view. Now I am trying to incoporate a search string variable (if the user types in the string, then give me the table records that contain this string). My code is as follows

string searchValue = TempData["String"].ToString();

var model = new List<ProductViewModel>();      

NpgsqlConnection connection = new NpgsqlConnection
     ("Host=192.168.0.52;Database=test;Username=test;Password=test");
connection.Open();
NpgsqlCommand cmd = new NpgsqlCommand
     ("SELECT * FROM q_product WHERE q_description like @string", connection);

        //lets include our paramater
cmd.Parameters.Add("@string", NpgsqlTypes.NpgsqlDbType.Text);
cmd.Parameters["@string"].Value = searchValue;
cmd.Parameters.AddWithValue(searchValue);

NpgsqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
    var prod = new ProductViewModel();
    prod.q_description = dr["q_description"].ToString();
    prod.q_barcode = dr["q_barcode"].ToString();               

    model.Add(prod);  
}

var pagedProduct = new PaginatedSearch<ProductViewModel>(model, pageIndex, pageSize);

return View(pagedProduct);

Records are returned fine when I only have

SELECT * FROM q_product

but after including the where clause and my search string variable, I am getting an empty page. What am I doing wrong in my code for this?

What is the contents of searchValue? Also take a look at the documentation for LIKE. — 0xCAFEBABE
– 0xCAFEBABE, Commented Jun 27, 2017 at 11:45
on debugging searchValue is always whatever text I have typed in the searchbox (which is the expected behaviour). Its just skipping the while loop and going straight to the pagedProduct when where clause is added. I will have a look at documentation. — LavsTo
– LavsTo, Commented Jun 27, 2017 at 11:57
Possible duplicate of like statement for npgsql using parameter — JGH
– JGH, Commented Jun 27, 2017 at 12:21

JGH · Accepted Answer · 2017-06-27 12:20:55Z

1

You probably want to use % in your query to make use of the like operator.

NpgsqlCommand cmd = new NpgsqlCommand
     ("SELECT * FROM q_product WHERE q_description like '%' || @string || '%'", connection);

edited Jun 27, 2017 at 12:20

answered Jun 27, 2017 at 11:51

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

4 Comments

LavsTo Over a year ago

I had that exact line before, with the exact same empty result.

0xCAFEBABE Over a year ago

The missing % was what I was getting at. You could try to run the same query against the database manually, without your c# layer in between, and check if there's a result there.

JGH Over a year ago

updated with proper concatenation & auto parameter quoting

LavsTo Over a year ago

thanks for the assist @JGH and 0xCAFEBABE. The concatenation fix solved my problem, and I am getting the desired functionality now. Cheers.

Collectives™ on Stack Overflow

Npgsql query with search variable parameter returning empty result C# Postgres

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related