public static string[] CleanColumnNames(this ADOTabular.AdomdClientWrappers.AdomdDataReader reader)
{
string[] columns = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
columns[i] = reader.GetName(i);
}
var columnPattern = new Regex(@"\[(?<col>.*)]\d*$", RegexOptions.Compiled);
var mdxPattern = new Regex(@"\[[^\]]*\].\[[^\]]*\]");
const string MEASURES_MDX = "[Measures].";
var newColumnNames = new Dictionary <string, DaxColumn>();
// If at least one column has the Mdx syntax, identify the result as an MDX query (hoping the assumption is always true...)
//bool isMdxResult = (from DataColumn col in dataTable.Columns
// where col.ColumnName.IndexOf("].[") > 0
// select col).Count() > 0;
bool isMdxResult = (from col in columns
where mdxPattern.IsMatch(col)
select col).Count() > 0;
var measuresColumns = (from col in columns
where col.IndexOf(MEASURES_MDX) >= 0
select col);
bool hasPlainMeasures = (from col in measuresColumns
where col.IndexOf("].[", col.IndexOf(MEASURES_MDX) + MEASURES_MDX.Length) > 0
select col).Count() == 0;
foreach (string columnName in columns)
{
bool removeCaption = false;
string name = columnName;
bool removeSquareBrackets = !isMdxResult;
int measuresMdxPos = name.IndexOf(MEASURES_MDX);// + MEASURES_MDX.Length;
if (isMdxResult)
{
if ((measuresMdxPos >= 0))
{
if ((name.IndexOf("].[", measuresMdxPos + MEASURES_MDX.Length) == -1) &&
(name.IndexOf("].[", 0) == MEASURES_MDX.Length - 2))
{
removeSquareBrackets = true;
}
name = name.Replace(MEASURES_MDX, measuresMdxPos > 0 ? "\n" : "");
}
else
{
removeCaption = hasPlainMeasures;
}
}
if (removeSquareBrackets)
{
var m = columnPattern.Match(name);
if (m.Success)
{
name = m.Groups["col"].Value;
}
// Format column naming for DAX result or if it is a measure name
//int firstBracket = name.IndexOf('[') + 1;
//name = firstBracket == 0 ? name : name.Substring(firstBracket, name.Length - firstBracket - 1);
}
var dc = new DaxColumn()
{
//OriginalCaption = col.Caption,
OriginalName = columnName,
//NewCaption = (removeCaption) ? "" : name,
NewName = name.Replace(' ', '`').Replace(',', '`'),
};
newColumnNames.Add(dc.OriginalName, dc);
//col.Caption = (removeCaption) ? "" : name;
//col.ColumnName = name.Replace(' ', '_');
}
// check for duplicate names
for (var outerIdx = 0; outerIdx < newColumnNames.Count; outerIdx++)
{
for (var innerIdx = outerIdx + 1; innerIdx < newColumnNames.Count; innerIdx++)
{
if (newColumnNames.ElementAt(outerIdx).Value.NewName == newColumnNames.ElementAt(innerIdx).Value.NewName)
{
newColumnNames.ElementAt(outerIdx).Value.UseOriginalName = true;
newColumnNames.ElementAt(innerIdx).Value.UseOriginalName = true;
}
}
}
string[] newNames = new string[columns.Length];
// Update names
for (int i = 0; i < columns.Length; i++)
{
var c = newColumnNames.ElementAt(i).Value;
newNames[i] = c.UseOriginalName ? c.OriginalName : c.NewName;
}
return(newNames);
}
public static void FixColumnNaming(this DataTable dataTable, string query)
{
var columnPattern = new Regex(@"\[(?<col>.*)]\d*$", RegexOptions.Compiled);
const string MEASURES_MDX = "[Measures].";
var newColumnNames = new Collection <DaxColumn>();
// If at least one column has the Mdx syntax, identify the result as an MDX query (hoping the assumption is always true...)
//bool isMdxResult = (from DataColumn col in dataTable.Columns
// where col.ColumnName.IndexOf("].[") > 0
// select col).Count() > 0;
bool isMdxResult = IsMdxQuery(query);
var measuresColumns = (from DataColumn col in dataTable.Columns
where col.ColumnName.IndexOf(MEASURES_MDX) >= 0
select col);
bool hasPlainMeasures = measuresColumns.Count() > 0 && (from DataColumn col in measuresColumns
where col.ColumnName.IndexOf("].[", col.ColumnName.IndexOf(MEASURES_MDX) + MEASURES_MDX.Length) > 0
select col).Count() == 0;
foreach (DataColumn col in dataTable.Columns)
{
bool removeCaption = false;
string name = col.ColumnName;
bool removeSquareBrackets = !isMdxResult;
int measuresMdxPos = name.IndexOf(MEASURES_MDX);// + MEASURES_MDX.Length;
if (isMdxResult)
{
if ((measuresMdxPos >= 0))
{
if ((name.IndexOf("].[", measuresMdxPos + MEASURES_MDX.Length) == -1) &&
(name.IndexOf("].[", 0) == MEASURES_MDX.Length - 2))
{
removeSquareBrackets = true;
}
name = name.Replace(MEASURES_MDX, measuresMdxPos > 0 ? "\n" : "");
}
else
{
removeCaption = hasPlainMeasures;
}
}
if (removeSquareBrackets)
{
var m = columnPattern.Match(name);
if (m.Success)
{
name = m.Groups["col"].Value;
}
// Format column naming for DAX result or if it is a measure name
//int firstBracket = name.IndexOf('[') + 1;
//name = firstBracket == 0 ? name : name.Substring(firstBracket, name.Length - firstBracket - 1);
}
var dc = new DaxColumn()
{
OriginalCaption = col.Caption,
OriginalName = col.ColumnName,
NewCaption = (removeCaption) ? "" : name,
NewName = name.Replace(' ', '`').Replace(',', '`'),
};
newColumnNames.Add(dc);
//col.Caption = (removeCaption) ? "" : name;
//col.ColumnName = name.Replace(' ', '_');
}
// check for duplicate names
for (var outerIdx = 0; outerIdx < newColumnNames.Count; outerIdx++)
{
for (var innerIdx = outerIdx + 1; innerIdx < newColumnNames.Count; innerIdx++)
{
if (newColumnNames[outerIdx].NewName == newColumnNames[innerIdx].NewName)
{
newColumnNames[outerIdx].UseOriginalName = true;
newColumnNames[innerIdx].UseOriginalName = true;
}
}
}
// Update names
foreach (DaxColumn c in newColumnNames)
{
var dc = dataTable.Columns[c.OriginalName];
dc.Caption = c.NewCaption;
if (!c.UseOriginalName)
{
// var dc = dataTable.Columns[c.OriginalName];
// dc.Caption = c.NewCaption;
dc.ColumnName = c.NewName;
}
}
}