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.
For purpose of this post, I have created a SharePoint list as below called TestInsertion,
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
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 .
In URL give the URL of the SharePoint Service.
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.
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
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.
Now XML document is created and we are due to create XML body representing data to be inserted.
In above code snippet points to be noted are as below,
- We are making call to GetItemsToInsert() function .
- Converting List<Items> to array
- Iterating through each element of array and creating Methods.
- 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.
- 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
For your reference full source code is given below. Feel free to use it and modify it for your requirement
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.
Follow @debugmode_
Leave a comment