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)));
}
}
}
}