public List <DetailExportBill> GetDetailExportBillByID(int id) { List <DetailExportBill> lDetail = new List <DetailExportBill>(); conn = DBContext.GetDBConnection(); conn.Open(); try { string query = "SELECT Detail_ExportBill.export_id, Product.product_name, Detail_ExportBill.quantity, Product.price_sale, Detail_ExportBill.discount, Detail_ExportBill.total_price\n" + "FROM Detail_ExportBill INNER JOIN\n" + " Product ON Detail_ExportBill.product_id = Product.product_id Where Detail_ExportBill.export_id = @id"; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = query; cmd.Parameters.Add(new SqlParameter("@id", id)); using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { DetailExportBill de = new DetailExportBill(); ExportBill e = new ExportBill(); e.ID = Convert.ToInt32(reader["export_id"]); de.ExportBill = e; Product p = new Product(); p.Name = reader["product_name"].ToString(); p.PriceSale = Convert.ToSingle(reader["price_sale"]); de.Product = p; de.Quantity = Convert.ToSingle(reader["quantity"]); de.Discount = Convert.ToSingle(reader["discount"]); de.ToTalPrice = Convert.ToSingle(reader["total_price"]); lDetail.Add(de); } } } return(lDetail); } catch (Exception e) { throw e; } finally { conn.Close(); } }
public List <ExportBill> GetExportBill1(int numberInPage, int pageCurrent, string begin, string end) { List <ExportBill> listExportBill = new List <ExportBill>(); conn = DBContext.GetDBConnection(); conn.Open(); try { string query = "Select * from( Select *, ROW_NUMBER() OVER (ORDER BY id ASC) as row_num FROM\n" + "\n" + "(SELECT ExportBill.id, ExportBill.export_date, Customer.customer_name, ExportBill.total_price\n" + "FROM Customer INNER JOIN\n" + "ExportBill ON Customer.customer_id = ExportBill.customer_id where export_date >= @begin and export_date <= @end) ab) xy\n" + "Where row_num >= @from and row_num <= @to"; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = query; int from = (pageCurrent - 1) * numberInPage + 1; int to = pageCurrent * numberInPage; cmd.Parameters.Add(new SqlParameter("@begin", begin)); cmd.Parameters.Add(new SqlParameter("@end", end)); cmd.Parameters.Add(new SqlParameter("@from", from)); cmd.Parameters.Add(new SqlParameter("@to", to)); using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { ExportBill e = new ExportBill(); e.ID = Convert.ToInt32(reader["id"]); e.Date = reader["export_date"].ToString(); e.TotalPrice1 = Convert.ToSingle(reader["total_price"]); Customer c = new Customer(); c.Name = reader["customer_name"].ToString(); e.Customer = c; listExportBill.Add(e); } } } return(listExportBill); } catch (Exception e) { throw e; } finally { conn.Close(); } }
public ExportBill GetExportBillByID(int id) { int check = 1; conn = DBContext.GetDBConnection(); conn.Open(); try { string query = "SELECT ExportBill.id, ExportBill.export_date, Customer.customer_name, Staff.staff_id, Staff.staff_name, Staff.note\n" + "FROM Customer INNER JOIN\n" + " ExportBill ON Customer.customer_id = ExportBill.customer_id INNER JOIN\n" + " Staff ON ExportBill.staff_id = Staff.staff_id Where ExportBill.id = @id"; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = query; cmd.Parameters.Add(new SqlParameter("@id", id)); using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { if (reader.Read()) { check = 0; ExportBill eb = new ExportBill(); eb.ID = Convert.ToInt32(reader["id"]); eb.Date = Convert.ToDateTime(reader["export_date"]).ToString(); eb.Note = reader["note"].ToString(); Staff s = new Staff(); s.Name = reader["staff_name"].ToString(); eb.Staff = s; Customer c = new Customer(); c.Name = reader["customer_name"].ToString(); eb.Customer = c; return(eb); } } } Console.WriteLine(check); return(null); } catch (Exception e) { throw e; } finally { conn.Close(); } }
public void insert(ExportBill eb) { conn = DBContext.GetDBConnection(); conn.Open(); try { String insertOrder = "INSERT INTO [dbo].[ExportBill]\n" + " ([export_date]\n" + " ,[staff_id]\n" + " ,[customer_id]\n" + " ,[total_price]\n" + " ,[note])\n" + " VALUES\n" + " (GETDATE(), @staff, @cus, @total, @note)"; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = insertOrder; cmd.Parameters.Add(new SqlParameter("@staff", eb.Staff.ID)); cmd.Parameters.Add(new SqlParameter("@cus", eb.Customer.ID)); cmd.Parameters.Add(new SqlParameter("@total", eb.TotalPrice())); cmd.Parameters.Add(new SqlParameter("@note", eb.Note)); cmd.ExecuteNonQuery(); string queryGetExportBillId = "SELECT @@IDENTITY as oid"; SqlCommand cmd1 = new SqlCommand(); cmd1.Connection = conn; cmd1.CommandText = queryGetExportBillId; using (DbDataReader reader = cmd1.ExecuteReader()) { if (reader.HasRows) { if (reader.Read()) { eb.ID = Convert.ToInt32(reader["oid"]); } } } string insertOrderline = "INSERT INTO [dbo].[Detail_ExportBill]\n" + " ([export_id]\n" + " ,[product_id]\n" + " ,[quantity]\n" + " ,[total_price]\n" + " ,[discount])\n" + " VALUES\n" + " (@ebId, @pdId, @quan, @total, @discount)"; foreach (var item in eb.Lines) { SqlCommand cmd2 = new SqlCommand(); cmd2.Connection = conn; cmd2.CommandText = insertOrderline; cmd2.Parameters.Add(new SqlParameter("@ebId", eb.ID)); cmd2.Parameters.Add(new SqlParameter("@pdId", item.Product.Id)); cmd2.Parameters.Add(new SqlParameter("@quan", item.Quantity)); cmd2.Parameters.Add(new SqlParameter("@total", item.ToTalPrice)); cmd2.Parameters.Add(new SqlParameter("@discount", item.Discount)); cmd2.ExecuteNonQuery(); } } catch (Exception e) { throw e; } finally { conn.Close(); } }