SQL Server 使用关系同时向2个表添加数据

flseospp 于 2022-12-22 发布在其他

关注(0)|答案(1)|浏览(262)

我有以下数据库模式：

Equipment.cs:

public class Equipment
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [DisplayName("Serial Number")]
        public string Id { get; set; }

        [Required]
        public string Name { get; set; }

        [Required]
        public int Amount { get; set; }

        public int EquipmentStatusId { get; set; }
        [ValidateNever]
        public EquipmentStatus? EquipmentStatus { get; set; }

        public int LocationId { get; set; }
        [ValidateNever]
        public Location? Location { get; set; }

        [Required]
        [DisplayName("Internal or Consigned")]
        public string InternalOrConsigned { get; set; }

        [DisplayName("EOL Date")]
        public DateTime? EOLDate { get; set; }

        public string? EOLDescription { get; set; }
        [ValidateNever]
        [DisplayName("Image")]
        public string? ImageUrl { get; set; }

Location.cs:

public class Location
    {
        [Key]
        public int Id { get; set; }

        public int FloorId { get; set; }
        [ValidateNever]
        public Floor? Floor { get; set; }

        public int RoomId { get; set; }
        [ValidateNever]
        public Room? Room { get; set; }

        public int RackId { get; set; }
        [ValidateNever]
        public Rack? Rack { get; set; }

        [Required]
        public string Row { get; set; }
    }

我希望能够在一个Create.cshtmlEquipments视图中将所有属性添加到数据库中，我知道如何使用SelectListItem对EquipmentStatusId执行此操作，因为它只有一个名称，但当LocationId具有多个属性并且它们也来自其他表时，我不确定如何对LocationId执行此操作。
设备测试控制器. cs创建方法：

// GET: User/EquipmentsTest/Create
        public IActionResult Create()
        {
            ViewData["EquipmentStatusId"] = new SelectList(_context.EquipmentStatuses, "Id", "Name");
            ViewData["LocationId"] = new SelectList(_context.Locations, "Id", "Row");
            return View();
        }

        // POST: User/EquipmentsTest/Create
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create(Equipment equipment)
        {
            if (ModelState.IsValid)
            {
                _context.Add(equipment);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            ViewData["EquipmentStatusId"] = new SelectList(_context.EquipmentStatuses, "Id", "Name", equipment.EquipmentStatusId);
            ViewData["LocationId"] = new SelectList(_context.Locations, "Id", "Row", equipment.LocationId);
            return View(equipment);
        }

Create.cshtml:

<form asp-action="Create">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="Id" class="control-label"></label>
                <input asp-for="Id" class="form-control" />
                <span asp-validation-for="Id" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Name" class="control-label"></label>
                <input asp-for="Name" class="form-control" />
                <span asp-validation-for="Name" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Amount" class="control-label"></label>
                <input asp-for="Amount" class="form-control" />
                <span asp-validation-for="Amount" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="EquipmentStatusId" class="control-label"></label>
                <select asp-for="EquipmentStatusId" class ="form-control" asp-items="ViewBag.EquipmentStatusId"></select>
            </div>
            <div class="form-group">
                <label asp-for="LocationId" class="control-label"></label>
                <select asp-for="LocationId" class ="form-control" asp-items="ViewBag.LocationId"></select>
            </div>
            <div class="form-group">
                <label asp-for="InternalOrConsigned" class="control-label"></label>
                <input asp-for="InternalOrConsigned" class="form-control" />
                <span asp-validation-for="InternalOrConsigned" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="EOLDate" class="control-label"></label>
                <input asp-for="EOLDate" class="form-control" />
                <span asp-validation-for="EOLDate" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="EOLDescription" class="control-label"></label>
                <input asp-for="EOLDescription" class="form-control" />
                <span asp-validation-for="EOLDescription" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ImageUrl" class="control-label"></label>
                <input asp-for="ImageUrl" class="form-control" />
                <span asp-validation-for="ImageUrl" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Create" class="btn btn-primary" />
            </div>
        </form>

sql-server

来源：https://stackoverflow.com/questions/74870825/adding-data-to-2-tables-at-the-same-time-with-relationship

1条答案

按热度按时间

chy5wohz1#

你可以创建一个ViewModel来实现它，请参考这个简单的演示：

- 型号：**

public class Room
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public Location location { get; set; }
    }

public class Floors
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public Location location { get; set; }
    }

public class Location
    {
        public int Id { get; set; }
        public int FloorId { get; set; }
        public Floors Floor { get; set; }
            
        public int RoomId { get; set; }
        public Room Room { get; set; }

        public Equipment equipment { get; set; }

        [Required]
        public string Row { get; set; }
    }

public class Equipment
    {
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        [Required]
        public int Amount { get; set; }

        public int LocationId { get; set; }
        [ValidateNever]
        public Location? Location { get; set; }
    }

然后创建包含Location和Equipment的视图模型：

public class ViewTestModel
    {
        public Location location { get; set; }
        public Equipment equipment { get; set; }
    }

- 控制器：**

public IActionResult Index()
        {
            var rooms = _dbcontext.rooms.ToList();
            var floors = _dbcontext.floors.ToList();

            List<SelectListItem> list1 = new List<SelectListItem>();
            List<SelectListItem> list2 = new List<SelectListItem>();
            foreach (var ro in rooms)
            {
                var listItem = new SelectListItem();
                listItem.Text = ro.Name;
                listItem.Value = ro.Id.ToString();
                list1.Add(listItem);
            }
            foreach (var item in floors)
            {
                var listItem = new SelectListItem();
                listItem.Text = item.Name;
                listItem.Value = item.Id.ToString();
                list2.Add(listItem);
            }
            ViewBag.rooms = list1;
            ViewBag.floors = list2;
            return View();
        }

        [HttpPost]
        public IActionResult Index(ViewTestModel model)
        {
            Location location = new Location();
            var room = _dbcontext.rooms.Where(x=>x.Id==model.location.RoomId).FirstOrDefault();
            var floor = _dbcontext.floors.Where(x => x.Id == model.location.FloorId).FirstOrDefault();
            location.Room = room;
            location.Floor = floor;
            location.Row = model.location.Row;

            Equipment equipment = new Equipment();
            equipment.Name = model.equipment.Name;
            equipment.Amount = model.equipment.Amount;
            equipment.Location = location;
            _dbcontext.equipments.Add(equipment);
            _dbcontext.SaveChanges();

            //.....other code..............
                
        }

- 查看**

@model ViewTestModel

<form method="post">
    <div>
        <g2>Select your Room</g2>
        <br />
        <select asp-for="@Model.location.RoomId" asp-items="ViewBag.rooms"></select>
    </div>
    <br />
    <div>
        <g2>Select your Floor</g2>
        <br />
        <select asp-for="@Model.location.FloorId" asp-items="ViewBag.floors"></select>
    </div>
    <br />
    <div class="form-group">
        <label asp-for="@Model.location.Row"></label>
        <input asp-for="@Model.location.Row" />

    </div>
    <div class="form-group">
        <label asp-for="@Model.equipment.Name"></label>
        <input asp-for="@Model.equipment.Name" />
    </div>
    <div class="form-group">
        <label asp-for="@Model.equipment.Amount"></label>
        <input asp-for="@Model.equipment.Amount" />
    </div>

    <button type="submit">Create</button>
</form>

演示：
提交表格后：