在一定条件下，建立两个数据表中的一个 [英] Build one datatable out of two with certain conditions
问题描述
首先，我需要从ODBC中获取所有数据（这已经在工作）。
Firstly I need to get all the data from ODBC (this is working already).
然后是最复杂的部分，我不确定该如何完成。
ODBC中有两个数据表。我将它们与当前代码合并，并使用某些参数对其进行过滤。
Then comes the most complicated part that I am not sure yet how it can be done. There are two tables of data in ODBC. I am merging them with my current code and filtering them with certain parameters.
数据库中的表1：
NRO NAME NAMEA NAMEB ADDRESS POSTA POSTN POSTADR COMPANYN COUNTRY ID ACTIVE
123 Fiat Punto 500 J5 K4 O3 P4 O2 JT 1 1
133 Opel Meriva FTG J5 K4 O3 P4 O2 JO 3 1
153 MB E200 C25 JN KI OP PY OR JD 5 1
183 BMW E64 SE0 JR KE OT PG OL J8 9 1
103 Audi S6 700 JP KU OU PN OH J6 11 1
数据库中的表2：
NRO NAME NAMEA NAMEB ADDRESS POSTA POSTN POSTADR COMPANYN COUNTRY ID ACTIVE
423 Fiat Punto 500 J5 K4 O3 P4 O2 JT 1 1
463 BMW E64 SE0 JR KE OT PG OL J8 9 1
合并数据表外观像这样：
Merged dataTable look like this:
NRO NAME NAMEA NAMEB ADDRESS POSTA POSTN POSTADR COMPANYN COUNTRY ID ACTIVE
423 Fiat Punto 500 J5 K4 O3 P4 O2 JT 1 1
463 BMW E64 SE0 JR KE OT PG OL J8 9 1
123 Fiat Punto 500 J5 K4 O3 P4 O2 JT 1 1
133 Opel Meriva FTG J5 K4 O3 P4 O2 JO 3 1
153 MB E200 C25 JN KI OP PY OR JD 5 1
183 BMW E64 SE0 JR KE OT PG OL J8 9 1
103 Audi S6 700 JP KU OU PN OH J6 11 1
但是合并的输出数据表应该看起来像这样（有可能进一步使用它）：
However merged output dataTable should look like this (to have a possibility to work with it further):
NRO NRO1 NAME NAMEA NAMEB ADDRESS POSTA POSTN POSTADR COMPANYN COUNTRY ID ACTIVE
123 423 Fiat Punto 500 J5 K4 O3 P4 O2 JT 1 1
133 Opel Meriva FTG J5 K4 O3 P4 O2 JO 3 1
153 MB E200 C25 JN KI OP PY OR JD 5 1
183 463 BMW E64 SE0 JR KE OT PG OL J8 9 1
103 Audi S6 700 JP KU OU PN OH J6 11 1
在 NAME 中查找重复项。仅保留其中之一，将表1中的数字分配给 NRO ，将表2中的数字分配给 NRO1 。表1的数字应位于 NRO 中，表2的数字应位于 NRO1 中。
Find duplicates in NAME. Leave only one of them, assign a number from Table 1 to NRO from Table 2 to NRO1. Table 1 numbers should be in NRO, Table 2 numbers should be in NRO1.
连接到ODBC后，我要用表1中的数据填充一张表
After connecting to ODBC I am filling one table with data from Table 1
DataTable dataTable = new DataTable("COMPANY");
using (OdbcConnection dbConnectionSE = new OdbcConnection(connectionStringSE))
{
dbConnectionSE.Open();
OdbcDataAdapter dadapterSE = new OdbcDataAdapter();
dadapterSE.SelectCommand = new OdbcCommand(queryStringSE, dbConnectionSE);
dadapterSE.Fill(dataTable);
}
然后我从另一个表2获取数据并合并它们通过：
then I am getting data from another Table 2 and merging them by:
using (OdbcConnection dbConnectionFI = new OdbcConnection(connectionStringFI))
{
dbConnectionFI.Open();
OdbcDataAdapter dadapterFI = new OdbcDataAdapter();
dadapterFI.SelectCommand = new OdbcCommand(queryStringFI, dbConnectionFI);
var newTable = new DataTable("COMPANY");
dadapterFI.Fill(newTable);
dataTable.Merge(newTable);
}
此后，我将执行过滤（我只需要以4开头的行和 NRO 中的1，也有其他起始编号的行）：
After that I am performing filtering (I need to have rows only starting with 4 and 1 in NRO, there are also rows with other starting number):
DataTable results = dataTable.Select("ACTIVE = '1' AND (NRO Like '1%' OR NRO Like '4%')").CopyToDataTable();
然后我为 NRO1 （这也添加了零（0），我在列 NRO1 中不需要它们）：
Then I am adding one more Column for NRO1 (this is also adding zeros (0) I don't need them in Column NRO1):
results.Columns.Add("NRO1", typeof(int)).SetOrdinal(1);
foreach (DataRow row in results.Rows)
{
//need to set value to NewColumn column
row["NRO1"] = 0; // or set it to some other value
}
我可以使用此代码捕获重复项
I can catch duplicates with this code
var duplicates = results.AsEnumerable().GroupBy(r => r[2]).Where(gr => gr.Count() > 1);
但是其余的如何执行？这应该通过建立新表的循环来执行吗？我如何执行联接并将重复项删除到 dataTable ？
but how to perform the rest? This should be performed by a loop with building a new table? How I can perform joining and removing duplicates to dataTable?
推荐答案
您可以用自定义方法替换 merge（）调用，该方法可以同时进行合并和过滤。请参见下面的示例。我认为这是比先合并（在结果表中引入重复的行）然后过滤（即删除重复的行）更好的方法。
You could replace the merge() call with a custom method, which does the merging and filtering at the same time. See the example below. I think this is a better approach than first merging (introducing duplicate rows in the result table) and then filtering (i.e. removing the duplicate rows).
在这里，假定参数都具有相同的格式。 tTemp 表用作表 t2 的内容的临时存储，但带有额外的列。这样可以导入结果表中的行。
Here, it is assumed that the parameters all have the same format. The tTemp table is used as a temporary storage for the contents of table t2 but with the extra column. This allows importing the rows in the result table.
也许有一个更优雅的解决方案，但这应该可以正常工作。请注意，对于 NRO 的允许值，我没有赘述，我相信您可以轻松添加。
Maybe there is a more elegant solution, but this should work as intended. Please note that I have left out your additional requirement regarding the allowed values for NRO, which I am sure you can add easily.
static void merge_it(DataTable t1, DataTable t2, DataTable tResult, DataTable tTemp)
{
tResult.Merge(t1);
tResult.Columns.Add("NRO1", typeof(int));
tTemp.Merge(t2);
tTemp.Columns.Add("NRO1", typeof(int));
foreach (DataRow row in tTemp.Rows)
{
string name1 = row.Field<string>("NAME");
string name2 = row.Field<string>("NAMEA");
DataRow[] matches = tResult.Select($"NAME = '{name1}' AND NAMEA = '{name2}'");
if (matches.Length > 0)
{
matches[0].SetField<int>("NRO1", row.Field<int>("NRO"));
}
else
{
tResult.ImportRow(row);
}
}
foreach (DataRow row in tResult.Rows)
{
if (row["NRO1"] == DBNull.Value)
{
row["NRO1"] = 0;
}
}
}
这篇关于在一定条件下，建立两个数据表中的一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
