Accessing Sharepoint Data Using C# Without Running Code On The Sharepoint Server (part 2)

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

Jennifer Lewis

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

Overview SharePoint is an excellent tool for centralizing communications for a project team, department or company. Developers can also write programs that can take SharePoint’s functionality to the next level. For example, a developer can write a program to take survey data and create a robust report based on the survey data. There are two ways you can reference SharePoint when writing code: • Reference and use the classes the Microsoft.Sharepoint library • Make a web reference to the SharePoint services. While referencing and using the classes in the Microsoft.SharePoint library is the easier coding method of the two, it requires that you run the program on the same server as SharePoint. You may have a situation where you don’t want to run the code on the same server as SharePoint. For example, I needed to write a program that took data from a survey list, make calculations with the data, and put the calculated and summarized data in a new list. Because of our current SharePoint architecture, I didn’t really want to run the code on the same server because it would require me to move my code to the production SharePoint server, log in to the production server, and run the code. This series will demonstrate how to access SharePoint data “behind the scenes” without having to run the program on the SharePoint server. In Part 1 of this series, I will demonstrate how to access the SharePoint service using a web reference as well as how to access a list. In Part 2 of this series, I will demonstrate how to actually extract the data in the list. In Part 3 of this series, I will demonstrate how to add data to a SharePoint list using the program.

Scenario for this Illustration The site has a survey where users answer a few questions (see Figures 1 and 2). The assignment is to use a program to access the survey so the results can be evaluated, and some of the survey information can be added to a separate list. If you have already followed the tutorial for Part 1 of this series, you should have already created a project called SPBehindTheScenes. We will be modifying that project for this tutorial.

Figure 1: The survey

Page 2 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

Figure 2: Responding to the Survey

Directions 1) Open Visual Studio 2) Select File-Open-Project/Solution

Page 3 of 14 Written on 12/9/2008

Tip: From Windows Explorer, you can also go directly to the directory where you saved your project and double-click on the Solution file to open Visual Studio and automatically open your project.

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

3) In the Open File dialog window, change the directory to where you saved your project.

Page 4 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

4) Select the SPBehindTheScenes solution from the open file dialog. 5) Click the Open button. 6) In the Solution Explorer, double-click on Program.cs to open it for editing.

Page 5 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

Now we are going to make a few changes to our code. The first change that we are going to do is surround the call to the GetListItems() function in a try-catch loop for better error handling. 1) In your code, look for the following line: System.Xml.XmlNode activeItemData = listService.GetListItems(listGUID, activeItemViewGUID, null, null, "9000", null, ""); 2) Move your cursor immediately above the line. 3) Add the following code: try { 4) Move your cursor to the last closing bracket in the following statement: // Go through the list to see what was returned foreach (System.Xml.XmlNode listItem in activeItemData) { // Get the attributes System.Xml.XmlAttributeCollection attrs = listItem.Attributes; Console.WriteLine(listItem.OuterXml); } 5) Move your cursor to the line after the closing bracket.

Page 6 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

6) Add another closing bracket. (}) When you add the closing bracket, your try loop should automatically indent. 7) Immediately after the closing bracket, add the following code block: catch (Exception e) { Console.WriteLine("The following error has occurred: " + e.Message); } At this point, your code should look similar to this: using using using using

System; System.Collections.Generic; System.Text; System.Net;

