Esempio n. 1
0
        public async Task <IHttpActionResult> Get(int id)
        {
            using (var connection = new SqlConnection(SqlConnectionString))
            {
                using (var command = new SqlCommand(Query, connection))
                {
                    command.Parameters.AddWithValue("@TerritoryId", id);

                    await connection.OpenAsync();

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        var xml = new StringBuilder();
                        while (await reader.ReadAsync())
                        {
                            xml.Append(reader.GetString(0));
                        }
                        return(ResponseMessage(HttpResponseHelper.CreateMessageFrom(xml.ToString())));
                    }
                }
            }
        }
        public async Task <IHttpActionResult> Get(int id)
        {
            using (var connection = new SqlConnection(SqlConnectionString))
            {
                using (var command = new SqlCommand(Query, connection))
                {
                    command.Parameters.AddWithValue("@TerritoryId", id);

                    await connection.OpenAsync();

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        var lastOrderNumber = string.Empty;

                        var doc    = new XDocument();
                        var orders = new XElement("Orders");

                        doc.Add(orders);
                        XElement lineItems = null;

                        while (await reader.ReadAsync())
                        {
                            var orderNumber = reader["OrderNumber"].ToString();

                            if (orderNumber != lastOrderNumber)
                            {
                                lastOrderNumber = orderNumber;

                                var order = new XElement("Order");
                                orders.Add(order);

                                var customer = new XElement("Customer");
                                lineItems = new XElement("OrderLineItems");
                                order.Add(customer, lineItems);

                                var orderDate      = (DateTime)reader["OrderDate"];
                                var totalDue       = (Decimal)reader["TotalDue"];
                                var reviewRequired = totalDue > 5000 ? 'Y' : 'N';

                                order.Add(
                                    new XAttribute("OrderNumber", orderNumber),
                                    new XAttribute("Status", reader["Status"]),
                                    new XAttribute("ShipDate", reader["ShipDate"]),
                                    new XAttribute("OrderDateYear", orderDate.Year),
                                    new XAttribute("OrderDateMonth", orderDate.Month),
                                    new XAttribute("DueDate", reader["DueDate"]),
                                    new XAttribute("SubTotal", RoundAndFormat(reader["SubTotal"])),
                                    new XAttribute("TaxAmt", RoundAndFormat(reader["TaxAmt"])),
                                    new XAttribute("TotalDue", RoundAndFormat(totalDue)),
                                    new XAttribute("ReviewRequired", reviewRequired));

                                var fullName = string.Join(" ",
                                                           reader["CustomerTitle"],
                                                           reader["CustomerFirstName"],
                                                           reader["CustomerMiddleName"],
                                                           reader["CustomerLastName"],
                                                           reader["CustomerSuffix"]
                                                           )
                                               .Replace("  ", " ") //remove double spaces
                                               .Trim()
                                               .ToUpper();

                                customer.Add(
                                    new XAttribute("AccountNumber", reader["AccountNumber"]),
                                    new XAttribute("FullName", fullName));
                            }

                            var productId = (int)reader["ProductID"];
                            var quantity  = (short)reader["Quantity"];

                            var inventoryCheckRequired = (productId >= 710 && productId <= 720 && quantity >= 5) ? 'Y' : 'N';

                            var lineItem = new XElement("LineItem",
                                                        new XAttribute("Quantity", quantity),
                                                        new XAttribute("UnitPrice", ((Decimal)reader["UnitPrice"]).ToString("C")),
                                                        new XAttribute("LineTotal", RoundAndFormat(reader["LineTotal"])),
                                                        new XAttribute("ProductId", productId),
                                                        new XAttribute("InventoryCheckRequired", inventoryCheckRequired));

                            lineItems.Add(lineItem);
                        }

                        // match the exact formatting of the XML returned from SQL
                        var xml = doc
                                  .ToString(SaveOptions.DisableFormatting)
                                  .Replace(" />", "/>");

                        return(ResponseMessage(HttpResponseHelper.CreateMessageFrom(xml)));
                    }
                }
            }
        }