如何使用C＃datagridview为SQL查询选择多个行值 [英] How to use C# datagridview selected multiple row values to a SQL query

问题描述

我在c＃表单中有一个datagridview包含数据。我想选择一些行（使用选中复选框）。并且有一个包含数千行的sql表。我只需要过滤在gridview上选择的值并插入临时表，这样我就可以用它来报告部分。我怎么能这样做。

例如

dataGridview1栏目

猫

狗

马

狮子

狐狸

现在我选择了猫和狗的复选框。

Sql数据表视图是这样的。

名称代码

cat cat001

dog dg001

horse hrs001

crow crw001

cat cat002

lion ln001

cat cat003

cat cat004

dog dog002

所以我在gridview中选择了猫狗。所以我希望在点击按钮时使用这样的数据过滤临时表。

名称代码

cat cat001

dog dg001
cat cat002

cat cat003

cat cat004

dog dog002

所以我想尝试使用sql查询传递此但失败。我怎么能这样做。



我尝试过：



SqlConnection conn1 = new SqlConnection（@Data Source = .\sqlexpress; Initial Catalog = acc; Integrated Security = True）;

SqlCommand cmd1 = new SqlCommand（@select db.date，db .type，db.refno，db.itmcod，db.qty，db.cuscod，db.cstcod，cus.cusnam INTO ## wec from fstktxn as db INNER JOIN fcustomer as cus on db.cuscod = cus.cuscod where itmcod = dataGridView1.Rows [j] .Cells [title]。值'，conn1）;

conn1.Open（）;

cmd1.ExecuteNonQuery（） ;

DataTable dt = new DataTable（）;

SqlBulkCopy bulkCopy = new SqlBulkCopy（conn1）;

bulkCopy.DestinationTableName =## tmp1 ;

bulkCopy.WriteToServer（dt）;

conn1.Close（）;

解决方案

  string  name =  string  .Empty; 
  for （ int  i =  0 ; i <  dgvSupplier.Rows.Count; i ++）
 {
  if （Convert.ToBoolean（dgvSupplier.Rows [i] .Cells [  CheckBoxColumn]。 ））
 name + =  ' + Convert.ToString（dgvSupplier.Rows [i ] .Cells [  Title]。Value）+   '，; 
} 

现在将where子句更改为select db.date，db.type，db.refno，db.itmcod ，db.qty，db.cuscod，db.cstcod，cus.cusnam INTO ## wec from fstktxn as db INNER JOIN fcustomer as cus on db.cuscod = cus.cuscod where itmcod IN（+ name +），conn1 ）;

I have a datagridview in a c# form contains datas. I want to select some of the rows (using select check boxes). And there is an sql table which has thousands of rows. I need to filter only the values which were selected on gridview and insert into a temp table so i can use it for reporting sections. how can i do this.
e.g.
dataGridview1 Column
cat
dog
horse
lion
fox
now i am selecting the checkboxes for cat and dog.
Sql data table view is like this.
name name code
cat cat001
dog dg001
horse hrs001
crow crw001
cat cat002
lion ln001
cat cat003
cat cat004
dog dog002
so I've selected cat and dog in the gridview. So i want a filtered temporary table with datas like this on a button click.
name name code
cat cat001
dog dg001
cat cat002
cat cat003
cat cat004
dog dog002
So i am trying to pass this using a sql query but failed. How can i do this.

What I have tried:

SqlConnection conn1 = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=acc;Integrated Security=True");
SqlCommand cmd1 = new SqlCommand(@"select db.date,db.type,db.refno,db.itmcod,db.qty,db.cuscod, db.cstcod,cus.cusnam INTO ##wec from fstktxn as db INNER JOIN fcustomer as cus on db.cuscod = cus.cuscod where itmcod = "dataGridView1.Rows[j].Cells["title"].Value"'", conn1);
conn1.Open();
cmd1.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn1);
bulkCopy.DestinationTableName = "##tmp1";
bulkCopy.WriteToServer(dt);
conn1.Close();

解决方案

string name = string.Empty;
      for (int i = 0; i < dgvSupplier.Rows.Count; i++)
      {
        if (Convert.ToBoolean(dgvSupplier.Rows[i].Cells["CheckBoxColumn"].Value))
          name += "'" + Convert.ToString(dgvSupplier.Rows[i].Cells["Title"].Value) + "',";
      }

Now change where clause to "select db.date,db.type,db.refno,db.itmcod,db.qty,db.cuscod, db.cstcod,cus.cusnam INTO ##wec from fstktxn as db INNER JOIN fcustomer as cus on db.cuscod = cus.cuscod where itmcod IN (" + name + ")", conn1);

这篇关于如何使用C＃datagridview为SQL查询选择多个行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！