namespace SPBehindTheScenes { class Program { static void Main(string[] args) { SharePointSvce.Lists listService = new SharePointSvce.Lists(); listService.Credentials = System.Net.CredentialCache.DefaultCredentials; // Replace the values in brackets with the actual brackets. For example, if the site // you will be working with is http://Bogus/Site1/SubSite1, enter // http://Bogus/Site1/SubSite1/_vti_bin/lists.asmx as the value listService.Url = "http://Bogus/Site1/SubSite1/_vti_bin/lists.asmx"; // Choose the list and the view. Remember to substitute the values with the actual // List GUID and View GUID that you want to work with. String listGUID = "FA929542-19F6-47C0-9966-81234F0AA9E9"; String activeItemViewGUID = "C08B3AFA-2FA6-40CD-9DE0-F8CCFBDED6B7"; try { System.Xml.XmlNode activeItemData = listService.GetListItems(listGUID, activeItemViewGUID, null, null, "9000", null, ""); // Go through the list to see what was returned foreach (System.Xml.XmlNode listItem in activeItemData) { // Get the attributes System.Xml.XmlAttributeCollection attrs = listItem.Attributes; Console.WriteLine(listItem.OuterXml); } } catch (Exception e) { Console.WriteLine("The following error has occurred: " + e.Message); } // Prompts the user to "press a key" when finished Console.WriteLine("Press any key to continue"); Console.ReadLine(); } } }

8) Compile and test your program by clicking the start button You should get a similar result.

Page 7 of 14 Written on 12/9/2008

at the top of your toolbar.

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

The next thing we are going to do is write code so we can access the data from the list. If you noticed when you were running your program, the results returned by the GetListItems() function is in an XML format. We will be using classes from the System.Xml library so we can easily navigate the XML to get the data that we need. 1) Add a reference to the System.Xml library. You will need this library to use the classes that will allow you to easily navigate the XML results from GetListItems(). using System.Xml;

2) In the Main function, look for the following code block and remove the code block from your code: // Go through the list to see what was returned foreach (System.Xml.XmlNode listItem in activeItemData) { // Get the attributes System.Xml.XmlAttributeCollection attrs = listItem.Attributes; Console.WriteLine(listItem.OuterXml); } We are going to replace this code with the code to actually get the individual values. In order to get the values, you will need to know the actual fields names that SharePoint uses for the questions in the survey. Nearly all the fields names start with ows_, followed with the field name that was used in the list definition. Note that SharePoint will substitute special characters like spaces with the hexadecimal value. For example, if you have a field on your list called First Name, SharePoint will reference the field name as ows_First_x0020_Name. Also note that if your field names are longer that what SharePoint supports for field names, it will truncate the names. In this illustration, the survey’s field names are the following: ows_What_x0020_is_x0020_your_x0020_N

Page 8 of 14 Written on 12/9/2008

You can get the field names by using the Stramit CAML Viewer (http://www.codeplex.com/S PCamlViewer) or the GUID Picker (http://blogs.msdn.com/ronal us/archive/2007/09/08/a-littleguid-picker.aspx)

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

ows_What_x0020_is_x0020_your_x0020_q ows_Who_x0020_is_x0020_your_x0020_fa Since my list is a Survey List, I will also be using the ows_MetaInfo field to get the response number. Your information will vary depending on the list that you are using to access the information. 3) We are going to instantiate an XmlDocument object and load the results from the GetListItems function in an XmlDocument. At the same time, we are going to “clean up” the response by replacing any carriage returns (\r) with spaces. Add the following lines to your code: // Get the records from the list XmlDocument doc = new XmlDocument(); String temp = activeItemData.InnerXml.Replace("\r\r", ""); doc.LoadXml(temp);

4) On the next line, we are going to set up the namespace information for our XmlDocument by instantiating the XmlNamespaceManager object and configuring the namespaces for the object. Add the following code: XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable); nsmgr.AddNamespace("z", "#RowsetSchema"); nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset"); 5) Now we are going to navigate through the results by putting the results in an XmlNodeList object and navigating the XmlNodeList object using a foreach loop. Add the following lines of code: XmlNodeList xl = doc.SelectNodes("/rs:data/z:row", nsmgr); foreach (XmlNode node in xl) { } 6) In my code, I am going to store the information in variables and print out the variable values on the screen. For practice (and preparation for Part 3 of this series), you should also do the same. To access the field value, you would use the following logic: String var = node.Attributes["fieldname"].InnerText; Where: • var = the name of the variable you will be using in your code. • fieldname = the name of the field in the list In my example, if I wanted the value from the question in the survey “What is Your Name?”, which is stored in SharePoint as ows_What_x0020_is_x0020_Your_x002_N, I would write my code in the following manner within the foreach code block: String fname = node.Attributes["ows_What_x0020_is_x0020_Your_x0020_N"].InnerText; In my example, I would like to retrieve the values for all my questions and display the results on the screen. Add the code within your foreach loop that will get the values from the SharePoint list and write them out on the screen. My code looks like this: foreach (XmlNode node in xl) { String responseNumber = node.Attributes["ows_MetaInfo"].InnerText; String fname = node.Attributes["ows_What_x0020_is_x0020_Your_x0020_N"].InnerText;

