如何检查我的数据表和过滤器中是否存在字符串数组 [英] How do I check if my string array exists in my datatable and filter

问题描述

<pre> string[] meassteps = { "B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10" };

                foreach (string ms in meassteps)
                {
                    conStr = String.Format(conStr, filePath, "Yes");
                    OleDbConnection connExcel = new OleDbConnection(conStr);
                    OleDbCommand cmdExcel = new OleDbCommand();
                    OleDbDataAdapter oda = new OleDbDataAdapter();

                    cmdExcel.Connection = connExcel;

                    DataTable sheets = GetSchemaTable(conStr);
                    connExcel.Open();

                     int cnt = 0;
                     foreach (DataRow r in sheets.Rows)
                         {
                            if (cnt > 0) break;
                            string sheetName = r[2].ToString();

                            DataTable dt = new DataTable(sheetName);


                            cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                            oda.SelectCommand = cmdExcel;
                            oda.Fill(dt);

                            dtArray[cnt] = dt;

                            //dt.DefaultView.RowFilter = "measstep";

                            DataRow[] row;
                            row = dt.Select(ms);
                            if (row != null & row.Length > 0)
                            {
                                dt = row.CopyToDataTable();
                                dt = dt.DefaultView.ToTable();

                            }

                }

大家好。我在这里有一个代码，我创建了一个新的字符串数组，用于在我的数据表中搜索从B1到B10的meassteps，其中有一个名为measstep的列。

我的代码应该做的是过滤掉measstep并将其放在另一个数据表中。如果measstep列中不存在B1，则搜索下一个字符串B2并过滤B2 out，依此类推，直到B10。



但是，当字符串ms = B1时，我遇到错误找不到列[B1]。



如何编写我的代码，以便搜索数据表中的measstep？



我尝试了什么：



我尝试将我的字符串数组放入foreach循环中，以便字符串数组存在于我的数据表中。

Hi all. I have a code here which I have created a new string array to search for the meassteps from "B1" to "B10" in my datatable which has a column named "measstep".
What my code is supposed to do is to filter out the measstep and put it in another datatable. If "B1" does not exist in the column "measstep", then search for the next string which is "B2" and filter B2 out and so on, until "B10".

However, I'm having an error that says "Cannot find Column[B1]" when string ms = B1.

How do I write my code such that I'm searching the measstep that is in my datatable?

What I have tried:

I have tried putting my string array into the foreach loop so that the string array exist in my datatable.

推荐答案

DataTable.Select不能那样： DataTable.Select方法（字符串）（System.Data） [ ^ ] - 您正在提供搜索条件thjat只是B1或B2等 - 这不是搜索。您需要将其替换为ColumnName ='B1'等等。

DataTable.Select doesn't work like that: DataTable.Select Method (String) (System.Data)[^] - you are supplying a search criteria thjat is just "B1", or "B2", etc. - which isn't a search. You need to replace that with "ColumnName = 'B1'" and so on.

引用：

哦，所以我必须将字符串数组[]更换为ColumnName = B1到B10？

Oh, so I have to replace the string array[] to ColumnName = B1 to B10?

它的工作原理如下： br />

It works like this:

DataTable dt = new DataTable();
dt.Columns.Add("A1");
dt.Columns.Add("A2");
dt.Rows.Add("r1", "B1");
dt.Rows.Add("r2", "B2");
dt.Rows.Add("r3", "B1");
dt.Rows.Add("r4", "B2");
DataRow[] rows = dt.Select("A2 = 'B2'");
foreach (DataRow row in rows)
    {
    Console.WriteLine("{0}:{1}", row[0], row[1]);
    }

给：

r2:B2
r4:B2

所以我改变你的代码就像这样：

So I'd change your code like this:

DataRow[] row;
row = dt.Select(string.Format("measstep ='{0}'", ms));

首先：if，你想使用 foreach（...）循环，由于效率的原因，我会在 foreach（...）循环之外移动几行代码。这些是行，您打开Excel连接并填写数据表。

其次：您不需要第二个循环（ foreach（SheetRow中的DataRow） ），因为您使用选择方法来过滤数据！

First of all: if, you would like to use foreach(...) loop, i'd move few lines of your code outside the foreach(...) loop due to the efficiency. Those are the lines, where you open Excel connection and fill in datatable.
Second of all: you don't need second loop (foreach (DataRow r in sheets.Rows)), because you're using Select method to filter data!

foreach(string ms in meassteps)
{
	DataRow[] msrows= dt.Select(string.Format("measstep Like %{0}%", ms));
}

另一种方法是使用 Linq 。

如果您想通过将每一行与字符串集合进行比较来过滤数据表，您可以通过使用 Where（） + Any（）：
$ b $来实现这一点b

Another way is to use Linq.
If you would like to filter datatable by comparing each row to the collection of strings, you're able to achieve that by using Where() + Any():

var filteredrows = dt.AsEnumerable()
	.Where(x=> meassteps.Any(y=> x.Field<string>("measstep").Contains(y)))
	.ToList();

或交叉加入：

or cross join:

var filteredrows1 = from dr in dt.AsEnumerable()
	from ms in meassteps
	where dr.Field<string>("measstep").Contains(ms)
	select dr;

这篇关于如何检查我的数据表和过滤器中是否存在字符串数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！