public DataTable ExecuteTable(string qry, ADOMD.AdomdConnection cnn) { ADOMD.AdomdDataAdapter dataAdapter = new ADOMD.AdomdDataAdapter(qry, cnn); DataTable tabularResults = new DataTable(); dataAdapter.Fill(tabularResults); return(tabularResults); }
public DataTable ExecuteTable(string qry, ADOMD.AdomdConnection cnn) { ADOMD.AdomdDataAdapter dataAdapter = new ADOMD.AdomdDataAdapter(qry, cnn); DataTable tabularResults = new DataTable(); dataAdapter.Fill(tabularResults); foreach (System.Data.DataColumn column in tabularResults.Columns) { column.ColumnName = DaxDrillParser.RemoveBrackets(column.ColumnName); } return(tabularResults); }
public static DataTable ExecuteDrillthroughAndFixColumns(string sDrillthroughMDX) { AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP;"); try { AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand(); cmd.Connection = conn; cmd.CommandText = sDrillthroughMDX; DataTable tbl = new DataTable(); AdomdClient.AdomdDataAdapter adp = new AdomdClient.AdomdDataAdapter(cmd); TimeoutUtility.FillAdomdDataAdapter(adp, tbl); Dictionary <string, int> dictColumnNames = new Dictionary <string, int>(StringComparer.InvariantCultureIgnoreCase); foreach (DataColumn col in tbl.Columns) { string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", ""); if (dictColumnNames.ContainsKey(sNewColumnName)) { dictColumnNames[sNewColumnName]++; } else { dictColumnNames.Add(sNewColumnName, 1); } } foreach (DataColumn col in tbl.Columns) { string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", ""); if (dictColumnNames[sNewColumnName] > 1) { sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('[') + 1).Replace("[", "").Replace("]", "").Replace("$", ""); } if (!tbl.Columns.Contains(sNewColumnName)) { col.ColumnName = sNewColumnName; } } return(tbl); } finally { conn.Close(); } }
public static DataTable ExecuteDrillthroughAndFixColumns(string sDrillthroughMDX) { AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP"); try { AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand(); cmd.Connection = conn; cmd.CommandText = sDrillthroughMDX; DataTable tbl = new DataTable(); AdomdClient.AdomdDataAdapter adp = new AdomdClient.AdomdDataAdapter(cmd); TimeoutUtility.FillAdomdDataAdapter(adp, tbl); Dictionary<string, int> dictColumnNames = new Dictionary<string, int>(StringComparer.InvariantCultureIgnoreCase); foreach (DataColumn col in tbl.Columns) { string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", ""); if (dictColumnNames.ContainsKey(sNewColumnName)) dictColumnNames[sNewColumnName]++; else dictColumnNames.Add(sNewColumnName, 1); } foreach (DataColumn col in tbl.Columns) { string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", ""); if (dictColumnNames[sNewColumnName] > 1) sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('[') + 1).Replace("[", "").Replace("]", "").Replace("$", ""); if (!tbl.Columns.Contains(sNewColumnName)) col.ColumnName = sNewColumnName; } return tbl; } finally { conn.Close(); } }
public static DataTable ExecuteDrillthroughAndTranslateColumns(string sDrillthroughMDX) { Regex columnNameRegex = new Regex(@"\[(?<cube>[^]]*)]\.\[(?<level>[^]]*)]", RegexOptions.Compiled); string connStr = "Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP;Locale Identifier=" + Context.CurrentConnection.ClientCulture.LCID; AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient(connStr); Context.TraceEvent(999, 0, string.Format("ExecuteDrillthroughAndTranslateColumns ConnectionString: {0}", connStr)); try { Dictionary <string, string> translations = new Dictionary <string, string>(); // get level names var resColl = new AdomdClient.AdomdRestrictionCollection(); resColl.Add("CUBE_SOURCE", "3"); // dimensions resColl.Add("LEVEL_VISIBILITY", "3"); // visible and non-visible resColl.Add("CUBE_NAME", Context.CurrentCube); // visible and non-visible var dsLevels = conn.GetSchemaDataSet("MDSCHEMA_LEVELS", resColl); foreach (DataRow dr in dsLevels.Tables[0].Rows) { var sColName = string.Format("[${0}.[{1}]", dr["DIMENSION_UNIQUE_NAME"].ToString().Substring(1), dr["LEVEL_NAME"].ToString()); if (!translations.ContainsKey(sColName)) { translations.Add(sColName, dr["LEVEL_CAPTION"].ToString()); } } // get measure names resColl.Clear(); resColl.Add("CUBE_NAME", Context.CurrentCube); resColl.Add("MEASURE_VISIBILITY", 3); // visible and non-visible var dsMeasures = conn.GetSchemaDataSet("MDSCHEMA_MEASURES", resColl); foreach (DataRow dr in dsMeasures.Tables[0].Rows) { if (!translations.ContainsKey(string.Format("[{0}].[{1}]", dr["MEASUREGROUP_NAME"].ToString(), dr["MEASURE_NAME"].ToString()))) { translations.Add(string.Format("[{0}].[{1}]", dr["MEASUREGROUP_NAME"].ToString(), dr["MEASURE_NAME"].ToString()), dr["MEASURE_CAPTION"].ToString()); } } // get dimension names resColl.Clear(); resColl.Add("CUBE_NAME", Context.CurrentCube); var dsDims = conn.GetSchemaDataSet("MDSCHEMA_DIMENSIONS", resColl); AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand(); cmd.Connection = conn; cmd.CommandText = sDrillthroughMDX; DataTable tbl = new DataTable(); AdomdClient.AdomdDataAdapter adp = new AdomdClient.AdomdDataAdapter(cmd); TimeoutUtility.FillAdomdDataAdapter(adp, tbl); // loop through the columns looking for duplicate translation names Dictionary <string, int> dictColumnNames = new Dictionary <string, int>(StringComparer.InvariantCultureIgnoreCase); foreach (DataColumn col in tbl.Columns) { var colKey = col.ColumnName.Substring(0, col.ColumnName.LastIndexOf(']') + 1); if (translations.ContainsKey(colKey)) { string sNewColumnName = translations[colKey]; if (dictColumnNames.ContainsKey(sNewColumnName)) { dictColumnNames[sNewColumnName]++; } else { dictColumnNames.Add(sNewColumnName, 1); } } else { Context.TraceEvent(999, 0, string.Format("The translation for the column '{0}' was not found", col.ColumnName)); } } foreach (DataColumn col in tbl.Columns) { var colKey = col.ColumnName.Substring(0, col.ColumnName.LastIndexOf(']') + 1); var suffix = col.ColumnName.Substring(col.ColumnName.LastIndexOf("]") + 1); if (translations.ContainsKey(colKey)) { string sNewName = translations[colKey]; if (dictColumnNames[sNewName] > 1) { //if (string.IsNullOrWhiteSpace( suffix)){ //prefix with tablename var m = columnNameRegex.Matches(col.ColumnName); var dimName = m[0].Groups["cube"].Value.TrimStart('$'); var dimCaption = dsDims.Tables[0].Select(string.Format("DIMENSION_NAME = '{0}'", dimName))[0]["DIMENSION_CAPTION"].ToString(); sNewName = dimCaption + "." + sNewName + suffix; //} //else { // col.ColumnName = sNewName + suffix; //} } Context.TraceEvent(999, 0, string.Format("translating: '{0}' to '{1}'", col.ColumnName, sNewName)); col.ColumnName = sNewName; } } //foreach (DataColumn col in tbl.Columns) //{ // string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", ""); // if (dictColumnNames[sNewColumnName] > 1) // sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('[') + 1).Replace("[", "").Replace("]", "").Replace("$", ""); // if (!tbl.Columns.Contains(sNewColumnName)) // col.ColumnName = sNewColumnName; //} return(tbl); } catch (Exception ex) { Context.TraceEvent(999, 0, string.Format("Unhandled Exception: {0}", ex.Message)); return(null); } finally { conn.Close(); } }