public static BLProduct RetrieveFromDB(string databaseConnectionString, int id) { SqlConnection sqlConnection = new SqlConnection(databaseConnectionString); SqlCommand sqlGetProduct = new SqlCommand($"SELECT CategoryID, Name, Description, Price, Popularity, StockQuantity, VAT FROM Products WHERE ID = {id}", sqlConnection); SqlDataReader sqlReader = null; try { sqlConnection.Open(); sqlReader = sqlGetProduct.ExecuteReader(); while (sqlReader.Read()) { BLProduct product = new BLProduct(id: id, categoryId: int.Parse(sqlReader["CategoryID"].ToString()), name: sqlReader["Name"].ToString(), description: sqlReader["Description"].ToString(), price: double.Parse(sqlReader["Price"].ToString()), popularity: int.Parse(sqlReader["Popularity"].ToString()), stockQuantity: int.Parse(sqlReader["StockQuantity"].ToString()), VAT: double.Parse(sqlReader["VAT"].ToString())); product.GetDiscountFromDB(databaseConnectionString); product.GetTrailerUrlFromDB(databaseConnectionString); return product; } return null; } finally { if (sqlReader != null) { sqlReader.Close(); sqlReader.Dispose(); } sqlConnection.Close(); sqlConnection.Dispose(); sqlGetProduct.Dispose(); } }
public static List <BLProduct> RetrieveTopNPopularProductsFromDB(string databaseConnectionString, int numberOfProducts) { List <BLProduct> products = new List <BLProduct>(); SqlConnection sqlConnection = new SqlConnection(databaseConnectionString); SqlCommand sqlGetPopularProducts = new SqlCommand($"SELECT TOP {numberOfProducts} ID, CategoryID, Name, Description, Price, Popularity, StockQuantity, VAT FROM Products ORDER BY Popularity DESC", sqlConnection); SqlDataReader sqlReader = null; try { sqlConnection.Open(); sqlReader = sqlGetPopularProducts.ExecuteReader(); while (sqlReader.Read()) { BLProduct product = new BLProduct(id: int.Parse(sqlReader["ID"].ToString()), categoryId: int.Parse(sqlReader["CategoryID"].ToString()), name: sqlReader["Name"].ToString(), description: sqlReader["Description"].ToString(), price: double.Parse(sqlReader["Price"].ToString()), popularity: int.Parse(sqlReader["Popularity"].ToString()), stockQuantity: int.Parse(sqlReader["StockQuantity"].ToString()), VAT: double.Parse(sqlReader["VAT"].ToString())); product.GetDiscountFromDB(databaseConnectionString); product.GetTrailerUrlFromDB(databaseConnectionString); products.Add(product); } return(products); } finally { if (sqlReader != null) { sqlReader.Close(); sqlReader.Dispose(); } sqlConnection.Close(); sqlConnection.Dispose(); sqlGetPopularProducts.Dispose(); } }
public static BLProduct RetrieveFromDB(string databaseConnectionString, int id) { SqlConnection sqlConnection = new SqlConnection(databaseConnectionString); SqlCommand sqlGetProduct = new SqlCommand($"SELECT CategoryID, Name, Description, Price, Popularity, StockQuantity, VAT FROM Products WHERE ID = {id}", sqlConnection); SqlDataReader sqlReader = null; try { sqlConnection.Open(); sqlReader = sqlGetProduct.ExecuteReader(); while (sqlReader.Read()) { BLProduct product = new BLProduct(id: id, categoryId: int.Parse(sqlReader["CategoryID"].ToString()), name: sqlReader["Name"].ToString(), description: sqlReader["Description"].ToString(), price: double.Parse(sqlReader["Price"].ToString()), popularity: int.Parse(sqlReader["Popularity"].ToString()), stockQuantity: int.Parse(sqlReader["StockQuantity"].ToString()), VAT: double.Parse(sqlReader["VAT"].ToString())); product.GetDiscountFromDB(databaseConnectionString); product.GetTrailerUrlFromDB(databaseConnectionString); return(product); } return(null); } finally { if (sqlReader != null) { sqlReader.Close(); sqlReader.Dispose(); } sqlConnection.Close(); sqlConnection.Dispose(); sqlGetProduct.Dispose(); } }
public static List<BLProduct> RetrieveTopNPopularProductsFromDB(string databaseConnectionString, int numberOfProducts) { List<BLProduct> products = new List<BLProduct>(); SqlConnection sqlConnection = new SqlConnection(databaseConnectionString); SqlCommand sqlGetPopularProducts = new SqlCommand($"SELECT TOP {numberOfProducts} ID, CategoryID, Name, Description, Price, Popularity, StockQuantity, VAT FROM Products ORDER BY Popularity DESC", sqlConnection); SqlDataReader sqlReader = null; try { sqlConnection.Open(); sqlReader = sqlGetPopularProducts.ExecuteReader(); while (sqlReader.Read()) { BLProduct product = new BLProduct(id: int.Parse(sqlReader["ID"].ToString()), categoryId: int.Parse(sqlReader["CategoryID"].ToString()), name: sqlReader["Name"].ToString(), description: sqlReader["Description"].ToString(), price: double.Parse(sqlReader["Price"].ToString()), popularity: int.Parse(sqlReader["Popularity"].ToString()), stockQuantity: int.Parse(sqlReader["StockQuantity"].ToString()), VAT: double.Parse(sqlReader["VAT"].ToString())); product.GetDiscountFromDB(databaseConnectionString); product.GetTrailerUrlFromDB(databaseConnectionString); products.Add(product); } return products; } finally { if (sqlReader != null) { sqlReader.Close(); sqlReader.Dispose(); } sqlConnection.Close(); sqlConnection.Dispose(); sqlGetPopularProducts.Dispose(); } }