时间戳

首页 > 解决方案 > 如何仅显示 MVC 中数据库表中的特定记录？

asp.net-mvc - 如何仅显示 MVC 中数据库表中的特定记录？

问题描述

我是 MVC 的新手。我正在尝试开发汽车预订应用程序。我有一个名为Reservations的表和一个名为Cars的表。我必须按给定的位置、时间间隔（目前未预订的汽车）为用户显示可用的汽车。

我的想法是创建一个视图以从用户那里获取数据（位置、时间间隔），在他提交该数据后，他会被重定向到显示这些记录（汽车详细信息）的页面。问题是我真的不知道如何绑定视图以及如何从列表中显示。

这是我尝试过的：

我的控制器：

public ActionResult DisplayCars()
        {
            return View();
        }

在这里，我尝试制作一个列表并添加数据库中的结果记录

 [HttpPost]
        public ActionResult AvailableCars([Bind(Include = "StartDate,EndDate,Location")] Reservations reservation)
        {
            List<Cars> carList = null;

            if (ModelState.IsValid)
            {
                if (reservation.StartDate != null && reservation.EndDate != null && reservation.Location != null)
                {
                    carList = db.Database.SqlQuery<Cars>("Select * from Cars WHERE Location = @location AND CarID NOT IN" +
                         "(Select CarID FROM Reservations WHERE NOT (StartDate > @endDate) OR (EndDate < @startDate))",
                         new SqlParameter("location", reservation.Location), new SqlParameter("endDate", reservation.EndDate), new SqlParameter("startDate", reservation.StartDate)).ToList<Cars>();
                }
                else if(reservation.StartDate == null && reservation.EndDate == null && reservation.Location != null)
                {
                    carList = db.Database.SqlQuery<Cars>("Select * from Cars WHERE Location = @location",
                         new SqlParameter("location", reservation.Location)).ToList<Cars>();
                }
                else if(reservation.StartDate != null && reservation.EndDate != null && reservation.Location == null)
                {
                    carList = db.Database.SqlQuery<Cars>("Select * from Cars WHERE CarID NOT IN" +
                        "(Select CarID FROM Reservations WHERE NOT (StartDate > @endDate) OR (EndDate < @startDate))",
                        new SqlParameter("endDate", reservation.EndDate), new SqlParameter("startDate", reservation.StartDate)).ToList<Cars>();
                }
            }

            if(carList == null)
            {
                ModelState.AddModelError("", "No available cars");
            }

            return View(carList);
        }

这是我从用户那里获取输入的视图：

@model RentC.UI.Models.Reservations

@{
    ViewBag.Title = "DetailsAvailableCars";
}

<h2>DetailsAvailableCars</h2>

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

    <div class="form-horizontal">
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })

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

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

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


        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Search" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "AvailableCars")
</div>

这是显示记录的视图

@model IEnumerable<RentC.UI.Models.Cars>

@{
    ViewBag.Title = "Available Cars List";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Plate)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Manufacturer)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Model)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.PricePerDay)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Location)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Plate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Manufacturer)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Model)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.PricePerDay)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Location)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.CarID }) |
            @Html.ActionLink("Details", "Details", new { id=item.CarID }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.CarID })
        </td>
    </tr>
}

</table>

这是我的汽车模型

 public partial class Cars
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Cars()
        {
            this.Reservations = new HashSet<Reservations>();
        }

        public int CarID { get; set; }
        [Display(Name = "Cart Plate")]
        public string Plate { get; set; }
        public string Manufacturer { get; set; }
        public string Model { get; set; }
        public decimal PricePerDay { get; set; }
        public string Location { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Reservations> Reservations { get; set; }
    }
}

这是预订的模型

public partial class Reservations
    {
        public int ReservationID { get; set; }
        public int CarID { get; set; }
        public int CustomerID { get; set; }
        public System.DateTime StartDate { get; set; }
        public System.DateTime EndDate { get; set; }

        public virtual Cars Cars { get; set; }
    }

请帮助我一些提示或想法。谢谢！

标签： asp.net-mvc

解决方案

无法说出您到底有什么问题，但我喜欢这种尝试，所以我为您创建了一个示例（链接）。希望它会给你一些想法和所有的评论。

无论如何，这里是主要文件：

家庭控制器.cs

