Batch Update In SharePoint

SharePoint support batch update in List by webservice and SharePoint object model.

For Example i used sample Custom List (Orders).

1. By Use of Web Service.

SharePoint webservice allows update list items as a batch.At a time only one list can be updated by single webservice call.

CAML That use for Batch Update. Here i update item with ID 2 and create new item in Orders List in one webservice call.

<Batch OnError=’Continue’>
         <Method ID=’1’ Cmd=’Update’>
                <Field Name=’ID’ >2</Field>
                <Field Name=’OrderDate’ >2007-1-21</Field>
                <Field Name=’CustomerID’ >1;#Cust_1</Field>
         </Method>
         <Method ID=’2’ Cmd=’New’>
                <Field Name=’OrderDate’ >2007-1-21</Field>
                <Field Name=’CustomerID’ >2;#Cust_2</Field>
         </Method>
</Batch>

C# Code (Either Add web reference of SharePoint webservice or Create Proxy Class using wsdl.exe and use that class)

Lists lst = new Lists();
lst.Url = “http://<your site>/_vti_bin/lists.asmx”;
lst.Credentials = new System.Net.NetworkCredential ("test", "test");
XmlDocument doc = new XmlDocument();
XmlElement batchElement =  doc.CreateElement("Batch");
batchElement.SetAttribute("OnError", "Continue");
batchElement.InnerXml = "<Method ID='1' Cmd='Update'>" +
               "<Field Name='ID'>2</Field>" +
               "<Field Name='OrderDate'>2009-1-31</Field><Field Name='OrderDateTime'>2009-1-31</Field></Method><Method ID='2' Cmd='New'><Field Name='CustomerID'>1;#Cust_1</Field><Field Name='OrderDate'>2009-1-31</Field><Field Name='OrderDateTime'>2009-1-31</Field></Method>";
XmlNode result = lst.UpdateListItems("Orders", batchElement); // This line of code make webservice call to update lists.

One more thing In CAML displayed above has two bold line for CustomerID field. CustomerID is a LookUp Field. So i mention that date id;#Value (Commom lookup Structure).If you want to choose lookup value only by id then use following CAML for Field.

<Batch OnError=’Continue’>
         <Method ID=’1’ Cmd=’Update’>
                <Field Name=’ID’ >2</Field>
                <Field Name=’OrderDate’ >2007-1-21</Field>
                <Field Name=’CustomerID’  Type=’LookUp’ LookUpID=’True’>1</Field>
         </Method>
         <Method ID=’2’ Cmd=’New’>
                <Field Name=’OrderDate’ >2007-1-21</Field>
                <Field Name=’CustomerID’ Type=’LookUp’  LookUpID=’True’ >2</Field>
         </Method>
</Batch>

2. By Use Of SharePoint Object Model. (SPWeb.ProcessBatchData)

SharePoint Object Model has SPWeb class. SPWeb class has ProcessBatchData Method , this method is used to update Batch via object model. Even this method allows you update two list items in Single method call.

Sample CAML .
<?xml version="1.0" encoding="UTF-8"?>
<ows:Batch OnError="Continue">
  <Method ID="1">
    <SetList>71a9fac3-2e94-4246-8fec-41e30ea65b06</SetList>
    <SetVar Name="Cmd">Save</SetVar>
    <SetVar Name="ID">New</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDate">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDateTime">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#CustomerID">1;#Cust_1</SetVar>
  </Method>
  <Method ID="2">
    <SetList>71a9fac3-2e94-4246-8fec-41e30ea65b06</SetList>
    <SetVar Name="Cmd">Save</SetVar>
    <SetVar Name="ID">3</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDate">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDateTime">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#CustomerID">1;#Cust_1</SetVar>
  </Method>
  <Method ID="3">
    <SetList>71a9fac3-2e94-4246-8fec-41e30ea65b06</SetList>
    <SetVar Name="Cmd">Delete</SetVar>
    <SetVar Name="ID">4</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDate">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDateTime">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#CustomerID">2;#Cust_2</SetVar>
  </Method>
