How to get HTML table with header data from database and row data from database as well in mvc 4

Question

I am working in mvc project where I want to generate one gridview(html table). The headers should bind from db and data also should bind from db dynamically. This is my metadata or master table.

Id      Name    Label           Value         Emp_id
31      Adhar   DocumentNumber  12345678       1
32      Adhar   ExpiryDate      1/1/2015       1
33      Pan     DocumentNumber  123456789      2
34      Pan     ExpiryDate      1/1/2015       2
36      Pan     IssueLoc        India          2

and my table should be like this below. if the input parameter emp_id is 1 then table should look like below. This is HTML

<Table><tr><th>DocumentNumber</th><th>ExpiryDate</th><td>12345678       </td>1/1/2015<td></td></tr></table>

OR this is plain web sheet.

DocumentNumber   ExpiryDate
12345678         1/1/2015

I am working in mvc4 entity framework. How to overcome from this? Can anybody give me some idea to solve it. I have googed across and found nothing. Please provide me some idea. If question is uncleared please let me know

This is for emp_id with 2

DocumentNumber   ExpiryDate  IssueLoc
12345678         1/1/2015    India

With respect to above data I need output in the form of

    Name   DocumentNumber   ExpiryDate
    Adhar  12345678         1/1/2015
    Name   DocumentNumber   ExpiryDate  IssuedLoc
    Pan    123456789        1/1/2015    India

Yes, The answer you provided is almost correct as per my requirement. I tried myself to make table as I need but I could not get it. Sorry for troubling you

What should be, if sets of Labels are same for all items, should headers repeated or only one top header is needed? — Slava Utesinov
– Slava Utesinov, Commented Mar 7, 2016 at 11:36

user3559349 · Accepted Answer · 2016-03-07 07:47:17Z

1

You need to group your data by the Name and then create a separate table for each group. To make this easier, add another view model

public class GroupVM
{
    public string Name { get; set; }
    public IEnumerable<MyItem> Items { get; set; }
}

and in the controller method

public ActionResult Test()
{
    var table = new List<MyItem> {
        new MyItem { Label = "DocumentNumber", Value = "12345678", Emp_id = 1, Name = "First" },
        new MyItem { Label = "ExpiryDate", Value = "1/1/2015", Emp_id = 1, Name = "First" },
        new MyItem { Label = "IssueLoc", Value = "India", Emp_id = 1, Name = "First" },
        new MyItem { Label = "DocumentNumber", Value = "SecondValue", Emp_id = 2, Name = "Second" },
        new MyItem { Label = "ExpiryDate", Value = "SecondValue", Emp_id = 2, Name = "Second" },                
    };
    var data = table.GroupBy(x => x.Name).Select(x => new GroupVM
    {
        Name = x.Key,
        Items = x
    });
    return View(data);
}

and then in the view

@model IEnumerable<GroupVM>
@foreach(var group in Model)
{
    <table>
        <thead>
            <tr>
                <th>Name</th>
                @foreach(var item in group.Items)
                {
                    <th>@item.Label</th>
                }
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>@group.Name</td>
                @foreach(var item in group.Items)
                {
                    <td>@item.Value</td>
                }
            </tr>
        </tbody>
    </table>
}

Refer DotNetFiddle

answered Mar 7, 2016 at 7:47

user3559349

Sign up to request clarification or add additional context in comments.

4 Comments

Niranjan Over a year ago

Great Logic. This is what i need. thanks Stephen. You are helping me from long time. Thanks a lot

user3559349 Over a year ago

You seem to have another related question here which should be deleted assuming this answer also solves that.

user3559349 Over a year ago

Also, when you get an answer that helps you, up vote, but don't actually accept it unless it solves you issue - it just confuses other users)

Niranjan Over a year ago

okay sure. up Vote I did not know it. I tried your answer its working fine.

Slava Utesinov · Accepted Answer · 2016-03-04 14:22:36Z

0

If I understand your question, solution will be look like this:

Action:

public ActionResult Test()
{
    //Here I mock your DB, use EF context (context.table) instead of table variable or what you want.
    var table = new List<MyItem> {
        new MyItem { Label = "DocumentNumber", Value = "12345678", Emp_id = 1, Name = "First" },
        new MyItem { Label = "ExpiryDate", Value = "1/1/2015", Emp_id = 1, Name = "First" },
        new MyItem { Label = "IssueLoc", Value = "India", Emp_id = 1, Name = "First" },

        new MyItem { Label = "DocumentNumber", Value = "SecondValue", Emp_id = 2, Name = "Second" },
        new MyItem { Label = "ExpiryDate", Value = "SecondValue", Emp_id = 2, Name = "Second" },                
    };

    var items = table.Where(x => x.Emp_id == 1 || x.Emp_id == 2).ToList();
    var headers = items.Select(x => x.Label).Distinct().ToList();
    var employers = items.Select(x => x.Emp_id).Distinct().ToList();

    if(employers.Count > 1)
        headers.Insert(0, "Name");

    var data = new List<List<string>>();
    data.Add(headers);            

    foreach (var emp in employers)
    {
        var row = new List<string>();
        foreach (var header in headers)
        {                    
            if (header != "Name")
            {
                var cell = items.Where(x => x.Label == header && x.Emp_id == emp).FirstOrDefault();
                row.Add(cell == null ? "" : cell.Value);
            }                        
            else
                row.Add(items.Where(x => x.Emp_id == emp).First().Name);
        }
        data.Add(row);
    }

    return View(data);
}

View:

@model List<List<string>>

<table>
    <thead>
        <tr>
            @foreach (var header in Model.First())
            {
                <th>@header</th>
            }
        </tr>        
    </thead>
    <tbody>
        @foreach (var row in Model.Skip(1))
        {
            <tr>
                @foreach (var cell in row)
                {
                    <td>@cell</td>
                }
            </tr>
        }
    </tbody>
</table>

edited Mar 4, 2016 at 14:22

answered Mar 4, 2016 at 9:42

Slava Utesinov

13.5k2 gold badges21 silver badges26 bronze badges

7 Comments

Niranjan Over a year ago

Yes exactly but for emp_id there are 3 fields so how to get third column? I just shown in question(edited main question)

Niranjan Over a year ago

Yes exactly but for emp_id there are 3 fields so how to get third column? I just shown in question(edited main question) Based on emp_id if there are 2 fields I want to display only two fields(for example, emp_id with 1 contains 2 fields) and if there are 3 fields I want to display three fields(e.g emp_id with 2) and final case is if I pass two both emp_id 's then I want to display all details(e.g Adhar and Pan with respect to above table).

Slava Utesinov Over a year ago

My solution not depends on concrete fields or their count, it is flexible and universal. Look at edited answer, I just add new element to table variable collection.

Niranjan Over a year ago

Yes I will study the solution. Thanks a lot Slava. If any doubts I get can I ask you?

Niranjan Over a year ago

Hi Slava, May I know what is Model.First and Model.Skip. Do I need to create any view model?

|

Collectives™ on Stack Overflow

How to get HTML table with header data from database and row data from database as well in mvc 4

2 Answers 2

4 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related