Combine two table data in one rows

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

C#

{
"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}]
}

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

Posted 18-May-16 3:15am

Member 11466758

Updated 18-May-16 4:22am

Add a Solution

Comments

Herman<T>.Instance 18-May-16 9:26am

What's the question?

Member 11466758 18-May-16 9:27am

question is that i have to combine two tablees data in above format

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2016-05-18T04:22:00

Solution 1

You'll need two classes to hold your data:

C#

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

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

C#

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

EDIT: 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:

C#

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

Posted 18-May-16 4:22am

Richard Deeming

Updated 18-May-16 4:35am

v3

Comments

Member 11466758 19-May-16 5:47am

using (var multi = connection.QueryMultiple(query)) this line gives me error as QueryMultiple does not support is there any dll(references) for it

Richard Deeming 19-May-16 7:29am

Dapper:
https://github.com/StackExchange/dapper-dot-net[^]
https://www.nuget.org/packages/Dapper[^]

Member 11466758 19-May-16 8:20am

not working plea
se send me another solution

Richard Deeming 19-May-16 8:22am

"Not working" is not enough information for anyone to help you.

If you're not going to put any effort in to explaining what's wrong with the current solution, why should anyone put any effort into providing you with another?

Member 11466758 19-May-16 8:58am

i have download nudget package for dapper class but still show an error on querymultiple line as missing assembly refrences
please help

Richard Deeming 19-May-16 13:43pm

Make sure you have a using Dapper; line at the top of your code file.

Member 11466758 20-May-16 1:49am

thanks Richard.I got an answer but there is one problem i want output in following format :
{
"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}]
}

and now i am getting output as

{
"subproduct": [
{
"Id": 1,
"Name": null,
"ProductId": 2,
"Weight": "250g",
"Price": 150.00
},
{
"Id": 2,
"Name": null,
"ProductId": 2,
"Weight": "300g",
"Price": 200.00
}
],
"Id": 2,
"Name": "Tandoori Tikka (Chicken)",
"Category": "RealGood",
"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_Sugars": null,
"CookingInstructions": "Tava Grill 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. Remove the cover and flip the product, continue for another 1 minute. Oven Place Tandoori Tikka pieces in a greased nonstick pan/mesh and heat in an OTG (oven, toaster & griller) at 180°C for 4-5 minutes or until done. "
}
how can i modify above code to getting this output.
Your reply is very helpful for me.thanks again

Richard Deeming 20-May-16 7:36am

As far as I can see, the current output matches the structure of the expected output. The only difference is in the order of the properties, which is irrelevant in JSON.

If you're really that worried about it, use the JsonProperty attribute[^] to override the order:

[JsonProperty(Order = 1000)]
public IList<SubProduct> subproduct { get; } = new List<SubProduct>();

Member 11466758 20-May-16 7:49am

Thanks for Reply.