</ows:Batch>

In above SetList element set GUID of SPList. Here i only update Orders List but you can set another list id combine it with above by just adding another Method Element.

SetVar element with attribute Name ‘cmd’ used to identify type of functionality. It has mainly two values : Save and Delete . Save is used for either for New Item or in case of update existing item.

SetVar element with attribute Name ‘ID’ used to find out on which item it operate. ID value ‘New’ is for new item and in case of Update and Delete it should have valid id value. (Integer).

SetVar element with Name attribute value urn:schemas-microsoft-com:office:office#<Field_Name> indicate perticular field of ListItem,<Field_Name> should replace by respetive field internal name.

C# Code.

SPSite site = new SPSite("http://avani:45830");
SPWeb web = site.OpenWeb();
SPList lst = web.Lists["Orders"];
string batchFormat = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<ows:Batch OnError=\"Return\">{0}</ows:Batch>";

string methodFormat = "<Method ID=\"{0}\">" +
             "<SetList>{1}</SetList>" +
             "<SetVar Name=\"Cmd\">{2}</SetVar>" +
             "<SetVar Name=\"ID\">{3}</SetVar>" +
             "<SetVar Name=\"urn:schemas-microsoft-com:office:office#OrderDate\">{4}</SetVar>" +
             "<SetVar Name=\"urn:schemas-microsoft-com:office:office#OrderDateTime\">{5}</SetVar>" +
             "<SetVar Name=\"urn:schemas-microsoft-com:office:office#CustomerID\">{6}</SetVar>" +            
             "</Method>";

StringBuilder strmethodFormat = new StringBuilder();
strmethodFormat.AppendFormat(methodFormat, 1, lst.ID.ToString(), "Save", "New", "2009-2-21", "2009-2-21","1;#Cust_1");
strmethodFormat.AppendFormat(methodFormat, 2, lst.ID.ToString(), "Save", "3", "2009-2-21", "2009-2-21","1;#Cust_1");
strmethodFormat.AppendFormat(methodFormat, 3, lst.ID.ToString(), "Save", "4", "2009-2-21", "2009-2-21","2;#Cust_2");
string processtext = String.Format(batchFormat, strmethodFormat.ToString());

web.ProcessBatchData(processtext);

Other usefull post about Batch element creation through XLinq or Linq are
http://dotnetstep.blogspot.com/2009/01/xlinq-to-generate-batch-element-for.html
http://dotnetstep.blogspot.com/2009/01/sharepoint-to-linq.html

ThreadPool Wait For All Thread To Complete

I already discuss issue related to WaitHandle.WaitAll that it does not support wait for more 64 waithandle. In that article discuss about 64 chunks of thread to be executed parallel.

When you use ThreadPool and you combine it with 64 chunks of thread concept than you can not take advantage of ThreadPool. In ThreadPool all task wait for pool become empty.( Not fully). As ThreadPool have available thread then new task automatically executed.

So wait for more than 64 waithandle is needed, i found solution by following way.

C# Code.

