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);
}
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);
}
//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 void CreateEyewear(Eyewear e)
{
Sql_Connection sqlCon = new Sql_Connection();
sqlCon.ExecuteSql($"insert into Product(Manufactor,ModelName,Price,CategoryID) Values ('{e.manufactor}','{e.modelName}',{e.price},{e.categoryID})");
sqlCon.PrepareSql("SELECT * FROM Product WHERE ProductID=(SELECT max(productId) FROM Product);");
SqlDataReader sqld = sqlCon.ReadData();
sqld.Read();
sqlCon.ExecuteSql($"insert into Frame(Sex,Shape,Color,Length,productID) Values ('{e.sex}','{e.shape}','{e.color}',{e.length},{Convert.ToInt32(sqld["ProductID"])})");
}
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);
}