Monday, August 27, 2012

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;
        }

Monday, August 6, 2012

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>

Thursday, August 2, 2012

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

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