Archive | BCS RSS for this section

Sharepoint 2013 BCS in Visual Studio 2012

Sharepoint 2013 BCS in Visual Studio 2012

When we need to integrate external data sources in SharePoint, one recommended way is using BCS services (BDC).

BCS can be very useful when we need to create a BackOffice in SharePoint 2013 to manage data from an external relational database.

In this post I’ll talk about how to create a BCS Model to manage an entity (Product) in an external SQL database, using Visual Studio 2012).

BCS0

I started creating an Empty SharePoint 2013 project and selected the farm deployment.

Then I add to my project a new Item (Business Data Connectivity Model), named it BdcProduct:

BCS1

By default, the Business Data Connectivity Model adds Entity1.cs and Entity1Service.cs classes. At this point, if you deploy your solution, you can test this BCS Entity1 (I think this is a good test to start).

After that I renamed the classes Entity1.cs and Entity1Service.sc to Product.cs and Product1.cs and started changing the BdcProduct.bdcm to have the properties and methods that I needed:

BDC5

After that I edited my BdcProduct.bdcm with XML editor to check that I didn’t miss anything. My BdcProduct.bdcm is like:


<?xml version="1.0" encoding="utf-8"?>
<Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog" Name="BdcProduct">
  <LobSystems>
    <LobSystem Name="BdcProduct" Type="DotNetAssembly">
      <LobSystemInstances>
        <LobSystemInstance Name="BdcProduct" />
      </LobSystemInstances>
      <Entities>
        <Entity Name="Product" Namespace="BCSSample.BdcProduct" EstimatedInstanceCount="1000" Version="1.0.0.53">
          <Properties>
            <Property Name="Class" Type="System.String">BCSSample.BdcProduct.ProductService, BdcProduct</Property>
          </Properties>
          <Identifiers>
            <Identifier Name="ID" TypeName="System.Int32" />
            <!-- TODO: Change the name of the ID and if needed the TypeName of your identifier. -->
          </Identifiers>
          <Methods>
            <Method Name="ReadList">
              <Parameters>
                <Parameter Name="productList" Direction="Return">
                  <TypeDescriptor Name="ProductList" TypeName="System.Collections.Generic.IEnumerable`1[[BCSSample.BdcProduct.Product, BdcProduct]]" IsCollection="true">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="BCSSample.BdcProduct.Product, BdcProduct" Name="Product">
                        <TypeDescriptors>
                          <TypeDescriptor Name="ID" TypeName="System.Int32" IsCollection="false" IdentifierName="ID" LobName="ID" />
                          <TypeDescriptor Name="Name" TypeName="System.String" LobName="Name" />
                          <TypeDescriptor Name="Description" TypeName="System.String" LobName="Description" /></TypeDescriptors></TypeDescriptor></TypeDescriptors></TypeDescriptor></Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="ReadList" Type="Finder" ReturnParameterName="productList" />
              </MethodInstances></Method>
            <Method Name="ReadItem">
              <Parameters>
                <Parameter Name="product" Direction="Return">
                  <TypeDescriptor TypeName="BCSSample.BdcProduct.Product, BdcProduct" Name="Product">
                    <TypeDescriptors>
                      <TypeDescriptor Name="ID" TypeName="System.Int32" IsCollection="false" IdentifierName="ID" LobName="ID" />
                      <TypeDescriptor Name="Name" TypeName="System.String" LobName="Name" />
                      <TypeDescriptor Name="Description" TypeName="System.String" LobName="Description" /></TypeDescriptors></TypeDescriptor></Parameter>
                <Parameter Name="ID" Direction="In">
                  <TypeDescriptor Name="ID" TypeName="System.Int32" IdentifierName="ID" /></Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="ReadItem" Type="SpecificFinder" ReturnParameterName="product" />
              </MethodInstances></Method>
            <Method Name="GetConnection" />
            <Method Name="Create">
              <Parameters>
                <Parameter Name="returnProduct" Direction="Return">
                  <TypeDescriptor Name="ReturnProduct" TypeName="BCSSample.BdcProduct.Product, BdcProduct">
                    <TypeDescriptors>
                      <TypeDescriptor Name="ID" LobName="ID" IdentifierName="ID" IsCollection="false" TypeName="System.Int32" />
                      <TypeDescriptor Name="Name" LobName="Name" TypeName="System.String" />
                      <TypeDescriptor Name="Description" LobName="Description" TypeName="System.String" /></TypeDescriptors></TypeDescriptor></Parameter>
                <Parameter Name="newProduct" Direction="In">
                  <TypeDescriptor Name="NewProduct" TypeName="BCSSample.BdcProduct.Product, BdcProduct">
                    <TypeDescriptors>
                      <TypeDescriptor Name="Name" LobName="Name" TypeName="System.String" CreatorField="true" />
                      <TypeDescriptor Name="Description" LobName="Description" TypeName="System.String" CreatorField="true" /></TypeDescriptors></TypeDescriptor></Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="Create" Type="Creator" ReturnParameterName="returnProduct" ReturnTypeDescriptorPath="ReturnProduct" />
              </MethodInstances></Method>
            <Method Name="Delete">
              <Parameters>
                <Parameter Name="iD" Direction="InOut">
                  <TypeDescriptor Name="ID" TypeName="System.Int32" IdentifierEntityName="Product" IdentifierEntityNamespace="BCSSample.BdcProduct" IdentifierName="ID" /></Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="Delete" Type="Deleter" />
              </MethodInstances></Method>
            <Method Name="Update">
              <Parameters>
                <Parameter Name="product" Direction="In">
                  <TypeDescriptor Name="Product" TypeName="BCSSample.BdcProduct.Product, BdcProduct">
                    <TypeDescriptors>
                      <TypeDescriptor Name="Name" LobName="Name" TypeName="System.String" UpdaterField="true" />
                      <TypeDescriptor Name="Description" LobName="Description" TypeName="System.String" UpdaterField="true" />
                      <TypeDescriptor Name="ID" TypeName="System.Int32" IsCollection="false" ReadOnly="false" UpdaterField="true" PreUpdaterField="true" /></TypeDescriptors></TypeDescriptor></Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="Update" Type="Updater" />
              </MethodInstances></Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>
  </LobSystems>
