public static Workspace GetWorkspace()
{
string path = "workspace"; // absolute or relative path to the workspace folder
Workspace workspace = new Workspace();
workspace.Init(path);
workspace.Clear();
WebClient wc = new WebClient();
string download = wc.DownloadString("https://services.odata.org/v4/Northwind/Northwind.svc/Invoices");
JObject root = JObject.Parse(download);
JArray values = (JArray)root["value"];
List <Invoice> collection = JsonConvert.DeserializeObject <List <Invoice> >(values.ToString());
var connector = new ObjectConnector <Invoice>(workspace, collection);
connector.GetData("Invoices");
workspace.Save();
dynamic invoices = workspace.table("Invoices");
dynamic query = workspace.query("SalesByEmployeeCountry", new
{
invoices.Salesperson,
invoices.Country,
Sales = Op.Sum(invoices.ExtendedPrice)
});
query.Query.Execute();
return(workspace);
}
public static void Initialize()
{
// Create and initialize a new workspace folder relative to the project root
workspace = new Workspace();
workspace.KeepFiles = KeepFileType.Results;
workspace.Init("workspace");
// Uncomment the following line to clear the workspace before each run
// workspace.Clear();
// Extract JSON data from a zip file, if not already done
if (!File.Exists("seattle-pet-licenses.json") || !File.Exists("washington-zip-codes.json"))
{
ZipFile.ExtractToDirectory("data.zip", ".");
}
// Import the main license table
if (!workspace.TableExists("PetLicenses"))
{
List <PetLicense> collection = JsonConvert.DeserializeObject <List <PetLicense> >(File.ReadAllText("seattle-pet-licenses.json"));
ObjectConnector <PetLicense> connector = new ObjectConnector <PetLicense>(workspace, collection);
connector.GetData("PetLicenses");
workspace.Save();
}
// Import the secondary location table
if (!workspace.TableExists("Locations"))
{
List <Location> collection = JsonConvert.DeserializeObject <List <Location> >(File.ReadAllText("washington-zip-codes.json"));
ObjectConnector <Location> connector = new ObjectConnector <Location>(workspace, collection);
connector.GetData("Locations");
workspace.Save();
}
}
// get data from the sample workbook
private string GetFirstWorkbookTable()
{
using (OleDbConnection conn = new OleDbConnection(GetConnectionString()))
{
Cursor = Cursors.WaitCursor;
label1.Text = "Getting data from the sample workbook...";
Application.DoEvents();
conn.Open();
// get workbook table list
var tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// get the name of the first table
string name = tables.Rows[0]["TABLE_NAME"].ToString();
// get data from the first table
var command = new OleDbCommand("select * from [" + name + "]", conn);
using (var reader = command.ExecuteReader())
{
var connector = new ObjectConnector <Ticket>(_c1FlexPivotPage.FlexPivotPanel.Workspace, Ticket.GetTicletsInfo(reader));
connector.GetData(name);
}
label1.Text = string.Empty;
Cursor = Cursors.Default;
return(name);
}
}
private void OnSurfaceMouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
var sourceConnector = VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), _surface).OfType <ObjectConnector>().FirstOrDefault();
if (sourceConnector != null && CanStartConnection(sourceConnector))
{
_sourceConnector = sourceConnector;
_lineGeometry = new LineGeometry();
_lineGeometry.StartPoint = _lineGeometry.EndPoint = e.GetPosition(_surface);
_path = new Path
{
Stroke = new SolidColorBrush(Colors.Black),
StrokeThickness = 2,
Data = _lineGeometry
};
_surface.Children.Add(_path);
return;
}
var designerControl = VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), _surface).OfType <ActivityHost>().FirstOrDefault();
if (designerControl != null)
{
SelectItem(designerControl);
_clickPosition = e.GetPosition(designerControl);
_dragStarted = true;
return;
}
var link = VisualTreeHelper.FindElementsInHostCoordinates(e.GetPosition(null), _surface).OfType <LinkHost>().FirstOrDefault();
if (link != null)
{
SelectItem(link);
return;
}
SelectItem(null);
}
static void Main(string[] args)
{
// Create and initialize a new workspace folder relative to the project root
Workspace workspace = new Workspace();
workspace.KeepFiles = KeepFileType.Results;
workspace.Init("workspace");
// Uncomment the following line to clear the workspace before each run
workspace.Clear();
// Extract JSON data from a zip file, if not already done
if (!File.Exists("seattle-pet-licenses.json") || !File.Exists("washington-zip-codes.json"))
{
ZipFile.ExtractToDirectory("data.zip", ".");
}
// Import the main license table
if (!workspace.TableExists("PetLicenses"))
{
List <PetLicense> collection = JsonConvert.DeserializeObject <List <PetLicense> >(File.ReadAllText("seattle-pet-licenses.json"));
ObjectConnector <PetLicense> connector = new ObjectConnector <PetLicense>(workspace, collection);
connector.GetData("PetLicenses");
workspace.Save();
Console.WriteLine("{0:d} licenses imported.", collection.Count);
}
// Import the secondary location table
if (!workspace.TableExists("Locations"))
{
List <Location> collection = JsonConvert.DeserializeObject <List <Location> >(File.ReadAllText("washington-zip-codes.json"));
ObjectConnector <Location> connector = new ObjectConnector <Location>(workspace, collection);
connector.GetData("Locations");
workspace.Save();
Console.WriteLine("{0:d} locations imported.", collection.Count);
}
// Retrieve the main table for use in constructing queries
dynamic licenses = workspace.table("PetLicenses");
// Number of licenses, by species
if (!workspace.QueryExists("BySpecies"))
{
dynamic query = workspace.query("BySpecies", new {
licenses.Species,
Count = Op.Count(licenses.Species)
});
query.Query.Execute();
}
// Number of licenses, by calendar year
if (!workspace.QueryExists("ByYear"))
{
// Create a query with all base table columns and add a Year field, extracted from the DateTime value
dynamic parent = workspace.query(new {
_base = "*",
Year = Op.DtPart(licenses.IssueDate, DateTimeParts.Year)
});
// Derive another query from the unnamed query above and perform the aggregation
dynamic query = workspace.query("ByYear", new {
parent.Year,
Count = Op.Count(parent.Year)
});
query.Query.Execute();
}
// Most popular dog names (sort criteria and row limits are applied later)
if (!workspace.QueryExists("DogNames"))
{
// Use the _range attribute to limit the results to dogs only
dynamic query = workspace.query("DogNames", new {
_range = licenses.Species.Eq("Dog"),
Species = licenses.Species,
DogName = licenses.AnimalName,
Count = Op.Count(licenses.AnimalName)
});
query.Query.Execute();
}
// List names for all species except dogs and cats
if (!workspace.QueryExists("OtherAnimals"))
{
// Create a query with specified base table columns and use the _filter attribute to limit the results
// (_filter is used instead of _range because the latter does not support the Ne operator)
dynamic parent = workspace.query(new {
_filter = licenses.Species.Ne("Dog").And().Ne("Cat"),
licenses.LicenseNumber,
licenses.Species,
licenses.AnimalName
});
// Derive another query from the unnamed query above and use First as the aggregation operator
// (otherwise the query will not yield any results)
dynamic query = workspace.query("OtherAnimals", new {
parent.LicenseNumber,
parent.Species,
AnimalName = Op.First(parent.AnimalName)
});
query.Query.Execute();
}
// Retrieve the secondary table for use in a join query
dynamic locations = workspace.table("Locations");
// Number of licenses in King County, by city
if (!workspace.QueryExists("KingCounty"))
{
// Create a join from the main table (licenses) to the secondary table (locations)
// * The addition expression to the left of the vertical bar denotes which secondary fields to include
// * The equality expression to the right of the vertical bar specifies the join condition
// * The assignment statement specifies a property of the anonymous type (its name is not significant)
dynamic join = workspace.join(licenses, new {
locale = locations.County + locations.City | licenses.ZipCode == locations.Zip
});
// Derive another query from the join query above, specifying both a _range and an aggregation
dynamic query = workspace.query("KingCounty", new {
_range = join.County.Eq("King"),
join.County,
join.City,
Count = Op.Count(join.LicenseNumber)
});
query.Query.Execute();
}
// Output query results in CSV format to the console
IDataList species = workspace.GetQueryData("BySpecies");
Console.WriteLine();
Console.WriteLine("// Licenses issued, by species");
DataList.Write(species, Console.Out);
IDataList years = workspace.GetQueryData("ByYear");
Console.WriteLine();
Console.WriteLine("// Licenses issued, by year");
DataList.Write(years, Console.Out);
IDataList names = workspace.GetQueryData("DogNames", 10); // Limit results to 10 rows
Console.WriteLine();
Console.WriteLine("// Most popular dog names");
DataList.Sort(names, "Count", false); // Sort by Count in descending order
DataList.Write(names, Console.Out);
IDataList others = workspace.GetQueryData("OtherAnimals");
Console.WriteLine();
Console.WriteLine("// Excluding dogs and cats");
DataList.Write(others, Console.Out);
IDataList cities = workspace.GetQueryData("KingCounty");
Console.WriteLine();
Console.WriteLine("// Licenses issued in King County, by city");
DataList.Write(cities, Console.Out);
}
