How to create view in MVC for a dynamically columns and rows

Question

0.00/5 (No votes)

See more:

I have a stored sql server to get a dynamic pivot from table
now i want to display this in a view in mvc
i using asp.net and MVC4
thanks for all.

What I have tried:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using YMOReporting.Models;
using System.Data.SqlClient;

namespace YMOReporting.Controllers.Reportings
{
    public class Proc1Controller : Controller
    {
        private YMOReportingConnex db = new YMOReportingConnex();

            // GET: Proc1 : stored for a dynamic pivot
            public ActionResult Index()
            {
                return View();
            }
        public DataTable PivotTableView()
        {
            db.GetProc1().ToList();

            DataTable dt = new DataTable();

            //GetProc1() return All data for Table.
            var data = db.GetProc1().ToList();

            //Applying linq for geeting pivot output
            var d = (from f in data
                     group f by new { f.Planner, f.Vendor_Location, f.Bulk_Material }
                into myGroup
                     where myGroup.Count() > 0
                     select new
                     {
                         myGroup.Key.Planner,
                         myGroup.Key.Vendor_Location,
                         myGroup.Key.Bulk_Material,
                         subject = myGroup.GroupBy(f => f.Week).Select
                             (m => new { Sub = m.Key, Value = m.Sum(c => c.Value) })
                     }).ToList();

            var sub = db.GetProc1().ToList();
            // Distinct Week Like Below
            //Creating array for adding dynamic columns
            ArrayList objDataColumn = new ArrayList();

            if (data.Count() > 0)
            {
                //Three column are fix "Planner","Vendor_Location","Bulk_Material".
                objDataColumn.Add("Planner");
                objDataColumn.Add("Vendor_Location");
                objDataColumn.Add("Bulk_Material");

                //Add Subject Name as column in Datatable
                for (int i = 0; i < sub.Count; i++)
                {
                    objDataColumn.Add(sub[i].Week);
                }

                //Add dynamic columns name to datatable dt
                for (int i = 0; i < objDataColumn.Count; i++)
                {
                    dt.Columns.Add(objDataColumn[i].ToString());
                }

                //Add data into datatable with respect to dynamic columns and dynamic data
                for (int i = 0; i < d.Count; i++)
                {
                    List<string> tempList = new List<string>();
                    tempList.Add(d[i].Planner.ToString());
                    tempList.Add(d[i].Vendor_Location.ToString());
                    tempList.Add(d[i].Bulk_Material.ToString());

                    var res = d[i].subject.ToList();
                    for (int j = 0; j < res.Count; j++)
                    {
                        tempList.Add(res[j].Value.ToString());
                    }

                    dt.Rows.Add(tempList.ToArray<string>());
                }

            }
            return dt;

        }

    }
}

C#

Posted 19-Nov-17 4:49am

Elvally Boubacar

Updated 30-Nov-17 18:13pm

v3

Add a Solution

3 solutions

Solution 1

try

public ActionResult Index()
       {
           DataTable dt = PivotTableView();
           return View(dt);
       }

@using System.Data
@model DataTable

<table cellpadding="0" cellspacing="0">
    <tr>
        @foreach (DataColumn col in Model.Columns)
        {
            <th>@col.ColumnName</th>
        }

    </tr>
    @foreach (DataRow row in Model.Rows)
    {
        <tr>
            @foreach (DataColumn col in Model.Columns)
            {
                <td>@row[col.ColumnName]</td>
            } 
        </tr>
    }
</table>

Posted 19-Nov-17 5:16am

Karthik_Mahalingam

Comments

Elvally Boubacar 20-Nov-17 16:32pm

Hi thanks Sir for your a proposition but my stored return null (it return : NullReferenceException) so i can't show the result in my view

Karthik_Mahalingam 20-Nov-17 20:30pm

in which line you are getting the error. the view code sholuldnt since we have not accessed any property which prones to null reference error.

Elvally Boubacar 21-Nov-17 3:03am

in the first foreach exactly in Model.Columns

Karthik_Mahalingam 21-Nov-17 3:08am

is the Model null ?

Elvally Boubacar 21-Nov-17 3:27am

the model is for a dynamic pivot table. in below

namespace YMOReporting.Models
{
using System;
using System.Collections.Generic;
using System.Data;

public partial class Proc1_Result
{
public string Planner { get; set; }
public string Vendor_Location { get; set; }
public string Bulk_Material { get; set; }
public Nullable<double> Value { get; set; }
public string Week { get; set; }
}
}

Karthik_Mahalingam 21-Nov-17 3:33am

can you show a screenshot on how your data is appearing?
are you using datatable or Class as model ?

Elvally Boubacar 21-Nov-17 3:44am

yeh im using datatable
my view must return something as

