public string[] SetCountriesArr()
{
string[] Countries = {};
// Server Name: DESKTOP-N6LPCB1\SQLEXP2014
SqlConnection Cconn = new SqlConnection("Data Source=DESKTOP-N6LPCB1\\SQLEXP2014;Initial Catalog=Day9;Integrated Security=True");
Cconn.Open();
SqlCommand CountriesCmd = Cconn.CreateCommand();
CountriesCmd.CommandType = CommandType.StoredProcedure;
CountriesCmd.CommandText = SpCmd;
SqlDataAdapter AllCountriesAdapter = new SqlDataAdapter(CountriesCmd);
DataSet AllCountriesDs = new DataSet();
AllCountriesAdapter.Fill(AllCountriesDs);
Return_Data GrabData = new Return_Data(AllCountriesDs);
Countries = GrabData.ReadAllCountries();
Console.WriteLine("How many Countries: {0}.", Countries.Length);
return(Countries);
}
public void OpDataCrossTablesNLevels()
{
string pcmd = "FetchAllProducts";
List <OrdersObjL1> Orders = this.RetriveOrders();
Return_Data rd = new Return_Data(pcmd);
List <Product> plist = new List <Product>();
plist = rd.ReadAllProducts();
var SummaryReport = from o in Orders
from i in o.OrderItems
join p in plist on i.OrderItemId equals p.pid
select new { p.pname, p.pqty, o.OrderId, i.OrderItemId };
foreach (var rz in SummaryReport)
{
Console.WriteLine("77 -- ProductName: {0} -- Quantity: {1}.", rz.pname, rz.pqty.ToString());
}
// Split Table into two Then see the hierachy.
var OrderItemsList = from o in Orders
from i in o.OrderItems
select i;
var ProductAndOrderItems = from p in plist
join v in OrderItemsList
on p.pid equals v.OrderItemId
into NewTable
select new { p.pname, NewTable };
string tab = new string(' ', 8);
foreach (var a in ProductAndOrderItems)
{
Console.WriteLine(String.Format("96 -- PdName: {0}.", a.pname));
foreach (var i in a.NewTable)
{
Console.WriteLine(tab + "99 -- OrderItemId: {0} -- Quantity: {1}.", i.OrderItemId, i.Qty);
}
}
// Method Syntax
var SummaryReport0 = Orders.SelectMany(o => o.OrderItems.Join(plist, i => i.OrderItemId, j => j.pid, (i, j) => new { j.pname, i.OrderItemId, o.OrderAmount }));
foreach (var i in SummaryReport0)
{
Console.WriteLine("109 -- ProductName: {0}, OrderAmount: {1}.", i.pname, i.OrderAmount);
}
// Group Join
var SummaryReport1 = plist.GroupJoin(Orders.SelectMany(o => o.OrderItems), p => p.pid, m => m.OrderItemId, (p, NewTable0) => new { p.pid, p.pname, NewTable0 });
foreach (var p in SummaryReport1)
{
if (p.pid <= 8)
{
Console.WriteLine(String.Format("96 -- PdName: {0}.", p.pname));
foreach (var pd in p.NewTable0)
{
Console.WriteLine(tab + "99 -- OrderItemId: {0} -- Quantity: {1}.", pd.OrderItemId, pd.Qty);
}
}
else
{
return;
}
}
}