sql-server - 使用具有复合键和更新级联功能的 SQL Server 数据库进行 ASP.NET MVC 编码

Question

我将 EF 6 与数据库优先方法一起用于我的 ASP.NET MVC 项目，因为与 C# 和 LINQ 编码(使用 SQL Server 2008、Visual Studio 2015)相比，我更喜欢 SQL。

我在 SQL Server 中有 4 个表，每个表用于:

Link, Employee, MapLinkEmp, Location 

viz, link_sd1, emp_sd1, MapEmpLink_sd1, loc_sd1 respectively

Link 与 Emp 表存在多对多关系，我为此创建了另一个表，引用这两个表的复合主键。

为了实现软删除，我研究并最终采用了 Entity framework override save changes to soft delete entities ，这表明我提到的复合键 how to map composite key in CRUD functionality

在我的项目的数据库结束时:我在 SQL Server 中使用复合键实现了级联功能但是在应用程序结束时:当有我的实体中的复合键。

用于级联的 SQL Server 代码:

ALTER TABLE emplinkloc_sd1
ADD emid int

ALTER TABLE emplinkloc_sd1
ADD lmid int

ALTER TABLE emplinkloc_sd1
ADD isdl bit DEFAULT 0 NOT NULL

ALTER TABLE emplinkloc_sd1
ADD isde bit DEFAULT 0 NOT NULL

ALTER TABLE emplinkloc_sd1
ADD PRIMARY KEY (melid)

ALTER TABLE emplinkloc_sd1
ADD CONSTRAINT FK_ell_e_sd1 
    FOREIGN KEY (emid, isde) REFERENCES emp_sd1(eid, isdeleted) 
            ON UPDATE CASCADE;

ALTER TABLE emplinkloc_sd1
ADD CONSTRAINT FK_ell_l_sd1 
    FOREIGN KEY (lmid, isdl) REFERENCES link_sd1(lid, isdeleted) 
            ON UPDATE CASCADE;

我发现了很多技术，如 ISoftDelete、Interceptor 等，但我认为这些概念用于代码优先方法或 EF Core 等。这些也需要很多超出我目前理解水平的编程。

我在数据库端做的最多(如 Entity framework override save changes to soft delete entities 中所建议)，我应该如何使用 EF 数据库优先方法将应用程序端的功能合并到 ASP.NET MVC 的 Controller 和 View 中以及这样的数据库模式(具有复合主键和外键)？

提前致谢!

Answer 1

我正在解释这项技术 - “使用 EF6 存储过程进行 CRUD 操作的数据库优先方法” 经过大量实验后，它对我来说效果最好。整个过程包括数据库端的步骤，然后是应用程序端的步骤，如下面的实体描述 - Employee(在我的例子中是 emp_Oct1，以 eid 和 isdeletede 作为其复合键):

数据库结束

第 1 步。 我设计的 Schema 如下(使用复合键):

第 2 步。 我在 SQL Server 中为我的实体“emp_Oct1”创建了用于创建、更新和软删除的存储过程，如下所示:

 ---- For SOFT DELETION -----
CREATE PROCEDURE [dbo].[sp_deleteempt1_Oc1]
    -- Add the parameters for the stored procedure here
    @eid int
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    UPDATE emp_Oct1 SET isdeletede='1' WHERE eid=@eid 
END

