Best Practice Alternative to overcome List view lookup threshold limitations

Recently, once again i have come across the List view lookup threshold limitation while designing a custom webpart. I can not use more than 8 lookup fields(it includes user fields too) in a single list view or while fetching the list data using SPQuery.

There is configuration in Central adminstration however as per Best practice it is not at all advisable. It hamper the Server cpu very badly. Please finds links below to know more about it.
http://sympmarc.com/2012/07/23/sharepoints-list-view-lookup-threshold-and-why-we-dont-change-it/
http://technet.microsoft.com/en-us/library/cc262813.aspx

So there are few options available to overcome this.

Option 1 - Reduce number of lookup fields in the view. (This is not possible all the time as per requirement)
Option 2 - Increase List View Look up Threshold for that webapplication in central admin. (This is not advisable as it hampers Server CPU badly.)

So i have implemented following option. Option 3-

- While query list data split them in to two or more query groups such that you dont query more than 8 lookup fields in a single query.
- Get different data sets based on different SPQuery. Just make sure you have ID column in all the query groups.
- Merge them using LINQ

Please find the implementations as below -
- While query list data split them in to two or more query groups such that you dont query more than 8 lookup fields in a single query.
 protected void Page_Load(object sender, EventArgs e)
        {

               SPList splistObject = objSPWeb.Lists["ListName"];
                    SPQuery spqPart1 = new SPQuery();
                    spqPart1.ViewFields = @"<FieldRef Name='Title' />
   <FieldRef Name='LookupFieldName1' />
   <FieldRef Name='LookupFieldName2' />
   <FieldRef Name='LookupFieldName3' />
   <FieldRef Name='LookupFieldName4' />
   <FieldRef Name='LookupFieldName5' />
   <FieldRef Name='LookupFieldName6' />
   <FieldRef Name='LookupFieldName7' />
    <FieldRef Name='ID' />";
                    spqPart1.ViewFieldsOnly = true;

                    SPQuery spqPart2 = new SPQuery();
                    spqPart2.ViewFields = @"<FieldRef Name='LookupFieldName8' />
   <FieldRef Name='LookupFieldName9' />
   <FieldRef Name='LookupFieldName10' />
   <FieldRef Name='LookupFieldName11' />
   <FieldRef Name='LookupFieldName12' />
   <FieldRef Name='LookupFieldName13' />
   <FieldRef Name='ID' />";
                    spqPart2.ViewFieldsOnly = true;


                      SPListItemCollection splicPart1 = splistObject.GetItems(spqPart1);
                        DataTable dtListPart1 = splicPart1.GetDataTable();

                        SPListItemCollection splicPart2 = splistObject.GetItems(spqPart2);
                        DataTable dtListPart2 = splicPart2.GetDataTable();

                        dtOppInfo = MergeDataTablesVertically(dtListPart1, dtListPart2);

}

- Get different data sets based on different SPQuery. Just make sure you have ID column in all the query groups.

- Merge them using LINQ
public DataTable MergeDataTablesVertically(DataTable table1, DataTable table2)
        {
            DataTable retTable = new DataTable();
            var dtMergeVertically =
    from part1 in table1.AsEnumerable()
    join part2 in table2.AsEnumerable() on part1.Field<Int32>("ID") equals part2.Field<Int32>("ID")
    select new
   {
       Title = part1.Field<String>("Title"),
       LookupFieldName1 = part1.Field<String>("LookupFieldName1"),
       LookupFieldName2 = part1.Field<String>("LookupFieldName2"),
       LookupFieldName3 = part1.Field<String>("LookupFieldName3"),
       LookupFieldName4 = part1.Field<String>("LookupFieldName4"),
       LookupFieldName5 = part1.Field<String>("LookupFieldName5"),
       LookupFieldName6 = part1.Field<String>("LookupFieldName6"),
       LookupFieldName7 = part1.Field<String>("LookupFieldName7"),
       ID = part1.Field<Int32>("ID"),


       LookupFieldName8 = part2.Field<String>("LookupFieldName8"),
       LookupFieldName9= part2.Field<String>("LookupFieldName9"),
       LookupFieldName10= part2.Field<String>("LookupFieldName10"),
       LookupFieldName11= part2.Field<String>("LookupFieldName11"),
       LookupFieldName12= part2.Field<String>("LookupFieldName12"),
       LookupFieldName13= part2.Field<String>("LookupFieldName13"),
   };
            retTable = ConvertToDataTable(dtMergeVertically);
            return retTable;

        }


        public DataTable ConvertToDataTable<T>(IEnumerable<T> varlist)
        {
            DataTable dtReturn = new DataTable();

            // column names
            PropertyInfo[] oProps = null;

            if (varlist == null) return dtReturn;

            foreach (T rec in varlist)
            {
                // Use reflection to get property names, to create table, Only first time, others will follow
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType;

                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }

                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }

                DataRow dr = dtReturn.NewRow();

                foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                    (rec, null);
                }

                dtReturn.Rows.Add(dr);
            }
            return dtReturn;
        }