https://drive.google.com/file/d/1dxpXch3LaM3ArKyAX-FpfGsN9WyWwPhX/view?usp=sharing

Karthik_Mahalingam 21-Nov-17 3:57am

The image is datable rite?

Elvally Boubacar 21-Nov-17 4:11am

i put it on google drive

Karthik_Mahalingam 21-Nov-17 4:12am

yeah saw that image, it is in datatable rite?
post datatable data you are doing some formatting?

Elvally Boubacar 21-Nov-17 4:17am

this is my source data who i want pivot https://drive.google.com/file/d/1NM6ZvPfTN96oHicEnI26zEU9clrRwfai/view?usp=sharing

Karthik_Mahalingam 21-Nov-17 4:19am

how pivot is applied?
your question is about applying pivot or dynamic table in view?

Elvally Boubacar 21-Nov-17 4:28am

my question:
i have a table with a dynamic column (Week) and I want to display it by pivoting this column dynamically on a mvc view

Karthik_Mahalingam 21-Nov-17 4:30am

how are you pivoting?
c# or javascript ?

Elvally Boubacar 21-Nov-17 4:30am

c#

Karthik_Mahalingam 21-Nov-17 4:30am

check this C# Pivot Table[^]

Elvally Boubacar 21-Nov-17 4:34am

can i using dynamic datatable on view with foreach to get this pivot?

Karthik_Mahalingam 21-Nov-17 5:00am

this is how you should do
because dynamic things cant be handled properly using class entity

Elvally Boubacar 21-Nov-17 7:15am

thanks for your replay
now i ask how i can dispaly this on view

Solution 3

for (int i = 0; i < objDataColumn.Count; i++)
                {
                    dt.Columns.Add(objDataColumn[i].ToString());
                }



for (int i = 0; i < d.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    List<string> tempList = new List<string>();
                    tempList.Add(d[i].Planner.ToString());
                    tempList.Add(d[i].Vendor_Location.ToString());
                    tempList.Add(d[i].Bulk_Material.ToString());
                    
                    var res = d[i].subject.ToList();
                    for (int j = 0; j < res.Count; j++)
                    {
                        tempList.Add(res[j].Value.ToString());
                    }
                    int  k = 0;
                    foreach(var v in tempList)
                    {
                        dr[k] = v.toString();
                        k++;
                     }
                     dt.Rows.Add(dr);
                   // dt.Rows.Add(tempList.ToArray<string>());
                }

Posted 20-Nov-17 22:41pm

Laxmidhar tatwa technologies

Updated 20-Nov-17 23:01pm

v2

Comments

Elvally Boubacar 30-Nov-17 11:49am

sorry a have a time
so i don't understand how can i use that can you explain more please

Add a Solution

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

Laxmidhar tatwa technologies · Accepted Answer · 2017-11-30T18:12:00

sir I am so sorry to send you the code for converting list to datatable as per your code.Actually the process is given bellow

1- cretate a model means a class as per the fields come from list

I modefy that in bellow

 var data = db.GetProc1().ToList();
var d = (from f in data
                     group f by new { f.Planner, f.Vendor_Location, f.Bulk_Material }
                into myGroup
                     where myGroup.Count() > 0
                     select new
                     {
                      planner =  myGroup.Key.Planner,
                      location =   myGroup.Key.Vendor_Location,
                       material =  myGroup.Key.Bulk_Material,
                         subject = myGroup.GroupBy(f => f.Week).Select
                             (m => new { Sub = m.Key, Value = m.Sum(c => c.Value) })
                     }).ToList();

here create model class in your project model

1- right click on the model folder add a class like bellow

public class planning
{
 public string planner { get; set; }
public string location { get; set;}
public string material { get; set;}
public decimal subject { get; set; }

}

public ActionResult PivotTableView()
{
 var data = db.GetProc1().ToList();             
var d = (from f in data
                     group f by new { f.Planner, f.Vendor_Location, f.Bulk_Material }
                into myGroup
                     where myGroup.Count() > 0
                     select new
                     {
                      planner =  myGroup.Key.Planner,
                      location =   myGroup.Key.Vendor_Location,
                       material =  myGroup.Key.Bulk_Material,
                         subject = myGroup.GroupBy(f => f.Week).Select
                             (m => new { Sub = m.Key, Value = m.Sum(c => c.Value) })
                     }).ToList();
   return View(d);

 }

Proces for view 

1- Right click on the method PivotTableView

2- Click the add view option from the option list
3 = tick the check box create a strolnly-typed view
4-select the model that created above as a class from the the dropdown list
5- click add

then in the view page add on he top 

<pre>@model IEnumerable<projectname.Models.classname>

in the for loop and table row display the data

How to create view in MVC for a dynamically columns and rows

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

How to create view in MVC for a dynamically columns and rows

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us