public static DataSet ConvertToDataSet(this ADOTabular.AdomdClientWrappers.AdomdDataReader reader) { ADOTabular.ADOTabularColumn daxCol; DataSet ds = new DataSet(); bool moreResults = true; int tableIdx = 1; while (moreResults) { DataTable dtSchema = reader.GetSchemaTable(); DataTable dt = new DataTable(tableIdx.ToString()); // You can also use an ArrayList instead of List<> List <DataColumn> listCols = new List <DataColumn>(); if (dtSchema != null) { foreach (DataRow drow in dtSchema.Rows) { string columnName = System.Convert.ToString(drow["ColumnName"]); DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"])); column.Unique = (bool)drow["IsUnique"]; column.AllowDBNull = (bool)drow["AllowDBNull"]; //column.AutoIncrement = (bool)drow["IsAutoIncrement"]; daxCol = null; reader.Connection.Columns.TryGetValue(columnName, out daxCol); if (daxCol != null) { column.ExtendedProperties.Add("FormatString", daxCol.FormatString); } listCols.Add(column); dt.Columns.Add(column); } } // Read rows from DataReader and populate the DataTable while (reader.Read()) { DataRow dataRow = dt.NewRow(); for (int i = 0; i < listCols.Count; i++) { dataRow[((DataColumn)listCols[i])] = reader[i] ?? DBNull.Value; } dt.Rows.Add(dataRow); } dt.FixColumnNaming(reader.CommandText); ds.Tables.Add(dt); moreResults = reader.NextResult(); tableIdx++; } return(ds); }
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 DataSet ConvertToDataSet(this ADOTabular.AdomdClientWrappers.AdomdDataReader reader, bool autoFormat = false) { ADOTabular.ADOTabularColumn daxCol; DataSet ds = new DataSet(); bool moreResults = true; int tableIdx = 1; int localeId = reader.Connection.LocaleIdentifier; while (moreResults) { DataTable dtSchema = reader.GetSchemaTable(); DataTable dt = new DataTable(tableIdx.ToString()); // You can also use an ArrayList instead of List<> List <DataColumn> listCols = new List <DataColumn>(); if (dtSchema != null) { foreach (DataRow row in dtSchema.Rows) { string columnName = Convert.ToString(row["ColumnName"]); DataColumn column = new DataColumn(columnName, (Type)(row["DataType"])); column.Unique = (bool)row[Constants.IS_UNIQUE]; column.AllowDBNull = (bool)row[Constants.ALLOW_DBNULL]; daxCol = null; reader.Connection.Columns.TryGetValue(columnName, out daxCol); if (daxCol != null) { column.ExtendedProperties.Add(Constants.FORMAT_STRING, daxCol.FormatString); if (localeId != 0) { column.ExtendedProperties.Add(Constants.LOCALE_ID, localeId); } } else if (autoFormat) { string formatString; switch (column.DataType.Name) { case "Decimal": case "Double": if (column.Caption.Contains(@"%") || column.Caption.Contains("Pct")) { formatString = "0.00%"; } else { formatString = "#,0.00"; } break; case "Int64": formatString = "#,0"; break; default: formatString = null; break; } if (formatString != null) { column.ExtendedProperties.Add(Constants.FORMAT_STRING, formatString); if (localeId != 0) { column.ExtendedProperties.Add(Constants.LOCALE_ID, localeId); } } } listCols.Add(column); dt.Columns.Add(column); } } // Read rows from DataReader and populate the DataTable while (reader.Read()) { DataRow dataRow = dt.NewRow(); for (int i = 0; i < listCols.Count; i++) { dataRow[((DataColumn)listCols[i])] = reader[i] ?? DBNull.Value; } dt.Rows.Add(dataRow); } dt.FixColumnNaming(reader.CommandText); ds.Tables.Add(dt); moreResults = reader.NextResult(); tableIdx++; } return(ds); }
/// <summary> /// Writes a DataTable object to a StreamWriter /// </summary> /// <param name="reader"></param> /// <param name="textWriter"></param> /// <param name="sep"></param> /// <param name="shouldQuoteStrings"></param> /// <param name="isoDateFormat"></param> /// <param name="statusProgress"></param> /// <returns></returns> public static int WriteToStream(this ADOTabular.AdomdClientWrappers.AdomdDataReader reader, TextWriter textWriter, string sep, bool shouldQuoteStrings, string isoDateFormat, IStatusBarMessage statusProgress) { int iMaxCol = reader.FieldCount - 1; int iRowCnt = 0; using (var csvWriter = new CsvHelper.CsvWriter(textWriter, CultureInfo.InvariantCulture)) { // CSV Writer config csvWriter.Configuration.Delimiter = sep; // Datetime as ISOFormat csvWriter.Configuration.TypeConverterOptionsCache.AddOptions( typeof(DateTime), new CsvHelper.TypeConversion.TypeConverterOptions() { Formats = new string[] { isoDateFormat } }); // write out clean column names foreach (var colName in reader.CleanColumnNames()) { csvWriter.WriteField(colName); } csvWriter.NextRecord(); while (reader.Read()) { iRowCnt++; for (int iCol = 0; iCol < reader.FieldCount; iCol++) { var fieldValue = reader[iCol]; // quote all string fields if (reader.GetFieldType(iCol) == typeof(string)) { if (reader.IsDBNull(iCol)) { csvWriter.WriteField("", shouldQuoteStrings); } else { csvWriter.WriteField(fieldValue.ToString(), shouldQuoteStrings); } } else { csvWriter.WriteField(fieldValue); } } csvWriter.NextRecord(); if (iRowCnt % 1000 == 0) { statusProgress.Update($"Written {iRowCnt:n0} rows to the file output"); } } } return(iRowCnt); }
public static DataSet ConvertToDataSet(this ADOTabular.AdomdClientWrappers.AdomdDataReader reader, bool autoFormat, bool IsSessionsDmv, string autoDateFormat) { ADOTabular.ADOTabularColumn daxCol; DataSet ds = new DataSet(); bool moreResults = true; int tableIdx = 1; int localeId = reader.Connection.LocaleIdentifier; while (moreResults) { DataTable dtSchema = reader.GetSchemaTable(); DataTable dt = new DataTable(tableIdx.ToString()); // You can also use an ArrayList instead of List<> List <DataColumn> listCols = new List <DataColumn>(); if (dtSchema != null) { foreach (DataRow row in dtSchema.Rows) { string columnName = Convert.ToString(row["ColumnName"]); Type columnType = (Type)row["DataType"]; if (columnType.Name == "XmlaDataReader") { columnType = typeof(string); } DataColumn column = new DataColumn(columnName, columnType); // (Type)(row["DataType"])); column.Unique = (bool)row[Constants.IsUnique]; column.AllowDBNull = (bool)row[Constants.AllowDbNull]; daxCol = null; reader.Connection.Columns.TryGetValue(columnName, out daxCol); if (IsSessionsDmv && columnName == Common.Constants.SessionSpidColumn) { column.ExtendedProperties.Add(Constants.SessionSpidColumn, true); } if (daxCol != null && !string.IsNullOrEmpty(daxCol.FormatString)) { column.ExtendedProperties.Add(Constants.FormatString, daxCol.FormatString); if (localeId != 0) { column.ExtendedProperties.Add(Constants.LocaleId, localeId); } } else if (autoFormat) { string formatString; switch (column.DataType.Name) { case "Decimal": case "Double": case "Object": if (column.Caption.Contains(@"%") || column.Caption.Contains("Pct")) { formatString = "0.00%"; } else { formatString = "#,0.00"; } break; case "Int64": formatString = "#,0"; break; case "DateTime": if (string.IsNullOrWhiteSpace(autoDateFormat) || column.Caption.ToLower().Contains(@"time") || column.Caption.ToLower().Contains(@"hour")) { formatString = null; } else { formatString = "yyyy-MM-dd"; } break; default: formatString = null; break; } if (formatString != null) { column.ExtendedProperties.Add(Constants.FormatString, formatString); if (localeId != 0) { column.ExtendedProperties.Add(Constants.LocaleId, localeId); } } } listCols.Add(column); dt.Columns.Add(column); } } // Read rows from DataReader and populate the DataTable while (reader.Read()) { DataRow dataRow = dt.NewRow(); for (int i = 0; i < listCols.Count; i++) { if (reader.IsDataReader(i)) { dataRow[((DataColumn)listCols[i])] = reader.GetDataReaderValue(i); } else { dataRow[((DataColumn)listCols[i])] = reader[i] ?? DBNull.Value; } } dt.Rows.Add(dataRow); } dt.FixColumnNaming(reader.CommandText); ds.Tables.Add(dt); moreResults = reader.NextResult(); tableIdx++; } return(ds); }
/// <summary> /// Writes a ADODataReader to a Worksheet /// </summary> public static int WriteToWorksheet(ADOTabular.AdomdClientWrappers.AdomdDataReader reader, XlsxWriter xlsxWriter, IStatusBarMessage statusProgress, IQueryRunner runner) { int iMaxCol = reader.FieldCount - 1; int iRowCnt = 0; ADOTabularColumn daxCol; int colIdx = 0; XlsxStyle[] columnStyles = new XlsxStyle[reader.FieldCount]; var headerStyle = new XlsxStyle( new XlsxFont("Segoe UI", 9, Color.White, bold: true), new XlsxFill(Color.FromArgb(0, 0x45, 0x86)), XlsxStyle.Default.Border, XlsxStyle.Default.NumberFormat, XlsxAlignment.Default); var wrapStyle = XlsxStyle.Default.With(new XlsxAlignment(vertical: Vertical.Top, wrapText: true)); var defaultStyle = XlsxStyle.Default; // Write out Header Row xlsxWriter.SetDefaultStyle(headerStyle).BeginRow(); foreach (var colName in reader.CleanColumnNames()) { // write out the column name xlsxWriter.Write(colName); // cache the column formatstrings as Excel Styles reader.Connection.Columns.TryGetValue(reader.GetName(colIdx), out daxCol); if (daxCol != null) { columnStyles[colIdx] = GetStyle(daxCol); } else { columnStyles[colIdx] = defaultStyle; } colIdx++; } xlsxWriter.SetDefaultStyle(defaultStyle); while (reader.Read()) { // check if we have reached the limit of an xlsx file if (iRowCnt >= 999999) { runner.OutputWarning("Results truncated, reached the maximum row limit for an Excel file"); break; } // increment row count iRowCnt++; // start outputting the next row xlsxWriter.BeginRow(); for (int iCol = 0; iCol < reader.FieldCount; iCol++) { var fieldValue = reader[iCol]; switch (fieldValue) { case int i: xlsxWriter.Write(i, columnStyles[iCol]); break; case double dbl: xlsxWriter.Write(dbl, columnStyles[iCol]); break; case decimal dec: xlsxWriter.Write(dec, columnStyles[iCol]); break; case DateTime dt: xlsxWriter.Write(dt, columnStyles[iCol]); break; case string str: if (str.Contains("\n") || str.Contains("\r")) { xlsxWriter.Write(str, wrapStyle); } else { xlsxWriter.Write(str); } break; case bool b: xlsxWriter.Write(b.ToString()); // Writes out TRUE/FALSE break; case null: xlsxWriter.Write(); break; case long lng: if (lng < int.MaxValue && lng > int.MinValue) { xlsxWriter.Write(Convert.ToInt32(lng), columnStyles[iCol]); } else // TODO - should we be converting large long values to double?? { xlsxWriter.Write(lng.ToString()); // write numbers outside the size of int as strings } break; default: xlsxWriter.Write(fieldValue.ToString()); break; } } if (iRowCnt % 1000 == 0) { statusProgress.Update($"Written {iRowCnt:n0} rows to the file output"); } } return(iRowCnt); }