public List <ProductLine> GetAllProductLines(int orderID) { Sql_Connection sqlCon = new Sql_Connection(); List <ProductLine> pList = new List <ProductLine>(); sqlCon.PrepareSql($"SELECT * FROM ProductLine PL JOIN Product P ON PL.productID=P.productID WHERE OrdreID = {orderID}"); SqlDataReader sqld = sqlCon.ReadData(); if (sqld.HasRows) { while (sqld.Read()) { pList.Add(new ProductLine( Convert.ToInt32(sqld["productLineID"]), Convert.ToInt32(sqld["ordreID"]), Convert.ToInt32(sqld["productID"]), Convert.ToInt32(sqld["quantity"]), sqld["manufactor"].ToString(), sqld["modelName"].ToString(), Convert.ToDouble(sqld["price"]))); } } sqlCon.CloseConnection(); return(pList); }
public List <Product> GetAllProducts(String modelName) { Sql_Connection sqlCon = new Sql_Connection(); sqlCon.PrepareSql($"SELECT * from Product P JOIN Category C ON P.CategoryId=C.CategoryId WHERE modelName LIKE '%{modelName}%'"); List <Product> alleVarer = new List <Product>(); SqlDataReader sqld; sqld = sqlCon.ReadData(); if (sqld.HasRows) { while (sqld.Read()) { alleVarer.Add(new Product( Convert.ToInt32(sqld["ProductID"]), sqld["manufactor"].ToString(), sqld["modelName"].ToString(), Convert.ToDouble(sqld["price"]), Convert.ToInt32(sqld["CategoryID"]), sqld["catName"].ToString())); } } sqlCon.CloseConnection(); return(alleVarer); }
//Author: Jesper public List <Eyewear> GetEyewear(string sex, string shape, string color, int length, double fromPrice, double toPrice) { Sql_Connection sqlCon = new Sql_Connection(); sqlCon.PrepareSql($"select * from frame f join product p on f.ProductId = p.ProductId " + $"where sex like '%{sex}%' " + $"and shape like '%{shape}%' " + $"and color like '%{color}%' " + $"and length between {length-10} and {length+10} " + $"and price between {fromPrice} and {toPrice}"); List <Eyewear> EyewearList = new List <Eyewear>(); SqlDataReader sqld = sqlCon.ReadData(); if (sqld.HasRows) { while (sqld.Read()) { EyewearList.Add(new Eyewear( sqld["sex"].ToString(), sqld["shape"].ToString(), sqld["color"].ToString(), Convert.ToInt32(sqld["length"]), sqld["manufactor"].ToString(), sqld["modelName"].ToString(), Convert.ToDouble(sqld["price"]) )); } } sqlCon.CloseConnection(); return(EyewearList); }
public List <string> GetAllCategories() { Sql_Connection sqlCon = new Sql_Connection(); sqlCon.PrepareSql("Select * From category"); List <string> AllCategories = new List <string>(); SqlDataReader sqld = sqlCon.ReadData(); if (sqld.HasRows) { while (sqld.Read()) { AllCategories.Add($"{sqld[0]} {sqld[1]}"); } } sqlCon.CloseConnection(); return(AllCategories); }
public List <string> GetDistinctFromFrame(string column) { Sql_Connection sqlCon = new Sql_Connection(); sqlCon.PrepareSql($"SELECT DISTINCT {column} FROM frame"); List <string> list = new List <string>(); SqlDataReader sqld = sqlCon.ReadData(); if (sqld.HasRows) { while (sqld.Read()) { list.Add(sqld[0].ToString()); } } sqlCon.CloseConnection(); return(list); }
public Order GetLastOrder() { Sql_Connection sqlCon = new Sql_Connection(); sqlCon.PrepareSql("SELECT * FROM Ordre O join Customer C ON O.customerID=C.customerID WHERE OrdreID=(SELECT max(OrdreID) FROM Ordre)"); SqlDataReader sqld = sqlCon.ReadData(); if (sqld.HasRows) { sqld.Read(); return(new Order( Convert.ToInt32(sqld["ordreID"]), Convert.ToInt32(sqld["customerID"]), sqld["name"].ToString(), Convert.ToDateTime(sqld["date"]) )); } sqlCon.CloseConnection(); return(null); }
public List <Order> GetAllOrders(string name) { Sql_Connection sqlCon = new Sql_Connection(); List <Order> oList = new List <Order>(); sqlCon.PrepareSql($"Select * FROM Ordre O Join Customer C ON O.customerID=C.customerID WHERE C.name LIKE '%{name}%'"); SqlDataReader sqld = sqlCon.ReadData(); if (sqld.HasRows) { while (sqld.Read()) { oList.Add(new Order(Convert.ToInt32(sqld["ordreID"]), Convert.ToInt32(sqld["customerID"]), sqld["name"].ToString(), Convert.ToDateTime(sqld["date"]) )); } } sqlCon.CloseConnection(); return(oList); }
public Customer GetCustomer(int CustomerID) { Sql_Connection sqlCon = new Sql_Connection(); sqlCon.PrepareSql($"select * from Customer C join zipcode Z ON C.zipcode=Z.zipcode where CustomerID = {CustomerID}"); SqlDataReader sqld; sqld = sqlCon.ReadData(); if (sqld.HasRows) { while (sqld.Read()) { return(new Customer( Convert.ToInt32(sqld["customerID"]), sqld["name"].ToString(), sqld["adress"].ToString(), Convert.ToInt32(sqld["zipCode"]), sqld["city"].ToString())); } } sqlCon.CloseConnection(); return(null); }
public Product GetProduct(int productID) { Sql_Connection sqlCon = new Sql_Connection(); Sql_Connection SqlCon = new Sql_Connection(); SqlCon.PrepareSql($"SELECT * from Product P JOIN Category C ON P.CategoryId=C.CategoryId where ProductID = {productID}"); SqlDataReader sqld; sqld = SqlCon.ReadData(); if (sqld.HasRows) { sqld.Read(); return(new Product( Convert.ToInt32(sqld["ProductID"]), sqld["manufactor"].ToString(), sqld["modelName"].ToString(), Convert.ToDouble(sqld["price"]), Convert.ToInt32(sqld["CategoryID"]), sqld["catName"].ToString())); } sqlCon.CloseConnection(); return(null); }
public List <Customer> GetAllCustomer(string name) { Sql_Connection sqlCon = new Sql_Connection(); sqlCon.PrepareSql($"Select * FROM Customer C join zipcode z ON c.zipcode=z.zipcode WHERE name LIKE '%{name}%'"); List <Customer> alleKunder = new List <Customer>(); SqlDataReader sqld; sqld = sqlCon.ReadData(); if (sqld.HasRows) { while (sqld.Read()) { alleKunder.Add(new Customer( Convert.ToInt32(sqld["customerID"]), sqld["name"].ToString(), sqld["adress"].ToString(), Convert.ToInt32(sqld["zipCode"]), sqld["city"].ToString())); } } sqlCon.CloseConnection(); return(alleKunder); }