public static List<BLProduct> RetrieveVariantsOfProductFromDB(string databaseConnectionString, int productId)
{
List<BLProduct> variants = new List<BLProduct>();
SqlConnection sqlConnection = new SqlConnection(databaseConnectionString);
SqlDataReader sqlReader = null;
try
{
sqlConnection.Open();
SqlCommand sqlCheckForVariants = new SqlCommand($"SELECT VariantID FROM ProductVariants WHERE ProductID = {productId}", sqlConnection);
sqlReader = sqlCheckForVariants.ExecuteReader();
while (sqlReader.Read())
variants.Add(RetrieveFromDB(databaseConnectionString, int.Parse(sqlReader["VariantID"].ToString())));
sqlReader.Close();
sqlReader.Dispose();
SqlCommand sqlCheckForOriginal = new SqlCommand($"SELECT ProductID FROM ProductVariants WHERE VariantID = {productId}", sqlConnection);
bool isNotOriginal = false;
sqlReader = sqlCheckForOriginal.ExecuteReader();
while (sqlReader.Read())
{
variants.Add(RetrieveFromDB(databaseConnectionString, int.Parse(sqlReader["ProductID"].ToString())));
isNotOriginal = true;
}
sqlReader.Close();
sqlReader.Dispose();
if (isNotOriginal)
{
SqlCommand sqlCheckForOriginalVariants = new SqlCommand($"SELECT VariantID FROM ProductVariants WHERE ProductID = {variants[0].Id}", sqlConnection);
sqlReader = sqlCheckForOriginalVariants.ExecuteReader();
while (sqlReader.Read())
{
if (int.Parse(sqlReader["VariantID"].ToString()) != productId)
variants.Add(RetrieveFromDB(databaseConnectionString, int.Parse(sqlReader["VariantID"].ToString())));
}
sqlCheckForOriginalVariants.Dispose();
}
sqlCheckForVariants.Dispose();
sqlCheckForOriginal.Dispose();
return variants;
}
finally
{
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
sqlConnection.Close();
sqlConnection.Dispose();
}
}