mardi 19 avril 2016

ASP.NET MVC Posting two Actions at once to write multiple entries into two tables

I am building a program in ASP.NET MVC with a database. It is for recipe and ingredient matching in your kitchen, so to speak. It pulls recipes from a database table, and ingredients from another table, related through a junction table.

RecipeTable 
ID PK int not null
RecipeName varchar(25) not null
CategoryID int FK(references Cateogory(ID) not null
Directions varchar(max) not null

and

Recipe_IngredientsTable
RecipeID int FK(references Recipe(ID) not null
IngredientID int FK(references Ingredient(ID) not null (Ingredient table is just IDs and names)
IngredientAmount varchar(25) not null

With a unique constraint set up as RecipeID and IngredientID.

Now, the issue I'm having is with creating a new recipe, and I want to go ahead and save the list of RecipeIngredients at the same time. RecipeDM contains a field for ID, RecipeName, CategoryID, a List, and a field Directions. As it sits right now, on my DAL level, I have this method for writing the recipe:

public void CreateRecipeIngredients(RecipeDM recipe)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            // Building single SQL query statement to reduce trips to database for multiple RecipeIngredients
            StringBuilder queryString = new StringBuilder();
            int rowsAffected = 0;
            foreach (RecipeIngredientDM ingredient in recipe.RecipeIngredients)
            {
                queryString.AppendFormat("Insert into Recipe_Ingredients (RecipeID, IngredientID, IngredientAmount) Values ({0}, {1}, {2});",
                    recipe.RecipeID,
                    ingredient.IngredientID,
                    ingredient.IngredientAmount);
            }
            try
            {
                using (SqlCommand command = new SqlCommand(queryString.ToString(), connection))
                {
                    command.CommandType = CommandType.Text;
                    rowsAffected = command.ExecuteNonQuery();
                }
                logger.LogError("Event", "User was able create a list of ingredients for a recipe.", "Class: RecipeIngredientDAO -- Method: CreateRecipeIngredients");
            }
            catch (Exception e)
            {
                logger.LogError("Error", "User was unable to create a list of ingredients for a recipe, error: " + e, "Class: RecipeIngredientDAO -- Method: CreateRecipeIngredients");
            }
            finally
            {
                if (rowsAffected != recipe.RecipeIngredients.Count())
                {
                    recipeData.DeleteRecipe(recipe);
                }
                logger.LogError("Error", "All RecipeIngredients did not make it into the table; rolling back recipe creation.", "Class: RecipeIngredientDAO -- Method: CreateRecipeIngredients");
                // If the number of RecipeIngredients inserted into the table does not equal the number of ingredients the recipe has, then roll back entire creation of recipe to prevent bad data
            }
        }
    }

And this method for writing the recipe:

        public void CreateRecipe(RecipeDM recipe)
    {
        try
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@RecipeName", recipe.RecipeName)
                ,new SqlParameter("@CategoryID", recipe.CategoryID)
                ,new SqlParameter("@Directions", recipe.Directions)
            };
            dataWriter.Write(parameters, "CreateRecipe");
            logger.LogError("Event", "User was able to create a recipe to the database", "Class: RecipeDAO -- Method: CreateRecipe");
        }
        catch (Exception e)
        {
            logger.LogError("Error", "User was unable to create a recipe to the database, error: " + e, "Class: RecipeDAO -- Method: CreateRecipe");
        }

    }

Model - CreateRecipeVM

public class CreateRecipeVM
{
    public int RecipeID { get; set; }

    [Required]
    [Display(Name = "Recipe Name")]
    [StringLength(25, ErrorMessage = "Please enter a recipe name at least {2} and no more than {1} characters long.", MinimumLength = 3)]
    public string RecipeName { get; set; }

    [Required]
    [Display(Name = "Categories")]
    public List<CategorySM> Categories { get; set; }
    public int CategoryID { get; set; }

    [Required]
    [Display(Name = "Ingredients")]
    public List<RecipeIngredientVM> Ingredients { get; set; }
    public string IngredientAmount { get; set; }

    [Required]
    [Display(Name = "Directions")]
    public string Directions { get; set; }
}

Model - RecipeIngredientVM

public class RecipeIngredientVM
{
    public int RecipeID { get; set; }
    public int IngredientID { get; set; }

    [Required]
    [Display(Name = "Ingredient Name")]
    public string IngredientName { get; set; }

    [Required]
    [Display(Name = "Quantity")]
    public string IngredientAmount { get; set; }
}

Now, I'm almost sure I've got the CreateRecipeIngredients method written correctly, but I'm not sure. And I know this is a bit of a long-winded post, but I promise, once I get the foundation laid out, I will explain what my issue is.

