async private void btnOrders_Click(object sender, RoutedEventArgs e)
{
try
{
pbProgress.IsIndeterminate = true;
await connection.OpenAsync();
SqlCommand cmdSelect = connection.CreateCommand();
cmdSelect.CommandText = "WAITFOR DELAY '0:0:02' SELECT Customers.CompanyName, Customers.ContactName, Customers.Address, Customers.City, Customers.Country, Customers.Phone, "
+"Orders.OrderDate, Orders.ShippedDate, Orders.Freight, Orders.ShipCity, Orders.ShipCountry, Orders.ShipRegion, "
+"[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount "
+"FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID";
SqlDataReader reader = await cmdSelect.ExecuteReaderAsync();
List<Order> orders = new List<Order>();
while (await reader.ReadAsync())
{
Customer currentCustomer = new Customer() { CompanyName=reader[0].ToString(), ContactName=reader[1].ToString(), Address=reader[2].ToString(),
City=reader[3].ToString(), Country=reader[4].ToString(), Phone=reader[5].ToString() };
OrderInfo currentOrderInfo = new OrderInfo() { UnitPrice=reader[12].ToString(), Quantity=reader[13].ToString(), Discount=reader[14].ToString() };
Order order = new Order() { OrderDate=reader[6].ToString(), ShippedDate=reader[7].ToString(), Freight=reader[8].ToString(),
ShipCity=reader[9].ToString(), ShipCountry=reader[10].ToString(), ShipRegion=reader[11].ToString(), customer=currentCustomer, orderInfo=currentOrderInfo };
orders.Add(order);
}
dgClientsOrders.ItemsSource = orders;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
pbProgress.IsIndeterminate = false;
}
}
async private void btnClientsInfo_Click(object sender, RoutedEventArgs e)
{
try
{
pbProgress.IsIndeterminate = true;
await connection.OpenAsync();
SqlCommand cmdSelect = connection.CreateCommand();
cmdSelect.CommandText = "SELECT Customers.ContactName, Customers.CompanyName, Customers.City, Customers.Country, Groups.OrdersSum "+
"FROM Customers INNER JOIN (SELECT CustomerID, SUM([Order Details].UnitPrice*(1+Discount)*Quantity) as OrdersSum "+
"FROM Orders INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID WHERE Orders.OrderDate BETWEEN @from AND @to "+
"GROUP BY CustomerID) as Groups ON Customers.CustomerID=Groups.CustomerID";
string dt = dpFrom.DisplayDate.ToString("dd-MM-yyyy");
cmdSelect.Parameters.AddWithValue("@from", dpFrom.DisplayDate.ToString("dd/MM/yyyy"));
cmdSelect.Parameters.AddWithValue("@to", dpTo.DisplayDate.ToString("dd/MM/yyyy"));
SqlDataReader reader = await cmdSelect.ExecuteReaderAsync();
List<ClientsOrdersSumm> clientsSumm = new List<ClientsOrdersSumm>();
while (await reader.ReadAsync())
{
Customer cust = new Customer()
{
ContactName = reader[0].ToString(),
CompanyName = reader[1].ToString(),
City = reader[2].ToString(),
Country = reader[3].ToString()
};
ClientsOrdersSumm cos = new ClientsOrdersSumm()
{
customer = cust,
Summ = Convert.ToDecimal(String.Format("{0:0.##}", reader[4]))
};
clientsSumm.Add(cos);
}
dgClientsOrdersSumm.ItemsSource = clientsSumm;
}
catch (Exception ex)
{
throw ex;
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
pbProgress.IsIndeterminate = false;
}
}