How to get data from database using Repeater control

See more:

C#

ASP.NET

repeater

I need to use the jquery datatable server-side processing (http://datatables.net) for my asp.net (C#) Web-Site.

My Web-Site has thousands of records to show in the table as list. I am using jquery datatable server-side to enable paging, filtering and search.

In below code i get data from database using simple table and also i using server side data-table, but how to get data using repeater control and also using server side data-table.

So, how to implement repeater control in this code?

data.ashx file code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Configuration;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;

namespace example
{
/// <summary>
/// Summary description for Data
/// </summary>
public class Data : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        // Those parameters are sent by the plugin
        var iDisplayLength = int.Parse(context.Request["iDisplayLength"]);
        var iDisplayStart = int.Parse(context.Request["iDisplayStart"]);
        var iSortCol = int.Parse(context.Request["iSortCol_0"]);
        var iSortDir = context.Request["sSortDir_0"];
        var sSearch = context.Request["sSearch"];

        // Fetch the data from a repository (in my case in-memory)
        var persons = Person.GetPersons();

        // Define an order function based on the iSortCol parameter
        Func<Person, object> order = p =>
        {
            if (iSortCol == 0)
            {
                return p.Id;
            }
            return p.UserName;
        };            

        // prepare an anonymous object for JSON serialization
        var result = new
        {
            iTotalRecords = persons.Count(),
            iTotalDisplayRecords = persons.Count(),
            aaData = persons
                .Where(p => p.UserName.Contains(sSearch))  
                .Select(p => new[] { p.Id, p.UserName, p.Address, p.Time, p.Status })
                .Skip(iDisplayStart)
                .Take(iDisplayLength)
        };

        var serializer = new JavaScriptSerializer();
        var json = serializer.Serialize(result);
        context.Response.ContentType = "application/json";
        context.Response.Write(json);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

public class Person
{
    public string Id { get; set; }
    public string UserName { get; set; }
    public string Address { get; set; }
    public string Time { get; set; }
    public string Status { get; set; }        

    [WebMethod]
    public static List<Person> GetPersons()
    {
        List<Person> productt = new List<Person> { };
        string query = "SELECT [Id] ,[UserName] ,[Address] ,[Time] ,[Status] FROM [Ajax]";
        SqlCommand cmd = new SqlCommand(query);
        DataSet ds = GetData(cmd);
        DataTable dt = ds.Tables[0];
        foreach (DataRow item in ds.Tables[0].Rows)
        {
            Person pro = new Person();
            pro.Id = item["Id"].ToString();
            pro.UserName = item["UserName"].ToString();
            pro.Address = item["Address"].ToString();
            pro.Time = item["Time"].ToString();
            pro.Status = item["Status"].ToString();
            productt.Add(pro);
        }

        return productt;
    }

    private static DataSet GetData(SqlCommand cmd)
    {            
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    return ds;
                }
            }
        }
    } 
}    
}

aspx file code:

<table class="table table-stoured table-bordered" id="tab"> 
        <thead> 
        <tr> 
            <th>EmployeeId</th> 
            <th>UserName</th> 
            <th>Address</th> 
            <th>LocationTime</th> 
            <th>DeviceStatus</th> 
        </tr> 
        </thead> 

    </table>

script code:

<script type="text/javascript">
      $(function () {
          $('#tab').dataTable({
              'bProcessing': true,
              'bServerSide': true,
              'sAjaxSource': '/data.ashx'
          });
      });
</script>