Querying SharePoint 2010 Lists to build SharePoint UI

1. Greg Galipeau Enterprise Architect Department of Treasury Querying SharePoint 2010 Lists to build SharePoint UI

2. Overview SharePoint provides many ways to query data. Some ways are for external applications (web services, rss, REST, etc…). However, some ways are for querying within SharePoint. You can create dynamic user interfaces by storing configurations and data within SharePoint lists and using these techniques to query that data.

3. Lots of Options! • Develop • Server-Side • CAML • LINQ • Client-Side • Client Side Object Model • Out of the Box • Content Query Web Part

4. The basics – SPsitevsSPWeb • SPSite = site collection • SPWeb = sub-web • SPSite.RootWeb = root sub-web

5. The basics - Get Web Context SPContext.Current.Site vs new SPSite(“http://sharepoint.com”) • Context • Understands where you are at • Disposes itself • new SPSite • Use when opening site in another area • You must dispose!

6. Examples The following slides show many techniques for querying lists. All the slides assume a couple things: • A list called Announcements exists at the web that the webpart is placed on • The Annoucements list has a title, body and show column

7. Querying the List //get the list SPListmylist = SPContext.Current.Web.Lists["Announcements"]; //loop through and get the values List<Announce> announcements = newList<Announce>(); foreach (SPListItem item inmylist.Items) { if (Convert.ToBoolean(item["Show"]) == true) { announcements.Add( newAnnounce {Title = item["Title"] asstring, Body = item["Body"] asstring, Show = Convert.ToBoolean(item["Show"]) }); } }

8. CAML • XMl defined querying language specific to SharePoint • U2U CAML builder is a good place to start learning • Same technique as SharePoint 2007 used with SharePoint 2010

9. CAML Example SPListmylist = SPContext.Current.Web.Lists["Announcements"]; SPQueryquery = newSPQuery(); query.Query= string.Concat( "<Where><Eq><FieldRef Name='Show'/><Value", "Type='bit'>1</Value></Eq></Where>"); query.ViewFields= string.Concat( "<FieldRefName='Title' />", "<FieldRef Name='Body' />", "<FieldRef Name='Show' />"); query.ViewFieldsOnly= true; //only query the viewfields SPListItemCollectionitems = mylist.GetItems(query);

10. LINQ • LINQ = Language Integrated Query • Reference Microsoft.SharePoint.LINQ.dll from the 14 hive ISAPI folder • Technically LINQ for SharePoint just translates query into CAML

11. LINQ SPMetal • SPMetal is a command line tool that generates the object model for a SharePoint site • Can be found at: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN • Ex: spmetal.exe /web:http://win2008r2 /code:"C:\LINQ.cs"

12. LINQ Example // Get DataContext from page context LINQDataContextdataContext = newLINQDataContext(SPContext.Current.Web.Url); // Query varquery = from a indataContext.Announcements orderbya.Title wherea.Show == true selectnew { title = a.Title, body = a.Body, show = a.Show };

13. Client Side Object Model - Server • The client object model can be used on the server side • This technique is usually needed for external applications like Silverlight • Reference Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.ClientRuntime.dll from the 14 hive ISAPI folder

14. Client Side Object Model – Server Example ClientContextclientContext = newClientContext(SPContext.Current.Web.Url); Webweb = clientContext.Web; Listlist = web.Lists.GetByTitle("Announcements"); CamlQuerycamlQuery = newCamlQuery(); camlQuery.ViewXml= @"<View> <Query> <Where> <Eq> <FieldRef Name='Show'/> <Value Type='bit'>1</Value> </Eq> </Where> </Query> </View>"; Microsoft.SharePoint.Client.ListItemCollectionlistItems = list.GetItems(camlQuery); clientContext.Load( listItems, items => items .Include( item => item["Title"], item => item["Body"], item => item["Show"])); clientContext.ExecuteQuery();

15. Client Side Object Model - javascript • The client object model can be used on the client side with javascript • This technique is useful for no code queries • The following tag must be on your page for this to work: <SharePoint:ScriptLink Name="SP.js" runat="server" OnDemand="true" Localizable="false" />

16. Client Side Object Model - javascript • Many ways to incorporate javascript into a SharePoint page • Page Layout • Application Pages • Custom WebParts • Etc… • Easy way – Save javascript in txt file, upload file to SharePoint library, use the content editor webpart to point to txt file

17. Client Side Object Model – javascript example <div id="demoHtmlArea"></div> <script type="text/javascript"> ExecuteOrDelayUntilScriptLoaded(getTheList, "sp.js"); function getTheList() { // Get the current client context to start. Most things run from the client context. clientContext = new SP.ClientContext.get_current(); varmyList = clientContext.get_web().get_lists().getByTitle('Announcements'); // use a standard syntax CAML query to filter your results and the fields returned if required var query = new SP.CamlQuery(); varstrQuery = "<View><Query><Where><Eq><FieldRef Name='Show'/><Value Type='bit'>1</Value></Eq></Where></Query></View>"; query.ViewXml = strQuery; // add your query to the getItems command for your list this.collListItems = myList.getItems(query); // issue the load command, these can be batched for multiple operations clientContext.load(collListItems); // execute the actual query against the server, and pass the objects we have created to either a success or failure function clientContext.executeQueryAsync(Function.createDelegate(this, this.mySuccessFunction), Function.createDelegate(this, this.myFailFunction)); }

18. Client Side Object Model – javascript example function mySuccessFunction(sender, args) { // Do something useful like loop through your returned list items and output them somewhere // create an enumerator to loop through the list with varlistItemEnumerator = this.collListItems.getEnumerator(); varhtmlTable = "<table border='1'><tr><th>Title</th><th>Body</th><th>Show</th></tr>"; // loop through the list items while (listItemEnumerator.moveNext()) { varoListItem = listItemEnumerator.get_current(); var Title = oListItem.get_item('Title'); var Body = oListItem.get_item('Body'); var Show = oListItem.get_item('Show'); if(Show) htmlTable+= "<tr><td>" + Title + "</td><td>" + Body + "</td><td>" + Show + "</td></tr>"; } htmlTable += "</table>"; $("#demoHtmlArea").append(htmlTable); //jquery to insert into the html (regular js can be used too) } function myFailFunction(sender, args) { // do something to alert the user as to the error here. } </script>

19. Content Query WebPart • Out of the box WebPart with SharePoint • Multiple configurations to accomplish the Goal of Querying

20. Content Query WebPart Query and Filter

21. Content Query WebPart Group and Sort Use Predefined Styles and set fields

22. Content Query WebPart • Custom XSLT • Found in Style Library – XSL Style Sheets – ItemStyle.xsl • Add xslt node to create new item style • HeaderStyle.xsl contains header styles

23. Content Query WebPart example <xsl:template name="CustomDemo" match="Row[@Style='CustomDemo']" mode="itemstyle"> <xsl:variable name="SafeLinkUrl"> <xsl:call-template name="OuterTemplate.GetSafeLink"> <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/> </xsl:call-template> </xsl:variable> <xsl:variable name="DisplayTitle"> <xsl:call-template name="OuterTemplate.GetTitle"> <xsl:with-param name="Title" select="@Title"/> <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/> </xsl:call-template> </xsl:variable> <div > <xsl:value-of select="$DisplayTitle"/> -- <xsl:value-of select="@Description" /> -- <xsl:value-of select="@Show" /> </div> </xsl:template>

24. Custom Content Query Webpart • Sometimes you need to build a production ready, reusable content query webpart with the following • Custom xsl • Custom header xsl • Custom item xsl • Deployable – through SharePoint solution • Feature Activated • Reusable

25. Custom Content Query Webpart

26. Thank You • Blog: www.greggalipeau.com • Twitter: @ggalipeau • LinkedIn: http://www.linkedin.com/in/greggalipeau • SharePointShout –http://www.sharepointshout.com