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 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);
}
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);
}