Bind List Data to JQuery Data Table

Many a time we come across the situation to bind list collection to GridView or Data table. The below code snippet is used to bind list collection to a JQuery Data table.

 

If we used JQuery data table then we are able to search particular values in the table without any custom code. It also sorts the table based on column.

 

For this we need to add reference of js files in .ascx file. We need to download the below files and need to upload it into our style library and accordingly we need to add the reference as shown below. 

<link rel="stylesheet" type="text/css" href="/DatatableAssets/css/jquery.dataTables.css">
<script type="text/javascript" src="/DatatableAssets/js/jquery.js"></script>
<script type="text/javascript" src="/DatatableAssets/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="/DatatableAssets/js/dataTables.tableTools.js"></script>

Then we need to add table as shown below:

<table id="tblGroup" class="display cell-border" width="100%" border="0" cellspacing="0" cellpadding="0">
    <thead width="100%">
        <tr>
            <th>ID</th>
            <th>Group Name</th>
        </tr>
    </thead>
    <tbody>
        <asp:Repeater runat="server" ID="repGroupDetails">
            <ItemTemplate>
                <tr>
                    <td>
                        <%# DataBinder.Eval(Container.DataItem, "ID")%>
                    </td>
                    <td>
                        <%# DataBinder.Eval(Container.DataItem, "Title")%>
                    </td>
                </tr>
            </ItemTemplate>
        </asp:Repeater>
    </tbody>
</table>

To bind above table with JQuery table we need to add the below script.

<script>
    $(document).ready(function()
    {
        $('#tblGroup').dataTable
        ({
            "scrollY": "325px",
            "scrollCollapse": true,
            "paging": false
        });
    });
</script>

First we need to get data from SharePoint list. Add below list of code into .cs file. 

protected void Page_Load(object sender, EventArgs e)
{
    GetGroupDetails();
}


public void GetGroupDetails()
{
    try
    {
        string cQuery = "";
        string cViewFields = "<FieldRef Name='ID'/><FieldRef Name='Title'/><FieldRef Name='Created'/>";
        cQuery = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
        SPListItemCollection itemColl = GetListItems(SPContext.Current.Site.Url, "ListName", cQuery, cViewFields);
        if (itemColl.Count != 0)
        {
            dtItemColl = itemColl.GetDataTable();
            dtCP = dtItemColl.Clone();
            foreach(SPListItem item in itemColl)
            {
                DataRow drCP = dtCP.NewRow();
                drCP["ID"] = item["ID"];
                drCP["Title"] = item["Title"];
                dtCP.Rows.Add(drCP);
            }
        }
        if ((dtCP != null) || (dtCP.Rows.Count != 0))
        {
            repGroupDetails.DataSource = dtCP;
            repGroupDetails.DataBind();
        }
    } catch (Exception ex) {}
}


public SPListItemCollection GetListItems(string sWebUrl, string sListName, string sQuery, string sViewFields)
{
    SPListItemCollection objListItemCollection = null;
    SPSecurity.RunWithElevatedPrivileges(delegate()
    {
        using(SPSite objSite = new SPSite(sWebUrl))
        {
            using(SPWeb objWeb = objSite.OpenWeb())
            {
                SPList objList = objWeb.Lists.TryGetList(sListName);
                if (objList != null)
                {
                    SPQuery objSPQuery = new SPQuery();
                    objSPQuery.Query = sQuery;
                    objSPQuery.ViewFields = sViewFields;
                    objListItemCollection = objList.GetItems(objSPQuery);
                }
            }
        }
    });
    return objListItemCollection;
}

Build and deploy the web part. Create one web part page and add the above web part and it will show you the result.