On my Recipe controller, I have for Create Recipe:

 // GET: Recipe/Create
    public ActionResult Create()
    {
        CreateRecipeVM recipe = new CreateRecipeVM();
        recipe.Categories = catLog.GetAllCategories();
        recipe.Ingredients = Mapper.Map<List<RecipeIngredientVM>>(ingLog.GetAllIngredients());            
        return View(recipe);
    }

    // POST: Recipe/Create
    [HttpPost]
    public ActionResult Create(CreateRecipeVM recipe, List<RecipeIngredientVM> ingredients)
    {
        try
        {
            TempData["NewRecipeID"] = recipe.RecipeID;                
            recipe.Ingredients = (List<RecipeIngredientVM>)TempData.Peek("NewRecipeIngredients");
recLog.CreateRecipe(Mapper.Map<RecipeSM>(recipe));
            recIngLog.CreateRecipeIngredients(Mapper.Map<RecipeSM>(recipe));
            return RedirectToAction("Details", new { id = recipe.RecipeID }); ;
        }
        catch
        {
            return View();
        }
    }

My Create view for recipe is as follows:

@model MyKitchen.Models.CreateRecipeVM
@{
    ViewBag.Title = "Create";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

 <h3>Add a New Recipe</h3>

@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()

<div class="form-horizontal">

    <hr />
    @Html.ValidationSummary(true, "", new { @class = "text-danger" })
    @Html.HiddenFor(model => model.RecipeID)

    <div class="form-group">
        @Html.LabelFor(model => model.RecipeName, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.RecipeName, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.RecipeName, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.Categories, htmlAttributes: new { @class = "control-label col-md-2"})
        <div class="col-md-10">
            @Html.DropDownList("CategoryID", new SelectList(Model.Categories, "CategoryID", "CategoryName"), "--- Select A Category ---")
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.Directions, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.Directions, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.Directions, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.Ingredients, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            <button type="button" name="AddIngredients" id="showPartial" class="btn btn-default">Click here to add ingredients for this recipe</button>
            <div id="partialView"></div>
        </div>
    </div>
    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input type="submit" value="Create" class="btn btn-default" />
        </div>
    </div>
</div>
}

When you click the button named "AddIngredients" with the id of "showPartial", it renders in a PartialView in the respectively named Div right below it. My jquery for that works fine, after many hours of trying to figure that one out (did I mention I'm new to this?).

Now, farther down in my RecipeController, I have the following method, which is on that partial view:

// GET Recipe/CreateIngredientsForRecipe
        public ActionResult CreateIngredientsForRecipe()
        {
            List<RecipeIngredientVM> ingredients = Mapper.Map<List<RecipeIngredientVM>>(ingLog.GetAllIngredients());
        return View(ingredients);
    }

    // POST Recipe/CreateIngredientsForRecipe
    [HttpPost]
    public ActionResult CreateIngredientsForRecipe(List<RecipeIngredientVM> ingredients)
    {
        List<RecipeIngredientVM> recIngredients = new List<RecipeIngredientVM>();
        foreach(RecipeIngredientVM food in ingredients)
        {
            RecipeIngredientVM recFood = new RecipeIngredientVM();
            if(food.IngredientAmount != null)
            {
                recFood.RecipeID = (int)TempData.Peek("NewRecipeID");
                recFood.IngredientID = food.IngredientID;
                recFood.IngredientName = food.IngredientName;
                recFood.IngredientAmount = food.IngredientAmount;
                recIngredients.Add(recFood);
            }
        }
        TempData["NewRecipeIngredients"] = recIngredients;
        return RedirectToAction("Details", new { id = recIngredients[0].RecipeID }); ;
    }
}
}

And the partial renders in correctly, and that CreateIngredientsForRecipe.cshtml is:

<table class="table">
<tr>
    <th>
        @Html.DisplayNameFor(model => model.IngredientName)
    </th>
    <th>
        @Html.DisplayName("Is it in your kitchen?")
    </th>
    <th></th>
</tr>

@foreach (var item in Model) {
<tr>
    <td>
        @Html.DisplayFor(modelItem => item.IngredientName)
    </td>
    <td>
        @Html.EditorFor(modelItem => item.IngredientAmount)
    </td>
</tr>
 }
<tr>
    <td>@Html.ActionLink("Don't see the ingredients you need?  Click here to add them to the ingredient database!", "Create", "Ingredient")</td>
</tr>

Now, my problem. When I click the Create button at the bottom of the page, I want it to fire off the action and methods for CreateRecipe, as well as for CreateRecipeIngredients. I'm not sure how to do this, but I've written what I have so far, and this is what is currently in my code. I don't remember what all I've tried, but right now, the exception it's shooting back at me is

User was unable to create a list of ingredients for a recipe, error: System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at DAL.RecipeIngredientDAO.CreateRecipeIngredients(RecipeDM recipe) in C:\Users\Sabba\Documents\Visual Studio 2015\Projects\MyKitchen\DAL\RecipeIngredientDAO.cs:line 73

I've spent almost a full 20 hours in the past two days trying to get this thing to work, but to no avail. I've gotten almost the entire rest of the project done, except for this one thing, and it is driving me absolutely bonkers.

Can someone PLEASE point me in the write direction to make this work the way I want it to, or at least the way it NEEDS to?

Aucun commentaire:

Enregistrer un commentaire