OfflineCubeHelper

操作OfflineCube,自写查询工具使用了该类

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.OleDb; using System.Xml.Linq; using System.Text.RegularExpressions; namespace DAL { public class CubeHelper { public static string ConnStr = ConfigurationManager.ConnectionStrings["MSOLAPConnstr"].ConnectionString; public static string QueryResultFileName = @"Queryinfo.xml"; private const string columnFullNamePattern = @"(/[(.*?)/])+"; private const string DEFULT_COLUMN_NAME = "MEMBER_CAPTION"; public void Query(string mdx, Dictionary<string, string> columnNames, List<List<object>> rowValues,List<string> prefixColumns, bool saveXml) { using (OleDbConnection conn = new OleDbConnection(ConnStr)) { using (OleDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = mdx; conn.Open(); OleDbDataReader read = cmd.ExecuteReader(); for (int i = 0; i < read.FieldCount; i++) { string columnFullName = read.GetName(i); columnNames.Add(columnFullName, GetColumnName(columnFullName)); } while (read.Read()) { List<object> row = new List<object>(); for (int i = 0; i < read.FieldCount; i++) { object val = read.GetValue(i); row.Add(val); } rowValues.Add(row); } read.Close(); } } SaveToXml(mdx, columnNames, rowValues); } public void Query(string mdx, List<string> prefixColumns, bool saveXml = true) { Dictionary<string, string> columnNames = new Dictionary<string, string>(); List<List<object>> rowValues = new List<List<object>>(); Query(mdx, columnNames, rowValues,prefixColumns, saveXml); } private void SaveToXml(string mdx, Dictionary<string, string> columnNames, List<List<object>> rowValues,List<string> prefixColumns) { XElement xRoot = new XElement("CubeQuery"); //Mdx Elenment //XAttribute xMdx = new XAttribute("MDX", mdx); //xRoot.Add(xMdx); XElement xMdx = new XElement("Mdx"); XCData xMdxValue = new XCData(mdx); xMdx.Add(xMdxValue); xRoot.Add(xMdx); //Columns Elenment XElement xColumnNames = new XElement("Columns"); XAttribute xColumnNameCount = new XAttribute("Count", columnNames.Count); xColumnNames.Add(xColumnNameCount); int index = 0; foreach (var item in columnNames.Keys) { XElement xColumn = new XElement("Column" + index); XAttribute xColumnName = new XAttribute("Name", columnNames[item]); XAttribute xColumnFullName = new XAttribute("FullName", item); xColumn.Add(xColumnName); xColumn.Add(xColumnFullName); xColumnNames.Add(xColumn); index++; } xRoot.Add(xColumnNames); //Row Data(for each column) Elenment XElement xRows = new XElement("Rows"); XAttribute xVRowCount = new XAttribute("Count", rowValues.Count); xRows.Add(xVRowCount); for (int i = 0; i < rowValues.Count; i++) { XElement xRow = new XElement("Row" + i); for (int j = 0; j < rowValues[i].Count; j++) { XAttribute xColumnAtt = new XAttribute("Column" + j, rowValues[i][j]); xRow.Add(xColumnAtt); } xRows.Add(xRow); } xRoot.Add(xRows); xRoot.Save(QueryResultFileName); } private static string GetColumnName(string columnFullName) { List<string> fields = GetAllFields(columnFullName); int count = fields.Count - 1; while (count > 0) { string tempColumnName = fields[count]; if (tempColumnName.Length > 0 || !tempColumnName.Equals(DEFULT_COLUMN_NAME)) { return tempColumnName; } count--; } return DEFULT_COLUMN_NAME; } public static List<string> GetAllFields(string columnFullName) { List<string> list = new List<string>(); MatchCollection collection = Regex.Matches(columnFullName, columnFullNamePattern); for (int i = 0; i < collection.Count; i++) { list.Add(collection[i].Groups[2].Value); } return list; } public static List<string> GetParents(List<string> ancestry, List<List<string>> childrenCollection) { List<string> list = new List<string>(); foreach (var children in childrenCollection) { int m = children.Count - 1; for (int i = ancestry.Count - 1, j = m; i > 0 && j >= 0; ) { if (ancestry[i] == children[j]) { i--; j--; } else { i--; j = m; } if (j < 0 && !string.IsNullOrEmpty(ancestry[i])) list.Add(ancestry[i]); } } return list; } public XElement QueryX(string mdx, List<string> prefixColumns) { } } }