SharePoint PDF Viewer Webpart

Simply use Content Editor Web part with HTML Embed tag and point it to your PDF.

Here is the handy code -

<embed allowtransparency="true" frameborder="no" scrolling="no" src="http://<DocLibPath>/PDFFile.pdf" style="height: 900px ! important; width: 100% ! important;"></embed>

Links

Time tools
http://www.calendarsquick.com/printables/download.html?action=customWeekly
http://www.vertex42.com/calendars/weekly-planner.html

SharePoint Shortcuts
http://www.heathersolomon.com/blog/articles/1116.aspx

Claims based Authentication
http://blog.slalom.com/2011/11/09/sharepoint-2010-fba-and-claims

Create site Provision using Sandboxed Web Template
http://sergeluca.wordpress.com/2011/05/31/deploying-site-definitions-to-a-sandbox-use-the-new-webtemplate-tag/

Word Automation Services and OpenXML- Imp Links
http://msdn.microsoft.com/en-us/library/ff742315%28v=office.14%29.aspx
http://technet.microsoft.com/en-us/library/hh202032%28d=printer,v=office.14%29.aspx
http://krunaljani.blogspot.com/2012/01/sharepoint-2010-limits-and-boundaries.html

**Tabify SharePoint Webparts**
1. http://usermanagedsolutions.com/SharePoint-User-Toolkit/Pages/Easy-Tabs-v5.aspx
2. http://kyleschaeffer.com/sharepoint/wp-tabify/

***Get Files From Document library using Silver Light Object Model***
http://www.aboutsharepoint2010.com/2012/03/29/how-to-get-files-from-document-library-silverlight-client-object-model

Working with SharePoint 2010 Projects on Visual Studio Without Installing The Server

http://blog.raminassar.com/2011/10/14/working-with-sharepoint-2010-projects-on-visual-studio-without-installing-the-server/

Remotly Working and Debugging SharePoint 2010 Solutions

http://blog.raminassar.com/2011/11/23/remotly-working-and-debugging-sharepoint-2010-solutions/

 Tool to copy SharePoint List Data to SQL table - SLAM
SLAM @ Codeplex.
http://slam.codeplex.com/wikipage?title=Getting%20Started&referringTitle=Home

