Fork me on GitHub

MvcPager demo - DataRow collection paging

This sample demonstrates how to page for DataRow collection filled with ADO.NET DataAdapter.

Order Id Customer Id Company Name Employee Name
10760MAISDMaison Dewey Margaret Peacock
10761RATTCRattlesnake Canyon Grocery Steven Buchanan
10762FOLKOFolk och fä HB Janet Leverling
10763FOLIGFolies gourmandes Janet Leverling
10764ERNSHErnst Handel Michael Suyama
First Prev 1 2 3 4 5 6 7 8 9 10 ... Next Last 

View:

        
@model PagedList<System.Data.DataRow>
<table class="table table-striped table-bordered">
    <tr>
        <th>Order Id</th>
        <th>Customer Id</th>
        <th>Company Name</th>
        <th>Employee Name</th>
    </tr>
    @foreach (var dr in Model)
    {
        <tr><td>@dr["OrderId"]</td><td>@dr["CustomerId"]</td><td>@dr["CompanyName"]</td>
    <td>@dr["EmployeeName"]</td></tr>
    }
</table>
@Html.Pager(Model,new PagerOptions{PageIndexParameterName = "id",PagerItemTemplate = "{0}&nbsp;"})

Controller:

        
        public ActionResult DataRows(int id=1)
        {
            var pageSize = 5;
            var startIndex = (id - 1) * pageSize + 1;
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString);
            SqlCommand cmd = new SqlCommand("USP_GetPagedOrders", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@startIndex",SqlDbType.Int).Value=startIndex;
            cmd.Parameters.Add("@endIndex",SqlDbType.Int).Value=startIndex + pageSize;
            SqlParameter prmTotal = new SqlParameter("@totalItems", SqlDbType.Int);
            prmTotal.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(prmTotal);
            SqlDataAdapter adapter=new SqlDataAdapter(cmd);
            DataTable tbl=new DataTable("Orders");
            adapter.Fill(tbl);
            int totalItems = (int) prmTotal.Value; 
            PagedList<DataRow> arts=new PagedList<DataRow>(tbl.Select(),id,pageSize,totalItems);
            return View(arts);
        }