How to Connect MOSS BDC to a Web Service
This article describes how to create a Web Service and connect it to the Microsoft Office SharePoint Server Business Data Catalog (BDC) with MOSS BDC Design Studio.
Overview
In this example we will create a Web Service for the Products table from the popular Northwind database and connect to the BDC.
Step 1 - Create a Web Service
The sample code below shows how to create a Web Service suitable for connecting to the BDC with MOSS BDC Design 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 Schema window to open the Data Source Connection Dialog.
-
Connect Data Source -
Northwind Web Service Opened in Schema Window
Step 2 - Create Product Entity
Use the BDC Entity Wizard to Create the default default options for the Entity.
Start the Wizard
To Start the Wizard drag the Product class from the Schema window onto the design surface with the mouse.
Step through the Wizard
-
Name the Entity -
Select Entity Primary Key -
Select Entity Link Field -
Assign Web Service Methods
Products Entity created
-
Entity displayed in MOSS BDC Design Studio
Step 3 - Generate BDC Xml
Under the Build menu select Build BDC Xml to generate the Xml document required to upload to your MOSS Server.
-
Generated BDC Xml
Step 4 - Upload to your MOSS Server
Open a browser to your MOSS Central Administration Console and locate the Business Data Catalog application to upload the Xml Application definition.