using System;
using System.Collections.Generic;
using System.Threading;
namespace ThreadPoolWaitForAllThread
{
    class Program
    {
        static void Main(string[] args)
        {
            List<ManualResetEvent> events = new  List<ManualResetEvent>();
            for (int i = 0; i < 100; i++)
            {
                ThreadPoolObj obj = new ThreadPoolObj();
                obj.ObjectID = i;
                obj.signal =  new ManualResetEvent(false);
                events.Add(obj.signal);
                WaitCallback callback = new WaitCallback(ThreadFunction);
                ThreadPool.QueueUserWorkItem(callback,obj);
            }          
           WaitForAll(events.ToArray());
           Console.WriteLine("Compelted");
           Console.ReadLine();
        }
        static bool WaitForAll(ManualResetEvent[] events)
        {
            bool result = false;
            try
            {
                if (events != null)
                {
                    for (int i = 0; i < events.Length; i++)
                    {
                        events[i].WaitOne();
                    }
                    result = true;
                }
            }
            catch
            {
                result = false;
            }
            return result;
        }
        static void ThreadFunction(object threadobj)
        {
            ThreadPoolObj obj = threadobj as ThreadPoolObj;
            if (obj != null)
            {
                Console.WriteLine(obj.ObjectID.ToString());
                Thread.Sleep(2000); // Just Wait To Show Syncronization
                obj.signal.Set();
            }
        }
    }
    class ThreadPoolObj
    {
        public int ObjectID;
        public ManualResetEvent signal;
    }
}
By this way you can wait for more than 64 Waithandle.

Same function you can use anywhere even in case of article (WaitHandle.WaitAll Limitation). After use of this function you can avoid logic of 64 chunks, but if your thread consume more resource than it is better to run as chunks of thread instead of starting all thread at once. ThreadPool maintain this thing automatically.

Please give your comment on this.

Find BigNumber Factorial (!)

Sometime when work with mathematical application , there is need to find out “Big Number” Factorial.

By Use of Simple Factorial Function

C# Code.

using System;
class Program
    {
        static void Main(string[] args)
        {
            for (int i = 1; i < 50; i++)
            {
            Console.WriteLine(i.ToString() + "\t\t" +SimpleFactorial(i).ToString());
            }
            Console.ReadLine();
        }

        static System.Int64 SimpleFactorial(System.Int64 number)
        {
            System.Int64 result = 1;
            for (System.Int64 i = 2; i <= number; i++)
            {
                result = result * i;
            }
            return result;
        }
}
If you are using above code than maximum up to value 20 you can find right answer. (Means 20!). If need is to find out Factorial larger this than you have to use FSharp Library in C#.

You can find F#Sharp Library at DownLoad .

After download , extract it and Add reference of FSharp.Core in C# Project.

using System;
using Microsoft.FSharp.Math;
class Program
    {
        static void Main(string[] args)
        {
            for (int i = 1; i < 50; i++)
            {
           Console.WriteLine(i.ToString() + "\t\t" + FSharpFactorial(i).ToString());
            }
            Console.ReadLine();
        }       

        static BigInt FSharpFactorial(System.Int64 number)
        {
            BigInt num = new BigInt(number);
            return BigInt.Factorial(num);
        }
    }

By using FSharp Version of factorial 50! easily calculated. Even i test upto 20000! and It product result.

Connect To Office 2007(Access 2007 , Excel 2007) Without Installing Entire Office 2007

Deploy application that use office 2007 ( Access 2007 , Excel 2007) and end user machine does not have Office installed on machine. In this case it is not necessary that end user machine must have office 2007 installed on machine.

Just install Data Connectivity driver.
2007 Office System Driver: Data Connectivity Components

Office 2007 Data Connectivity driver

• ODBC to connect to Microsoft Office Access data, set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
  
  
• ODBC to connect to Microsoft Office Excel data, set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”
  

Driver is around 25mb so it can reduce overhead of office 2007.

XLinq To Generate Batch Element For SharePoint ListService

