Using the SharePoint Search API to Iterate Through SharePoint List Items

Recently, I took a SharePoint developer boot camp that prepared us to take the MCTS 50-753 and the MCPD 70-576 exams.  The class was very thorough with great lecture and plenty of labs to help solidify our understanding of SharePoint 2010 development.  During the class, the instructor brought up some great points about using the SharePoint Object Model and the iteration of items within a list.  The long and the short of the point had to do with the creation of objects and the processor load.  (I asked the instructor for a brief review of the reasons and I’m sure he’ll get back to me soon so I can update this post.)

One of the options to bypass using the SP Object Model was to use the SP Search API and the Search index as a means of producing the same desired results.  Hearing this, I wanted to produce a POC to prove out this idea.  Honestly, what I wanted to accomplish was to see if I could duplicate the same visual web part using two different methods – one with the SP Object Model and one with the Search API.  The second part of this exercise was to see if I could find a difference in rendering the two web parts as far as speed of data retrieval and processor load.  This post deals with the first task.

My environment is a very vanilla SharePoint Enterprise VM with Visual Studio 2010 Professional.  The Enterprise Search Service is configured and running, containing one content source – the out of the box Local Sites content source.  The taxonomy of the environment being used in this exercise is simple – there is one site collection with one sub web under the root web called Portal Team Sub-Site (url – http://sp2010/prtlteamsub/)

The steps, in order, of what I did are as follows:

• Create a custom content type called TestDoc based upon the Document system content type.
• Create 3 custom Site Columns:
  

  Column Name	Data Type
  DocCategory	Choice (Report, Summation, Detail, Other)
  Purpose	Single Line of Text
  RelevantDept	Choice (HR, Finance, Marketing, Sales, Executive)

• Create a document library called Company Docs.  Add the TestDoc content type to the document library and delete the default Document content type.  This library should now have only one content type associated with it – TestDoc.
• Upload 5 documents to the library and set the metadata values of the documents to whatever you like.
• Open Central Admin, navigate to the Search Service application.  Create 3 managed properties based upon the three custom site columns created in step 2 – DocCategory, Purpose and RelevantDept.
• With CA still open, create a custom scope called PortalTeamSiteScope.  Create a rule for the scope with the following settings: Scope Rule Type = Web Address (http://server/site), Web Address = Folder – http://sp2010/prtlteamsub, Behavior = Include – Any item that matches this rule will be included…..
• Go back to the search admin screen and run a full crawl on the content source.  After the crawl is complete, you should be all set in querying the index for these items.
• Open Visual Studio 2010.  Create a new project with the Empty SharePoint Project template.  Name it whatever you like.  Use the sub site you created for the debugging site and create the project as a farm solution.
• Create a new class called DocItem, adding it to the project.  Add the following code to the file, between the two class brackets:

 1 private string _Name;
 2         private string _DocCat;
 3         private string _Purpose;
 4         private string _RelDept;
 5 
 6         public string Name
 7         {
 8             get
 9             {
10                 return _Name;
11             }
12             set
13             {
14                 _Name = value;
15             }
16         }
17 
18         public string DocCat
19         {
20             get
21             {
22                 return _DocCat;
23             }
24             set
25             {
26                 _DocCat = value;
27             }
28         }
29 
30         public string Purpose
31         {
32             get
33             {
34                 return _Purpose;
35             }
36             set
37             {
38                 _Purpose = value;
39             }
40         }
41 
42         public string RelDept
43         {
44             get
45             {
46                 return _RelDept;
47             }
48             set
49             {
50                 _RelDept = value;
51             }
52         }

• Add a new Visual Web Part to the project.  Name it StandardOMRetrieval.  This web part will use the object model to pull back the list items into a table.
• Place the following code in the StandardOMRetrievalUserControl:

  1 <h2>Standard OM Retrieval</h2>
  2 <asp:Label ID="lblError" 
  3     runat="server" 
  4     ForeColor="Red" 
  5     Visible="false" />
  6 <asp:Table ID="dynTable" 
  7     runat="server" 
  8     GridLines="Both">
  9 </asp:Table>

• Open the StandardOMRetrievalUserControl.ascx.cs file.  Add the Microsoft.SharePoint using statement to the top of the code file, below the rest of the using statements.  Copy in the following code, replacing all the code between the class brackets:

   1 List<DocItem> docItems;
   2 
   3         protected void Page_Load(object sender, EventArgs e)
   4         {
   5             try
   6             {
   7                 TableCell cell = new TableCell();
   8                 TableRow row = new TableRow();
   9                 cell.Text = "Doc Title";
  10                 cell.Font.Bold = true;
  11                 row.Cells.Add(cell);
  12                 cell = new TableCell();
  13                 cell.Text = "Doc Cat";
  14                 cell.Font.Bold = true;
  15                 row.Cells.Add(cell);
  16                 cell = new TableCell();
  17                 cell.Text = "Purpose";
  18                 cell.Font.Bold = true;
  19                 row.Cells.Add(cell);
  20                 cell = new TableCell();
  21                 cell.Text = "Relevant Dept";
  22                 cell.Font.Bold = true;
  23                 row.Cells.Add(cell);
  24                 dynTable.Rows.Add(row);
  25 
  26                 GetDocItems();
  27                 if (docItems.Count > 1)
  28                 {
  29                     foreach (DocItem di in docItems)
  30                     {
  31                         row = new TableRow();
  32                         cell = new TableCell();
  33                         cell.Text = di.Name;
  34                         row.Cells.Add(cell);
  35                         cell = new TableCell();
  36                         cell.Text = di.DocCat;
  37                         row.Cells.Add(cell);
  38                         cell = new TableCell();
  39                         cell.Text = di.Purpose;
  40                         row.Cells.Add(cell);
  41                         cell = new TableCell();
  42                         cell.Text = di.RelDept;
  43                         row.Cells.Add(cell);
  44                         dynTable.Rows.Add(row);
  45                     }
  46                 }
  47             }
  48             catch (Exception ex)
  49             {
  50                 lblError.Text = ex.Message;
  51                 lblError.Visible = true;
  52             }
  53         }
  54 
  55         void GetDocItems()
  56         {
  57             docItems = new List<DocItem>();
  58             try
  59             {
  60                 using (SPSite site = new SPSite("http://sp2010/"))
  61                 {
  62                     using (SPWeb web = site.AllWebs["prtlteamsub"])
  63                     {
  64                         SPList list = 
  65                             web.Lists["Company Docs"];
  66                         SPListItemCollection col = list.Items;
  67                         foreach (SPListItem item in col)
  68                         {
  69                             DocItem i = new DocItem();
  70                             i.Name = item["Name"].ToString();
  71                             i.DocCat = 
  72                                     item["DocCategory"].ToString();
  73                             i.Purpose = 
  74                                     item["Purpose"].ToString();
  75                             i.RelDept = 
  76                                     item["RelevantDept"].ToString();
  77                             docItems.Add(i);
  78                         }
  79                     }
  80                 }
  81 
  82             }
  83             catch (Exception ex)
  84             {
  85                 throw ex;
  86             }
  87         }

• Rename the Feature1 created with the project to whatever you like.
• Add a new Visual Web Part to the project and name it SearchAPIRetrieval.
• Add the following code to the SearchAPIRetrievalUserControl.ascx file:

  1 <h2>Search API Retrieval</h2>
  2 <asp:Label ID="lblError" 
  3                runat="server" 
  4                ForeColor="Red" 
  5                Visible="false" />
  6 <asp:Table ID="dynTable" 
  7             runat="server" 
  8             GridLines="Both">
  9 </asp:Table>

• Add the Microsoft.Office.Server.Search.Query (available in the ISAPI folder in the 14 folder) using statement and the Microsoft.SharePoint using statement to the top of the SearchAPIRetrievalUserControl.ascx.cs code file.  Then, add the following code, replacing all code between the class brackets:

   1 List<DocItem> docItems;
   2 
   3         protected void Page_Load(object sender, EventArgs e)
   4         {
   5             try
   6             {
   7                 TableCell cell = new TableCell();
   8                 TableRow row = new TableRow();
   9                 cell.Text = "Doc Title";
  10                 cell.Font.Bold = true;
  11                 row.Cells.Add(cell);
  12                 cell = new TableCell();
  13                 cell.Text = "Doc Cat";
  14                 cell.Font.Bold = true;
  15                 row.Cells.Add(cell);
  16                 cell = new TableCell();
  17                 cell.Text = "Purpose";
  18                 cell.Font.Bold = true;
  19                 row.Cells.Add(cell);
  20                 cell = new TableCell();
  21                 cell.Text = "Relevant Dept";
  22                 cell.Font.Bold = true;
  23                 row.Cells.Add(cell);
  24                 dynTable.Rows.Add(row);
  25 
  26                 GetDocItems();
  27                 if (docItems.Count > 1)
  28                 {
  29                     foreach (DocItem di in docItems)
  30                     {
  31                         row = new TableRow();
  32                         cell = new TableCell();
  33                         cell.Text = di.Name;
  34                         row.Cells.Add(cell);
  35                         cell = new TableCell();
  36                         cell.Text = di.DocCat;
  37                         row.Cells.Add(cell);
  38                         cell = new TableCell();
  39                         cell.Text = di.Purpose;
  40                         row.Cells.Add(cell);
  41                         cell = new TableCell();
  42                         cell.Text = di.RelDept;
  43                         row.Cells.Add(cell);
  44                         dynTable.Rows.Add(row);
  45                     }
  46                 }
  47             }
  48             catch (Exception ex)
  49             {
  50                 lblError.Text = ex.Message;
  51                 lblError.Visible = true;
  52             }
  53         }
  54 
  55         void GetDocItems()
  56         {
  57             docItems = new List<DocItem>();
  58             try
  59             {
  60                 var query = new FullTextSqlQuery(SPContext.Current.Site)
  61                 {
  62                     QueryText = "SELECT title, DocCategory, Purpose1, RelevantDept FROM SCOPE() WHERE \"scope\" = 'PortalTeamSiteScope' AND CONTAINS(Title, 'Doc')",
  63                     ResultTypes = ResultType.RelevantResults
  64                 };
  65                 ResultTableCollection queryResults = query.Execute();
  66                 ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];
  67                 var results = new DataTable();
  68                 results.Load(queryResultsTable, LoadOption.OverwriteChanges);
  69                 foreach (DataRow row in results.Rows)
  70                 {
  71                     DocItem di = new DocItem
  72                     {
  73                         Name = row[0].ToString(),
  74                         DocCat = row[1].ToString(),
  75                         Purpose = row[2].ToString(),
  76                         RelDept = row[3].ToString()
  77                     };
  78                     docItems.Add(di);
  79                 }
  80             }
  81             catch (Exception ex)
  82             {
  83                 throw ex;
  84             }
  85         }

• Create a new feature named whatever you like.  Add the SearchAPIRetrieval web part to the feature. Open the other feature in the project and remove the SearchAPIRetrieval web part from it.  Now, you should have one feature with the StandardOMRetrieval web part and one feature with the SearchAPIRetrieval web part.
• Deploy the project and add both web parts (located in the Custom group) to a page within the sub site.

The end result should be what you see below (or something similar):

In the code, you’ll notice I used the FullTextSqlQuery method from the search API to query the Search index and display the properties needed.  To do this, I had to make each of those properties a managed property so I could select it within the query.  The Search web part doesn’t create any SharePoint objects when executing – all it does is query the existing Search index.  This means you don’t get any of the overhead of using the SharePoint objects within the object model.  On large lists, this speeds up the rendering of the results as well as decreases the pressure on the CPU. 

In this post, I outlined the steps I took to create two web parts – one that pulls back document metadata using the SharePoint Object Model, the other that uses the Search API to query the Search index for the same metadata.  I hope I covered everything I did – I kind of hurried through this post to get it done before the kids woke up.   If you have any questions or issues, please let me know and I’ll try and clarify what I did.  I hope you find it useful.

Deleting Content Types in SharePoint 2010

While developing some content types and site columns for a client, I had the issue of being unable to delete the content types after deleting any libraries and documents that were using them.  The issue turned out to be the second recycle bin that lives at the site collection level.  The process that finally worked is the following:

1. Delete all items using the content type
2. Go to the site level recycle bin (typically accessible from the link toward the bottom of the left navigation bar) and delete items from there.
3. Go to site collection recycle bin (accessible from the Site Collection settings

When these steps were completed, I was able to delete the content types and site columns successfully.