Retrieve SharePoint List Data with XLINQ using VS 2010 Beta 1

Article
06/17/2009

One of the key things you’ll likely want to do with SharePoint is interact a lot with lists. In earlier posts on this blog, I’ve discussed adding data to a SharePoint list using both custom and native SharePoint services (i.e. services that ship with either WSS or MOSS—when I say SharePoint I include both of these SharePoint pieces). A recent post got me thinking about how to not only retrieve data from SharePoint, but then having the ability to take that data offline (e.g. save to a file) or to use it in-memory for other types of processing (e.g. filtering/management within a UI), so I put together a small application that does a couple of things:

1. Retrieves data from a SharePoint list using the Lists web service (a service native to SharePoint 2007);

2. Uses XML to add items to a custom object; and

3. Uses LINQ to query the object and filter/manage into a XAML UI.

All this using the latest Visual Studio 2010 Beta bits.

First, let’s take a quick look at the SharePoint list. It’s called “Products” and lives on my test SharePoint server (“https://stefoxdemo”). You can see a cropped screenshot of the list. It’s pretty simple and should be thought of as such because we really just want to show data coming from the list—not hoards of data.

If you’ve never created a list, you open SharePoint, navigate to your home page, click View All Site Content, click Create, and under Custom Lists click Custom List. Complete the Name and Description fields for your new list and click Create.

Now that you’ve created the list, open Visual Studio 2010 and click File, New Project. Select WPF Application and provide a name (e.g. MySharePointData) and location for your project and click OK—see the figure below.

We’re going to keep things simple here and add nine controls to the WPF UI:

Four labels
One listbox
Two textboxes
Two buttons

If you inspect the XAML code below, you’ll see that this is the resulting code that was generated from me dragging and dropping controls from the VS 2010 Toolbox onto the XAML designer. Check out the different properties of the controls.

The resulting UI from the above code looks like the following:

Once you’ve created your WPF UI, you now will want to add some code-behind to actually do something. The first thing you’ll want to do is to add the SharePoint Lists web service. This provides you with the ability to interact with lists and data in your lists. You can see from the figure below the subset of methods that are available to you. To add the web service to the VS 2010 project, right click References and select Service References. Click Advanced and then click Add Web Reference, and then discover the Lists web service by clicking Web Services on the Local Machine (or entering the service URL in the URL: field, e.g. “https://stefoxdemo/_vti_bin/Lists.asmx”). Provide a name for the service reference (e.g. SPListGetData) and click Add Reference. You now have a reference added to the Lists web service and can use it in code.

One thing I used in my solution was a custom object, which I used to store (in-memory) a list collection of the different elements in my list. This allowed me to use the collection of objects later and run a simple LINQ query against it. To add an object, right-click the project node and select Add, and then select Class.

The custom object comprises three strings (mapping to the three parts of my SharePoint list): 1) a title (or product name), 2) a product number, and 3) a sales figure. The code I have for my custom object is as follows—note that I’ve set the properties on my object to both read/write, but in this file all we’re really doing is reading.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MySharePointData
{
public class MySPData
{
public string clsTitle { get; set; }
public string clsProductNum { get; set; }
public string clsSales { get; set; }
}
}

After you’ve added the class to the project, you can now add some of the heavier-weight code. This code is going to set some class-level variables, use the Lists web service to get the data in my list, iterate through the list and use XLINQ to create and populate the custom object, and then if the user changes the selection on the list the fields will be updated through a LINQ query.

Let’s start with the class-level variables. I’ve added a string to represent the selected item in the listbox, a list collection object (which will be in-memory collection object) called lstSPData, and the declaration of an XDocument (called myXMLDoc), which will be used as the document to which I’ll add the items from the SharePoint list.

…

string strSelectedItem = "";

List<MySPData> lstSPData = new List<MySPData>();

XDocument myXMLDoc = new XDocument();

…

Once you’ve added these three top-level items to the class, you’ll want to double-click on one of the buttons you added to the UI and then add some code for that. In my case, the name of the button was btnGetData and the subsequent event was called btnGetData_Click. The code below shows what I added into that method. There are a few things that are interesting in the code. For example, note that one of the first things we do is create a proxy for the web service, pass my default credentials to call the service (SharePoint must know who you are even at the service level), and then uses an XMLNode object to get all of the items from the Products list. You’ll then also see that I’ve began creating an XML object, but creating an XElement and then setting the declaration type of the XML Document I created earlier. Then to get the data out of the object, you iterate through the nodes and grab the inner text of the XML. In this case, you need to be sure you’re pulling from the SharePoint XML (this is the “ows_Title,” “ows_ProductNum,” and “ows_Sales”). From there on, two major things are happening. The first is that I’m reassigning the XML data from the object being passed back from the web service call with some XML that I’m more comfortable with (and a structure that I prefer to work with), and then I’m creating an instance of the custom object I created and then am populating the list collection for that custom object as I iterate through each of the XML nodes of the returned XML data. You’ll then see two lines of code lying outside of the foreach statement, and this is where I’m first adding the more comfortable version of the XML data to my originally-created XML document, and then I’m saving that XML document to a file share.

