/// <summary>
/// Returns a DataTable from an AdomdCommand reader (MDX Query in an OLAP Cube)
/// </summary>
static public DataTable FromAdomdCommand(AdomdCommand command)
{
AdomdDataReader dr = command.ExecuteReader();
DataTable result = new DataTable("Data");
// output the rows in the DataReader
DataTable dtSchema = dr.GetSchemaTable();
foreach (DataRow schemarow in dtSchema.Rows)
{
var columnName = schemarow.ItemArray[0].ToString().Replace("[", "").Replace("]", "").Replace(" ", "");
result.Columns.Add(columnName, Type.GetType(schemarow.ItemArray[5].ToString()));
}
while (dr.Read())
{
object[] ColArray = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr[i] != null)
{
ColArray[i] = dr[i];
}
}
result.LoadDataRow(ColArray, true);
}
dr.Close();
return(result);
}
public static IEnumerable <T> ReturnFromReader <T>(this AdomdDataReader source) where T : new()
{
var type = typeof(T);
var schemaTable = source.GetSchemaTable();
var columnNames = schemaTable.Rows
.Cast <DataRow>()
.Select(x => x[0].ToString())
.ToArray();
var props = type.GetProperties()
.Where(x => System.Attribute.IsDefined(x, typeof(MapToAttribute)))
.Select(x => new
{
Attribute = x.GetCustomAttribute <MapToAttribute>(),
PropertyInfo = x
})
.Where(x => columnNames.Contains(x.Attribute.MdxColumn))
.OrderBy(x => x.Attribute.MdxColumn);
var bindingList = new Dictionary <ParameterExpression, MemberAssignment>();
var converterList = new TypeConverter[props.Count()];
var stringArrayParam = Expression.Parameter(typeof(string[]), "values");
var converterArrayParam = Expression.Parameter(typeof(TypeConverter[]), "converters");
var converter = typeof(TypeConverter).GetMethod("ConvertFromString", new [] { typeof(string) });
props.For((v, i) =>
{
var prop = v.PropertyInfo;
var paramExp = Expression.Parameter(prop.PropertyType, prop.Name);
var arrayAssignment = Expression.ArrayIndex(stringArrayParam, Expression.Constant(i));
var typeConverter = TypeDescriptor.GetConverter(prop.PropertyType);
var converterArrayAssignment = Expression.ArrayIndex(converterArrayParam, Expression.Constant(i));
var defaultValue = prop.PropertyType.GetDefault();
var defaultConstant = Expression.Constant(defaultValue == null ? null : defaultValue.ToString(), typeof(string));
var methodExp = Expression.Call(converterArrayAssignment, converter, Expression.Coalesce(arrayAssignment, defaultConstant));
Expression.Bind(prop,
Expression.Convert(methodExp, prop.PropertyType))
.Finally(bind => bindingList.Add(paramExp, bind));
converterList[i] = typeConverter;
});
var newExp = Expression.New(typeof(T));
var memberInit = Expression.MemberInit(newExp, bindingList.Values.ToArray());
var lambda = Expression.Lambda <Func <TypeConverter[], string[], T> >(memberInit, new[] { converterArrayParam, stringArrayParam });
var creatorMethod = lambda.Compile();
while (source.Read())
{
var rawResults = new string[columnNames.Length];
columnNames
.OrderBy(x => x)
.For((v, i) => rawResults[i] = source[v].ToString());
yield return(creatorMethod(converterList, rawResults));
}
}
private void ExecuteOlapQuery()
{
OlapManager objOlapManager = new OlapManager();
CellSet objCellSet = objOlapManager.GetQueryResult(TextBoxQuery.Text);
AdomdDataAdapter objDataAdaptor = new AdomdDataAdapter(objOlapManager.ObjCommand);
AdomdDataReader objDataReader = objOlapManager.ObjCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataTable objTable = new DataTable();
DataColumn myColumn = new DataColumn();
DataRow myRow = null;
DataTable objSchemaTable = objDataReader.GetSchemaTable();
List <string> lMeta = new List <string>();
// init meta values
for (int i = 0; i < objSchemaTable.Rows.Count; i++)
{
lMeta.Add(objSchemaTable.Rows[i][0].ToString());
}
// add columns and column captions
for (int i = 0; i < objDataReader.FieldCount; i++)
{
myColumn = new DataColumn(lMeta[i]);
objTable.Columns.Add(myColumn);
}
// output the rows in the DataReader
while (objDataReader.Read())
{
// new row
myRow = objTable.NewRow();
// set the row values
for (int i = 0; i < objDataReader.FieldCount; i++)
{
myRow[i] = objDataReader[i];
}
// add row to the table
objTable.Rows.Add(myRow);
}
// close reader
objDataReader.Close();
GridViewData.DataSource = objTable;
GridViewData.DataBind();
// export TEST
//ExportDataTableToExcel(objTable);
objOlapManager.CloseConnection();
// load the main table data
this.ObjMainTable = objTable;
Session.Add("queryData", objMainTable);
}
//</snippetReturnCommandUsingCellSet>
//<snippetOutputCommandWithDataReader>
void OutputCommandWithDataReader()
{
//Connect to the local server
AdomdConnection conn = new AdomdConnection("Data Source=localhost");
conn.Open();
//Create a command to send to the server.
AdomdCommand cmd = new AdomdCommand(@"WITH MEMBER [Measures].[FreightCostPerOrder] AS
[Measures].[Reseller Freight Cost]/[Measures].[Reseller Order Quantity],
FORMAT_STRING = 'Currency'
SELECT [Geography].[Geography].[Country].&[United States].Children ON ROWS,
[Date].[Calendar].[Calendar Year] ON COLUMNS
FROM [Adventure Works]
WHERE [Measures].[FreightCostPerOrder]", conn);
//Execute the command, returning an AdomdDataReader
AdomdDataReader reader = cmd.ExecuteReader();
//Retrieve the schema information about the AdomdDataReader
System.Data.DataTable schema = reader.GetSchemaTable();
//Cycle through the schema, printing out the
//ColumnName information as column headers.
for (int n = 0; n < schema.Rows.Count; n++)
{
Console.Write(schema.Rows[n]["ColumnName"] + "\t");
}
Console.WriteLine();
//Loop through the reader, outputting each cell's value.
while (reader.Read())
{
for (int n = 0; n < reader.FieldCount; n++)
{
Console.Write(reader.GetValue(n) + "\t");
}
Console.WriteLine();
}
//Close the connection and await user input.
conn.Close();
Console.ReadLine();
}
private void EnsureSQLTableExists(SqlConnection conn, string sqlTableName, AdomdDataReader reader)
{
var strColumns = new StringBuilder();
var schemaTable = reader.GetSchemaTable();
foreach (System.Data.DataRow row in schemaTable.Rows)
{
var colName = row.Field <string>("ColumnName");
var regEx = System.Text.RegularExpressions.Regex.Match(colName, @".+\[(.+)\]");
if (regEx.Success)
{
colName = regEx.Groups[1].Value;
}
colName.Replace('|', '_');
var sqlType = ConvertDotNetToSQLType(row);
strColumns.AppendLine($",[{colName}] {sqlType} NULL");
}
var cmdText = @"
declare @sqlCmd nvarchar(max)
IF object_id(@tableName, 'U') is not null
BEGIN
raiserror('Droping Table ""%s""', 1, 1, @tableName)
set @sqlCmd = 'drop table ' + @tableName + char(13)
exec sp_executesql @sqlCmd
END
IF object_id(@tableName, 'U') is null
BEGIN
declare @schemaName varchar(20)
set @sqlCmd = ''
set @schemaName = parsename(@tableName, 2)
IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = @schemaName)
BEGIN
set @sqlCmd = 'CREATE SCHEMA ' + @schemaName + char(13)
END
set @sqlCmd = @sqlCmd + 'CREATE TABLE ' + @tableName + '(' + @columns + ');'
raiserror('Creating Table ""%s""', 1, 1, @tableName)
exec sp_executesql @sqlCmd
END
ELSE
BEGIN
raiserror('Table ""%s"" already exists', 1, 1, @tableName)
END
";
using (var cmd = new SqlCommand(cmdText, conn))
{
cmd.Parameters.AddWithValue("@tableName", sqlTableName);
cmd.Parameters.AddWithValue("@columns", strColumns.ToString().TrimStart(','));
cmd.ExecuteNonQuery();
}
}
