带有搜索变量参数的Npgsql查询返回空结果C＃Postgres [英] Npgsql query with search variable parameter returning empty result C# Postgres

问题描述

我正在使用Npgsql查询数据库表并在页面/视图上显示结果。 在没有where子句和参数的情况下，代码可以正常工作，因为它会将表中的所有行都移到视图上。现在，我尝试合并搜索字符串变量（如果用户键入字符串，则给我包含该字符串的表记录）。我的代码如下

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);

当我只有

SELECT * FROM q_product

但在包含where子句之后和我的搜索字符串变量，我得到一个空白页。我的代码在做什么呢？

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?

推荐答案

您可能想使用％在查询中使用喜欢

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);

这篇关于带有搜索变量参数的Npgsql查询返回空结果C＃Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！