/************* Repository Methods ******************/
public List<Customer> getCustomers()
{
OrderEntryDbContext db = new OrderEntryDbContext();
List<Customer> customers = new List<Customer>();
try
{
customers = db.customers.ToList();
} catch (Exception ex)
{ Console.WriteLine(ex.Message); }
return customers;
}
/***** Use EF to get the customer orders *****/
public CustomerOrders getCustomerOrdersEF(string custNo)
{
CustomerOrders customerOrders = new CustomerOrders();
OrderEntryDbContext db = new OrderEntryDbContext();
try
{
customerOrders.customer = db.customers.Find(custNo);
var query = (from ot in db.orders where ot.CustNo == custNo select ot);
customerOrders.orders = query.ToList();
} catch (Exception ex) { Console.WriteLine(ex.Message); }
return customerOrders;
}
/***** Use SQL to get the customer orders *****/
public CustomerOrders getCustomerOrdersSQL(string custNo)
{
CustomerOrders customerOrders = new CustomerOrders();
OrderEntryDbContext db = new OrderEntryDbContext();
List <SqlParameter> sqlParams = new List <SqlParameter>();
sqlParams.Add(new SqlParameter("@CustNo", custNo));
try
{
string sql = "SELECT * FROM Customer WHERE CustNo = @CustNo";
customerOrders.customer =
db.customers.SqlQuery(sql, sqlParams.ToArray()).First();
sqlParams.Clear();
sqlParams.Add(new SqlParameter("@CustNo", custNo));
sql = "SELECT * FROM OrderTbl WHERE CustNo = @CustNo";
customerOrders.orders =
db.orders.SqlQuery(sql, sqlParams.ToArray()).ToList();
// sqlParams.Clear();
// sqlParams.Add(new SqlParameter("@CustNo", custNo));
// sql = "SELECT p.ProdName, p.ProdPrice " +
// "FROM Customer c INNER JOIN OrderTbl o " +
// " ON c.CustNo = o.CustNo " +
// " INNER JOIN OrdLine l " +
// " ON o.OrdNo = l.OrdNo " +
// " INNER JOIN Product p " +
// " ON l.ProdNo = p.ProdNo " +
// "WHERE c.CustNo = @CustNo " +
// "ORDER BY o.OrdNo;";
// customerOrders.products =
// db.products.SqlQuery(sql, sqlParams.ToArray()).ToList();
// sqlParams.Clear();
// sqlParams.Add(new SqlParameter("@CustNo", custNo));
// sql = "SELECT l.Qty " +
// "FROM Customer c INNER JOIN OrderTbl o " +
// " ON c.CustNo = o.CustNo " +
// " INNER JOIN OrdLine l " +
// " ON o.OrdNo = l.OrdNo " +
// " INNER JOIN Product p " +
// " ON l.ProdNo = p.ProdNo " +
// "WHERE c.CustNo = @CustNo;";
// customerOrders.orderLines =
// db.orderLines.SqlQuery(sql, sqlParams.ToArray()).ToList();
} catch (Exception ex) { Console.WriteLine(ex.Message); }
return(customerOrders);
}
/***** Use SQL to get the customer orders *****/
public CustomerOrders getCustomerOrdersSQL(string custNo)
{
CustomerOrders customerOrders = new CustomerOrders();
OrderEntryDbContext db = new OrderEntryDbContext();
List<SqlParameter> sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@CustNo", custNo));
try
{
string sql = "SELECT * FROM Customer WHERE CustNo = @CustNo";
customerOrders.customer =
db.customers.SqlQuery(sql, sqlParams.ToArray()).First();
sqlParams.Clear();
sqlParams.Add(new SqlParameter("@CustNo", custNo));
sql = "SELECT * FROM OrderTbl WHERE CustNo = @CustNo";
customerOrders.orders =
db.orders.SqlQuery(sql, sqlParams.ToArray()).ToList();
} catch (Exception ex) { Console.WriteLine(ex.Message); }
return customerOrders;
}
public List <OrderDetail> getOrderDetails(string ordNo)
{
OrderEntryDbContext db = new OrderEntryDbContext();
List <OrderDetail> orderDetails = new List <OrderDetail>();
List <SqlParameter> sqlParams = new List <SqlParameter>();
sqlParams.Add(new SqlParameter("@OrdNo", ordNo));
string sql = "SELECT o.OrdNo, l.ProdNo, p.ProdName, l.Qty, p.ProdPrice " +
"FROM OrderTbl o INNER JOIN OrdLine l " +
" ON o.OrdNo = l.OrdNo " +
" INNER JOIN Product p " +
" ON l.ProdNo = p.ProdNo " +
"WHERE l.OrdNo = @OrdNo;";
try
{
orderDetails =
db.orderDetails.SqlQuery(sql, sqlParams.ToArray()).ToList();
}
catch (Exception ex) { Console.WriteLine(ex.Message); }
return(orderDetails);
}