private ResultSet PopulateFromXml(XmlReader xmlReader)
{
var crs = new ResultSet();
using (xmlReader)
{
var doc = XDocument.Load(xmlReader);
var axis = GetAxis(doc);
var rows = axis.Where(a => a.Axis == "Axis1");
var columns = axis.Where(a => a.Axis == "Axis0");
var cells = GetCellData(doc);
var rowColumnCount = AddColumnsFromRowAxis(rows, crs);
AddColumnsFromColumnAxis(columns, cells, crs);
ProcessOrdinalColumns(crs);
AddRows(rows, cells, rowColumnCount, crs);
}
return crs;
}
private void ProcessOrdinalColumns(ResultSet crs)
{
foreach (var column in _columnMap)
{
int ordinal;
if (!Int32.TryParse(column.NameWithoutPrefixes, out ordinal)) continue;
if (ordinal >= 0 && ordinal < crs.Columns.Count)
{
crs.Columns[ordinal].Name = column.Value.ToString();
}
}
}
private void AddRows(IEnumerable<Tuple> rows, List<Cell> cells, int rowColumnCount, ResultSet crs)
{
var start = 0;
var columnCountFromColumnAxis = crs.Columns.Count - rowColumnCount;
var finish = columnCountFromColumnAxis - 1;
var cellsIndexer = 0;
var cellsCount = cells.Count();
var ordinal = 0;
if (!rows.Any() && cells.Count > 0)
{
// data coming back from the cube only has cell for actual data, nulls are not represented. We need to fill in those cells so that the data appears
// in the correct columns
var ordinals = cells.Select(a => a.Ordinal);
Enumerable.Range(0, columnCountFromColumnAxis)
.Except(ordinals)
.ToList()
.ForEach(a => cells.Add(new Cell() { Ordinal = a}));
cells.ForEach(a => AdjustValueFromColumnType(a, a.Ordinal, crs));
crs.Rows.Add(new Row() { Cells = cells.OrderBy(a => a.Ordinal).ToList() });
}
else
{
// tokened maps that get properties from members in the row
var memberProperties = _columnMap.GetMemberProperties();
var columnsAdded = memberProperties.Count();
AddColumnsFromRowProperties(crs, memberProperties);
foreach (var row in rows)
{
var r = new Row();
// main row data
foreach (var member in row.Members)
{
r.Cells.Add(new Cell() { FormattedValue = member.Caption, Value = member.Caption, Ordinal = ordinal++ });
}
// dimension property row data
// this is done as another pass since dimension properties columns are added at the end of normal columns from row
foreach (var property in row.Members.Where(member => member.DimensionProperties != null).SelectMany(member => member.DimensionProperties))
{
r.Cells.Add(new Cell() { FormattedValue = property.Value, Value = property.Value, Ordinal = ordinal++ });
}
// cells are in a single dimension array, have to determine which row it belongs to to intermix the row data correctly
for (var i = start; i <= finish; i++)
{
var cellToAdd = new Cell();
// cell indexer can go past its range, only try to get values while in range
if (!(cellsIndexer >= cellsCount))
{
// get this cell
var cell = cells[cellsIndexer];
// if the ordinal of the cell is the column we are looking at, add it's values, otherwise an empty cell is added.
// this is done because the xml coming back does not include nulls/empty data. We have to fill in the gap or the subsequent objects will throw the data off
if (Convert.ToInt32(cell.Ordinal) == i)
{
cellToAdd = cell;
AdjustValueFromColumnType(cellToAdd, cell.Ordinal % (columnCountFromColumnAxis) + rowColumnCount, crs);
cellsIndexer++;
}
}
cellToAdd.Ordinal = ordinal++;
r.Cells.Add(cellToAdd);
}
// go threw the members again, this time for the special columns, this is done here since the columns for them are done at the end
// if we tried to adding the cells in the for above, it would through the ordinals off for all cells.
if (columnsAdded > 0)
{
for (var i = 0; i < row.Members.Count; i++ )
{
var previousCount = r.Cells.Count;
r.Cells.AddRange(AddCellsFromMemberProperties(memberProperties, row.Members[i], ordinal, i));
if (previousCount != r.Cells.Count)
{
// advance the ordinal since extra columns were added, the normal process would only take into consideration
// the cells visible in ssms
ordinal = r.Cells.Last().Ordinal + 1;
}
}
}
crs.Rows.Add(r);
start += columnCountFromColumnAxis;
finish += columnCountFromColumnAxis;
}
}
}
private int AddColumnsFromRowAxis(IEnumerable<Tuple> rows, ResultSet crs)
{
var dimensionProperties = new List<string>();
var columnCount = 0;
if (null != rows && rows.Any())
{
foreach (var member in rows.First().Members)
{
var column = new Column {ColumnOrdinal = columnCount++};
SetColumnNameAndType(column, member.LevelName, typeof(string));
column.Items.Add(column.Name);
crs.Columns.Add(column);
}
var dimensionPropertyColumns = rows.SelectMany(
(row) => row.Members.SelectMany(
// Project the dimension properties so we also get the member's index within the row for each:
(member, memberIndex) => member.DimensionProperties.Select(
(dimensionProp) => new
{
DimensionProperty = dimensionProp,
MemberIndex = memberIndex
}),
// Turn all this business into what we're really looking for:
(member, x) => new
{
//ParentColumn = member.LevelName,
ChildColumn = x.DimensionProperty.UniqueName,
MemberIndex = x.MemberIndex
})).Distinct();
// dimension properties are looked at for all rows where the columns above is just the first row
// it is very possible to get data in further down rows for a dimension properties that doesn't exist on the first row
// an example is in org with parent child where a property may exist for only one level
foreach (var dimensionProperty in dimensionPropertyColumns)
{
var propertyColumn = new Column() {ColumnOrdinal = columnCount};
//only add column if not already added
// each column can have a dimension property that may already be present
var columnName = dimensionProperty.MemberIndex.ToString(CultureInfo.InvariantCulture) + dimensionProperty.ChildColumn;
if (!dimensionProperties.Any(a => string.Equals(a, columnName)))
{
SetColumnNameAndType(propertyColumn, columnName, typeof(string));
columnCount++;
crs.Columns.Add(propertyColumn);
propertyColumn.Items.Add(propertyColumn.Name);
dimensionProperties.Add(dimensionProperty.ChildColumn);
}
}
}
return columnCount;
}
private void AddColumnsFromColumnAxis(IEnumerable<Tuple> columns, IEnumerable<Cell> cells, ResultSet crs)
{
var cellOrdinal = 0;
var columnOrdinal = crs.Columns.Count;
foreach (var tuple in columns)
{
var sb = new StringBuilder();
var column = new Column();
foreach (var member in tuple.Members)
{
sb.Append(member.UniqueName);
column.Items.Add(member.Caption);
}
column.CellOrdinal = ++cellOrdinal;
column.ColumnOrdinal = columnOrdinal++;
SetColumnNameAndType(column, sb.ToString());
crs.Columns.Add(column);
}
// this is done after all the columns are added because we need to know the total column count for any of the modus math to work correctly
// at least any of the modulus math i could think of so far :)
crs.Columns.Where(a => a.Type == null).ToList().ForEach(a => a.Type = GetTypeForColumn(cells, a, cellOrdinal));
}
private static void AdjustValueFromColumnType(Cell cell, int columnIndex, ResultSet crs)
{
// change type was giving odd results when a culture was passed in on the thread, for example German 5.324145E1 came out as 5324145 instead of 53.24145
// we give it invariant culture to fix this. It will be up to the end user to apply formatting.
cell.Value = Convert.ChangeType(cell.Value, crs.Columns[columnIndex].Type ?? ConvertXmlTypeToType(cell.Type), CultureInfo.InvariantCulture);
}
/// <summary>
/// find the tokened column map
/// </summary>
/// <param name="crs"></param>
/// <param name="columnMap"></param>
private static void AddColumnsFromRowProperties(ResultSet crs, IEnumerable<ColumnMap> columnMap)
{
int max;
var cels = crs.Columns.Select(a => a.CellOrdinal);
if (cels.Any())
{
max = cels.Max();
}
else
{
max = 0;
}
foreach(var map in columnMap)
{
var type = map.IsLevelNumber ? typeof(int) : typeof (string);
crs.Columns.Add(new Column() { CellOrdinal = ++max, Name = map.Value.ToString(), Type = type });
}
}