</Model>

After define the Properties and Methods we  need to code to define how and where we’ll get our data.

In this sample I used LINQ to SQL, so I add a new item of this type to my project:

BCS6

To simplify my job, I copied the code from partial classe Product (added automatically by LINQ to SQL), to my BDC class Product.cs and deleteted the LINQ to SQL Product partial class.

My BDC Product.cs is:

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

namespace BCSSample.BdcProduct
{
    ///
/// This class contains the properties for Entity1. The properties keep the data for Entity1.
    /// If you want to rename the class, don't forget to rename the entity in the model xml as well.
    ///
        //TODO: Implement additional properties here. The property Message is just a sample how a property could look like.

    [global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Product")]
	public partial class Product
	{

		private int _ID;

		private string _Name;

		private string _Description;

		public Product()
		{
		}

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_ID",  DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
   	    public int ID
		{
			get
			{
				return this._ID;
			}
			set
			{
				if ((this._ID != value))
				{
					this._ID = value;
				}
			}
		}

		[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Name", DbType="VarChar(250) NOT NULL", CanBeNull=false)]
		public string Name
		{
			get
			{
				return this._Name;
			}
			set
			{
				if ((this._Name != value))
				{
					this._Name = value;
				}
			}
		}

		[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Description", DbType="VarChar(250)")]
		public string Description
		{
			get
			{
				return this._Description;
			}
			set
			{
				if ((this._Description != value))
				{
					this._Description = value;
				}
			}
		}
	}
}
 

After that I created a ProductManager class to implement my methods:

 using BCSSample.BdcProduct;
using System.Collections.Generic;
using System.Linq;
using BCSSample;

namespace BCSSample
{
    public  class ProductManager
    {
        public string strCon = BCSSample.BdcProduct.ProductService.GetConnection();

        public Product[] GetProducts()
        {
            var products = new List();

            using (ProductDataDataContext ctx = new ProductDataDataContext(strCon))
            {
                products = (from prd in ctx.Products
                            select prd).ToList();
            }
            return products.ToArray();
        }

        public Product GetProductById(int productId)
        {
            var product = new Product();

            using (ProductDataDataContext ctx = new ProductDataDataContext(strCon))
            {
                product = (from prod in ctx.Products
                           where prod.ID == productId
                           select prod).First();
            }

            return product;
        }

        public void UpdateProduct(Product product)
        {
            var productDB = new Product();

            using (ProductDataDataContext ctx = new ProductDataDataContext(strCon))
            {
                productDB = (from prd in ctx.Products
                             where prd.ID == product.ID
                             select prd).First();

                // Alters the object
                productDB.Name = product.Name;
                productDB.Description = product.Description;

                ctx.Refresh(System.Data.Linq.RefreshMode.KeepChanges, productDB);
                ctx.SubmitChanges();
            }
        }

        public void AddProduct(Product product)
        {
            using (ProductDataDataContext ctx = new ProductDataDataContext(strCon))
            {
                ctx.Products.InsertOnSubmit(product);
                 ctx.SubmitChanges();
            }
        }

        public void DeleteProduct(int productId)
        {
            using (ProductDataDataContext ctx = new ProductDataDataContext(strCon))
            {
                var product = (from prd in ctx.Products
                               where prd.ID == productId
                               select prd).First();
                ctx.Products.DeleteOnSubmit(product);
                ctx.SubmitChanges();
            }
        }

    }
}
 

And then changed my ProductService class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.BusinessData.MetadataModel;
using Microsoft.BusinessData.SystemSpecific;
using Microsoft.BusinessData.Runtime;
using Microsoft.BusinessData.MetadataModel.Collections;

namespace BCSSample.BdcProduct
{
    ///
/// All the methods for retrieving, updating and deleting data are implemented in this class file.
    /// The samples below show the finder and specific finder method for Entity1.
    ///
    public class ProductService : IContextProperty
    {

        private static IMethodInstance methodInstance;
        private static ILobSystemInstance lobSystem;
        private static IExecutionContext executionContext;
        public IMethodInstance MethodInstance
        {
            get { return methodInstance; }
            set { methodInstance = value; }
        }

        public ILobSystemInstance LobSystemInstance
        {
            get { return lobSystem; }
            set { lobSystem = value; }
        }

        public IExecutionContext ExecutionContext
        {
            get { return executionContext; }
            set { executionContext = value; }
        }

        public static string GetConnection()
        {

            INamedPropertyDictionary properties = lobSystem.GetLobSystem().GetProperties();

            string template = "Data Source={0};Initial Catalog={1};Integrated Security={2};";

            string dataSource = "RdbConnection Data Source";
            string initialCatalog = "RdbConnection Initial Catalog";
            string integratedSecurity = "RdbConnection Integrated Security";

            if (!properties.ContainsKey(dataSource) || !properties.ContainsKey(initialCatalog) || !properties.ContainsKey(integratedSecurity))
                throw new Exception("LobSystemInstance does not contain a connection string");

            return string.Format(template, properties[dataSource].ToString(), properties[initialCatalog].ToString(), properties[integratedSecurity].ToString());

        }

        ///
/// This is a sample specific finder method for Entity1.
        /// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
        ///
        ///
        /// Entity1
        public static Product ReadItem(int id)
        {

            //TODO: This is just a sample. Replace this simple sample with valid code.
            ProductManager prdmgr = new ProductManager();
            Product product = prdmgr.GetProductById(id);
            return product;

        }
        ///
/// This is a sample finder method for Entity1.
        /// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
        ///
        /// IEnumerable of Entities
        public static IEnumerable ReadList()
        {

            //TODO: This is just a sample. Replace this simple sample with valid code.
            ProductManager prdmgr = new ProductManager();
            Product[] productList = prdmgr.GetProducts();
            return productList;

        }

        public static Product Create(Product newProduct)
        {
            if (string.IsNullOrEmpty(newProduct.Name))
                throw new ObjectNotFoundException("Product Name is mandatory.");
            ProductManager prdmgr = new ProductManager();
            prdmgr.AddProduct(newProduct);
            return newProduct;

        }

        public static void Delete(ref int iD)
        {
            ProductManager prdmgr = new ProductManager();
            prdmgr.DeleteProduct(iD);

        }

        public static void Update(Product product)
        {
            if (string.IsNullOrEmpty(product.Name))
                throw new ObjectNotFoundException("Product Name is mandatory.");
            ProductManager prdmgr = new ProductManager();

            prdmgr.UpdateProduct(product);

        }

    }
}
 

I add a new item (BdcResource1.bdcr) to my project to define connection properties:

<?xml version="1.0" encoding="utf-8"?>
<!-- TODO: Replace the $ModelName$ and $LobSystemName$ with your model name and <?xml version="1.0" encoding="utf-8"?>
<!-- TODO: Replace the $ModelName$ and $LobSystemName$ with your model name and LobSystem name respectively. -->
<Model xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog/Resources" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Name="BdcProduct">
    <LobSystems>
        <LobSystem Name="BdcProduct">
          <Properties>
            <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
            <!-- AuthenticationMode can be set to PassThrough, RevertToSelf, RdbCredentials, or WindowsCredentials. -->
            <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
            <!-- Options:  SQL Server, OleDB, Oracle, or ODBC. -->
            <Property Name="RdbConnection Data Source" Type="System.String">WIN-F9PITQQTMF3</Property>
            <!-- Type the database server name or the SQL Server instance name in the format SQLServer\Instance. -->
            <Property Name="RdbConnection Initial Catalog" Type="System.String">SampleDatabse</Property>
            <!-- Type the database name. -->
            <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
            <!-- Type SSPI for Integrated Security. -->
            <Property Name="RdbConnection Pooling" Type="System.String">false</Property>
            <!-- Type true or false for Pooling -->
          </Properties>
        </LobSystem>
    </LobSystems>
</Model> 

 

After that I deployed my solution and test it.

Created  an External List:

BDC2

Selected my new External Content type:

BDC3

Started using my new list:

BDC4

References: http://msdn.microsoft.com/en-us/library/jj163782.aspx