Sunday, January 11, 2009

SharePoint to Linq

Here specially talking about SharePoint and XLinq. When you choose SharePoint webservice to get data from SharePoint List, data return by web service is in XML format. To read this data as well as filter data XLinq is more powerful.

How to Use SharePoint webservice.
1. Add Web reference or generate proxy using WSDL tool.
then

Lists lst = new Lists();
lst.Url = “http://<your site>/_vti_bin/lists.asmx”;
lst.Credentials = new System.Net.NetworkCredential("test", "test");

Now use GetListItems to retrieve all items from SharePoint List. I would suggest take scenario into consideration. If you require less data to be return from SharePoint List then use Query in GetListItems function. ( Here concentration is on XLinq so it is out of scope for this post).

XmlNode resultNode = lst.GetListItems("Customers", String.Empty, null, null, int.MaxValue.ToString(), null, String.Empty);

XElement (System.Xml.Linq) to read result into XElement
XElement customers = XElement.Parse(resultNode.OuterXml);

XName to read type of Node.
XName name = XName.Get("data","urn:schemas-microsoft-com:rowset");

Read All Node Using Linq
var filtercustomers = from ele in customers.Element(name).Elements()                                
select new {CustomerID =  ele.Attribute("ows_ID").Value , Name = ele.Attribute("ows_CustomerName").Value , City = ele.Attribute("ows_CustomerCity").Value , Country = ele.Attribute("ows_CustomerCountry").Value};

Filtering Node using XLinq
var filtercustomers = from ele in customers.Element(name).Elements()   
where ele.Attribute("ows_CustomerCountry").Value == "India" && ele.Attribute("ows_CustomerCity").Value == "Banglore"                             
select new {CustomerID =  ele.Attribute("ows_ID").Value , Name = ele.Attribute("ows_CustomerName").Value , City = ele.Attribute("ows_CustomerCity").Value , Country = ele.Attribute("ows_CustomerCountry").Value};

Join Using XLinq

1. Join Two Result
This is use full case when join in needed on filter data set.
In following code customers and orders represent two sample reslut set.

XmlNode resultNode = lst.GetListItems("Customers", String.Empty, null, null, int.MaxValue.ToString(), null, String.Empty);
            XElement customers = XElement.Parse(resultNode.OuterXml);
            XName name = XName.Get("data","urn:schemas-microsoft-com:rowset");

            var filtercustomers = from ele in customers.Element(name).Elements()         
where ele.Attribute("ows_CustomerCountry").Value == "India" && ele.Attribute("ows_CustomerCity").Value == "Banglore"                       
select new {CustomerID =  ele.Attribute("ows_ID").Value , Name = ele.Attribute("ows_CustomerName").Value , City = ele.Attribute("ows_CustomerCity").Value , Country = ele.Attribute("ows_CustomerCountry").Value};

            // Retrive data From Orders
            resultNode = lst.GetListItems("Orders", String.Empty, null, null, int.MaxValue.ToString(), null, String.Empty);
            XElement orders = XElement.Parse(resultNode.OuterXml);
            var filterorders = from ele in orders.Element(name).Elements()                                 
                                  select new { OrderID = ele.Attribute("ows_ID").Value, CustomerID = ele.Attribute("ows_CustomerID").Value.Split(new string[]{";#"},StringSplitOptions.None)[0] , OrderDate = ele.Attribute("ows_OrderDate").Value };

Join Results

var joinresult = from customerele in filtercustomers
                     join orderele in filterorders on customerele.CustomerID equals orderele.CustomerID
                     select new { customerele, orderele };

2. Directly Join Two XElement

var directjoin = from customerele in customers.Element(name).Elements()
                             join orderele in orders.Elements(name).Elements() on customerele.Attribute("ows_ID").Value equals orderele.Attribute("ows_CustomerID").Value.Split(new string[] { ";#" }, StringSplitOptions.None)[0]
                             select new { CustomerID = customerele.Attribute("ows_ID").Value, Name = customerele.Attribute("ows_CustomerName").Value, City = customerele.Attribute("ows_CustomerCity").Value, Country = customerele.Attribute("ows_CustomerCountry").Value, OrderID = orderele.Attribute("ows_ID").Value, OrderDate = orderele.Attribute("ows_OrderDate").Value };

Note: When working with webservice do care while retrieve data. Whatever data needed from single list for operation try to retrieve in single webservice call instead of making many web service call.For my point of view this will improve the performance.

No comments: