Today at work, I come across a very simple requirement to "Insert Multiple Rows into a SharePoint list using SharePoint 2007 Services". Although this is a very common problem and you might get much information by Binging the topic, for the benefit of my readers I decided to go ahead and create this article with a solution of the 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 the list. Note that the following class has properties with the same name as columns of the 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 that the list TestInsertion resides in your subsite (See the Site URL).
Before starting to write code to insert multiple rows, we need to add a Web Service into the console program. To do that right-click on the console application and select Add Service Reference. Click on the 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. The following 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= "[email protected]",
Region = "USA"
},
new Items
{
FirstName = "Scott" ,
LastName ="Gui" ,
EmailAddress= "[email protected]",
Region = "USA"
},
new Items
{
FirstName = "Dhananjay" ,
LastName ="Kumar" ,
EmailAddress= "[email protected]",
Region = "India"
},
new Items
{
FirstName = "Pinal" ,
LastName ="dave" ,
EmailAddress= "[email protected]",
Region = "India"
},
new Items
{
FirstName = "Victor" ,
LastName ="Gui" ,
EmailAddress= "[email protected]",
Region = "USA"
},
new Items
{ FirstName = "Sahil" ,
LastName ="Malik" ,
EmailAddress= "[email protected]",
Region = "USA"
},
};
return lstItems;
}
To insert a record you need to first create a Proxy of the list web service as below. Pass default credentials 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 following 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 the above code snippet, points to be noted are as below:
- We are making a call to the GetItemsToInsert() function.
- Converting List<Items> to array
- Iterating through each element of array and creating Methods.
- Since we need to insert or add records, the cmd value would be New.
A point here needed to be understood is about Method. If we need to insert 5 records then we need to create 5 methods. So in the above code we are creating a method in the array loop such that the same number of methods would be created as of array counts.
- We need to make sure that field names are the same as internal names 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 the 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 article was useful to you.