如何在单个MVC视图中显示多个表中的数据

I am having a hard time solving the following with an MVC view.

我很难用MVC视图解决以下问题。

My goal is to display data from multiple tables in a single MVC view. The bulk of the data comes from a table called Retailers. I also have another table called RetailerCategories which stores the retailerid from the Retailers table and also a categoryid linking to a Category table.

我的目标是在单个MVC视图中显示来自多个表的数据。大部分数据来自名为Retailers的表。我还有另一个名为RetailerCategories的表，它存储来自Retailers表的retailerid，以及一个链接到Category表的categoryid。

Note that there are multiple records for each retailerid in the RetailerCategories table.

请注意，RetailerCategories表中的每个retailerid都有多条记录。

In the view I want to show a list of retailers and with each retailer I want to show the list of categories applicable to them.

在视图中，我想显示零售商列表，并且每个零售商都要显示适用于他们的类别列表。

What would be the best way to accomplish this? Some of the things I have tried are covered in Can you help with this MVC ViewModel issue?

实现这一目标的最佳方法是什么？我已经尝试过的一些内容包含在你能帮助解决这个MVC ViewModel问题吗？

This however does not appear to be the right approach.

然而，这似乎不是正确的方法。

3 个解决方案

#1

You need a view model specifically tailored to the needs of this view. When defining your view models you shouldn't be thinking in terms of tables. SQL tables have absolutely no meaning in a view. Think in terms of what information you need to show and define your view models accordingly. Then you could use AutoMapper to convert between your real models and the view model you have defined.

您需要一个专门针对此视图需求定制的视图模型。在定义视图模型时，您不应该考虑表格。 SQL表在视图中绝对没有意义。根据您需要显示哪些信息并相应地定义视图模型。然后，您可以使用AutoMapper在实际模型和已定义的视图模型之间进行转换。

So forget about all you said about tables and focus on the following sentence:

所以忘记关于表格的所有内容并关注以下句子：

In the view I want to show a list of retailers and with each retailer I want to show the list of categories applicable to them.

在视图中，我想显示零售商列表，并且每个零售商都要显示适用于他们的类别列表。

This sentence is actually very good as it explains exactly what you need. So once you know what you need go ahead and modelize it:

这句话实际上非常好，因为它准确地解释了你需要什么。所以，一旦你知道你需要什么继续并模拟它：

public class CategoryViewModel
{
    public string Name { get; set; }
}

public class RetailerViewModel
{
    public IEnumerable<CategoryViewModel> Categories { get; set; }
}

Now you strongly type your view to IEnumerable<RetailerViewModel>. From here it is easy-peasy to do what you want in the view:

现在，您强烈地将视图键入IEnumerable 。从这里开始，您可以轻松地在视图中执行您想要的操作：

showing a list of retailers with each retail having a list of associated categories.

显示零售商列表，每个零售商都有一个相关类别列表。

#2

this could be also helpful;

这可能也有帮助;

video from chris pels

来自chris pels的视频

#3

It is simple just do what I say step by step.

这很简单就是按照我说的一步一步做。

add connection string into web.config file

将连接字符串添加到web.config文件中
select models from solution explorer and add 4 classes as following

从解决方案资源管理器中选择模型并添加以下4个类
- 1st class for first table "i have employ table which have 3 columns
  
  第1表的第1类“我雇用了3列的表
  
  public class Employ { [Key] public int Emp_id { get; set; } public string Emp_name { get; set; } public string Emp_city { get; set; } }
  
  public class Employ {[Key] public int Emp_id {get;组; public字符号Emp_name {get;组;公共字符串Emp_city {get;组; }}
- 2nd class for my tempo table
  
  我的节奏表第二节课
  
  public class tempo { [Key] public int ID { get; set; } public int Emp_Id { get; set; } public string subject { get; set; } public string hobby { get; set; } }
  
  public class tempo {[Key] public int ID {get;组; public int Emp_Id {get;组;公共字符串主题{get;组;公共字符串爱好{get;组; }}
Now I create a third class in model folder which contain value that i want from employ table and tempo table

现在我在模型文件夹中创建了第三个类，它包含了我想要使用的表和速度表的值
```
public class Alladd
{
    public int ID { get; set; }
    public int Emp_Id { get; set; }
    public string subject { get; set; }
    public string hobby { get; set; }
    public string Emp_name { get; set; }
    public string Emp_city { get; set; }
}  
```
and the final class is datacontext class

最后一个类是datacontext类
```
public class DataContext:DbContext
{
    public DataContext() : base("DefaultConn")//connection string
    {
    }

    public DbSet<Employ> Empdata { get; set; }
    public DbSet<tempo> Tempdata { get; set; }
}
```

now go to the Home controller and add code as below

现在转到Home控制器并添加如下代码

public ActionResult file()
{
    // IList<tempo> tempi=new List<tempo>();

    IEnumerable<Alladd> model = null;

    // model = getVerifydetails(id);
    // return View(objcpModel);
    List<Alladd> verify = new List<Alladd>();

    cn.Open();

    if (cn.State == ConnectionState.Open)
    {
        string query = "select Employ.Emp_name,Employ.Emp_id,Employ.Emp_city,tempo.hobby,tempo.id,tempo.subject from Employ inner join tempo on Employ.Emp_id=tempo.Emp_id;";//joining two table 
        SqlCommand cmd=new SqlCommand(query,cn);
        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            verify.Add(new Alladd { Emp_name = dr[0].ToString(), Emp_Id= Convert.ToInt32(dr[1].ToString()), Emp_city = dr[2].ToString(), hobby = dr[3].ToString(),ID = Convert.ToInt32(dr[1].ToString()),subject= dr[4].ToString()});//filling values into Alladd class
        }

        cn.Close();
    }

    return View(verify);
}

now the final step is so simple

现在最后一步是如此简单
1. go to solution explorer
2. 去解决方案资源管理器
3. select views folder and left click on it and select add view
4. 选择views文件夹并左键单击它，然后选择添加视图
5. now name it as "file" which we give it into controller
6. 现在将其命名为“文件”，我们将其命名为控制器
7. check on create strongly type view
8. 检查创建强类型视图
9. select model class from dropdown-> Alladd
10. 从dropdown-> Alladd中选择模型类
11. select scaffold templet ->List
12. 选择脚手架模板 - >列表
13. hit Add button
14. 点击添加按钮

Now you're done

现在你已经完成了

Happy coding...

快乐的编码......

#1