You must Sign In to post a response.
  • Category: LINQ

    How to get record into hierarchy class(parent,child,subchild)

    tbl_Country
    Country_ID Country_Name
    1 India
    2 USA
    tbl_State
    State_Id State_Name Country_ID
    1 State1 2
    2 State2 1
    3 State3 1
    4 State4 2

    tbl_District
    District_Id District_Name State_Id
    1 District1 2
    2 District2 1
    3 District3 2
    4 District5 3

    tbl_Pruduct
    Product_Id ProductName District_Id
    1 Product1 1
    2 Product2 1
    3 Product3 1
    4 Product4 3
    5 Product5 4
    6 Product6 3
    7 Product7 4

    tbl_PrudcutDetails
    ID ProductType Product_Id Period QTY
    1 Rice 1 Jan 16 10
    2 Rice 1 Feb 16 11
    3 Rice 1 Mar 16 12
    4 Rice 1 Apr 16 13
    5 Wheat 1 Jan 16 14
    6 Wheat 1 Feb 16 15
    7 Wheat 1 Mar 16 16
    8 Wheat 1 Apr 16 17
    9 Wheat 1 May 16 18
    10 Rice 2 Jan 16 19
    11 Rice 2 Feb 16 20
    12 Rice 2 Mar16 21
    12 Rice 2 April16 22
    12 Rice 2 Aug 16 23

    above tables are already there but below output need to get. basically every month data will be display in pivot order based on producttype(Like rice, wheat). if no record for particular month then will be 'o'

    ProductType Product Jan16 Feb 16 Mar16 Apr16 May16 June16 July16 Aug16 Sept16 So on for 10 years
    Rice Product1 10 11 12 13 0 0 0 0 0
    Wheet Product1 14 15 16 17 18 0 0 0 0
    Rice Product2 19 20 21 22 0 0 0 23 0

    finally i want bind all table records into a class hierarchy using LINQ
    Country class ,inside country State class, inside state district class and inside district bind above output table.

    please let me know if need any more clarification.

    Thanks
  • #764697
    Looks like you posted complete requirement of yours!!!

    I request you to start the coding yourself and post only the issue what you are facing here. So there the members can help you quickly.


    Regards,
    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM

  • #764705
    Hai Santosh,
    If you want the data to be come from different tables, you need to join them.
    You can take any join objects example for Linq.
    See the below exmaple for the refernce:

    var results = from c in db.TableA
    join cn in db.Countries on c.CountryID equals cn.ID
    join ct in db.Cities on c.CityID equals ct.ID
    join sect in db.Sectors on c.SectorID equals sect.ID
    where (c.CountryID == cn.ID) && (c.CityID == ct.ID) && (c.SectorID == company.SectorID) && (company.SectorID == sect.ID)
    select new { country = cn.Name, city = ct.Name, c.ID, c.Name, c.Address1 + c.Address2 + c.Address3, c.PostCode, c.Telephone};
    return results.ToList();

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #764710
    1. Create the tables as you specified in your requirment. You can use SQL Server/Oracle etc. .based on your project selection
    2. In SQL server we have the concept PIVOT. You can handle it as per your requirement. Following is the sample code for PIVOT

    SELECT *
    FROM (
    SELECT
    year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
    InvoiceAmount as Amount
    FROM Invoice
    ) as s
    PIVOT
    (
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr,
    may, jun, jul, aug, sep, oct, nov, dec)
    )AS pvt

    3. Design the Grid view, if you are using ASP.net. Assign the data source and bind the data.

    SqlConnection conn = new SqlConnection(Sitecore.Configuration.Settings.GetConnectionString("feedback"));
    ...
    Your ADO.net code logic to get data from PIVOT
    ...
    GridView1.DataSource = ds;
    GridView1.DataBind();

    By Nathan
    Direction is important than speed


Sign In to post your comments