c# - 使用参考表显示公司资料页面

问题描述

我对 C# 和 MVC Razor 页面有些陌生。我想向作为该公司成员的登录用户显示公司资料页面。对于这个视图，我有 3 个表 UsersToAddresses、CustomerNames 和 Addresses。usersToAddresses 获取用户注册时由 SQL 中的触发器自动插入的 UserId。该表只有 2 列 UserId 和 AddressId。我这样做是因为不止一个用户可以是公司的成员，而且我不想存储重复的数据。在 SQL 中，UsersToAddresses.AddressId 通过外键链接到 Addresses.AddressId。CustomerNames.CustomerId 通过外键链接到 Addresses.CustomerId。我希望能够在用户仪表板上显示公司名称以及该公司的地址列表。他们可能只有一个地址。我假设创建 ViewModel 是最好的方法，我为页面创建了一个 viewModel。我也有 userId 作为链接的扩展。所以它会是 Http:localhost:3125/customers/fdsa531as654few44231431fdvafbfzs [编辑] 经过与@Marcelo Myara 的多次讨论，我终于让它按照我想要的方式工作。下面我将显示标有 [EDIT] 的更改。下面的代码功能齐全，可以按预期工作。

控制器：

        [Authorize(Roles = "CompanyAdmin")]
    public ActionResult Index(string UserId)
    {
        if (UserId == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }
   [EDIT] - "Changed to use modelInstance"
        //Trying to get a view model for customer from the received UserId.
        CompanyOverview modelInstance = CompanyOverview.GetCompanyByUser(UserId, db);
        if (modelInstance == null)
        {
            return HttpNotFound();
        }
        return View(modelInstance);

        // Addresses userAddress = db.Addresses.Find(UserId);
        // if (userAddress == null)
        // {
        //    return HttpNotFound();
        // }
        // return View(userAddress);
    }

基本上是控制器中详细信息的副本，但这使用了 db.Addresses 并且我没有在视图中使用它。我正在使用视图模型。

这是我的视图模型：

    public partial class CompanyOverview
{
    public CompanyOverview()
    [EDIT] - "Not used"
    //{
    //    AddressDetail = new List<Addresses>();
    //}
    [EDIT] - "Not Needed"
    // [Key]

    [Edit] - "Added proper joins for use of referance table"
            //Static method to get an instance of your model when given an userId and DbContext instance.
    public static CompanyOverview GetCompanyByUser(string userId, CustomerEntities db)
    {
        var qCus = from ad in db.Addresses
                   join ua in db.UserToAddresses on ad.AddressId equals ua.AddressId
                   join cus in db.CustomerNames on ad.CustomerId equals cus.CustomerId
                   where (ua.UserId == userId)
                   select new CompanyOverview()
                   {
                       UserId = userId,
                       AddressId = ad.AddressId,
                       Customer = cus.CustomerName,
                       CustomerName = cus,
                       Location = ad.LocationName,
                       Addresses = ad
                   };


        var result = qCus.SingleOrDefault();

        if (result != null)
        {
            result.AddressDetail = db.Addresses.Where(a => a.CustomerId == result.CustomerName.CustomerId);
        };
        return result;
    }

    public string UserId { get; set; }
    public string AddressId { get; set; }
    [EDIT] - "Added for use on page if needed - Customer allows me to show the company name at the top of the razor page"
    public string Customer { get; set; }
    public string Location { get; set; }
    public virtual CustomerNames CustomerName { get; set; }
    public virtual ICollection<Addresses> AddressDetail { get; set; }
}

这也可能不正确。因此，我必须在 UsersToAddresses 表中找到 UserId，并让它从与 UserId 匹配的地址表中提取地址。如果有人可以提供帮助，将不胜感激。谢谢你。

2018 年 11 月 8 日更新 - 添加了表格定义。

用户到地址：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UsersToAddresses](
[UserId] [nvarchar](128) NOT NULL,
[AddressId] [int] NOT NULL,
CONSTRAINT [PK_UsersToAddresses] PRIMARY KEY CLUSTERED 
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UsersToAddresses]  WITH CHECK ADD  CONSTRAINT
[FK_dbo.UsersToAddresses_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UsersToAddresses] CHECK CONSTRAINT 
[FK_dbo.UsersToAddresses_dbo.AspNetUsers_UserId]
GO

