Parsing DataTable from flat table to normailze table

I have a requirement for B2B inte开发者_运维问答gration, I will reading flat table from dataset and parse to a normalize form of datatable.

I will have reapting colums in the datatable Sample Data

Invoice num     Amount    LineNum   Line Amout  Ledger
INV1            100       1         50          11101
INV1            100       2         50          25631 

rows will repeat with different invoices

How can distinctly select into new datatable ? using ADO.NET

I want to parse the data into following format

Header Table

  Invoice num     Amount    
  INV1            100         

Line Table

  Invoice num  LineNum   Line Amout  Ledger
  INV1          1         50          11101
  INV1          2         50          25631 

QUESTION : I dont know what would be the best way to bring the above format ? I see examples usign linq , DataTable, Views ? I looking for a code snippet.

OK, to start the problem, I am working with a DataTable and data defined as follows. Change names and types to suit your needs.

// I am building this table in code just for the purposes of this answer.
// If you already have your data table, ignore!
DataTable salesTable = new DataTable();
salesTable.Columns.Add("InvoiceNum", typeof(string));
salesTable.Columns.Add("Amount", typeof(decimal));
salesTable.Columns.Add("LineNum", typeof(int));
salesTable.Columns.Add("LineAmount", typeof(decimal));
salesTable.Columns.Add("Ledger", typeof(string));

// This is also just to populate data for the sample.
// Omit as you already have your data.
salesTable.Rows.Add("INV1", 100M, 1, 50M, "11101");
salesTable.Rows.Add("INV1", 100M, 1, 50M, "25631");

Notice that I'm using the overload of .Rows.Add that accepts a params object[] array. The values I'm passing in are in the order and type of the columns they should populate. The code below uses the same approach.

First thing I want to do is define the tables for your new normalized format. First, the header table.

DataTable headerTable = new DataTable();
headerTable.Columns.Add("InvoiceNum", typeof(string));
headerTable.Columns.Add("Amount", typeof(decimal));

And then the line item table.

DataTable lineTable = new DataTable();
lineTable.Columns.Add("InvoiceNum", typeof(string));
lineTable.Columns.Add("LineNum", typeof(int));
lineTable.Columns.Add("LineAmount", typeof(decimal));
lineTable.Columns.Add("Ledger", typeof(string));

After this, I'm going to utilize LINQ to group the original sales table based on the invoice number.

var groupedData = from row in salesTable.AsEnumerable()
                  group row by row.Field<string>("InvoiceNum") into grp
                  select grp;

After this, it's just a matter of iterating over the groups and adding the data to the new tables.

foreach (var invoiceGroup in groupedData)
{
    string invoiceNumber = invoiceGroup.Key;
    decimal amount = invoiceGroup.First().Field<decimal>("Amount");

    headerTable.Rows.Add(invoiceNumber, amount);

    foreach (DataRow row in invoiceGroup)
    {
        lineTable.Rows.Add(
                invoiceNumber,
                row.Field<int>("LineNum"),
                row.Field<decimal>("LineAmount"),
                row.Field<string>("Ledger")
            );
    }
}

And now you have your data in the normalized format you prefer. Again, change relevant column names and data types to suit your needs.