Display dynamic Columns from database in ASP.Net MVC

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Related to previous question i want to get pivot table from my

stored procedure with dynamic query

Problem is field is not static so how can create in C# my viewModel to show in MVC page ?

You are viewing reply posted by: dharmendr 5 months ago.
Posted 5 months ago

Hi jon,

There is no need to create viewModel. You can pass DataSet as model for the view.

Then using loop you can display the result set in the view.

For more details refer below article.

Display (Show) DataSets and DataTables in View in ASP.Net MVC Razor

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespaces

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        DataSet ds = new DataSet();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            string query = "SELECT TOP 10 CustomerID,ContactName,City,Country FROM Customers";
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(ds);
                }
            }
        }

        return View(ds);
    }
}

View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<System.Data.DataSet>" %>

<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Index</title>    
</head>
<body>
    <table>
        <tr>
            <% foreach (DataColumn column in Model.Tables[0].Columns) {%>
            <th><%=column.ColumnName%></th>
            <% }%>
        </tr>
        <% foreach (DataRow row in Model.Tables[0].Rows) {%>
        <tr>
            <%foreach (DataColumn column in Model.Tables[0].Columns) {%>
            <td><%=row[column.ColumnName]%></td>
            <%} %>
        </tr>
        <% }%>
    </table>
</body>
</html>

Screenshot