Page 9 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

String quest = node.Attributes["ows_What_x0020_is_x0020_your_x0020_q"].InnerText; String python = node.Attributes["ows_Who_x0020_is_x0020_your_x0020_fa"].InnerText; Console.WriteLine("Response #: " + responseNumber); Console.WriteLine("What is your name? " + fname); Console.WriteLine("What is your quest? " + quest); Console.WriteLine("Who is your favorite Python? " + python); Console.WriteLine(); } At this point, your code should look similar to this: using using using using using

System; System.Collections.Generic; System.Text; System.Net; System.Xml;

namespace SPBehindTheScenes { class Program { static void Main(string[] args) { SharePointSvce.Lists listService = new SharePointSvce.Lists(); listService.Credentials = System.Net.CredentialCache.DefaultCredentials; // Replace the values in brackets with the actual brackets. For example, if the site // you will be working with is http://Bogus/Site1/SubSite1, enter // http://Bogus/Site1/SubSite1/_vti_bin/lists.asmx as the value listService.Url = "http://Bogus/Site1/SubSite1/_vti_bin/lists.asmx"; // Choose the list and the view. Remember to substitute the values with the actual // List GUID and View GUID that you want to work with. String listGUID = "FA929542-19F6-47C0-9966-81234F0AA9E9"; String activeItemViewGUID = "C08B3AFA-2FA6-40CD-9DE0-F8CCFBDED6B7"; try { System.Xml.XmlNode activeItemData = listService.GetListItems(listGUID, activeItemViewGUID, null, null, "9000", null, ""); // Get the records from the list XmlDocument doc = new XmlDocument(); String temp = activeItemData.InnerXml.Replace("\r\r", ""); doc.LoadXml(temp); XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable); nsmgr.AddNamespace("z", "#RowsetSchema"); nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset"); XmlNodeList xl = doc.SelectNodes("/rs:data/z:row", nsmgr); foreach (XmlNode node in xl) { /* * In this area, this is where you put the code to access the information on your list.

You * will reference the field names that are on your list.

For example, if

there is a field name * on your list called "First Name", the field will probably be known in SharePoint as * ows_First_x0020_Name.

You will need to verify the field names in

SharePoint by running * CAML Viewer or another tool that will return the information for you. */ String responseNumber = node.Attributes["ows_MetaInfo"].InnerText;

Page 10 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List String fname = node.Attributes["ows_What_x0020_is_x0020_Your_x0020_N"].InnerText; String quest = node.Attributes["ows_What_x0020_is_x0020_your_x0020_q"].InnerText; String python = node.Attributes["ows_Who_x0020_is_x0020_your_x0020_fa"].InnerText; Console.WriteLine("Response #: " + responseNumber); Console.WriteLine("What is your name? " + fname); Console.WriteLine("What is your quest? " + quest); Console.WriteLine("Who is your favorite Python? " + python); Console.WriteLine(); } } catch (Exception e) { Console.WriteLine("The following error has occurred: " + e.Message); } // Prompts the user to "press a key" when finished Console.WriteLine("Press any key to continue"); Console.ReadLine(); } } }

7) Compile and test your program by clicking the start button Your results should look similar to this:

at the top of your toolbar.

