How to Synchronise a Web Service to a SharePoint List
This article describes how to create a Web Service and synchronise the data with a List in SharePoint.
Overview
In this example we will create a Web Service for the Products table from the popular Northwind database.
Step 1 - Create a Web Service
The sample code below shows how to create a Web Service suitable for synchronising with Data Synchronisation Studio. Download Full Source Code
The Web Service Object
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using System.Collections.Generic;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.ComponentModel;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
namespace NorthwindService
{
[WebService(Namespace = "http://simego.com/webservices")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
public class Services : System.Web.Services.WebService
{
private string ConnectionString
{
get
{
AppSettingsReader reader = new AppSettingsReader();
return (string)reader.GetValue("ConnectionString", typeof(string));
}
}
[WebMethod(Description="Returns a List of Products IDs")]
public List<int> GetProductEnumeratorIDs()
{
List<int> retVal = new List<int>();
string sql = "SELECT [ProductID] FROM [Products]";
SqlDataReader reader = null;
try
{
reader = SqlHelper.ExecuteReader(this.ConnectionString, CommandType.Text, sql);
if (reader.HasRows)
{
while (reader.Read())
{
retVal.Add(reader.GetInt32(0));
}
}
}
catch
{
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
return retVal;
}
[WebMethod(Description="Returns a single Product Entity by ID")]
public Product GetProduct(int productID)
{
string sql = "SELECT [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued] FROM [Products] WHERE [ProductID] = @ProductID";
SqlDataReader reader = null;
try
{
reader = SqlHelper.ExecuteReader(this.ConnectionString,
CommandType.Text, sql,
new SqlParameter[] {
new SqlParameter("@ProductID", productID) });
if (reader.HasRows)
{
reader.Read();
return new Product(reader);
}
}
catch
{
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
return null;
}
[WebMethod(Description="Returns a List of Products Filtered By Name")]
public List<Product> GetProductsByName(string productName)
{
List<Product> retVal = new List<Product>();
string sql = "SELECT [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued] FROM [Products] WHERE [ProductName] Like @ProductName";
SqlDataReader reader = null;
try
{
reader = SqlHelper.ExecuteReader(this.ConnectionString,
CommandType.Text, sql,
new SqlParameter[] {
new SqlParameter("@ProductName", string.Format("%{0}%", productName)) });
if (reader.HasRows)
{
while (reader.Read())
{
retVal.Add(new Product(reader));
}
}
}
catch
{
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
return retVal;
}
[WebMethod(Description = "Returns a List of Products")]
public List<Product> GetProducts()
{
List<Product> retVal = new List<Product>();
string sql = "SELECT [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued] FROM [Products]";
SqlDataReader reader = null;
try
{
reader = SqlHelper.ExecuteReader(this.ConnectionString,
CommandType.Text, sql);
if (reader.HasRows)
{
while (reader.Read())
{
retVal.Add(new Product(reader));
}
}
}
catch
{
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
return retVal;
}
}
}
The Product Object
using System;
using System.Data;
using System.Collections;
namespace NorthwindService
{
[Serializable]
public class Product {
#region Variables
protected int m_ProductID;
protected string m_ProductName;
protected int m_SupplierID;
protected int m_CategoryID;
protected string m_QuantityPerUnit;
protected double m_UnitPrice;
protected short m_UnitsInStock;
protected short m_UnitsOnOrder;
protected short m_ReorderLevel;
protected bool m_Discontinued;
#endregion
#region Properties
public int ProductID {
get { return(m_ProductID); }
set { m_ProductID = value; }
}
public string ProductName {
get { return(m_ProductName); }
set { m_ProductName = value; }
}
public int SupplierID {
get { return(m_SupplierID); }
set { m_SupplierID = value; }
}
public int CategoryID {
get { return(m_CategoryID); }
set { m_CategoryID = value; }
}
public string QuantityPerUnit {
get { return(m_QuantityPerUnit); }
set { m_QuantityPerUnit = value; }
}
public double UnitPrice {
get { return(m_UnitPrice); }
set { m_UnitPrice = value; }
}
public short UnitsInStock {
get { return(m_UnitsInStock); }
set { m_UnitsInStock = value; }
}
public short UnitsOnOrder {
get { return(m_UnitsOnOrder); }
set { m_UnitsOnOrder = value; }
}
public short ReorderLevel {
get { return(m_ReorderLevel); }
set { m_ReorderLevel = value; }
}
public bool Discontinued {
get { return(m_Discontinued); }
set { m_Discontinued = value; }
}
#endregion
public Product() {
}
public Product(IDataRecord record) {
this.Fill(record);
}
internal void Fill(IDataRecord record) {
#region Read Data
m_ProductID = (int)record["ProductID"];
m_ProductName = (string)record["ProductName"];
if ( record["SupplierID"] != DBNull.Value ) {
m_SupplierID = (int)record["SupplierID"];
}
if ( record["CategoryID"] != DBNull.Value ) {
m_CategoryID = (int)record["CategoryID"];
}
if ( record["QuantityPerUnit"] != DBNull.Value ) {
m_QuantityPerUnit = (string)record["QuantityPerUnit"];
}
if ( record["UnitPrice"] != DBNull.Value ) {
m_UnitPrice = Convert.ToDouble(record["UnitPrice"]);
}
if ( record["UnitsInStock"] != DBNull.Value ) {
m_UnitsInStock = Convert.ToInt16(record["UnitsInStock"]);
}
if ( record["UnitsOnOrder"] != DBNull.Value ) {
m_UnitsOnOrder = Convert.ToInt16(record["UnitsOnOrder"]);
}
if ( record["ReorderLevel"] != DBNull.Value ) {
m_ReorderLevel = Convert.ToInt16(record["ReorderLevel"]);
}
m_Discontinued = (bool)record["Discontinued"];
#endregion
}
}
}
Step 2 - Connect to the Web Service
Choose the Connect Data Source link in the Data Source (A) Schema window to open the Data Source Connection Dialog.
-
Enter the Url to your Web Service -
Select the Web Service Method that returns your data
in this example GetProducts.
Step 3 - Modify Schema Map
We need to modify the Schema Map and set the Product ID column to be the Key Column and uncheck the Allow Null option. This is because the Web Service does not describe this behaviour in the WSDL.
-
Update the Schema Map
Step 4 - Create SharePoint List
Use the SharePoint List Wizard to Create a SharePoint List from the Schema loaded by Data Synchronisation Studio.
Start the Wizard
Step through the Wizard
-
Select SharePoint Server -
Select SharePoint Site -
Select List Name
Step 5 - Compare A->B and Synchronise
Run an initial compare to look for the new records that need to be added to SharePoint in this case we should see all records as the SharePoint list contains no records.
-
Compare Results -
Start a Synchronisation -
Product List in SharePoint Populated