using CarReservations.Models;
using CarReservations.ViewModels;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace CarReservations.Controllers
{
    [Route("[controller]")]
    public class HomeController : Controller
    {
        private readonly MyDbContext dbContext;

        public HomeController(MyDbContext dbContext)
        {
            this.dbContext = dbContext;
        }

        // 1. http://localhost:53436/Home/AvailableCars
        // ASP.NET Core cannot bind cars parameter neither [From*]  so it passes all Cars from our in-memory DB
        // We click <a asp-action="DisplayCars">Search for cars</a> in Home/AvailableCars.cshtml ----> DisplayCars action

        // 4. We got redirected from POST with model provided so cars are our result from POST not result from DB
        [HttpGet]
        // determinamtes URL: [Route("[controller]")]/[Route("AvailableCars")] -> Home/AvailableCars
        [Route("AvailableCars")]
        // determinates action name used by code (eg. new View()/asp-action): [ActionName("AvailableCars")] -> new View() = new View("AvailableCars")
        [ActionName("AvailableCars")]
        public async Task<IActionResult> AvailableCarsAsync(IEnumerable<Car> cars) => View(cars.Any()
            ? cars
            : await dbContext.Cars.AsNoTracking().ToListAsync());

        // 2. http://localhost:53436/Home/DisplayCars
        // ASP.NET Core cannot bind carSearch parameter neither [From*]  so it passes null
        // We submit a form ----> POST DisplayCars action (DisplayCarsPostAsync)
        [HttpGet]
        [Route("DisplayCars")]
        [ActionName("DisplayCars")]
        public IActionResult DisplayCarsGet(CarSearchViewModel carSearch) => View(carSearch);

        // 3. http://localhost:53436/Home/DisplayCars
        // got redirected from GET -> POST -> do stuff -> redirect back either to the same view (GET) on error or to some other GET action.
        [HttpPost]
        [Route("DisplayCars")]
        [ActionName("DisplayCars")]
        public async Task<IActionResult> DisplayCarsPostAsync(CarSearchViewModel carSearch)
        {
            if (ModelState.IsValid)
            {
                // liverage EF ORM
                // just build a query, hold on with execution
                IQueryable<Car> cars = dbContext.Cars
                    .Where(car => string.IsNullOrEmpty(carSearch.Location)
                        || car.Location.Equals(carSearch.Location));

                if (carSearch.StartDate.HasValue && carSearch.EndDate.HasValue)
                {
                    // hold on with execution
                    IQueryable<int> excludedCarsIds = dbContext.Reservations
                        .Where(reservation => reservation.EndDate < carSearch.StartDate.Value
                            && reservation.StartDate > carSearch.EndDate.Value)
                        .Select(reservation => reservation.CarID);

                    cars = cars.Where(car => !excludedCarsIds.Contains(car.CarID));
                }

                // ToListAsync will execute the query
                return View("AvailableCars", await cars.AsNoTracking().ToListAsync());
            }

            ModelState.AddModelError("", "No available cars");
            return View(carSearch);
        }
    }
}

CarSearchViewMode.cs

using System;

namespace CarReservations.ViewModels
{
    public class CarSearchViewModel
    {
        public string Location { get; set; }
        public DateTime? StartDate { get; set; }
        public DateTime? EndDate { get; set; }
    }
}

我做了什么：

  • DbSet用 EF和 LINQ替换原始 SQL 。尽管 SQL 可能无法如您所愿翻译，但发现它更容易。
  • 用 asp 标签助手替换 HTML 助手方法 - 只是一个选择。你是新人，所以我想向你展示 ASP.NET Core 标签助手语法
  • 引入 ViewModels 的概念：模型应该只与数据库条目相关，我们显示的内容应该从这些模型构建并针对视图进行定制（因此它们被称为 ViewModels），因此视图可以只显示日期，而不是试图以某种方式塑造它。

一般来说它是如何工作的（见 1-4 的评论）：

  1. 获取所有汽车的可用汽车
  2. -> GET DisplayCars（去搜索表格）
  3. -> POST DisplayCars（提交表单、验证、查询数据库、返回结果）-> 仅获取带有结果的 AvailableCars。

(3)是 Post/Redirect/Get 模式。

祝你在学习路上好运。

推荐阅读