Pass dynamic data to Flexigrid or jqGrid

How to pass dynamic data to Flexigrid? ala-ASP.NET's GridView which columns are auto-populated when you don't statically assigned it columns.

Use Linq. Cast the ADO.NET DataTable's Columns to Linq-able DataColumn. If you are using Flexigrid or jqGrid, use the following:


On Controller, use this:

public class HomeController : Controller
{
    //
    // GET: /Home/

    // public string TableName = "INFORMATION_SCHEMA.COLUMNS";
    public string TableName = "Country";

    public ActionResult Index()
    {
        ViewBag.Columns = TheColumns(TableName);
        return View();
    }

    public JsonResult List(int page, int rp)
    {
        
        int offset = (page - 1) * rp;

        var da = new SqlDataAdapter(                
string.Format(
@"
with a as
(
select ROW_NUMBER() over(order by {0}) - 1 as r, * from {1} 
)
select * from a
where r between {2} and {3}",
            TheColumns(TableName)[0].ColumnName, 
            TableName,
            offset, offset + rp - 1),  TheConnection());
        var dt = new DataTable();
        da.Fill(dt);

        var jsonData = new
        {
            page = page,
            total = RowCount(TableName),
            rows = dt.Select()
                .Select(row =>
                    new
                    {
                        // 0 is row number, 1 is the primary key
                        id = row[1].ToString(),
                        // don't include row number and primary key on display, it is in Ordinal 0 and 1 respectively                            
                        cell = dt.Columns.Cast<DataColumn>().Where(col => col.Ordinal > 1)
                            .Select(col => row[col.ColumnName].ToString())
                    }
                )
        };

        return Json(jsonData);
    }

    long RowCount(string tableName)
    {
        return (long)new SqlCommand("select count_big(*) from " + tableName, TheConnection()).ExecuteScalar();
    }

    DataColumnCollection TheColumns(string tableName)
    {
        var da = new SqlDataAdapter("select * from " + TableName + " where 1 = 0", TheConnection());
        var dt = new DataTable();
        da.Fill(dt);
        return dt.Columns;
    }

    SqlConnection TheConnection()
    {
        var c = new SqlConnection("Data Source=localhost; Initial Catalog=OkSystem; User Id=sa; Password=P@$$w0rd");
        c.Open();
        return c;
    }

}

Then on View, use this:

@using System.Data;

<script src="/Scripts/jQuery/jquery-1.4.4.min.js" type="text/javascript"></script>

<link href="/Scripts/flexigrid/flexigrid.css" rel="stylesheet" type="text/css" />
<script src="/Scripts/flexigrid/flexigrid-google-minified-simple.js" type="text/javascript"></script>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>


<table id="theNavigation"></table>


@{ int i = 0; }

<script type="text/javascript">
    $(function() {

        $('#theNavigation').flexigrid({
            url: '/Home/List',
            dataType: 'json',
            colModel: [ 
                @foreach (DataColumn c in ViewBag.Columns) {
                    ++i;                    
                    if (i == 1) {
                        continue; // skip primary key
                    }
                    <text>{ display: '@c.ColumnName', name: '@c.ColumnName', width: 100}@(i != ViewBag.Columns.Count ? "," : "")</text>
                }
            ],
            singleSelect: true,            
            usepager: true,
            title: 'Sample Dynamic',
            useRp: true,
            rp: 5,
            showTableToggleBtn: true,
            width: 680,
            height: 200

        }); //flexigrid

        $('#theNavigation').click(function() {
            alert('A');
            var items = $('.trSelected', this);
            alert(items.length);
            if (items.length == 1)
            {
                var pk = items[0].id.substring(3);
                alert(pk);
            }
        });
        
    });
</script>

There's no model, columns are dynamic, obtained from ADO.NET DataTable :-)