private void btnGetData_Click(object sender, RoutedEventArgs e)
{

MySharePointData.SPListGetData.Lists proxy = new MySharePointData.SPListGetData.Lists();
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
proxy.Url = "https://stefoxdemo/_vti_bin/Lists.asmx";

XmlNode myLIstItems = proxy.GetListItems("Products", null, null, null, null, null, null);

XElement newRootElement = new XElement("NewData");

myXMLDoc.Declaration = new XDeclaration("1.0", "utf-8", "true");

foreach (XmlNode outerNode in myLIstItems.ChildNodes)
{

if (outerNode.NodeType.Equals(System.Xml.XmlNodeType.Element))
{

foreach (XmlNode node in outerNode.ChildNodes)
{

if (node.NodeType.Equals(System.Xml.XmlNodeType.Element))
{

XmlNode listFieldTitle = node.Attributes.GetNamedItem("ows_Title");
XmlNode listFieldProductNum = node.Attributes.GetNamedItem("ows_ProductNum");
XmlNode listFieldSales = node.Attributes.GetNamedItem("ows_Sales");

string strListFieldTitle = listFieldTitle.InnerText;
string strListFieldProductNum = listFieldProductNum.InnerText;
string strListFieldSales = listFieldSales.InnerText;

XElement xmlData = new XElement("MyData",
new XElement("Title", strListFieldTitle),
new XElement("ProductNum", strListFieldProductNum),
new XElement("Sales", strListFieldSales));

newRootElement.Add(xmlData);
lstBxProducts.Items.Add(strListFieldTitle);

MySPData clsSPDataInstance = new MySPData();

clsSPDataInstance.clsTitle = strListFieldTitle;
clsSPDataInstance.clsProductNum = strListFieldProductNum;
clsSPDataInstance.clsSales = strListFieldSales;
lstSPData.Add(clsSPDataInstance);

}
}
}

}

myXMLDoc.Add(newRootElement);
myXMLDoc.Save("c:\\SPData\\MySharePointData.xml");
}

The key takeaway from this, though, is that there are now two data structures in this app: one that I’ve created as an XML file and saved to disk (or alternatively could be used to inject into another program, say a Word document through Open XML); and one that now represents a list collection of the custom object—this is my in-memory version of the data that I can use for querying.

The next bit of code we’ll look at handles the user interacting with the listbox; that is, when the user changes their selection this will invoke the lstBxProducts_SelectionChanged event, which triggers the following code. This code is fairly straight-forward. I’ve set three string variables as temporary variables, grabbed the currently selected item in the listbox (and am storing it in strSelectedItem) and then am using a LINQ query to get the other data in the record that maps to the product name selected in the listbox. Given the fact that this results in a list of one record, I’m then using the First method to grab the first (and only) element in that record and assigning the text property of the controls to those strings.

private void lstBxProducts_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
string strTempTitle = "";
string strTempProductNum = "";
string strTempSales = "";

strSelectedItem = lstBxProducts.SelectedItem.ToString();

var lstXMLObject = from lstXML in lstSPData
where lstXML.clsTitle == strSelectedItem
select new
{
strTempTitle = lstXML.clsTitle,
strTempProductNum = lstXML.clsProductNum,
strTempSales = lstXML.clsSales

};

txtBxProductNum.Text = lstXMLObject.First().strTempProductNum;
txtBxSales.Text = lstXMLObject.First().strTempSales;

}

The finished product results in the following cool and slick (okay, not so much…but functional) WPF UI first getting SharePoint list data and then allowing you to query that data in the context of your whiz-bang UI. Note that it also saves the XML to your local file system. When you F5, you should see the following UI. Click the Get Data button, which triggers the service call. This will populate the Product Name listbox and subsequently allow you to click on an item in the listbox and filter on that item.

If you browse to the location where you saved your XML file (in my case this was c:\SPData), you will find an XML file that hopefully resembles the data you have in your list.

