static public void Create_Select_Query_For_Excel_Source() { FederationDataSource federation = new FederationDataSource(); Source source = new Source("excelSource", CreateExcelDataSource("SalesPerson.xlsx", "Data")); var sourceNode = new SourceNode(source, "Orders"); var query = new SelectNode(sourceNode) { Alias = "excel", Expressions = { new SelectColumnExpression(sourceNode, "OrderID"), new SelectColumnExpression(sourceNode, "OrderDate"), new SelectColumnExpression(sourceNode, "Sales Person"), new SelectColumnExpression(sourceNode, "ProductName"), new SelectColumnExpression(sourceNode, "Extended Price") }, FilterString = "[Orders.CategoryName] = ?cat", }; federation.Queries.Add(query); federation.Fill(new[] { new DevExpress.DataAccess.Sql.QueryParameter("cat", typeof(string), "Seafood") }); var result = ((IListSource)federation).GetList() as IResultSet; DataSource = result[0]; }
static public void Two_Queries_Created_With_Fluent_Interface() { FederationDataSource federation = new FederationDataSource(); Source sourceProducts = new Source("Products", CreateSqlDataSource(), "Products"); Source sourceOrderDetail = new Source("OrderDetail", CreateExcelDataSource("SalesPerson.xlsx", "Data")); Source sourceHeader = new Source("OrderHeader", CreateExcelDataSource("OrderHeaders.xlsx", "OrderHeader")); SelectNode query1 = sourceProducts.From().Select("ProductName", "QuantityPerUnit", "UnitsInStock") .Join(sourceOrderDetail, "[Products.ProductName] = [OrderDetail.ProductName]") .Select("OrderDate") .Build("ProductsOrderDetail"); SelectNode query2 = sourceHeader.From().Select("OrderID", "Status", "Description") .Join(sourceOrderDetail, "[OrderHeader.OrderID] = [OrderDetail.OrderID]") .Select("Quantity", "Extended Price") .Build("OrderHeaderOrderDetail"); federation.Queries.AddRange(new[] { query1, query2 }); federation.Fill(); DataSource = federation; }
static public void Join_Sql_And_Excel_Sources() { FederationDataSource federation = new FederationDataSource(); Source sourceProducts = new Source("Products", CreateSqlDataSource(), "Products"); Source sourceOrderDetail = new Source("OrderDetail", CreateExcelDataSource("SalesPerson.xlsx", "Data")); var sourceNodeProducts = new SourceNode(sourceProducts, "Products"); var sourceNodeOrderDetail = new SourceNode(sourceOrderDetail, "OrderDetail"); var query = new SelectNode(sourceNodeProducts) { Alias = "ProductsOrderDetail", SubNodes = { new JoinElement { Node = sourceNodeOrderDetail, Condition = $"[{sourceNodeProducts.Alias}.ProductName] == [{sourceNodeOrderDetail.Alias}.ProductName]" } }, Expressions = { new SelectColumnExpression(sourceNodeProducts, "ProductName"), new SelectColumnExpression(sourceNodeProducts, "QuantityPerUnit"), new SelectColumnExpression(sourceNodeOrderDetail, "OrderID"), new SelectColumnExpression(sourceNodeOrderDetail, "OrderDate"), new SelectColumnExpression(sourceNodeOrderDetail, "Quantity"), new SelectColumnExpression(sourceNodeOrderDetail, "UnitPrice") } }; federation.Queries.Add(query); federation.Fill(); var result = ((IListSource)federation).GetList() as IResultSet; DataSource = result[0]; }
static public void Integrate_SQL_Excel_JSON_Data_Sources() { FederationDataSource federation = new FederationDataSource(); Source sourceProducts = new Source("Products", CreateSqlDataSource(), "Products"); Source sourceOrderDetail = new Source("OrderDetail", CreateExcelDataSource("SalesPerson.xlsx", "Data")); Source sourceHeader = new Source("OrderHeader", CreateExcelDataSource("OrderHeaders.xlsx", "OrderHeader")); Source sourceCustomer = new Source("Customer", CreateJsonDataSource("http://northwind.servicestack.net/customers.json"), "Customers"); SelectNode query = sourceHeader.From().Select("OrderID", "Status", "Description") .Join(sourceOrderDetail, "[OrderHeader.OrderID] = [OrderDetail.OrderID]") .Select("ProductName", "Quantity", "Extended Price") .Join(sourceCustomer, "[OrderHeader.CustomerID] = [Customer.Id]") .Select("CompanyName", "ContactName", "City", "Country") .Join(sourceProducts, "[OrderDetail.ProductName]=[Products.ProductName]") .Select("QuantityPerUnit", "CategoryID") .Build("OrderHeaderOrderCustomerProducts"); federation.Queries.Add(query); federation.Fill(); var result = ((IListSource)federation).GetList() as IResultSet; DataSource = result[0]; }
static public void Create_Master_Detail_Relation() { FederationDataSource federation = new FederationDataSource(); var ordersList = new List <Order> { new Order { OrderID = 10273, Status = "Paid", Description = "Smooth" }, new Order { OrderID = 10273, Status = "Paid", Description = "Bright" }, new Order { OrderID = 10274, Status = "Paid", Description = "Crisp" }, new Order { OrderID = 10276, Status = "Paid", Description = "Excellent" }, new Order { OrderID = 10278, Status = "Paid", Description = "Poor" } }; Source sourceOrders = new Source("Orders", ordersList); Source sourceExcel = new Source("OrderDetail", CreateExcelDataSource("SalesPerson.xlsx", "Data")); var sourceNodeOrders = new SourceNode(sourceOrders, "Orders"); var sourceNodeExcel = new SourceNode(sourceExcel, "OrderDetail"); var queryOrders = new SelectNode(sourceNodeOrders) { Alias = "Orders", Expressions = { new SelectColumnExpression(sourceNodeOrders, "OrderID"), new SelectColumnExpression(sourceNodeOrders, "Status"), new SelectColumnExpression(sourceNodeOrders, "Description") } }; federation.Queries.Add(queryOrders); var queryExcel = new SelectNode(sourceNodeExcel) { Alias = "OrderDetail", Expressions = { new SelectColumnExpression(sourceNodeExcel, "OrderID"), new SelectColumnExpression(sourceNodeExcel, "OrderDate"), new SelectColumnExpression(sourceNodeExcel, "Sales Person"), new SelectColumnExpression(sourceNodeExcel, "ProductName"), new SelectColumnExpression(sourceNodeExcel, "Extended Price") } }; federation.Queries.Add(queryExcel); federation.Relations.Add(new FederationMasterDetailInfo() { MasterQueryName = "Orders", DetailQueryName = "OrderDetail", KeyColumns = { new FederationRelationColumnInfo() { ParentKeyColumn = "OrderID", NestedKeyColumn = "OrderID", ConditionOperator = FederationConditionType.Equal } } } ); federation.Fill(); DataSource = federation; }