Splitting a DataTable into 2 using a column index

Question

I have a stored procedure which selects the differences between rows in a table and returns a DataTable in the following format:

col1_A, col2_A, col3_A, col1_B, col2_B, col3B

I would like to split the DataTable into 2 separate DataTables so it looks like

TableA

col1
col2
col3

TableB

col1
col2
col3

This code gets me the column index.

        foreach (DataColumn col in DT.Columns)
        {
            if (!col.ColumnName.EndsWith("B"))
                tableBIndex += 1;
            else
                break;
        }

but from here I'm not sure how to separate the rows into 2 DataTables. Any ideas on the best way to accomplish this?

Answer 1

This should do the trick and fill both tables in one loop:

DataColumn[] aCols = table.Columns.Cast<DataColumn>()
    .Where(c => c.ColumnName.EndsWith("A"))
    .Select(c => new DataColumn(c.ColumnName, c.DataType))
    .ToArray();
DataColumn[] bCols = table.Columns.Cast<DataColumn>()
    .Where(c => c.ColumnName.EndsWith("B"))
    .Select(c => new DataColumn(c.ColumnName, c.DataType))
    .ToArray();

var TableA = new DataTable();
TableA.Columns.AddRange(aCols);
var TableB = new DataTable();
TableB.Columns.AddRange(bCols);

foreach (DataRow row in table.Rows)
{ 
    DataRow aRow = TableA.Rows.Add();
    DataRow bRow = TableB.Rows.Add();
    foreach (DataColumn aCol in aCols)
        aRow.SetField(aCol, row[aCol.ColumnName]);
    foreach (DataColumn bCol in bCols)
        bRow.SetField(bCol, row[bCol.ColumnName]);
}

Answer 2

I'd recommend creating two new tables and copying out values like this.

DataTable one = new DataTable();
DataTable two = new DataTable();
foreach (DataColumn col in DT.Columns)
{
  if (!col.ColumnName.EndsWith("B"))
  {
    one.Columns.Add(col.ColumnName.Replace("_A", ""));
    foreach (DataRow row in DT.Rows)
    {
      one.Rows.Add(row[col.ColumnName]);
    }
  }
  else
  {
    one.Columns.Add(col.ColumnName.Replace("_B", ""));
    foreach (DataRow row in DT.Rows)
    {
      two.Rows.Add(row[col.ColumnName]);
    }
  }
}