In my article (http://dotnetstep.blogspot.com/2009/01/update-datetime-column-using-webservice.html) i used SharePoint List Service to update SharePoint ListItems. In that article i manually set batchElement.InnerXml property with Xml.

When there is to many row and you want to generate it automatically then you can use XLinq.

In following example first select all items from orders List then use item id and update each item orderdate and orderdatetime column with current date and current datetime respectively.

Lists lst = new Lists(); lst.Url = “http://<your site>/_vti_bin/lists.asmx”; lst.Credentials = new System.Net.NetworkCredential("test", "test"); // Site Administrator username and password XmlNode resultNode = lst.GetListItems("Orders", String.Empty, null, null, int.MaxValue.ToString(), null, String.Empty);

// Use XElement To Generate Batch Element Automatically

XElement orders = XElement.Parse(resultNode.OuterXml); XName name = XName.Get("data","urn:schemas-microsoft-com:rowset"); int methodsequence=1; XElement updatexml = new XElement("Batch", new XAttribute("OnError","Continue"), from order in orders.Element(name).Elements() // you can also use where condition over here // for eg. where order.Attribure(“ows_ID”).value = “2” select new  XElement("Method",  new XAttribute("ID", (methodsequence++).ToString()),  new XAttribute("Cmd", "Update"), new XElement("Field", new XAttribute("Name", "ID"), order.Attribute("ows_ID").Value), new XElement("Field",new XAttribute("Name","OrderDate"),DateTime.Now.SharepointFormatDate()), new XElement("Field", new XAttribute("Name", "OrderDateTime"), DateTime.Now.SharepointFormatDateTime())));

Now Convert XElement to XmlNode as UpdateListItems only accept XmlNode as parameter.

XmlDocument doc=  new XmlDocument(); doc.LoadXml(updatexml.ToString()); lst.UpdateListItems("Orders",doc.FirstChild);

Update DateTime Column Using SharePoint ListService

SharePoint webservice can be used to update item or items in SharePoint List.When SharePoint List contains “Date And Time” Column with only Date or Date And Time Mode, you have to pass date or datetime in specific format so it can be update by webservice.

UpdateListItems method is used for this purpose.

To format DateTime for SharePoint Webservice, i created following extension methods.

// Extensions Method

public static class DateTimeExtensions
{
    // Only Date Column
    public static string SharepointFormatDate(this DateTime dt)
    {
        return dt.ToString("yyyy-MM-dd");
    }

    // Date And Time column
    public static string SharepointFormatDateTime(this DateTime dt)
    {
        return dt.ToString("yyyy-MM-ddTHH:mm:ssZ");
    }
}

To Update List using webservice.

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();            System.Xml.XmlElement batchElement = doc.CreateElement("Batch"); batchElement.SetAttribute("OnError", "Continue"); batchElement.SetAttribute("ListVersion", "1"); batchElement.InnerXml =”<Method ID='1' Cmd='Update'>" + "<Field Name='ID'>2</Field>" + "<Field Name='OrderDate'>”+ DateTime.Now.SharepointFormatDate() +”</Field><Field Name='OrderDateTime'>”+ DateTime.Now.SharepointFormatDateTime()  +”</Field></Method>"; // List Serivce object  (Add web reference for this) Lists lst = new Lists(); lst.Url = “http://<your site>/_vti_bin/lists.asmx” ; lst.Credentials = new System.Net.NetworkCredential("test", "test"); lst.UpdateListItems(“Orders”,batchElement);

In above code <Field Name=’ID’>2</Field> is used to identify unique row that need to be update. In Sample only item with id 2 is updated. In order to update multiple items just add another method element. For example, (This update both item with id 2 and 3)

batchElement.InnetXml = “<Method ID='1' Cmd='Update'>" + "<Field Name='ID'>2</Field>" + "<Field Name='OrderDate'>”+ DateTime.Now.SharepointFormatDate() +”</Field><Field Name='OrderDateTime'>”+ DateTime.Now.SharepointFormatDateTime()  +”</Field></Method>" + "<Method ID='2' Cmd='Update'>" + "<Field Name='ID'>3</Field>" + "<Field Name='OrderDate'>”+ DateTime.Now.SharepointFormatDate() +”</Field><Field Name='OrderDateTime'>”+ DateTime.Now.SharepointFormatDateTime()  +”</Field></Method>"

Same way you can update another type of columns too.

More information available at following location.

http://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems.aspx
http://msdn.microsoft.com/en-us/library/ms440289.aspx

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.