Special Section: Manipulating the Data You may be able to use the data as it is stored in SharePoint. However, in some cases, like in Survey Lists, you may have to manipulate the data before you can use it. In this illustration, you probably noticed that the value for the response number had some extraneous characters, as well as the value for the answer to the question “What is your quest?” An easy way to remove the extraneous characters from the string value before you continue to process the value is using Regex expressions. For example, if I want to remove the pound sign (#) from the value for the

Page 11 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

response number, I would write the following lines after storing the value from the XML result into the variable: // Remove the extraneous characters from the response number responseNumber = System.Text.RegularExpressions.Regex.Replace(responseNumber, "#", ""); The first parameter in the Replace() function in the string value that I want to evaluate. The second parameter is the string value that I would like to replace. The third parameter is the string value with which to replace the string value in the second parameter. In this example, I’m evaluating the variable responseNumber, and I want to replace the pound sign (#) with a blank. Adding the additional code to remove the extraneous values by using the Regex function Replace(), here is an example of what those lines look like: /* * If you don't have extraneous characters to remove from the values from * SharePoint, you don't need to use the Regex functions. You would only * need to do this if you want to strip the extraneous data from the * values. */ responseNumber = System.Text.RegularExpressions.Regex.Replace(responseNumber, "#", ""); responseNumber = System.Text.RegularExpressions.Regex.Replace(responseNumber, ";", ""); quest = System.Text.RegularExpressions.Regex.Replace(quest, "", "");

Page 12 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List

Appendix: Source Code using using using using using

System; System.Collections.Generic; System.Text; System.Net; System.Xml;

namespace SPBehindTheScenes { class Program { static void Main(string[] args) { SharePointSvce.Lists listService = new SharePointSvce.Lists(); listService.Credentials = System.Net.CredentialCache.DefaultCredentials; // Replace the values in brackets with the actual brackets. For example, if the site // you will be working with is http://Bogus/Site1/SubSite1, enter // http://Bogus/Site1/SubSite1/_vti_bin/lists.asmx as the value listService.Url = "http://<server>/<site>/<subsite>/_vti_bin/lists.asmx"; // Choose the list and the view. Remember to substitute the values with the actual // List GUID and View GUID that you want to work with. String listGUID = "FA929542-19F6-47C0-9966-81234F0AA9E9"; String activeItemViewGUID = "C08B3AFA-2FA6-40CD-9DE0-F8CCFBDED6B7"; try { System.Xml.XmlNode activeItemData = listService.GetListItems(listGUID, activeItemViewGUID, null, null, "9000", null, ""); // Get the records from the list XmlDocument doc = new XmlDocument(); String temp = activeItemData.InnerXml.Replace("\r\r", ""); doc.LoadXml(temp); XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable); nsmgr.AddNamespace("z", "#RowsetSchema"); nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset"); XmlNodeList xl = doc.SelectNodes("/rs:data/z:row", nsmgr); foreach (XmlNode node in xl) { /* * In this area, this is where you put the code to access the information * on your list. You will reference the field names that are on your list. * For example, if there is a field name on your list called "First Name", * the field will probably be known in SharePoint as * ows_First_x0020_Name. You will need to verify the field names in * SharePoint by running CAML Viewer or another tool that will return the * information for you. */ String responseNumber = node.Attributes["ows_MetaInfo"].InnerText; String fname = node.Attributes["ows_What_x0020_is_x0020_Your_x0020_N"].InnerText; String quest = node.Attributes["ows_What_x0020_is_x0020_your_x0020_q"].InnerText; String python = node.Attributes["ows_Who_x0020_is_x0020_your_x0020_fa"].InnerText; /* * If you don't have extraneous characters to remove from the values from * SharePoint, you don't need to use the Regex functions. You would only * need to do this if you want to strip the extraneous data from the values. */ responseNumber = System.Text.RegularExpressions.Regex.Replace(responseNumber, "#", ""); responseNumber = System.Text.RegularExpressions.Regex.Replace(responseNumber, ";", "");

Page 13 of 14 Written on 12/9/2008

Accessing SharePoint Behind the Scenes: How to Access SharePoint Data using C# Without Running the Code on a SharePoint Server. Part 2: Accessing the Data in the SharePoint List quest = System.Text.RegularExpressions.Regex.Replace(quest, "", ""); Console.WriteLine("Response #: " + responseNumber); Console.WriteLine("What is your name? " + fname); Console.WriteLine("What is your quest? " + quest); Console.WriteLine("Who is your favorite Python? " + python); Console.WriteLine(); } } catch (Exception e) { Console.WriteLine("The following error has occurred: " + e.Message); } // Prompts the user to "press a key" when finished Console.WriteLine("Press any key to continue"); Console.ReadLine(); } } }

Page 14 of 14 Written on 12/9/2008