Click here to Skip to main content
15,890,123 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a stored sql server getting a dynamic pivot from table 
now i want to display this in a view in mvc
i using asp.net and MVC5
thanks for all.


What I have tried:

i tried code in below

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;
 
        }
 
    }
}
Posted
Updated 30-Nov-17 2:52am
Comments
Richard Deeming 1-Dec-17 14:08pm    
REPOST
This is now your THIRD copy of this question:
https://www.codeproject.com/Questions/1216102/How-to-create-view-for-a-dynamically-columns-and-r[^]
https://www.codeproject.com/Questions/1217645/Hello-how-can-I-get-a-view-on-MVC-Csharp-for-my-dy[^]

If you want to update your question with more information, click the green "Improve question" link at the bottom of the question. DO NOT post your update as a new question!

1 solution

I googled "mvc view show datatable" and this was the first result

https://www.aspsnippets.com/Articles/Display-Show-DataSets-and-DataTables-in-View-in-ASPNet-MVC-Razor.aspx[^]

If that doesn't suit your needs then google yourself to find more. Note it doesn't matter how the datatable is populated so don't dismiss results that don't involve pivot tables.
 
Share this answer
 
v2
Comments
Elvally Boubacar 30-Nov-17 11:01am    
hi thanks for your answer but i have a dynamic model and i want to display data on mvc view
i tried code in below but nothing


@model List<Dictionary<string, object>>
@{
ViewBag.Title = "DynamicIndex";
Layout = "~/Views/Shared/_Layout.cshtml";
}
@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>
();
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)
{
k++;
}
dt.Rows.Add(dr);
// dt.Rows.Add(tempList.ToArray<string>
());
}
tempList.Add(d[i].Planner.ToString()); tempList.Add(d[i].Vendor_Location.ToString()); tempList.Add(d[i].Bulk_Material.ToString()); dr[k] = v.toString();
F-ES Sitecore 30-Nov-17 11:10am    
Use the DataTable as the model you pass to the view.
Elvally Boubacar 30-Nov-17 11:50am    
yeah i already used datatable but i met an error like my model getting null
on below my view with datatable

@using System.Data
@model DataTable


@foreach (DataColumn col in Model.Columns)
{
}

@foreach (DataRow row in Model.Rows)
{
@foreach (DataColumn col in Model.Columns)
{
}
}
@col.ColumnName@row[col.ColumnName]
F-ES Sitecore 30-Nov-17 11:58am    
It's null because you're not passing a model to the view

return View();

you need to pass the model as a parameter

return View(PivotTableView());
Elvally Boubacar 30-Nov-17 12:25pm    
now i getting this error



System.Web.Mvc.WebViewPage<tmodel>.Model.get retournée null.


in @foreach (DataColumn col in Model.Columns) on my view

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900