将一个表中的两个表数据组合在一起 [英] Combine two table data in one rows

问题描述

我有两个表，一个是[tbl_product]表，另一个是[tbl_ProductDetails]

tbl_product表包含产品的所有信息和

[tbl_ProductDetails]包含的信息关于主要产品的子项目

i必须将arraylist显示为mainproductdetails，副产品为

i have two tables one is [tbl_product] table and another is [tbl_ProductDetails]
tbl_product table contain all the information of product and
[tbl_ProductDetails] contains information of subpoduct regarding to main product
i have to display arraylist as mainproductdetails with subproduct as

{
"Id":2,
"Name":"Tandoori Tikka (Chicken)",
"Category":"Chicken",
"Description":"No matter what the occasion – an evening hunger pang, hosting a bunch of friends at home or just watching the game on TV, this yummy chicken dish can be enjoyed at all times. Not only are these tikkas great to taste, but also very simple to make. Just cut open the pack and cook this dish as per the instructions for a delicious experience!",
"NFacts_Energy":"106.5kcal",
"NFacts_TotalFat":"4.2g",
"NFacts_TotalCarb":"4.7g",
"NFacts_Proteins":"12.4g",
"NFacts_Sugar":"\u003c0.5g",
"CookingInstructions":"\u003cstrong\u003eTava Grill\u003c/strong\u003e\u003cbr /\u003e \u003cp\u003e Heat 1 tbsp of oil in a non-stick frying pan. Put the Tandoori Tikka in preheated pan on medium flame for 3 minutes with cover on the pan",
  "subproduct":[
   {"Id":4,"Name":"Hot \u0026 Grill (Chicken)","ProductId":3,"Weight":"300g","Price":200.00},{"Id":7,"Name":"Hot \u0026 Grill (Chicken)","ProductId":3,"Weight":"250g","Price":150.00}]
}

我尝试过：



select [from [tbl_Product] inner join tbl_ProductDetails on [tbl_Product] .id = tbl_ProductDetails.ProductId where [tbl_Product] .isactive = 0 and tbl_ProductDetails.isactive = 0

What I have tried:

select * from [tbl_Product] inner join tbl_ProductDetails on [tbl_Product].id= tbl_ProductDetails.ProductId where [tbl_Product].isactive=0 and tbl_ProductDetails.isactive=0

推荐答案

您需要两个班级来保存您的数据：

You'll need two classes to hold your data:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public string Description { get; set; }
    public string NFacts_Energy { get; set; }
    public string NFacts_TotalFat { get; set; }
    public string NFacts_TotalCarb { get; set; }
    public string NFacts_Proteins { get; set; }
    public string NFacts_Sugars { get; set; }
    public string CookingInstructions { get; set; }
    
    public IList<SubProduct> subproduct { get; } = new List<SubProduct>();
}

public class SubProduct
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ProductId { get; set; }
    public string Weight { get; set; }
    public decimal Price { get; set; }
}

然后你需要加载数据 - Dapper [ ^ ]使这相当容易：

You then need to load the data - Dapper[^] makes this fairly easy:

const string query = "SELECT * FROM [tbl_Product] WHERE isactive = 0;
SELECT * FROM [tbl_ProductDetails] WHERE isactive = 0;";

using (var connection = new SqlConnection(...))
{
    List<Product> products;
    using (var multi = connection.QueryMultiple(query))
    {
        products = multi.Read<Product>().ToList();
        var subproducts = multi.Read<SubProduct>().ToLookup(sp => sp.ProductId);
        foreach (var product in products)
        {
            foreach (var subproduct in subproducts[product.id])
            {
                product.subproduct.Add(subproduct);
            }
        }
    }
    
    ...
}

编辑：不像我想象的那么简单。如果使用多映射选项，则最终会为结果中的每一行输入一个 Product 记录。您必须使用多个结果选项进行一对多查询。





然后您需要转换列表JSON的产品。 Json.NET [ ^ ]可能是最好的方法：

Not quite as simple as I thought. If you use the "multi-mapping" option, you end up with one Product record for each row in the results. You have to use the "multiple results" option for a one-to-many query.


Then you need to convert the list of products to JSON. Json.NET[^] is probably the best way to do that:

string json = JsonConvert.SerializeObject(products, Formatting.Indented);

这篇关于将一个表中的两个表数据组合在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！