Tuesday, July 17, 2012

Using JQuery Data Table with SharePoint Visual Web Part, SPSiteDataQuery with ContentType in Sandbox solution


1. Download Jquery Data Table files and copy following files to Layouts folder
Datatables/js/jquery.js
Datatables/js/jquery.dataTables.js
Datatables/css/demo_table.css

2. Create visual webpart. Sandbox solutions will be good here too

3. Sandbox Visual Webpart - VisualWP.ascx

<link rel="stylesheet" href="/_layouts/Datatables/css/demo_table.css" type="text/css" />
<script type="text/javascript" src="/_layouts/Datatables/js/jquery.js"></script>
<script type="text/javascript" src="/_layouts/Datatables/js/jquery.dataTables.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('#tblRepeater1').dataTable({ "oLanguage": { "sSearch": "Search the text:" },
            "iDisplayLength": 10,
            "aaSorting": [[0, "asc"]]
        });
    });
</script>
<asp:Repeater ID="rptRepeater1" runat="server">
    <HeaderTemplate>
        <table id="tblRepeater1" cellpadding="0" cellspacing="0" border="0" class="display">
            <thead>
                <tr>
                    <th>
                        Title
                    </th>
                    <th>
                        TextField1
                    </th>
                    <th>
                        TextField2
                    </th>
                    <th>
                        TextField3
                    </th>
                </tr>
            </thead>
            <tbody>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td>
                <%# Eval("Title")%>
            </td>
            <td>
                <%# Eval("TextField1")%>
            </td>
            <td>
                <%# Eval("TextField2")%>
            </td>
            <td>
                <%# Eval("TextField3")%>
            </td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
        </tbody> </table>
    </FooterTemplate>
</asp:Repeater>


4. Sandbox Visual Webpart - VisualWP.ascx.cs
SPWeb spWebMain = SPContext.Current.Web;
            SPSiteDataQuery spDataQry = new SPSiteDataQuery();

            //Querying all Task Lists from the site colletion

            string striewFields = "<FieldRef Name='Title' Type='Text'/>";
            striewFields += "<FieldRef Name='TextField1' Type='Text' Nullable='TRUE' />";
            striewFields += "<FieldRef Name='TextField2' Type='Text' Nullable='TRUE' />";
            striewFields += "<FieldRef Name='TextField3' Type='Text' Nullable='TRUE' />";
            spDataQry.ViewFields = striewFields;

            SPContentType cTypeCollection = spweb.ContentTypes["MyContentType"];
            string where = string.Format(
                  @"<Where><BeginsWith><FieldRef Name='ContentTypeId'/><Value Type='Text'>{0}</Value></BeginsWith></Where>", cTypeCollection.Id);


            spDataQry.Webs = "<Webs Scope=\"Recursive\">";
            spDataQry.Query = where;

            DataTable dtTable = spWebMain.GetSiteData(spDataQry);
            rptRepeater1.DataSource = dtTable;
            rptRepeater1.DataBind();


IMPORTANT NOTE-

SPSiteDataQuery does not support Multi lookup and Multi user fields while retrieving data. Given below are list of filled NOT supported.


- LookupMulti
- UserMulti
- TaxonomyFieldTypeMulti


Please find more information here from Microsoft KB.

http://support.microsoft.com/kb/2703054