DataTable组合值和删除源行 [英] DataTable Combine Values and Remove Source Rows
本文介绍了DataTable组合值和删除源行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
示例 dtOrginal ：
code> testID passFail描述主机名
1ab pass .... alpha
1ab pass .... bravo
1ab fail .... charlie
1ac pass .. ..
示例当前结果：
testID passFail描述主机名
1ab pass .... alpha，bravo
1ab pass .... bravo
1ab fail .... charlie
1ac pass .... alpha
我想要获得什么
testID passFail描述主机名
1ab pass .... alpha，bravo
1ab fail .... charlie
1ac pass .... alpha
这是我当前的功能，用于合并主机名和testID必要的
DataTable dtReducedColumns = CombineHostnames（dtOri nal）.Copy（）;
private static DataTable CombineHostnames（DataTable dt）
{
for（int i = 0; i
bool isDupe = false;
string a，b，c，d，e，f，g，h，k，l;
for（int j = 0; j< dt.Rows.Count; j ++）
{
a = dtOrginal.Rows [i] [0] .ToString（）; // testID
b = dt.Rows [j] [0] .ToString（）;
c = dtOrginal.Rows [i] [1] .ToString（）; // passFail
d = dt.Rows [j] [1] .ToString（）;
g = dtOrginal.Rows [i] [2] .ToString（）; // description
h = dt.Rows [j] [2] .ToString（）;
if（a == b&& c == d&& g == h）
{
e = dt.Rows [j] [10] .ToString（） ;
f = dtOrginal.Rows [i] [10] .ToString（）; // hostname
k = dtOrginalRows [i] [8] .ToString（）; // source
l = dt.Rows [j] [8] .ToString（）;
if（！e.Contains（f））
dt.Rows [j] [10] = e +，+ f; //组合主机名
if（！k.Contains（l））
dt.Rows [j] [8] = k +，+ l; // combine sources
isDupe = true;
//尝试添加dt.Rows [j] .Delete（）这里
//尝试添加dt.Rows [j-1] .Delete（）这里得到-1错误
break;
}
//尝试添加dt.Rows [j] .Delete（）这里
//尝试添加dt.Rows [j-1] .Delete（）这里得到-1错误
}
//尝试添加dt.Rows [j] .Delete（）这里
//尝试添加dt.Rows [j-1] .Delete（）这里得到-1错误
if（！isDupe）
{
dt.ImportRow（dtOrginal.Rows [i]）;
}
}
return dt;
}
我试图删除行的地方会抛出 IndexOutOfBounds 异常或返回一个类似于 dtOrginal 的表。
，这是我正在使用的整个DataTable：
dtChecklistFindingsTable = new DataTable（）; //列号
dtChecklistFindingsTable.Columns.Add（testID，typeof（string））; // 0
dtChecklistFindingsTable.Columns.Add（passFail，typeof（string））; // 1
dtChecklistFindingsTable.Columns.Add（description，typeof（string））; // 2
dtChecklistFindingsTable.Columns.Add（vulLevel，typeof（string））; // 3
dtChecklistFindingsTable.Columns.Add（推荐，typeof（string））; // 4
dtChecklistFindingsTable.Columns.Add（comments，typeof（string））; // 5
dtChecklistFindingsTable.Columns.Add（title，typeof（string））; // 6
dtChecklistFindingsTable.Columns.Add（testCheck，typeof（string））; // 7
dtChecklistFindingsTable.Columns.Add（source，typeof（string））; // 8
dtChecklistFindingsTable.Columns.Add（date，typeof（string））; // 9
dtChecklistFindingsTable.Columns.Add（hostName，typeof（string））; // 10
dtChecklistFindingsTable.Columns.Add（os，typeof（string））; // 11
dtChecklistFindingsTable.Columns.Add（ipAddr，typeof（string））; // 12
dtChecklistFindingsTable.Columns.Add（stigLevel，typeof（string））; // 13
dtChecklistFindingsTable.Columns.Add（stigSeverity，typeof（string））; // 14
dtChecklistFindingsTable.Columns.Add（sarStatus，typeof（string））; // 15
dtChecklistFindingsTable.Columns.Add（iaControl，typeof（string））; // 16
解决方案
这是一个很好的用例 Linq-To-DataTable ，特别是 Enumerable.GroupBy + String.Join
private static DataTable CombineHostnames（DataTable dtOrginal）
{
DataTable tblresult = dtOrginal。克隆（）; //空表，相同列
var rowGroups = dtOrginal.AsEnumerable（）
.GroupBy（row => new
{
Id = row.Field< string>（ testId），
passFail = row.Field< string>（passFail）
}）;
foreach（rowGroups中的var group）
{
DataRow row = tblresult.Rows.Add（）; //已经添加
row.SetField（testId，group.Key.Id）;
row.SetField（passFail，group.Key.passFail）;
row.SetField（description，group.First（）[description]）; // 首先？
string hostNames = String.Join（，，group.Select（r => r.Field< string>（hostname）））;
row.SetField（hostname，hostNames）;
}
return tblresult;
}
您可以按照我已经添加的相同方式添加其他列code> description 只需使用每个组中的第一个或另一个逻辑。
I have created a Datatable and I'm trying to reduce the number of rows. I was able to find a way to check if a testID exists in a table and merge hostnames but I am not sure how to remove the source rows.
Example dtOrginal:
testID passFail description hostname
1ab pass .... alpha
1ab pass .... bravo
1ab fail .... charlie
1ac pass .... alpha
Example Current Result:
testID passFail description hostname
1ab pass .... alpha, bravo
1ab pass .... bravo
1ab fail .... charlie
1ac pass .... alpha
What I'm trying to get
testID passFail description hostname
1ab pass .... alpha, bravo
1ab fail .... charlie
1ac pass .... alpha
Here is my current function to merge the hostnames and testIDs when neccessary
DataTable dtReducedColumns = CombineHostnames(dtOrinal).Copy();
private static DataTable CombineHostnames(DataTable dt)
{
for (int i = 0; i < dtOrginal.Rows.Count; i++)
{
bool isDupe = false;
string a, b, c, d, e, f, g, h, k, l;
for (int j = 0; j < dt.Rows.Count; j++)
{
a = dtOrginal.Rows[i][0].ToString(); //testID
b = dt.Rows[j][0].ToString();
c = dtOrginal.Rows[i][1].ToString(); //passFail
d = dt.Rows[j][1].ToString();
g = dtOrginal.Rows[i][2].ToString(); //description
h = dt.Rows[j][2].ToString();
if (a == b && c == d && g == h)
{
e = dt.Rows[j][10].ToString();
f = dtOrginal.Rows[i][10].ToString(); //hostname
k = dtOrginalRows[i][8].ToString(); //source
l = dt.Rows[j][8].ToString();
if (!e.Contains(f))
dt.Rows[j][10] = e + ", " + f; //combine hostnames
if (!k.Contains(l))
dt.Rows[j][8] = k + ", " + l; //combine sources
isDupe = true;
//tried adding dt.Rows[j].Delete() here
//tried adding dt.Rows[j-1].Delete() here get -1 error
break;
}
//tried adding dt.Rows[j].Delete() here
//tried adding dt.Rows[j-1].Delete() here get -1 error
}
//tried adding dt.Rows[j].Delete() here
//tried adding dt.Rows[j-1].Delete() here get -1 error
if (!isDupe)
{
dt.ImportRow(dtOrginal.Rows[i]);
}
}
return dt;
}
The places where I have tried to delete the rows either throws an IndexOutOfBounds exception or returns a table similar to dtOrginal.
For reference, here is the entire DataTable I'm working with:
dtChecklistFindingsTable = new DataTable(); //Column Number
dtChecklistFindingsTable.Columns.Add("testID", typeof(string)); // 0
dtChecklistFindingsTable.Columns.Add("passFail", typeof(string)); // 1
dtChecklistFindingsTable.Columns.Add("description", typeof(string)); // 2
dtChecklistFindingsTable.Columns.Add("vulLevel", typeof(string)); // 3
dtChecklistFindingsTable.Columns.Add("recommendation", typeof(string)); // 4
dtChecklistFindingsTable.Columns.Add("comments", typeof(string)); // 5
dtChecklistFindingsTable.Columns.Add("title", typeof(string)); // 6
dtChecklistFindingsTable.Columns.Add("testCheck", typeof(string)); // 7
dtChecklistFindingsTable.Columns.Add("source", typeof(string)); // 8
dtChecklistFindingsTable.Columns.Add("date", typeof(string)); // 9
dtChecklistFindingsTable.Columns.Add("hostName", typeof(string)); // 10
dtChecklistFindingsTable.Columns.Add("os", typeof(string)); // 11
dtChecklistFindingsTable.Columns.Add("ipAddr", typeof(string)); // 12
dtChecklistFindingsTable.Columns.Add("stigLevel", typeof(string)); // 13
dtChecklistFindingsTable.Columns.Add("stigSeverity", typeof(string)); // 14
dtChecklistFindingsTable.Columns.Add("sarStatus", typeof(string)); // 15
dtChecklistFindingsTable.Columns.Add("iaControl", typeof(string)); // 16
解决方案
This is a good use-case for Linq-To-DataTable, especially Enumerable.GroupBy + String.Join:
private static DataTable CombineHostnames(DataTable dtOrginal)
{
DataTable tblresult = dtOrginal.Clone(); // empty table, same columns
var rowGroups = dtOrginal.AsEnumerable()
.GroupBy(row => new
{
Id = row.Field<string>("testId"),
passFail = row.Field<string>("passFail")
});
foreach (var group in rowGroups)
{
DataRow row = tblresult.Rows.Add(); // already added now
row.SetField("testId", group.Key.Id);
row.SetField("passFail", group.Key.passFail);
row.SetField("description", group.First()["description"]); // the first?
string hostNames = String.Join(", ", group.Select(r => r.Field<string>("hostname")));
row.SetField("hostname", hostNames);
}
return tblresult;
}
You can add the other columns in the same way i've already added description by simply using the first of each group or by another logic.
这篇关于DataTable组合值和删除源行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