<?xml version="1.0" encoding="utf-8"?>
<NewData>
<MyData>
<Title>ALK-Bike Crank</Title>
<ProductNum>0398021</ProductNum>
<Sales>$209,002.98</Sales>
</MyData>
<MyData>
<Title>OUU-Bicycle Riding Helmet</Title>
<ProductNum>38271920</ProductNum>
<Sales>$901,199.23</Sales>
</MyData>
<MyData>
<Title>YTT-Road Bicycle Wheel</Title>
<ProductNum>03929901</ProductNum>
<Sales>$890,872.12</Sales>
</MyData>
<MyData>
<Title>HGT-Off-Road Bike Gear Pack</Title>
<ProductNum>3746281</ProductNum>
<Sales>$459,922.10</Sales>
</MyData>
<MyData>
<Title>BKP-Bianchi Racing Bike</Title>
<ProductNum>3902443</ProductNum>
<Sales>$2,019,100.29</Sales>
</MyData>
</NewData>

Lastly, the Exit button you see on my UI also calls a method; basically a simple method to exit the application. The code for this is as follows:

private void btnExit_Click(object sender, RoutedEventArgs e)
{
Application.Current.Shutdown();
}

And that’s it! Wasn’t that fun? I thought so. For your reference, the entire code sample from the core Windows.xaml.cs file is pasted below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Xml;
using System.Xml.Linq;

namespace MySharePointData
{
public partial class Window1 : Window
{
public Window1()
{
InitializeComponent();
}
string strSelectedItem = "";

List<MySPData> lstSPData = new List<MySPData>();

XDocument myXMLDoc = new XDocument();

private void btnGetData_Click(object sender, RoutedEventArgs e)
{

XmlNode myLIstItems = proxy.GetListItems("TR8", null, null, null, null, null, null);
//Creation
XElement newRootElement = new XElement("NewData");
myXMLDoc.Declaration = new XDeclaration("1.0", "utf-8", "true");

foreach (XmlNode outerNode in myLIstItems.ChildNodes)
{

if (outerNode.NodeType.Equals(System.Xml.XmlNodeType.Element))
{

foreach (XmlNode node in outerNode.ChildNodes)
{

if (node.NodeType.Equals(System.Xml.XmlNodeType.Element))
{

string strListFieldTitle = listFieldTitle.InnerText;
string strListFieldProductNum = listFieldProductNum.InnerText;
string strListFieldSales = listFieldSales.InnerText;

XElement xmlData = new XElement("MyData",
new XElement("Title", strListFieldTitle),
new XElement("ProductNum", strListFieldProductNum),
new XElement("Sales", strListFieldSales));

newRootElement.Add(xmlData);
//tempAll = strListFieldTitle + " " + strListFieldProductNum + " " + strListFieldSales;
lstBxProducts.Items.Add(strListFieldTitle);

MySPData clsSPDataInstance = new MySPData();

clsSPDataInstance.clsTitle = strListFieldTitle;
clsSPDataInstance.clsProductNum = strListFieldProductNum;
clsSPDataInstance.clsSales = strListFieldSales;
lstSPData.Add(clsSPDataInstance);

}
}
}

}

myXMLDoc.Add(newRootElement);
myXMLDoc.Save("c:\\SPData\\MySharePointData.xml");

MessageBox.Show("XML Data Saved to File!");
}

private void btnExit_Click(object sender, RoutedEventArgs e)
{
Application.Current.Shutdown();
}

private void lstBxProducts_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
string strTempTitle = "";
string strTempProductNum = "";
string strTempSales = "";

strSelectedItem = lstBxProducts.SelectedItem.ToString();

};

txtBxProductNum.Text = lstXMLObject.First().strTempProductNum;
txtBxSales.Text = lstXMLObject.First().strTempSales;

}
}
}

Happy coding!

Comments

Anonymous
June 17, 2009
The comment has been removed
Anonymous
June 17, 2009
The comment has been removed
Anonymous
June 23, 2009
It seems every week more and more great content appears! These could almost become daily Coffee break posts! WSP Carsten Keutmann: Beginning SharePoint Development (Bonus part #3 WSPBuilder overview)
Anonymous
November 30, 2010
iiiiiiiiiilllllllllllloooooooovvvvvvvvveeeeeeee sssssssssttttteeeevvveee ffffoooxxx
Anonymous
January 31, 2011
hi I have one issue i have a one sharepoint list and i want to retrieve the data into datagrid view by using windows application,. can u explain that one
Anonymous
July 31, 2011
The comment has been removed
Anonymous
November 11, 2011
The comment has been removed

Share via

Retrieve SharePoint List Data with XLINQ using VS 2010 Beta 1

Comments

Additional resources