/// <summary>
/// This method executes left outer join on orders and employees collections
/// </summary>
private void PerformLeftOuterJoin()
{
Console.WriteLine("Following query joins Orders and Employees Data using Left Outer Join whose shipment city is Paris.\n");
var resultset = new List <Tuple <Order, Employee> >();
string query = "SELECT o.OrderID, o.ShipCity, e.FirstName, e.City " +
"FROM Orders o LEFT JOIN Employees e ON o.EmployeeID = e.EmployeeID " +
"WHERE o.ShipCity = 'Paris'";
Console.WriteLine(query);
IDBCollectionReader reader = _database.ExecuteReader(query);
while (reader.ReadNext())
{
var order = new Order()
{
ID = reader.GetInt64("OrderID"),
ShipCity = reader.GetString("ShipCity"),
};
var employee = new Employee()
{
FirstName = reader.ContainsAttribute("FirstName") ? reader.GetString("FirstName") : null,
City = reader.ContainsAttribute("City") ? reader.GetString("City") : null,
};
resultset.Add(new Tuple <Order, Employee>(order, employee));
}
Console.WriteLine(string.Format("Left Outer Join returned {0} documents.", resultset.Count + "\n\n\n"));
resultset.Clear();
reader.Dispose();
}
/// <summary>
/// This method fetches products from the products collection
/// </summary>
/// <param name="products"> products whos ID will be used to fetch data from the collection </param>
private void GetMultipleDocuments(ICollection <Product> products)
{
ICollection <DocumentKey> productKeys = GetProductKeys(products);
// Get documents from the Products collection:
// With ReadPreference.PrimaryOnly
// It will return a reader populated with the retrieved items. You may iterate the reader as demonstrated below.
IDBCollectionReader reader = _productsCollection.GetDocuments(productKeys);
Console.WriteLine("\n\nThe following items were retrieved from the Products collection:\n");
while (reader.ReadNext())
{
Product fetchedProduct = reader.GetObject <Product>();
PrintProductDetails(fetchedProduct);
Console.WriteLine("\n");
}
reader.Dispose();
}
/// <summary>
/// This method executes inner join on orders and employees collections
/// </summary>
private void PerformInnerJoin()
{
var resultset = new List <Tuple <Order, Employee> >();
Console.WriteLine("Following query joins Orders and Employees Data whose shipment city is London and Freight greater than 100lb.\n");
string query = "SELECT * FROM Orders o JOIN Employees e ON o.EmployeeID = e.EmployeeID " +
"WHERE o.ShipCity = 'London' AND o.Freight > 100 ORDER BY o.OrderID ";
Console.WriteLine(query);
IDBCollectionReader reader = _database.ExecuteReader(query);
while (reader.ReadNext())
{
var order = new Order()
{
ID = reader.GetInt64("OrderID"),
OrderDate = reader.Get <DateTime>("OrderDate"),
EmployeeID = reader.GetInt64("EmployeeID"),
CustomerID = reader.GetString("CustomerID"),
};
var employee = new Employee()
{
EmployeeID = reader.GetInt64("e_EmployeeID"),
FirstName = reader.GetString("e_FirstName"),
LastName = reader.GetString("e_LastName"),
Title = reader.GetString("e_Title"),
};
resultset.Add(new Tuple <Order, Employee>(order, employee));
}
Console.WriteLine(string.Format("Inner Join returned {0} documents.", resultset.Count + "\n\n\n"));
resultset.Clear();
reader.Dispose();
}