Technical diagrams (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/cc263199.aspx

Connect Oracle Web Services from SharePoint
http://underthehood.ironworks.com/2010/01/why-doesnt-my-generated-proxy-class-build-wsse-elements-into-the-soap-request-header.html

PDF Printer
http://www.bullzip.com/products/pdf/info.php#download

List of SP webservices
http://www.infoq.com/articles/swanson-moss-web-services

SharePoint walkthrough-
http://sharepoint.wonderfulgroup.org/public/u/SharePoint%20Server%202010/Developer%20Files/SharePoint_2010_Developer_Walkthrough_Guide.pdf

http://blog.muhimbi.com/2010/03/porting-sharepoint-2007-wspbuilder.html

http://sureshannamalai.blogspot.com/2011/06/what-does-iisreset-really-do.html

SharePoint 2010/2013-
Alternative for CAML builder-
http://biwug-web.sharepoint.com/Pages/Caml_Designer.aspx

Tools:
Directory Printer to get all the folder path
http://www.karenware.com/powertools/ptdirprn.asp

CodePlex SharePoint Tools
1 . http://deletewssfiles.codeplex.com/documentation
- Deletes documents from within different site structure
- Ex.
Usage:
DeleteFiles.exe -url
[-recursive]
[-preview]
[-contains]
[-outfile ]
[-quiet]


-url The URL to process.
-recursive Will iterate all sub sites.
-preview Will do a preview instead of delete.
-contains File name contains value. (Remark: It's not a file mask, it's a string comparison with the 'containing' method)
-outfile Will write the result to a logfile.
-quiet Will silently answer Yes to all delete questions.

2. . http://tin.codeplex.com/
My Sites
- Bulk Create My Sites for All Users In Advance
- Bulk Delete All Personal Sites
- List all Current Personal Sites
User Profiles
- ToDo
Features/Solutions
- ToDo
Site and Site Collection Settings
- ToDo
and much, much more!...

3. Not codeplex but from Microsoft!!
http://www.microsoft.com/en-us/download/details.aspx?id=20022

4. Property Bag management
http://pbs2010.codeplex.com

POWERSHELL
1. Get size of content DB-
http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=193

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

Sandbox Solutions - Quicklist

After a long time, but yes.. i am back again!!

Sandbox solutions dll locations-
C:\ProgramData\Microsoft\SharePoint\UCCache
Service - Microsoft SharePoint Foundation Sandboxed Code Service
Validate Sandbox Code - http://visualstudiogallery.msdn.microsoft.com/8e602a8c-6714-4549-9e95-f3700344b0d9/

HOW TOs

How to Add/Change the domain for local machine.

1. Get the IP address of AD server
2. Set that IP in to Preferefered DNS server entry for the New VM/Server you are going to add on domain
3. Go to New VM/Server's properties and Change the domain name from existing WORKGROUP/Domain.



How to run queries in Oracle Using SQL*Plus

1. Install oracle client latest version from http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_client.zip

2. Please try to install maximum components you can while installation

3. Open SQL*Plus from Programs OR run SQLPLUS command from command prompt. See image below. Image given below is self describing further steps.

Connect Oracle with SharePoint/SQL using SSIS

Using the Microsoft Connector for Oracle by Attunity with SQL Server 2008 Integration Services

http://msdn.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

Microsoft Connectors for Oracle and Teradata by Attunity

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11180

Get Free Remote Server

http://www.cloudshare.com/

The trial period is of 15 days. However this is really good service.

Change Data Capture (CDC) in SQL Server 2008

http://www.mssqltips.com/sqlservertip/1474/using-change-data-capture-cdc-in-sql-server-2008/

SharePoint Newbies - List GUID, View GUID

To find list GUID -

- Go to “all sites content” - http://sitename/sites/webname/_layouts/viewlsts.aspx

- Click on any list (For ex. ListName) - http://sitename/sites/webname/Lists/ListName/AllItems.aspx

- Click on List Setting – As shown in Figure 1

- Page will be redirect to List Settings. Please see the URL there. You will find List GUID there – See Figure 2 and URL http://sitename/sites/webname/_layouts/listedit.aspx?List={2E9DD63B-0D31-4059-B5FE-AAA299E709F1}

To find View GUID –

- After getting List GUID in the url of setting page, just scroll down a bit on the same page. You should be able to find views section. (See Figure 3)

- Click on any of the view name you want to find GUID for. (See Figure 3)

- You will be redirect to “Edit View” page. Please check the URL on that page. It will look similar like as given below-

http://sitename/sites/webname/_layouts/ViewEdit.aspx?List=%7B2E9DD63B%2D0D31%2D4059%2DB5FE%2DAAA299E709F1%7D&View=%7B49311C81%2D0868%2D4638%2D892D%2DDE2AA53A7411%7D

- In above url, the string after text “View=” is view GUID. - %7B49311C81%2D0868%2D4638%2D892D%2DDE2AA53A7411%7D

- Here view GUID is encoded url. To decode, just replace “%7B” with “{“ ,“%7D” with “}” and replace all “%2D” with “–“).

