Bulk Insert/Update/Delete Items from SharePoint List/Library

Hi All,

In a followinig post we will see about what is ProcessBatchData method of Web in SharePoint. In our developement career what we do most is Insert,Update or delete the Items by using SPListItem.AddItem(),SPListItem.Update(),SPListItem.Delete() resp.

We may get performance issue if we need to Insert/update/delete multiple set of records(Items). These Methods fires a DB query each and every time when we call them, By using the ProcessBatchData() Method ,one query will be fired for DB changes for bulk Insert/update/delete.

Insert :
following example is used to Insert the Items to SharePoint List

 public void BulkInsert()
        {
            try
            {
                using (SPSite _oSite = new SPSite("SiteURL"))
                {
                    using (SPWeb _oWeb = _oSite.OpenWeb())
                    {
                        SPList _oList = _oWeb.Lists.TryGetList("MyList");

                        if (_oList != null)
                        {
                            _oWeb.AllowUnsafeUpdates = true;
                            Guid listGuid = _oList.ID;
                            StringBuilder query = new StringBuilder();
                            query.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
                            string test = "SomeValue";
                            //Building the XML and appending to StringBuilder
                            for (int itemCount = 0; itemCount < 5; itemCount++)
                            {
                                query.AppendFormat("<Method ID=\"{0}\">" +
                                "<SetList>{1}</SetList>" +
                                "<SetVar Name=\"ID\">New</SetVar>" +
                                "<SetVar Name=\"Cmd\">Save</SetVar>" +
                                "<SetVar Name=\"{3}Title\">{2}</SetVar>" +
                                "<SetVar Name=\"{3}Column1\">" + test + "</SetVar>" +
                                "</Method>", "test", listGuid, itemCount,

                                "urn:schemas-microsoft-com:office:office#");
                            }
                            query.Append("</Batch>");
                            _oWeb.ProcessBatchData(query.ToString());
                            _oWeb.AllowUnsafeUpdates = false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

We can see the Output as follows , for the above code snippet.

Update:
following Code snippet is used to update the List Items.

public void BulkUpdate()
        {
            try
            {
                using (SPSite _oSite = new SPSite("SiteURL"))
                {
                    using (SPWeb _oWeb = _oSite.OpenWeb())
                    {
                        SPList _oList = _oWeb.Lists.TryGetList("MyList");
                        if (_oList != null)
                        {
                            _oWeb.AllowUnsafeUpdates = true;
                            Guid listGuid = _oList.ID;
                            StringBuilder updateBuilder = new StringBuilder();
                            updateBuilder.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
                            SPQuery query = new SPQuery();
                            query.Query = "<Where>" +
                                              "<Eq>" +
                                                 "<FieldRef Name='Column1' />" +
                                                 "<Value Type='Text'>SomeValue</Value>" +
                                              "</Eq>" +
                                           "</Where>";

                            SPListItemCollection _oListCollection = _oList.GetItems(query);
                            //Looping through the ListItemCollection and forming the deleteBuilder
                            //here, we are using "Delete" as cmd
                            foreach (SPListItem item in _oListCollection)
                            {
                                updateBuilder.Append("<Method>");
                                updateBuilder.Append("<SetList Scope=\"Request\">" + listGu                                                     id + "</SetList>");
                                updateBuilder.Append("<SetVar Name=\"ID\">" + Convert.ToStr                                                     ing(item.ID) + "</SetVar>");
                                //Column1 is my column Name , which I am updating here.
                                //If we want to update another columns we need to add here.
                           //here urn:schemas-microsoft-com:office:office# is STATIC TEXT
                                updateBuilder.Append("<SetVar Name=\"urn:schemas-microsoft-                                com:office:office#Column1\">It is Updated</SetVar> ");
                                updateBuilder.Append("<SetVar Name=\"Cmd\">Save</SetVar>");
                                updateBuilder.Append("</Method>");
                            }
                            updateBuilder.Append("</Batch>");
                            _oWeb.ProcessBatchData(updateBuilder.ToString());
                            _oWeb.Update();
                            _oWeb.AllowUnsafeUpdates = false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

After Updating List will looks as follows,

Delete:
following example is used to delete the List Items.

public void BulkDelete()
        {
            try
            {
                using (SPSite _oSite = new SPSite("SiteURL"))
                {
                    using (SPWeb _oWeb = _oSite.OpenWeb())
                    {
                        SPList _oList = _oWeb.Lists.TryGetList("MyList");
                        if (_oList != null)
                        {
                            _oWeb.AllowUnsafeUpdates = true;
                            //Get the List GUID
                            Guid listGuid = _oList.ID;
                            StringBuilder deletebuilder = new StringBuilder();
                            deletebuilder.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");

                            SPQuery query = new SPQuery();
                            //you Can add the Query here
                            //query.Query = "";

                            //Get the List Item Collection 
                            SPListItemCollection _oListCollection = _oList.GetItems(query);

                            //Looping through the ListItemCollection and forming the deleteBuilder
                            //here, we are using "Delete" as cmd
                            foreach (SPListItem item in _oListCollection)
                            {
                                deletebuilder.Append("<Method>");
                                deletebuilder.Append("<SetList Scope=\"Request\">" + listGuid + "</SetList>");
                                deletebuilder.Append("<SetVar Name=\"ID\">" + Convert.ToString(item.ID) + "</SetVar>");
                                deletebuilder.Append("<SetVar Name=\"Cmd\">Delete</SetVar>");
                                deletebuilder.Append("</Method>");
                            }
                            deletebuilder.Append("</Batch>");
                            _oWeb.ProcessBatchData(deletebuilder.ToString());
                            _oWeb.Update();
                            _oWeb.AllowUnsafeUpdates = false;

                        }
                    }
                }

            }
            catch (Exception ex)
            {
                throw;
            }
        }

Insert into Multiple Lists:
By using ProcessBatchData() Method we can Insert values into Multiple Lists in a single Call. We have to add another Method. find below snippet for a Example

public void BulkInsertMultipleLists()
        {
            try
            {
                using (SPSite _oSite = new SPSite("SiteURL"))
                {
                    using (SPWeb _oWeb = _oSite.OpenWeb())
                    {
                        SPList _oList = _oWeb.Lists.TryGetList("MyList");

                        SPList _oList2 = _oWeb.Lists.TryGetList("MyList2");

                        if (_oList != null)
                        {
                            _oWeb.AllowUnsafeUpdates = true;
                            Guid listGuid = _oList.ID;
                            Guid seconGuid = _oList2.ID;
                            StringBuilder query = new StringBuilder();
                            query.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
                            string test = "SomeValue";

                            //Adding First List
                            for (int itemCount = 0; itemCount < 5; itemCount++)
                            {
                                query.AppendFormat("<Method ID=\"{0}\">" +
                                "<SetList>{1}</SetList>" +
                                "<SetVar Name=\"ID\">New</SetVar>" +
                                "<SetVar Name=\"Cmd\">Save</SetVar>" +
                                "<SetVar Name=\"{3}Title\">{2}</SetVar>" +
                                "<SetVar Name=\"{3}Column1\">" + test + "</SetVar>" +
                                "</Method>", "test", listGuid, itemCount, "urn:schemas-microsoft-com:office:office#");
                            }
                            //Adding Second List
                            for (int itemCount = 0; itemCount < 5; itemCount++)
                            {
                                query.AppendFormat("<Method ID=\"{0}\">" +
                                "<SetList>{1}</SetList>" +
                                "<SetVar Name=\"ID\">New</SetVar>" +
                                "<SetVar Name=\"Cmd\">Save</SetVar>" +
                                "<SetVar Name=\"{3}Title\">{2}</SetVar>" +
                                "<SetVar Name=\"{3}Column2\">" + test + "</SetVar>" +
                                "</Method>", "test", seconGuid, itemCount, "urn:schemas-microsoft-com:office:office#");
                            }
                            query.Append("</Batch>");
                            _oWeb.ProcessBatchData(query.ToString());
                            _oWeb.AllowUnsafeUpdates = false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

In XML Definition ,

SetList: SetList element set GUID of SPList .

SetVar element with attribute Name 'cmd': It is 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' : It is 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# indicate particular field of ListItem, should replace by respective field internal name.

Reference Links:

http://apmblog.compuware.com/2009/01/20/sharepoint-using-batch-updates-to-speed-up-performance/
http://stefan-stanev-sharepoint-blog.blogspot.in/2009/07/tips-for-using-spwebprocessbatchdata.html

Thanks.

SharePoint Waves

Bulk Insert/Update/Delete Items from SharePoint List/Library

Related Post

3 comments: