在sqlserver 2005中过滤项目的集合 [英] Filtrate collection of items in sqlserver 2005

问题描述

尊敬的先生，
我想过滤存储在字符串数组变量中的项目的集合，并在where子句中传递此变量，但仅过滤最后一个值，例如此处的"Coal".不过滤所有变量成员
我的查询是这样的//////////////

Respected Sir,
i want to filtrate collection of items that stored in string array variable and pass this variable in where clause but that filtrate only last value like here ''Coal''.not filtrate all variable members
my query is like this///////////////

 string[] r = { "TV", "Radio", "Coal" };
foreach (string name in r)
{
  // MessageBox.Show(name);
 SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ERPDB;Integrated Security=True");
 SqlDataAdapter adp;
 DataSet ds = new DataSet();
 adp = new SqlDataAdapter("Select * from Prod_Tbl where ProductName=''+name+''"), con);
 adp.Fill(ds, "Prod_Tbl");
 dataGridView1.DataSource = ds.Tables[0].DefaultView;
 }

推荐答案

更正:

Correction:

"Select * from Prod_Tbl where ProductName='" + name + "'"

更新:
我没有正确阅读您的答案.

您不应在循环上加载DataGridView.从循环中将其删除，并将其放置在外面.将foreach更改为以下之一.

Update:
I haven''t read your answer properly.

You should not load the DataGridView on the loop. Remove it from the loop and place it outside. Change the foreach to below one.

string filter = string.Empty;
foreach (string name in r)
{
  if (filter.Length > 0)
    filter +=",";
  filter += string.Format("'{0}'",name);
}

现在尝试以下操作:

Now try below:

"Select * from Prod_Tbl where ProductName IN (" + filter + ")"

此肮脏的代码将起作用但这不是最好的解决方案.您必须在db本身中处理此问题.

This dirty code will work but this is not the best solution. You got to handle this in db itself.

string[] r = { "TV", "Radio", "Coal" };
for (int i = 0; i < r.Length; i++)
            {
                ir +="ProductName = "+ r[i] + " or ";

            }
SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ERPDB;Integrated Security=True");
            SqlDataAdapter adp;
            DataSet ds = new DataSet();
            adp = new SqlDataAdapter("Select * from Prod_Tbl where "+ir+""), con);
            adp.Fill(ds, "Prod_Tbl");
            dataGridView1.DataSource = ds.Tables[0].DefaultView;

这篇关于在sqlserver 2005中过滤项目的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！