----- For CREATE & UPDATE -----
CREATE PROCEDURE sp_AddEditEmp_Oct1
    -- Add the parameters for the stored procedure here
    @eid int=0, --Named Parameter setting initial value with the parameter
    @name varchar(100),
    @cpf varchar(50),
    @Designation varchar(50),
    @Contact bigint,
    @email varchar(255),
    @emplocid int,
    @Pwd varchar(100),
    @Remarks varchar(200),
    @isdeletede bit,
    @elast_mod_dt datetime2,
    @elast_mod_by varchar(255) 
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    IF(@eid>0)
        BEGIN
        SET NOCOUNT ON;
        UPDATE emp_Oct1 SET 
        name=@name,cpf=@cpf,Designation=@Designation,Contact=@Contact,email=@email, emplocid=@emplocid,
        Pwd=@Pwd ,Remarks=@Remarks,isdeletede=@isdeletede,elast_mod_dt=CURRENT_TIMESTAMP,elast_mod_by=@elast_mod_by
        WHERE eid=@eid 
        END
    ELSE
        BEGIN
        INSERT INTO emp_Oct1(name,cpf,Designation,Contact,email,emplocid,Pwd,Remarks,isdeletede,elast_mod_dt,elast_mod_by) 
        VALUES(@name,@cpf,@Designation,@Contact,@email,@emplocid,@Pwd,@Remarks,@isdeletede,CURRENT_TIMESTAMP,@elast_mod_by)
        SELECT @eid = SCOPE_IDENTITY()
        END
    SELECT @eid as eid
END
GO

申请结束

第 1 步。创建 MVC 应用程序，添加实体数据模型，如所述here

第 2 步。 我通过存储过程实现了我的 CRUD，主要思想来自这个来源 CRUD Operations using Stored Procedure in Entity Framework .

第三步 Controller 代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using llpv5;
using System.Data.Entity.Validation;

namespace llpv5.Controllers
{
    public class emp_Oct1Controller : Controller
    {
       
        private MyEntity db = new MyEntity();

       
        // Emp with their respective location can see only the data  of their location
        public ActionResult resplocemp_linq()
        {
            
            var usercpf = System.Web.HttpContext.Current.User.Identity.Name;
            var userlocid = (from e in db.emp_Oct1 where e.cpf == usercpf select e.emplocid).FirstOrDefault(); //To find location ID of the logged in user
            var linkresult = (from l in db.emp_Oct1
                              join loc in db.loc_Oct1
                              on l.emplocid equals loc.locid
                              where l.emplocid == userlocid
                              && l.isdeletede == false
                              && l.name != "Admin"
                              select l).ToList();

            if (System.Web.HttpContext.Current.User.Identity.IsAuthenticated)
            {
                linkresult = (from l in db.emp_Oct1
                              join loc in db.loc_Oct1
                              on l.emplocid equals loc.locid
                              where l.emplocid == userlocid
                              && l.isdeletede == false
                              && l.name != "Admin"
                              select l).ToList();
            }
            return View(linkresult.ToList());
        }
        //For Admin only - Deleted items
        
        // GET: emp_sd1
        public ActionResult isdeindex()
        {
            var emp_sd1 = db.emp_Oct1.Include(e => e.loc_Oct1).Where(e => e.isdeletede).Where(e => e.name != "Admin");
            return View(emp_sd1.ToList());
        }
        
        // GET: emp_Oct1
        public ActionResult Index()
        {
            var emp_Oct1 = db.emp_Oct1.Include(e => e.loc_Oct1).Where(e => !e.isdeletede).Where(e => e.name != "Admin");
            return View(emp_Oct1.ToList());
        }
       
