Using Pagination with a DataTable object in MVC 3

1
votes

I am receiving a DataTable object from the controller and using that to display a table in the view with the following code:

@model System.Data.DataTable 

<table width="65%">
<tr>        

    <th>USERNAME</th>
    <th>ROLE</th>
    <th>ACTIVE</th>
    <th></th>
    <th></th>

</tr>

@foreach(DataRow row in Model.Rows)
{
    <tr>           

        @foreach (DataColumn column in Model.Columns)
        {
            <td>@row[column]</td>   
        }

        <td>@Html.ActionLink("Edit", "Edit", new { id = row[0] })</td>
        <td>@Html.ActionLink("Delete", "Delete",new{id=row[0]})</td>              

    </tr>        
}
</table>

I'd like to add pagination so that the user doesn't have to scroll down for a long period when the table becomes populated with a lot of data. This is for a basic learning exercise that I'm doing where I've hard-coded all of the database SQL calls in the relevant controller action methods. I'm not using an ORM like NHibernate so ideally I'm looking for a solution that just uses the DataTable object on the server side.

Here's the code from the controller (with the DB specifics removed):

public ActionResult Index()
    {            
        string oradb = "Data Source = XXXXXXXXXXXXXXXXXXX";

        OracleConnection conn = new OracleConnection(oradb);

        conn.Open();

        string sql = " SELECT USER_ROLE_ID, USERNAME, ROLE, ACTIVE_IND FROM LD_USER_ROLE";

        OracleDataAdapter adapter = new OracleDataAdapter(sql, conn);
        DataTable dt = new DataTable();
        adapter.Fill(dt);

        conn.Close();


        return View(dt);            
    }
2
c#databaseasp.net-mvc-3datatable
Typically you would paginate at the Data Access layer (SQL) to avoid sending more data than necessary over the wire. If you do that, then you don't need to worry about displaying only some of the records in the presentation layer. However, you would also need a count of records/pages from the database so you can display a page selector. - mellamokb
@mellamokb Well, another thing I would like to do is to sort the columns so I probably need all of the data to do that as well. - Sperick
That can be handled server-side as well. A SQL engine is far more efficient at both paging and sorting data than your client will be. - mellamokb

2 Answers

2
votes

As already pointed out, paging is usually done server side, that way you get the benefit of limiting what comes over the wire as well as limiting what the user has to take in. However, if you only care about the user experience and not so much about the page bandwidth, then you can do client-side paging using a JavaScript library such as knockout. Look at this knockout paging link for information on how to accomplish client-side paging.

0
votes

In order to solve this I had to rewrite the controller method so that it converted the DataTable into a List of User objects and then I passed this list through to the view. By doing this I was then able to use the WebGrid object which enables easy pagination. The controller code also uses a dataAccess object to encapsulate some of the database info and calls.

Controller code:

public ActionResult Index()
    {
        UserRoleDAL udl = new UserRoleDAL();
        DataSet ds = udl.GetData(" SELECT USER_ROLE_ID, USERNAME, ROLE, ACTIVE_IND FROM LD_USER_ROLE");


        var ZUsers = new List<ZUserRoleModel>();

        if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                var dr = ds.Tables[0].Rows[i];


                var ZUser = new ZUserRoleModel()
                {
                    UserRoleId = Convert.ToInt64(dr["USER_ROLE_ID"]),
                    UserName = dr["USERNAME"].ToString(),
                    Role = dr["ROLE"].ToString(),
                    ActiveInd = ActiveBool
                };
                ZUsers.Add(ZUser);
            }
        }

        return View(ZUsers);
    }

View Code:

@model IEnumerable<CIMsWebApp.Models.ZUserRoleModel>



@{  var grid = new WebGrid(Model, canSort: false, canPage: true, rowsPerPage:25); }


@grid.GetHtml(
                tableStyle: "dataGrid", 
                headerStyle: "header",
                alternatingRowStyle: "evenRow", 
                columns: grid.Columns
  (
          grid.Column(header: "UserName", columnName: "UserName"),
          grid.Column(header: "Role ", columnName: "Role"),
          grid.Column(header: "Active", columnName: "ActiveInd"),
          grid.Column(header: "Edit", format: @<text>@Html.ActionLink("Edit", "Edit",  new { id = @item.UserRoleId })</text>),
          grid.Column(header: "Delete", format: @<text>@Html.ActionLink("Delete", "Delete", new { id = @item.UserRoleId })</text>)
  ))