客户表：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CustomerNames](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [nvarchar](max) NOT NULL,
[Status] [int] NOT NULL,
[Terms] [nvarchar](50) NULL,
[TaxCode] [nvarchar](50) NULL,
CONSTRAINT [PK_dbo.CustomerNames] PRIMARY KEY CLUSTERED 
(
[CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustomerNames] ADD  CONSTRAINT
[DF_CustomerNames_Status]
DEFAULT ((1)) FOR [Status]
GO

地址表：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Addresses](
[AddressId] [int] IDENTITY(1,1) NOT NULL,
[Status] [bit] NULL,
[IsBilling] [bit] NULL,
[IsShipping] [bit] NULL,
[ContactName] [nvarchar](max) NULL,
[Line1] [nvarchar](max) NULL,
[Line2] [nvarchar](max) NULL,
[Country] [nvarchar](100) NULL,
[State] [nvarchar](100) NULL,
[City] [nvarchar](100) NULL,
[ZipCode] [nvarchar](50) NULL,
[EmailAddress] [nvarchar](100) NULL,
[PhoneNumber] [nvarchar](50) NULL,
[FaxNumber] [nvarchar](50) NULL,
[LastUpdated] [datetime] NOT NULL,
[CustomerId] [int] NULL,
 CONSTRAINT [PK_dbo.Addresses] PRIMARY KEY CLUSTERED 
 (
[AddressId] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
 OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO

ALTER TABLE [dbo].[Addresses] ADD  CONSTRAINT [DF_Addresses_LastUpdated]
DEFAULT (getdate()) FOR [LastUpdated]
GO

ALTER TABLE [dbo].[Addresses]  WITH CHECK ADD  CONSTRAINT
[FK_Addresses_CustomerNames] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[CustomerNames] ([CustomerId])
GO

ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT
[FK_Addresses_CustomerNames]
GO

2018 年 11 月 10 日更新 - 添加了 RazorView

剃刀视图：

@model BestenEquipment.Models.CompanyOverview

@{
ViewBag.Title = "CreateProfile";
Layout = "~/Views/Shared/CustomerDashboardLayout.cshtml";
}


<div id="page-wrapper">
<div class="row">
    <div class="col-lg-12">
        <h2>CompanyOverview</h2>
        <h4>@Html.DisplayFor(model => model.Customer)</h4>
    </div>
    <!-- /.col-lg-12 -->
</div>
<div class="row">
    <div class="col-lg-12">
        Company Addresses:
    [EDIT] - "Added Table for headers"
                    <table class="table">
            <tr>
                <th>
                    @Html.DisplayNameFor(model => model.Addresses.Status)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Addresses.LocationName)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Addresses.ContactName)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Addresses.EmailAddress)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Addresses.Line1)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Addresses.Line2)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Addresses.IsBilling)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Addresses.IsShipping)
                </th>
            </tr>
    [EDIT] - "Corrected the foreach statement"
          //  @foreach (var item in Model.AddressDetail)
          //  {
          //  <dd> @Html.DisplayFor(model => model.Location)</dd>
          //  }
              @foreach (var item in Model.AddressDetail)
                {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Status)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.LocationName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ContactName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.EmailAddress)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Line1)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Line2)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.IsBilling)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.IsShipping)
                </td>
                <td>
                    @Html.ActionLink("Edit", "UserEdit", "Addresses", new { id = item.AddressId }, null) |
                    @Html.ActionLink("Delete", "Delete", "Addresses", new { id = item.AddressId }, null)
                </td>
            </tr>
            }
            </table>
    </div>
    <!-- /.col-lg-12 -->
</div>
</div>

所以你有它。我将对 AddressId 添加加密，以便用户无法更改号码并查看其他客户记录。

标签： c#sqlasp.net-mvcentity-framework

解决方案

好的，我将尝试直接解决您的问题（尽管这里有很多可以学习和改进的东西）。所以：

[Authorize(Roles = "CompanyAdmin")]
public ActionResult Index(string UserId)
{
    if (UserId == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }

    //Trying to get a view model for customer from the received UserId.
    CompanyOverview modelInstance = CompanyOverview.GetCompanyByUser(UserId, db);
    if (modelInstance == null)
    {
        return HttpNotFound();
    } 
    return View(modelInstance);
}

//From your question I'm taking this class as a ViewModel only
public partial class CompanyOverview
{
    //Keeping it here just because you implemented it (if not used elsewhere, just remove this)
    public CompanyOverview()
    {
        AddressDetail = new List<Addresses>();
    }

    //Static method to get an instance of your model when given an userId and DbContext instance. Correct the DbContext type name in the below parameter.
    public static CompanyOverview GetCompanyByUser(int userId, YourDbContext db)
    {
        var qCus = from ad in db.Addresses
                      join ua in db.UsersToAddresses on ad.AddressID equals ua.AddressId
                      join cus in db.CustomerNames on ua.CustomerId equals cus.CustomerId
                      where (ua.UserId == userId)
                      select new CompanyOverview() {
                          UserId = userId,
                          AddressId = ad.AddressId,
                          CustomerName = cus
                      };

        var result = qCus.SingleOrDefault();

        if (result != null) 
        {
            result.AddressDetail = db.Addresses.Where(a => a.CustomerId == result.CustomerName.CustomerId)
        }
        return result;
    }

    //[Key] <- You don't need to define a Key annotation, since this will only be a ViewModel (thus, not having persistence by EF).
    public string UserId { get; set; }
    public string AddressId { get; set; }
    public virtual CustomerNames CustomerName { get; set; }
    public virtual ICollection<Addresses> AddressDetail { get; set; }
}

检查这是否对您有任何帮助...（我仍然不确定我是否正确，正如我之前所说，这里有很多需要改进的地方 - 但我正在尝试解决您的具体问题并使用仅限您的解决方案类型，以确保我不会混淆您）。

c# - 使用参考表显示公司资料页面

问题描述

解决方案

推荐阅读