Chris Chris - 4 months ago 16
C# Question

DataTable Combine Values and Remove Source Rows

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

Answer

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.