public override DbCommandSource Generate()
{
var dbCommandSource = new DbCommandSource();
var right = this.Right.Generate();
dbCommandSource.CommandText = string.Format("{0} ({1})", this.OperationText, right.CommandText);
foreach (var param in right.Parameters)
{
dbCommandSource.Parameters.Add(param.Key, param.Value);
}
return(dbCommandSource);
}
public override DbCommandSource Generate()
{
var dbCommandSource = new DbCommandSource {
CommandText = this.ConditionString
};
foreach (var param in this.Parameters)
{
dbCommandSource.Parameters.Add(param.Key, param.Value);
}
return(dbCommandSource);
}
public PivotData GetDataCube()
{
if (cachedCube != null)
{
return(cachedCube);
}
// SQLite database file
var sqliteDbFile = Path.Combine(HttpContext.Server.MapPath("~/"), "../db/northwind.db");
var sqliteConn = new SQLiteConnection("Data Source=" + sqliteDbFile);
var selectCmd = sqliteConn.CreateCommand();
selectCmd.CommandText = @"
select o.OrderID, c.CompanyName, c.ContactName, o.OrderDate, p.ProductName, od.UnitPrice, od.Quantity, c.Country
from [Order Details] od
LEFT JOIN [Orders] o ON (od.OrderId=o.OrderId)
LEFT JOIN [Products] p ON (od.ProductId=p.ProductId)
LEFT JOIN [Customers] c ON (c.CustomerID=o.CustomerID)
" ;
// this example uses data reader as input and aggregates data with .NET
// it is possible to aggregate data on database level with GROUP BY
// (see "ToolkitDbSource" example from PivotData Toolkit Trial package ( https://www.nrecosite.com/pivot_data_library_net.aspx )
var dbCmdSource = new DbCommandSource(selectCmd);
// lets define derived fields for 'OrderDate' to get separate 'Year' and 'Month'
var derivedValSource = new DerivedValueSource(dbCmdSource);
derivedValSource.Register("Order Year", new DatePartValue("OrderDate").YearHandler);
derivedValSource.Register("Order Month", new DatePartValue("OrderDate").MonthNumberHandler);
// configuration of the serialized cube
var pvtData = new PivotData(new[] { "Country", "Order Year", "Order Month" },
// lets define 2 measures: count and sum of UnitPrice
new CompositeAggregatorFactory(
new CountAggregatorFactory(),
new SumAggregatorFactory("UnitPrice")
));
pvtData.ProcessData(derivedValSource);
cachedCube = pvtData;
return(pvtData);
}