        // GET: emp_Oct1/Details/5
        public ActionResult Details(int? eid, bool isdeletede)
        {
            if (eid == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            emp_Oct1 emp_Oct1 = db.emp_Oct1.Find(eid, isdeletede);
            if (emp_Oct1 == null)
            {
                return HttpNotFound();
            }
            return View(emp_Oct1);
        }
       
        // GET: emp_Oct1/Create
        public ActionResult Create()
        {
            ViewBag.emplocid = new SelectList(db.loc_Oct1, "locid", "LocationName");
            return View();
        }
        
        // POST: emp_Oct1/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include = "eid,name,cpf,Designation,Contact,email,emplocid,Pwd,Remarks,isdeletede,elast_mod_dt,elast_mod_by")] emp_Oct1 emp_Oct1)
        {
            if (ModelState.IsValid)
            {
                emp_Oct1.elast_mod_by = System.Web.HttpContext.Current.User.Identity.Name;
                db.emp_Oct1.Add(emp_Oct1);
                db.SaveChanges();
                return RedirectToAction("gindex_filter1");
            }

            ViewBag.emplocid = new SelectList(db.loc_Oct1, "locid", "LocationName", emp_Oct1.emplocid);
            return View(emp_Oct1);
        }
       
        // GET: emp_Oct1/Edit/5
        public ActionResult Edit(int? eid, bool isdeletede)
        {
            if (eid == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            emp_Oct1 emp_Oct1 = db.emp_Oct1.Find(eid, isdeletede);
            if (emp_Oct1 == null)
            {
                return HttpNotFound();
            }
            ViewBag.emplocid = new SelectList(db.loc_Oct1, "locid", "LocationName", emp_Oct1.emplocid);
            return View(emp_Oct1);
        }
       
        // POST: emp_Oct1/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include = "eid,name,cpf,Designation,Contact,email,emplocid,Pwd,Remarks,isdeletede,elast_mod_dt,elast_mod_by")] emp_Oct1 emp_Oct1)
        {
            if (ModelState.IsValid)
            {
                emp_Oct1.elast_mod_by = System.Web.HttpContext.Current.User.Identity.Name;
                db.Entry(emp_Oct1).State = EntityState.Modified;
              
                return RedirectToAction("gindex_filter1");
            }
            ViewBag.emplocid = new SelectList(db.loc_Oct1, "locid", "LocationName", emp_Oct1.emplocid);
            return View(emp_Oct1);
        }
       
        // GET: emp_Oct1/Delete/5
        public ActionResult Delete(int? eid, bool isdeletede)
        {
            if (eid == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            emp_Oct1 emp_Oct1 = db.emp_Oct1.Find(eid, isdeletede);
            if (emp_Oct1 == null)
            {
                return HttpNotFound();
            }
            return View(emp_Oct1);
        }
        
        // POST: emp_Oct1/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int eid, bool isdeletede)
        {
            emp_Oct1 emp_Oct1 = db.emp_Oct1.Find(eid, isdeletede);
            db.emp_Oct1.Remove(emp_Oct1);
            db.SaveChanges();
            return RedirectToAction("gindex_filter1");
        }


        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

第 4 步。使用 this link: how to map composite key in CRUD functionality 在自动生成的 View 中进行适当的更改示例查看代码如下:

@model IEnumerable<llpv5.emp_Oct1>
@{
    ViewBag.Title = "Location-specific Users";
}

<center><h2>Location-specific Users</h2></center>


<table class="table table-hover  table-bordered table-striped">
    <tr class="table-primary">
        <th>
            @Html.DisplayNameFor(model => model.cpf)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.name)
        </th>
        <th>

            @Html.DisplayNameFor(model => model.Designation)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Contact)
        </th>
        <th>
               @Html.DisplayNameFor(model => model.Pwd)   
        </th>
        <th>
            @Html.DisplayNameFor(model => model.loc_Oct1.LocationName)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.cpf)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Designation)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Contact)
            </td>
            <td>
                @if (User.IsInRole("Admin"))
                { @Html.DisplayFor(modelItem => item.Pwd)   }
            </td>


            <td>
                @Html.DisplayFor(modelItem => item.loc_Oct1.LocationName)
            </td>


            <td>

                @Html.ActionLink("Details", "Details", new { eid = item.eid, isdeletede = item.isdeletede }) <text> | </text>
              
               @Html.ActionLink("Edit", "Edit", new { eid = item.eid, isdeletede = item.isdeletede }) <text> | </text>
                @Html.ActionLink("Delete", "Delete", new { eid = item.eid, isdeletede = item.isdeletede })
              
            </td>
        </tr>
    }

</table>