asp.net-mvc 如何在单个 MVC 视图中显示来自多个表的数据

How do I display data from multiple tables in a single MVC view

提问by Cunners

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 视图中显示来自多个表的数据。大部分数据来自名为“零售商”的表。我还有另一个表，称为 RetailerCategories，它存储来自 Retailers 表的retailid 以及链接到 Category 表的 categoryid。

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

请注意，RetailerCategories 表中的每个零售商 ID 都有多条记录。

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.

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

回答by Darin Dimitrov

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 AutoMapperto 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<RetailerViewModel>. 从这里可以轻松地在视图中执行您想要的操作：

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

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

回答by tugberk

this could be also helpful;

这也可能有帮助；

video from chris pels

克里斯·佩尔斯的视频

回答by Aakash jalodkar

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

很简单，按照我说的一步一步来。

1. add connection string into web.configfile

2. select models from solution explorer and add 4 classes as following

   • 1st class for first table "i have employ table which have 3 columns

     public class Employ { [Key] public int Emp_id { get; set; } public string Emp_name { get; set; } public string Emp_city { get; set; } }

   • 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; } }

   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

   public class DataContext:DbContext
   {
       public DataContext() : base("DefaultConn")//connection string
       {
       }
   
       public DbSet<Employ> Empdata { get; set; }
       public DbSet<tempo> Tempdata { get; set; }
   }
   

3. now go to the Home controller and add code as below

   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);
   }
   

4. now the final step is so simple

   1. go to solution explorer
   2. select views folder and left click on it and select add view
   3. now name it as "file" which we give it into controller
   4. check on create strongly type view
   5. select model class from dropdown-> Alladd
   6. select scaffold templet ->List
   7. hit Add button

1. 将连接字符串添加到web.config文件中

2. 从解决方案资源管理器中选择模型并添加 4 个类，如下所示

   • 第一个表的第一堂课“我使用了有 3 列的表

     public class Employ { [Key] public int Emp_id { get; 放; } 公共字符串 Emp_name { 获取；放; } 公共字符串 Emp_city { 获取；放; } }

   • 我的节奏表的第二堂课

     public class tempo { [Key] public int ID { get; 放; } public int Emp_Id { 获取；放; } 公共字符串主题 { 获取；放; } 公共字符串爱好{ get; 放; } }

   现在我在模型文件夹中创建了第三个类，其中包含我想要从雇佣表和节奏表中获得的值

   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; }
   }  
   

   最后一个类是数据上下文类

   public class DataContext:DbContext
   {
       public DataContext() : base("DefaultConn")//connection string
       {
       }
   
       public DbSet<Employ> Empdata { get; set; }
       public DbSet<tempo> Tempdata { get; set; }
   }
   

3. 现在转到 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);
   }
   

4. 现在最后一步如此简单

   1. 转到解决方案资源管理器
   2. 选择视图文件夹并左键单击它并选择添加视图
   3. 现在将其命名为“文件”，我们将其提供给控制器
   4. 检查创建强类型视图
   5. 从下拉列表中选择模型类-> Alladd
   6. 选择脚手架模板 -> 列表
   7. 点击添加按钮

Now you're done

现在你完成了

Happy coding...

快乐编码...