public static Recipe GetRecipeByID(int id)
{
Recipe recipe = null;
// Select recipe
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("db_datareader.Recipe_Select", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@RecipeID", id);
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows && dataReader.Read())
{
// Create recipe
recipe = new Recipe(id, (string)dataReader["RecipeName"], Convert.ToInt32(dataReader["RecipeServingSize"]), (string)dataReader["RecipeFileExtension"]);
// Create ingredient list
dataReader.NextResult();
recipe.Ingredients = new List<Ingredient>();
while (dataReader.Read())
{
recipe.Ingredients.Add(new Ingredient(
(int)dataReader["RecipeIngredientID"],
(string)dataReader["RecipeIngredientName"],
Convert.ToInt32(dataReader["RecipeIngredientQuantity"]),
new Unit(Convert.ToInt32(dataReader["UnitID"]), (string)dataReader["UnitName"], (string)dataReader["UnitAbbreviation"])
));
}
// Create equipment list
dataReader.NextResult();
recipe.Equipment = new List<Equipment>();
while (dataReader.Read())
{
recipe.Equipment.Add(new Equipment(
(int)dataReader["RecipeEquipmentID"],
(string)dataReader["RecipeEquipmentName"]
));
}
// Create instruction list
dataReader.NextResult();
recipe.Instructions = new List<Instruction>();
while (dataReader.Read())
{
recipe.Instructions.Add(new Instruction(
(int)dataReader["RecipeInstructionID"],
(string)dataReader["RecipeInstructionName"]
));
}
}
} // SqlDataReader
} // SqlCommand
} // SqlConnection
return recipe;
}
public static List<Recipe> GetRecipes()
{
List<Recipe> recipes = new List<Recipe>();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("db_datareader.Recipes_Select", connection))
{
command.CommandType = CommandType.StoredProcedure;
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
Recipe recipe = new Recipe();
recipe.Id = (int)dataReader["RecipeID"];
recipe.Name = (string)dataReader["RecipeName"];
recipe.ServingSize = Convert.ToInt32(dataReader["RecipeServingSize"]);
recipe.FileExtension = (string)dataReader["RecipeFileExtension"];
recipe.Ingredients = Ingredient.GetIngredients(recipe.Id);
recipe.Equipment = Domain.Equipment.GetEquipment(recipe.Id);
recipes.Add(recipe);
}
} // SqlDataReader
} // SqlCommand
} // SqlConnection
return recipes;
}
public static Recipe Create(string name, int servingSize, string fileExtension)
{
Recipe recipe;
// Insert new recipe
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("dbo.Recipe_Insert", connection))
{
command.CommandType = CommandType.StoredProcedure;
//command.Parameters.AddWithValue("@RecipeID", 0).Direction = ParameterDirection.Output;
command.Parameters.Add("@RecipeID", SqlDbType.Int);
command.Parameters["@RecipeID"].Direction = ParameterDirection.Output;
command.Parameters.AddWithValue("@RecipeName", name);
command.Parameters.AddWithValue("@RecipeServingSize", servingSize);
command.Parameters.AddWithValue("@RecipeFileExtension", fileExtension);
connection.Open();
command.ExecuteNonQuery();
recipe = new Recipe((int)command.Parameters["@RecipeID"].Value, name, servingSize, fileExtension);
connection.Close();
} // SqlCommand
} // SqlConnection
return recipe;
}
