ASP.NET MVC同时发布两个操作，将多个条目写入两个表

本文关键字：两个 MVC NET 布两个操作 ASP | 更新日期: 2024-10-21 09:50:54

我正在用ASP.NET MVC和数据库构建一个程序。可以说，这是为了在你的厨房里进行配方和配料匹配。它从数据库表中提取食谱，从另一个表中提取配料，通过连接表进行关联。

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

和

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

具有设置为RecipeID和IngredientID的唯一约束。

现在，我面临的问题是创建一个新的食谱，我想继续下去，同时保存食谱配料列表。RecipeDM包含一个ID字段、RecipeName字段、CategoryID字段、一个List字段和一个Directions字段。现在，在我的DAL级别上，我有这样的方法来编写食谱：

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
            }
        }
    }

这种编写配方的方法：

        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");
        }
    }

模型-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; }
    [Required]
    [Display(Name = "Directions")]
    public string Directions { get; set; }
}

型号-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; }
}

现在，我几乎确信我已经正确地编写了CreateRecipeIngredients方法，但我不确定。我知道这是一个有点冗长的帖子，但我保证，一旦我奠定了基础，我会解释我的问题是什么

在我的配方控制器上，我有创建配方：

 // 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();
        }
    }

我的食谱创建视图如下：

@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>
}

当你点击id为"showPartial"的名为"AddIngredients"的按钮时，它会在其下方分别命名的Div中的PartialView中渲染。经过数小时的尝试，我的jquery运行良好（我有没有提到我是新手？）。

现在，在我的RecipeController中，我有以下方法，它位于部分视图中：

// 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 }); ;
    }
}
}

并且部分渲染正确，并且CreateIngredientsForRecipe.cshtml是：

<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>

现在，我的问题。当我单击页面底部的创建按钮时，我希望它启动CreateRecipe以及CreateRecipeIngredients的操作和方法。我不知道如何做到这一点，但我已经写了迄今为止的内容，这就是目前我的代码中的内容。我不记得我都试过什么了，但现在，它对我的反击是

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

在过去的两天里，我花了将近整整20个小时试图让这件事发挥作用，但无济于事。除了这件事，我几乎完成了项目的其余部分，这让我非常疯狂。

有人能给我指明写作方向吗？让它按照我想要的方式工作，或者至少按照它需要的方式工作？

ASP.NET MVC同时发布两个操作，将多个条目写入两个表

根据错误，请打开连接。

使用（SqlConnection连接=新的SqlConnection（ConnectionString））｛

connection.open（）；

}

public class Address
{
    public int? AddressID { get; set; }
    public string City { get; set; }
}
public class Account
{
    public int? AccountID { get; set; }
    public string Name { get; set; }
    public List<Address> Addresses { get; set; }
}

public class AccountRepository
{ 
    public void Save(Account newAccount)
    {
        using (var conn = new SqlConnection())
        {
            conn.Open();
            var tran = conn.BeginTransaction();
            try
            {
                //add account
                var cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = @" 
INSERT INTO Accounts 
VALUEs (@p_account_name);
SET @p_account_ID = scope_identity(); 
";
                //param to get account ID
                var accountID = new SqlParameter("p_account_id", typeof(int));
                accountID.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(accountID);
                cmd.Parameters.AddWithValue("p_account_name", newAccount.Name);
                cmd.ExecuteNonQuery();
                newAccount.AccountID = (int)accountID.Value;
                if (newAccount.Addresses.Count > 0)
                {
                    //add address
                    foreach (var address in newAccount.Addresses)
                    {
                        cmd = new SqlCommand();
                        cmd.Connection = conn;
                        cmd.Transaction = tran;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = @" 
INSERT INTO Address (account_id, city)
VALUEs (@p_account_id, @p_city);
SET @p_address_ID = scope_identity(); 
";
                        //param to get address ID
                        var addressID = new SqlParameter("p_address_id", typeof(int));
                        addressID.Direction = ParameterDirection.Output;
                        cmd.Parameters.Add(addressID);
                        cmd.Parameters.AddWithValue("p_account_id", newAccount.AccountID);
                        cmd.Parameters.AddWithValue("p_city", address.City);
                        cmd.ExecuteNonQuery();
                        address.AddressID = (int)addressID.Value;
                    }
                }

                //commit transaction
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw;
            }
        }
    }
}

我没有测试代码，但你明白了。只需创建一个新的控制台应用程序并对其进行测试即可。