Inserting Multiple Rows in SharePoint list using SharePoint 2007 Web Services

Today at work, I come across a very simple requirement to

Insert Multiple Rows in SharePoint list using SharePoint 2007 Services”.

Although this is very common problem and you might get much information while binging on same topic. But to benefit of my readers, I decided to go ahead blog post solution of above problem.  Smile

For purpose of this post, I have created a SharePoint list as below called TestInsertion,

image

We will insert multiple records from a console application. Create a console application and add a DTO [Data Transfer object] class to represent list. If you notice below class, it got properties name same as columns of target list TestInsertion.


public class Items
   {
       public string FirstName { get; set; }
       public string LastName { get; set; }
       public string EmailAddress { get; set; }
       public string Region { get; set; }
   }

Now go ahead and define two global variables in Program.cs

image

Make sure list TestInsertion resides in Yoursubsite [See the Site URL]

Before start writing code to insert multiple rows, we need to ass Web Service in console program. To do that right click on console application and select Add service Reference. Click on Advanced button and select Add Service Reference .

imageimage

In URL give the URL of the SharePoint Service.

image

Assume you have a function returning List<Items> to insert as below. Below static function GetItemsToInsert() is returning List<items> to insert.


public  static List<Items> GetItemsToInsert()
       {
           List<Items> lstItems = new List<Items>
                                 {
                                     new Items
                                     {
                                         FirstName = "John " ,
                                         LastName ="Papa" ,
                                         EmailAddress= "John.papa@blahblah.com",
                                         Region = "USA"
                                     },
                                     new Items
                                     {
                                         FirstName = "Scott" ,
                                         LastName ="Gui" ,
                                         EmailAddress= "Scott.Gui@blahblah.com",
                                         Region = "USA"
                                     },

                                     new Items
                                     {
                                         FirstName = "Dhananjay" ,
                                         LastName ="Kumar" ,
                                         EmailAddress= "Dhananjay.kumar@blahblah.com",
                                         Region = "India"
                                     },
                                     new Items
                                     {
                                         FirstName = "Pinal" ,
                                         LastName ="dave" ,
                                         EmailAddress= "Pinal.dave@blahblah.com",
                                         Region = "India"
                                     },
                                     new Items
                                     {
                                         FirstName = "Victor" ,
                                         LastName ="Gui" ,
                                         EmailAddress= "Victor.Gui@Blahblah.com",
                                         Region = "USA"
                                     },
                                     new Items
                                     {
                                         FirstName = "Sahil" ,
                                         LastName ="Malik" ,
                                         EmailAddress= "sahil.Malik@blahblah.com",
                                         Region = "USA"
                                     },
                                 };
           return lstItems;

       }

To insert record you need to very first create Proxy of the list web service as below. Pass default credential to access the SharePoint Service.

image

After creation of proxy, we need GUID of list and default view of the list. We can get GUID of list and list default view with below code

image

To insert record we need to create a XML document and send that to SharePoint list service. When setting attribute for View, we are passing Guid of list default view fetched in previously.

image

Now XML document is created and we are due to create XML body representing data to be inserted.

image

In above code snippet points to be noted are as below,

  1. We are making call to GetItemsToInsert() function .
  2. Converting List<Items> to array
  3. Iterating through each element of array and creating Methods.
  4. Since we need to insert or add records so cmd value would be New.

Point here need to understand is about Method. If we need to insert 5 records then we need to create 5 methods. So in above code we are creating Method in array loop such that same number of Method would be created as of array count.

  1. We need to make sure that field names are same as internal name of the SharePoint list columns.

As of now we have created the data to be inserted in the list. To do the actual Insertion assign created data as inner XML of XML document and call the UpdateListItem() method of SharePoint Service

image

For your reference full source code is given below. Feel free to use it and modify it for your requirement  Smile

Program.cs


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

namespace InsertingIteminSharePointListRemotely
{

    class Program
    {

         static string SiteUrl = "http://yourserver:12345/Yoursite/Yoursubsite/ /_vti_bin/Lists.asmx";
static string ListName = "TestInsertion";

        static void Main(string[] args)
        {

                #region Creating Proxy for List Web Service

                    webServiceProxy.Lists listService  = new webServiceProxy.Lists();
                    listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
                    listService.Url = SiteUrl;

                #endregion

                #region Getting GUID of List and View

                System.Xml.XmlNode ndListView = listService.GetListAndView(ListName, "");
                 string strListID = ndListView.ChildNodes[0].Attributes["Name"].Value;
                 string strViewID = ndListView.ChildNodes[1].Attributes["Name"].Value;

                #endregion

                #region Creating XML Document

                      XmlDocument docToUpdate = new XmlDocument();
                      XmlElement docToElemnt = docToUpdate.CreateElement("Batch");
                      docToElemnt.SetAttribute("OnError", "Continue");
                      docToElemnt.SetAttribute("ListVersion", "1");
                      docToElemnt.SetAttribute("ViewName", strViewID);

                 #endregion

                #region Creating Data to Insert

                      var result = GetItemsToInsert ().ToArray();
                      string dataToInsert = string.Empty;
                      int id = 0;
                      for (int i = 0; i < result.Length; i++)
                      {
                          id = i + 1;
                          dataToInsert = dataToInsert + "<Method ID='"+id+"' Cmd='New'>";
                          dataToInsert = dataToInsert + "<Field Name=\"FirstName\">"+result[i].FirstName+"</Field>";
                          dataToInsert = dataToInsert + "<Field Name=\"LastName\">"+result[i].LastName+"</Field>";
                          dataToInsert = dataToInsert + "<Field Name=\"EmailAddress\">"+result[i].EmailAddress+"</Field>";
                          dataToInsert = dataToInsert + "<Field Name=\"Region\">"+result[i].Region+"</Field>";
                          dataToInsert = dataToInsert + "</Method>";

                      }

                #endregion

                #region Inserting Record
                      docToElemnt.InnerXml = dataToInsert;
                      try
                      {
                          listService.UpdateListItems(strListID, docToElemnt);
                          Console.WriteLine("Item Inserted");
                      }
                      catch (Exception ex)
                      {
                          Console.WriteLine(ex.StackTrace + ex.Message);
                      }

                #endregion

                      Console.ReadKey(true);
        }
    }
}

I hope this quick post was useful to you.  Smile

 

8 thoughts on “Inserting Multiple Rows in SharePoint list using SharePoint 2007 Web Services

  1. Excellent post, I have a quick query in the above example : In scenario where I am inserting multipul values for look-up field and they have space in the them, what would the be syntax to pass them and wrap them for spaces.

  2. Pingback: Monthly Report June 2011: Total Posts 33 « debug mode……

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s