17 April 2008

(C#) Flatten DataSet to DataTable

Starting with a valid DataSet:

DataSet s = new DataSet();
s.ReadXml(@"C:\orders.xml");

Add table name to all columns that aren't used for binding ...

foreach(DataTable table in s.Tables) {
foreach (DataColumn column in table.Columns)
{
if(!column.ColumnName.Contains("_Id")) column.ColumnName = table.TableName + "." + column.ColumnName;
}
}

Now join all using David M.'s great DataTable Join function (over at http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx):

DataTable t = s.Tables[s.Relations[0].ChildTable.TableName];
foreach (DataRelation r in s.Relations)
{
t = Join(s.Tables[r.ParentTable.TableName], t, r.ParentColumns[0].ColumnName, r.ChildColumns[0].ColumnName);
}

List removeColumns = new List();
foreach (DataColumn c in t.Columns) if (c.ColumnName.Contains("_Id")) removeColumns.Add(c.ColumnName);
foreach (string cName in removeColumns) t.Columns.Remove(cName);

Pretty easy, huh ;)

No comments: