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