public List <ProductSelectionDTO> FindAllPerInvoice(int invoiceID)
{
ProductSelectionDTO selection;
ProductDTO product;
InvoiceDTO invoice;
//do an innerjoin over the two tables to retrieve all values
List <ProductSelectionDTO> results = new List <ProductSelectionDTO>();
string queryString = "SELECT dbo.Product_ProdSelection.productID, dbo.ProductSelection.selectionID, dbo.ProductSelection.quantity, " +
"dbo.ProductSelection.originalSize, dbo.ProductSelection.originalPrice " +
"FROM dbo.Product_ProdSelection " +
"INNER JOIN dbo.ProductSelection " +
"ON dbo.ProductSelection.selectionID = dbo.ProductSelection.selectionID " +
"WHERE dbo.ProductSelection.invoiceID = " + invoiceID;
try
{
//The connection is automatically closed at the end of the using block.
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
cmd.Parameters.AddWithValue("@invoiceID", SqlDbType.Int).Value = invoiceID;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
product = new ProductDTO();
selection = new ProductSelectionDTO();
invoice = new InvoiceDTO();
selection = GenerateSelection(reader, product, invoice, selection);
//return product instance as data object
Debug.Print("ProductSelectionDAL: /FindAllByInvoice/ " + selection.GetID());
//add data objects to result-list
results.Add(selection);
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
Debug.Write(e.ToString());
}
return(results);
}
private static ProductSelectionDTO GenerateSelection(SqlDataReader reader, ProductDTO product, InvoiceDTO inv, ProductSelectionDTO selection)
{
product.SetId(Convert.ToInt32(reader["productID"]));
selection.SetID(Convert.ToInt32(reader["selectionID"]));
selection.SetProduct(product);
inv.SetID(Convert.ToInt32(reader["invoiceID"]));
selection.SetInvoice(inv);
selection.SetOrigPrice(Convert.ToDecimal(reader["originalPrice"]));
selection.SetOrigSize(Convert.ToInt32(reader["originalSize"]));
selection.SetQuantity(Convert.ToInt32(reader["quantity"]));
return(selection);
}