- You will get GUID looks like- {49311C81-0868-4638-892D-DE2AA53A7411}. This is View GUID

Tool to copy SharePoint List Data to SQL table - SLAM

SLAM @ Codeplex.

http://slam.codeplex.com/wikipage?title=Getting%20Started&referringTitle=Home

Connect Oracle Web Services from SharePoint

http://underthehood.ironworks.com/2010/01/why-doesnt-my-generated-proxy-class-build-wsse-elements-into-the-soap-request-header.html

List of SP webservices

http://www.infoq.com/articles/swanson-moss-web-services

SharePoint Excel Web Service

Excel Web Service

Get Workbook using excel web service –

http://msdn.microsoft.com/en-us/library/ms519570(v=office.12).aspx

Save Workbook –

http://msdn.microsoft.com/en-us/library/ms575918(v=office.12).aspx

Use it as required in SSRS/SSIS

SharePoint Excel web Service URL - http://SiteURL/sites/WEBName/_vti_bin/excelservice.asmx

SharePoint + Android

Experience The Creativity

Java/PHP - Integration With SharePoint

David's IT Blog

JQuery : Hide SharePoint Field with Specific Comments -

<script type="text/javascript" src="_layouts/Server/jQuery/jquery-1.4.2.js"></script>

<script type="text/javascript" src="_layouts/Server/jQuery/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="_layouts/Server/jQuery/jquery.SPServices-0.5.8.min.js"></script>

<script type="text/javascript" language="javascript" >


$(document).ready(function(){

$("td.ms-formbody").each(function () {
if (this.innerText.indexOf("For Admins Only") > 0) {

$(this).parent().hide();
}
else
{

}
});

});

</script>

SharePoint LogViewer - View ULS logs the easy and efficient way

SharePoint LogViewer - View ULS logs the easy and efficient way

Code to Create SharePoint views Programatically using SP WebService

public void CreateView(string strCategory)
{
WRCreateView.Views objCreateViews = new CreateViews.WRCreateView.Views();
objCreateViews.Url = "http://Sitename.net/sites/SiteCol/_vti_bin/views.asmx";

NetworkCredential netcred = new NetworkCredential("MyUserName", "MyPassword", "MyDomain");

objCreateViews.Credentials = netcred;

string strQuery = " " + strCategory + "";

string strRowLimit = "100";

string strViewFields = "";



System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();

System.Xml.XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
System.Xml.XmlNode ndRowLimit = xmlDoc.CreateNode(XmlNodeType.Element, "RowLimit", "");
System.Xml.XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");

ndQuery.InnerXml = strQuery;
ndRowLimit.InnerXml = strRowLimit;
ndViewFields.InnerXml = strViewFields;

XmlNode retNode = objCreateViews.AddView("{794D4A54-960D-2322-1234-B6A672927FB7}", strCategory, ndViewFields, ndQuery, ndRowLimit, "HTML", false);

//XmlNode xmln = objCreateViews.GetView("{794D4A54-960D-47FA-1234-B6A672927FB7}", "{44F80DED-1235-485B-9B47-3EB2FDD797B1}");
}

Programmatically start workflow

Steven Van de Craen's Blog - Programmatically start workflow: "Guid wfBaseId = new Guid('{6BE0ED92-BB12-4F8F-9687-E12DC927E4AD}');
SPSite site = ...;
SPWeb web = site.OpenWeb();
SPList list = web.Lists['listname'];
SPListItem item = list.Items[0];
SPWorkflowAssociation associationTemplate= list.WorkflowAssociations.GetAssociationByBaseID(wfBaseId);
site.WorkflowManager.StartWorkflow(item, associationTemplate, '');"

Round Pic

Round pic

Test WebService

http://webservicestudio.codeplex.com/

Adjust column size

.ms-vh2 div table tr td
{
white-space: normal
}

HTML TABLE SORT

http://tablesorter.com/docs/

SharePoint - Hide User field in Edit form using JQuery

$("nobr:contains('User')").parent().parent().parent().hide();