Abstract: In this article, we will be discussing integration between SharePoint 2010 web site and External Data Source using Visual Studio 2010 Business Data Connectivity Model project template
SharePoint as a web application platform is becoming popular amongst various domains of application development. There are several requirements for integrating a SharePoint 2010 Web Site with an external system which consumes data. If a developer has a strong programming background using Visual Studio 2010, the SharePoint 2010 Templates integrated with VS2010 prove useful to provide such an integrated solution.
In today’s scenario, where SharePoint 2010 based applications are used for automation purpose, similar integration requirements are going to popup every now and then. In VS 2010, the ‘Business Data Connectivity Model’ project template allows developers to design solutions for SharePoint 2010 for External Data Source integration.
In this article, we will be discussing integration between SharePoint 2010 web site and External Data Source using VS 2010 capabilities. You may be having some doubts about whether to use SharePoint 2010 Designer or Visual Studio 2010 for external content types, so here are some differences between them:
Make this project as deploy as farm solution as shown below:
Step 2: In this project, right click and add a new Business Data Connectivity Model, name it as ‘ProductModel:
Once you click OK, a model gets generated as shown below:
If you have worked with ADO.NET Entity Framework, you will find a similar type of entity generated. The reason behind this similar type of representation is because the BDCM is based upon ADO.NET EF. The Identifier1 represents the primary or Identity for the Item from the source. The ReadList method is designed to return collection type of data from the source and ReadItem is defined to return a single record from the source.
Step 3: For this demo, we are going to use LINQ to SQL for connecting to SQL Server database and its ProductInfo table. On the project, right click and add a new LINQ to SQL class as shown below and name it as ‘ProductInfoDataModel’:
Complete the Database connectivity Wizard.
Step 4: Once the DataModel is available with us, it’s time to configure the BCDM. Rename ‘Entity1’ to ‘ProductInfo’ and ‘Identity1’ to ‘ProductID’ and set its type to Int32 as shown below:
The final design will be as shown below:
Step 5: Now is the time for us to define operations (methods) for BCDM. Open BDC Explorer and rename ‘ReadList’ to ‘ReadProductList’, Expand ‘ReadProductList’ and rename the returnParameter from ‘Entity1List’ to ‘ProductList’ as shown below:
This is an out return type from the method. Now right-click on ‘ProductList’ and select ‘properties’, as shown below:
Click on Type Name
Select ‘ProductInfo’, as the method returns a List of ProductInfo and keep the ‘Is Enumerable’ checkbox checked, which indicates that the collection will be returned from the method.
Expand ProductInfo and you will find ‘Identifier1’ and ‘Message’ properties there, so change them to ‘ProductInfo’ properties. Right-Click on ‘ProductInfo’ and select ‘Add Type Descriptor’ and add ProductID, ProductName,Manifacturer and Price in it and set its data types. The final design will be as shown below:
Step 6: Now repeat similar steps for ‘ReadItem’ method and rename it to ‘ReadProduct’. To define ‘id’ and ‘return’ parameter, copy the ProductInfo from ‘ReadProductList’ and paste in ‘returnParameter’ of ‘ReadProduct’. Just click on ‘Yes’ in the message box that appears
Also copy ‘ProductID’ from the returnParameter of the ReadProductList and paste it in the ‘id’ of the ‘ReadProduct’ method. You will see a similar message as shown above for replacing Type Descriptor, just click ‘Yes’
Step 7: Click on ‘ProductInfo’ in BDCM designer, the method info will be as shown below:
Step 8: Now we need to add Create, Update and Delete method. So click on the ‘Add to Method’ at the bottom:
You will get a List of methods:
Add Creator, Updater and Deleter methods and rename them as ‘CreateProductInfo’, ‘UpdateProductInfo’ and ‘DeleteProductInfo’
Step 9: Now its time for us to write the code for database connectivity and implement Create, Read, ReadList, Update and Delete methods. Now the question is how to configure the Database ConnectionString to BDCM and access it in code. Go to the BDC Explorer and select ‘LocalSystemInstances’, rename ‘ProductModel’ to ‘productModelDBConnect’, go to the properties and select ‘Custom Properties’ and add a connection string as shown below:
In the project, add a reference to ‘Microsoft.BusinessData’ assembly from the following path:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.BusinessData.dll
Step 9: Open ‘ProductInfoService.cs’ and write the following code which performs CRUD operations:
using System;
using System.Collections.Generic;
using System.Linq;
using SPS_ECM_ProductList;
using Microsoft.BusinessData.SystemSpecific;
namespace SPS_ECM_ProductList.ProductModel
{
/// <summary>
/// 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.
/// </summary>
public class ProductInfoService : IContextProperty
{
internal string AccessConnectionString()
{
Microsoft.BusinessData.MetadataModel.Collections.INamedPropertyDictionary dictionary =
this.LobSystemInstance.GetProperties();
if (dictionary.ContainsKey("CompanyConnStr"))
{
return dictionary["CompanyConnStr"].ToString();
}
return null;
}
/// <summary>
/// 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.
/// </summary>
/// <param name="id"></param>
/// <returns>Entity1</returns>
public ProductInfo ReadProduct(int id)
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == id);
return Product;
}
/// <summary>
/// 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.
/// </summary>
/// <returns>IEnumerable of Entities</returns>
public IEnumerable<ProductInfo> ReadProductList()
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
return objContext.ProductInfos.ToList();
}
public ProductInfo CreateProductInfo(ProductInfo newProductInfo)
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
objContext.ProductInfos.InsertOnSubmit(newProductInfo);
objContext.SubmitChanges();
ProductInfo Product =
objContext.ProductInfos.Single(p => p.ProductID == newProductInfo.ProductID);
return Product;
}
public void UpdateProductInfo(ProductInfo productInfo)
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == productInfo.ProductID);
Product.ProductName = productInfo.ProductName;
Product.Manifacturer = productInfo.Manifacturer;
Product.Price = productInfo.Price;
objContext.SubmitChanges();
}
public void DeleteProductInfo(int productID)
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == productID);
objContext.ProductInfos.DeleteOnSubmit(Product);
objContext.SubmitChanges();
}
public Microsoft.BusinessData.Runtime.IExecutionContext ExecutionContext
{
get;
set;
}
public Microsoft.BusinessData.MetadataModel.ILobSystemInstance LobSystemInstance
{
get;
set;
}
public Microsoft.BusinessData.MetadataModel.IMethodInstance MethodInstance
{
get;
set;
}
}
}
The above code shows that an interface ‘IContextProperty’ is implemented which can access the Business Data Connectivity service. The method ‘AccessConnectionString’ reads the ConnectionString from the ‘LocSystemInstance’ properties collection using the Key of the Connection string set in Step 8.
Step 10: Open Feature1.Template.xml and add the following Xml which helps in deploying the application on the web site.
<Properties>
<Property Key="SiteUrl" Value="hxxp://MyServer/sites/ss"/>
</Properties>
Step 11: Deploy the application and using F5 to run it. This will start the SharePoint web site. To display the ProductInfo List on the Quick launch, click on ‘Site Actions’ on the top-left and select ‘More Options’ as shown below
This action will open the ‘Create’ window. Select ‘Data’ from Filter type and select ‘External List’ as shown below:
Click on the ‘Create’ button. A new Window will open where you need to set the Name of the List and also choose the External Content type to use as the Data source for the list:
This will open the ‘External Content Type Picker’ window. Select ‘ProductModelDBConnect’ and click on ’OK’:
This takes you back to the previous window where you Click on ‘Create’ and a new List will be created on the Quick Launch bar of the site.
Step 13: Now to set the access rights from the SharePoint Site to External Data Source, add the current login user from the List and set its access rights. You can do this using the ‘Central Administration Web Site’. Select ‘Application Management’ > Service Applications > Manage Service Applications > Business Data Connectivity Service. Here Select ‘ProductInfo’ checkbox and click on ‘Set Objects Permissions ’:
Add the User and set his/her rights as shown below :
This is required because the connection will be made from your Web Site hosted in IIS, to the External Data Source using Business Data Connectivity Web Service. So to authenticate and authorize the user, we need to set the access rights. Click on Ok.
Step 14: Return to the site and click on the ‘ProductInfoList’ (note: Your application in VS2010 must be running). A List Data appears:
Now you can test your Insert, Update and Delete operations.
Note: You must deploy the project assembly created in VS2010 in GAC so that it can be used by Web Site for performing CRUD operations.
Conclusion: Using VS 2010 SharePoint template, it is easy to design more controlled source code for Business Data Connectivity model using sophisticated programming methodology. Here a developer has complete freedom to implement domain specific logic to establish connectivity to an external data system from the SharePoint Web site.
In today’s scenario, where SharePoint 2010 based applications are used for automation purpose, similar integration requirements are going to popup every now and then. In VS 2010, the ‘Business Data Connectivity Model’ project template allows developers to design solutions for SharePoint 2010 for External Data Source integration.
In this article, we will be discussing integration between SharePoint 2010 web site and External Data Source using VS 2010 capabilities. You may be having some doubts about whether to use SharePoint 2010 Designer or Visual Studio 2010 for external content types, so here are some differences between them:
- SharePoint Designer 2010 directly connects to the SharePoint 2010 site and external content type is directly written in the business data store. Unlike SPS 2010 Designer, VS2010, creates a model file with extension “.bdcm”. This file defines all external content types. This file then gets packaged into the project output assembly and then gets deployed as .WSP file in SharePoint 2010.
- VS 2010 allows External Content Types (having data provided by .NET assemblies), which makes the code in the assembly as a proxy for External Data communication. Such an approach allows developer to add custom security code, additional business rules for working with more than one data source at a time.
Working with External Data Communication using VS 2010
Step 1: Open VS2010 and Create a new Empty SharePoint Project, name it as ‘SPS_ECM_ProductList’. Make this project as deploy as farm solution as shown below:
Step 2: In this project, right click and add a new Business Data Connectivity Model, name it as ‘ProductModel:
Once you click OK, a model gets generated as shown below:
If you have worked with ADO.NET Entity Framework, you will find a similar type of entity generated. The reason behind this similar type of representation is because the BDCM is based upon ADO.NET EF. The Identifier1 represents the primary or Identity for the Item from the source. The ReadList method is designed to return collection type of data from the source and ReadItem is defined to return a single record from the source.
Step 3: For this demo, we are going to use LINQ to SQL for connecting to SQL Server database and its ProductInfo table. On the project, right click and add a new LINQ to SQL class as shown below and name it as ‘ProductInfoDataModel’:
Complete the Database connectivity Wizard.
Step 4: Once the DataModel is available with us, it’s time to configure the BCDM. Rename ‘Entity1’ to ‘ProductInfo’ and ‘Identity1’ to ‘ProductID’ and set its type to Int32 as shown below:
The final design will be as shown below:
Step 5: Now is the time for us to define operations (methods) for BCDM. Open BDC Explorer and rename ‘ReadList’ to ‘ReadProductList’, Expand ‘ReadProductList’ and rename the returnParameter from ‘Entity1List’ to ‘ProductList’ as shown below:
This is an out return type from the method. Now right-click on ‘ProductList’ and select ‘properties’, as shown below:
Click on Type Name
Select ‘ProductInfo’, as the method returns a List of ProductInfo and keep the ‘Is Enumerable’ checkbox checked, which indicates that the collection will be returned from the method.
Expand ProductInfo and you will find ‘Identifier1’ and ‘Message’ properties there, so change them to ‘ProductInfo’ properties. Right-Click on ‘ProductInfo’ and select ‘Add Type Descriptor’ and add ProductID, ProductName,Manifacturer and Price in it and set its data types. The final design will be as shown below:
Step 6: Now repeat similar steps for ‘ReadItem’ method and rename it to ‘ReadProduct’. To define ‘id’ and ‘return’ parameter, copy the ProductInfo from ‘ReadProductList’ and paste in ‘returnParameter’ of ‘ReadProduct’. Just click on ‘Yes’ in the message box that appears
Also copy ‘ProductID’ from the returnParameter of the ReadProductList and paste it in the ‘id’ of the ‘ReadProduct’ method. You will see a similar message as shown above for replacing Type Descriptor, just click ‘Yes’
Step 7: Click on ‘ProductInfo’ in BDCM designer, the method info will be as shown below:
Step 8: Now we need to add Create, Update and Delete method. So click on the ‘Add to Method’ at the bottom:
You will get a List of methods:
Add Creator, Updater and Deleter methods and rename them as ‘CreateProductInfo’, ‘UpdateProductInfo’ and ‘DeleteProductInfo’
Step 9: Now its time for us to write the code for database connectivity and implement Create, Read, ReadList, Update and Delete methods. Now the question is how to configure the Database ConnectionString to BDCM and access it in code. Go to the BDC Explorer and select ‘LocalSystemInstances’, rename ‘ProductModel’ to ‘productModelDBConnect’, go to the properties and select ‘Custom Properties’ and add a connection string as shown below:
In the project, add a reference to ‘Microsoft.BusinessData’ assembly from the following path:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.BusinessData.dll
Step 9: Open ‘ProductInfoService.cs’ and write the following code which performs CRUD operations:
using System;
using System.Collections.Generic;
using System.Linq;
using SPS_ECM_ProductList;
using Microsoft.BusinessData.SystemSpecific;
namespace SPS_ECM_ProductList.ProductModel
{
/// <summary>
/// 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.
/// </summary>
public class ProductInfoService : IContextProperty
{
internal string AccessConnectionString()
{
Microsoft.BusinessData.MetadataModel.Collections.INamedPropertyDictionary dictionary =
this.LobSystemInstance.GetProperties();
if (dictionary.ContainsKey("CompanyConnStr"))
{
return dictionary["CompanyConnStr"].ToString();
}
return null;
}
/// <summary>
/// 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.
/// </summary>
/// <param name="id"></param>
/// <returns>Entity1</returns>
public ProductInfo ReadProduct(int id)
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == id);
return Product;
}
/// <summary>
/// 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.
/// </summary>
/// <returns>IEnumerable of Entities</returns>
public IEnumerable<ProductInfo> ReadProductList()
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
return objContext.ProductInfos.ToList();
}
public ProductInfo CreateProductInfo(ProductInfo newProductInfo)
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
objContext.ProductInfos.InsertOnSubmit(newProductInfo);
objContext.SubmitChanges();
ProductInfo Product =
objContext.ProductInfos.Single(p => p.ProductID == newProductInfo.ProductID);
return Product;
}
public void UpdateProductInfo(ProductInfo productInfo)
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == productInfo.ProductID);
Product.ProductName = productInfo.ProductName;
Product.Manifacturer = productInfo.Manifacturer;
Product.Price = productInfo.Price;
objContext.SubmitChanges();
}
public void DeleteProductInfo(int productID)
{
ProductInfoDataModelDataContext objContext =
new ProductInfoDataModelDataContext(AccessConnectionString());
ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == productID);
objContext.ProductInfos.DeleteOnSubmit(Product);
objContext.SubmitChanges();
}
public Microsoft.BusinessData.Runtime.IExecutionContext ExecutionContext
{
get;
set;
}
public Microsoft.BusinessData.MetadataModel.ILobSystemInstance LobSystemInstance
{
get;
set;
}
public Microsoft.BusinessData.MetadataModel.IMethodInstance MethodInstance
{
get;
set;
}
}
}
The above code shows that an interface ‘IContextProperty’ is implemented which can access the Business Data Connectivity service. The method ‘AccessConnectionString’ reads the ConnectionString from the ‘LocSystemInstance’ properties collection using the Key of the Connection string set in Step 8.
Step 10: Open Feature1.Template.xml and add the following Xml which helps in deploying the application on the web site.
<Properties>
<Property Key="SiteUrl" Value="hxxp://MyServer/sites/ss"/>
</Properties>
Step 11: Deploy the application and using F5 to run it. This will start the SharePoint web site. To display the ProductInfo List on the Quick launch, click on ‘Site Actions’ on the top-left and select ‘More Options’ as shown below
This action will open the ‘Create’ window. Select ‘Data’ from Filter type and select ‘External List’ as shown below:
Click on the ‘Create’ button. A new Window will open where you need to set the Name of the List and also choose the External Content type to use as the Data source for the list:
This will open the ‘External Content Type Picker’ window. Select ‘ProductModelDBConnect’ and click on ’OK’:
This takes you back to the previous window where you Click on ‘Create’ and a new List will be created on the Quick Launch bar of the site.
Step 13: Now to set the access rights from the SharePoint Site to External Data Source, add the current login user from the List and set its access rights. You can do this using the ‘Central Administration Web Site’. Select ‘Application Management’ > Service Applications > Manage Service Applications > Business Data Connectivity Service. Here Select ‘ProductInfo’ checkbox and click on ‘Set Objects Permissions ’:
Add the User and set his/her rights as shown below :
This is required because the connection will be made from your Web Site hosted in IIS, to the External Data Source using Business Data Connectivity Web Service. So to authenticate and authorize the user, we need to set the access rights. Click on Ok.
Step 14: Return to the site and click on the ‘ProductInfoList’ (note: Your application in VS2010 must be running). A List Data appears:
Now you can test your Insert, Update and Delete operations.
Note: You must deploy the project assembly created in VS2010 in GAC so that it can be used by Web Site for performing CRUD operations.
Conclusion: Using VS 2010 SharePoint template, it is easy to design more controlled source code for Business Data Connectivity model using sophisticated programming methodology. Here a developer has complete freedom to implement domain specific logic to establish connectivity to an external data system from the SharePoint Web site.
No comments:
Post a Comment