/// <summary>
/// Execute stored procedure CustOrdersDeatil for orderId and return result
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public CustOrdersDetail GetCustOrdersDetail(int orderId)
{
var result = new CustOrdersDetail();
result.Discount = new List <int>();
result.ExtendedPrice = new List <decimal>();
result.ProductName = new List <string>();
result.Quantity = new List <short>();
result.UnitPrice = new List <decimal>();
result.OrderId = orderId;
using (DbConnection connection = this.providerFactory.CreateConnection())
{
connection.ConnectionString = this.connectionString;
connection.Open();
var command = (SqlCommand)connection.CreateCommand();
command.Parameters.AddWithValue("@orderId", orderId);
command.CommandText = "EXECUTE [dbo].[CustOrdersDetail] @OrderID = @orderId";
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.ProductName.Add((string)reader[CustOrdersDetailFields.ProductName]);
result.UnitPrice.Add((decimal)reader[CustOrdersDetailFields.UnitPrice]);
result.Quantity.Add((short)reader[CustOrdersDetailFields.Quantity]);
result.Discount.Add((int)reader[CustOrdersDetailFields.Discount]);
result.ExtendedPrice.Add((decimal)reader[CustOrdersDetailFields.ExtendedPrice]);
}
}
}
return(result);
}
public IEnumerable <CustOrdersDetail> GetCustOrdersDetail(int orderID)
{
var result = new List <CustOrdersDetail>();
using (var connection = _providerFactory.CreateConnection())
{
connection.ConnectionString = _connectionString;
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = @"exec dbo.CustOrdersDetail @pOrderID";
command.Parameters.Clear();
var pOrderID = command.CreateParameter();
pOrderID.ParameterName = "@pOrderID";
pOrderID.Value = orderID;
command.Parameters.Add(pOrderID);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var custOrdersDetail = new CustOrdersDetail();
custOrdersDetail.ProductName = reader.GetString(0);
custOrdersDetail.UnitPrice = reader.GetDecimal(1);
custOrdersDetail.Quantity = reader.GetInt16(2);
custOrdersDetail.Discount = reader.GetInt32(3);
custOrdersDetail.ExtendedPrice = reader.GetDecimal(4);
result.Add(custOrdersDetail);
}
}
}
}
return(result);
}