void CreateFederationDataSource()
{
var sqlDataSource = SqlDataSourceSnippets.Code.CreateSqlDataSource();
var excelDataSource = ExcelDataSourceSnippets.Code.CreateExcelDataSource();
// Create the federated query's SQL and Excel sources.
Source sqlSource = new Source(sqlDataSource.Name, sqlDataSource, "Categories");
Source excelSource = new Source(excelDataSource.Name, excelDataSource, "");
// Create a federated query.
var selectNode = sqlSource.From()
// Select the "CategoryName" column from the SQL source.
.Select("CategoryName")
// Join the SQL source with the Excel source based on the "CategoryID" key field.
.Join(excelSource, "[Excel_Products.CategoryID] = [Sql_Categories.CategoryID]")
// Select columns from the Excel source.
.Select("CategoryID", "ProductName", "UnitPrice")
// Specify the query's name and build it.
.Build("CategoriesProducts");
// Create a federated data source and add the federated query to the collection.
var federationDataSource = new FederationDataSource();
federationDataSource.Queries.Add(selectNode);
// Build the data source schema to display it in the Field List.
federationDataSource.RebuildResultSchema();
}
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;
}
