SharePoint 2010: Programming with Business Data Connectivity Model Using Visual Studio 2010

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:

• 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.

For this application, I am using an already existing site ‘hxxp://win-v04rsve054t/sites/ss’. The SQL Server Database used here is ‘Company’ and the table is as shown below

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.

SharePoint 2010: Change the “Add New Item”

The Goal

To change from “Add new item”:
   
to “Click here to add team tasks”:
   

ID’s used by web parts

Each web part has a different ID and some share a common ID. You will need to modify the following code examples with the ID used by your list. Note that some lists share the same ID, like Tasks and KPIs.
Note: Many of these IDs have changed in 2010!
If the ID you need is not in this table, display the web page with the web part, use the browser’s View Page Source option and search for the message text. Then look backward in the <A> tag and find the ID.

Web Part	Default message	ID for “.getElementById” in the code below
Announcements	Add new announcement	“idHomePageNewAnnouncement” (changed in 2010)
Links	Add new link	"idHomePageNewLink"
Calendar	Add new event	"idHomePageNewEvent"
Picture Library	Add new picture	“idHomePageNewItem”  *  (changed in 2010)
KPI List	Add new item	"idHomePageNewItem"  *  (changed in 2010)
Tasks	Add new item	"idHomePageNewItem"  *  (changed in 2010)
Project Tasks	Add new item	"idHomePageNewItem"  *  (changed in 2010)
Document Library	Add new document	"idHomePageNewDocument"  (changed in 2010)
Wiki	Add new Wiki page	"idHomePageNewWikiPage"   (changed in 2010)
Discussion	Add new discussion	"idHomePageNewDiscussion"  (changed in 2010)
Custom List	Add new item	“idHomePageNewItem” *   (changed in 2010)

* = Shared with another list

If there is only one “Add new” message on a page

I.e. there is only one web part on the page that uses that ID then we can use this simple code:

<script>
  document.getElementById("idHomePageNewItem").innerHTML="your new text goes here"
</script> 

But the IDs won’t always work!

SharePoint can build pages with multiple elements with the same ID. This is not proper HTML, but, they do it…
So we will need something a little more complex:

<script>
var ttnA = document.getElementsByTagName('A');
for (var j=0; j<ttnA.length; j++)
{
  if (ttnA[j].id == 'idHomePageNewItem')
  {
    ttnA[j].innerHTML='your new text goes here'
  }
}
</script>

This code runs through all of the Anchor (<A>) tags in the page looking for the ones with the ID we need and then changes the text as needed.
What if you only wanted to change the second web part that uses that ID to a new message? Then try this:

<script>
var ttnA = document.getElementsByTagName('A');
var ttnCounter = 0;
for (var j=0; j<ttnA.length; j++)
{
  if (ttnA[j].id == 'idHomePageNewItem')
  {
    ttnCounter ++;
    if (ttnCounter == 2)
      { ttnA[j].innerHTML='your new text goes here' }
  }
}
</script>

Where should you add the code?

Add the code to the page using SharePoint Designer

1. Edit the page using SharePoint Designer
2. Find the “<asp:content” tag for PlaceHolderMain and then find the matching end tag (“</asp:content>”)
3. Just before the end tag, add the JavaScript listed above

Or add a Content Editor Web Part to the page
This is the easiest for testing, and does not require SharePoint Designer. In SharePoint 2007 this worked equally well for web part pages and View pages. In SharePoint 2010, adding a web part to a View page causes the View selector dropdown to be hidden, so the SharePoint Designer approach might be better for View pages. The CEWP works just fine for web part pages and the new “wiki style” home pages. The only got’ya is that they have removed the Source Editor button from the CEWP. If you use the Edit HTML button in the Ribbon you may find that your HTML and JavaScript gets modified by their editor. Best practice with the 2010 CEWP is to store your HTML and JavaScript in a text file in a library and then use the CEWP’s link to a file option.

1. Create a text file with the JavaScript code and upload to a library
2. In the library right-click the file, click Properties and copy the URL of the file
3. Add an Content Editor Web Part (CEWP) below the web part you want to customize
4. From the CEWP click web part’s dropdown arrow and click Edit Web Part
5. Paste the URL you copied into the Content Link box

Hide the “+” image?

If you want to get rid of the little icon just before the “New Item” text then just add one more line of code after where you change the message:
     ttnA[j].innerHTML='Click here to add team tasks';
     ttnA[j].previousSibling.previousSibling.style.display='none';

    

Hide the entire message?

No tricks needed!  Just edit the web part and change the “Toolbar Type” to “No Toolbar”. (The “New” link we’ve been working with is the “Summary Toolbar”.)

Deploying a custom master page

Deploying a custom master page in SharePoint 2010

Lately I’ve been playing around with Visual Studio 2010 tools for SharePoint. I’ve been trying to come up with examples on how to use each of the available item templates.
Today I created a Module. A module can be used to deploy files to your SharePoint environment and the scenario I came up with was to use a module to deploy a master page.

• I started out by creating an Empty SharePoint project. This can have any name you like and it can be both a farm and a sandboxed solution
• Next add an new item to the project and choose a module
• You can rename the module if you want to, I will leave the name at Module1
• After the module is created it contains an elements.xml file and a sample.txt file. The sample.txt file can be renamed to sample.master.

Project structure after adding a module and renaming the sample file to sample.master
I’m not a designer, so I wasn’t interested in creating a brilliant new design (good job, because I wouldn’t be able to do that!).
For the purpose of this example I just took the standard SharePoint 2010 master page contents.

• Open up SharePoint Designer and open the site you are working on
• Choose Master Pages on the left side
• Open v4.master

Don’t let SharePoint fool you. There is a default.master, but this is the old SharePoint 2007 master page that is used if you perform a visual upgrade. When creating a site in SharePoint 2010 the master page that will be used by default is v4.master. I know it’s a bit confusing, but if you have been working with SharePoint for a while you won’t be surprised by this.

• Copy the contents of v4.master and past them in your sample.master file in Visual Studio. Make the changes you want to.
• Note how the elements.xml file has changed to reference the sample.master rather than the sample.txt

 
The contents of the elements.xml file

• Make sure the module is added to a feature in your project that is scoped at the site (= site collection) level

You could stop here and this solution will deploy your sample.master master page to the master page gallery of your site collection.
This will not apply the master page to the site collection when the feature gets activated though. It will only make it available for selection.

• If you the master page to be applied when the feature gets activated you right click on the Feature1 and you add an event receiver. This will add a feature receiver file to the feature.
• In this file we will add some code that will apply the sample.master master page when the feature gets activated and it will apply the v4.master when the feature gets deactivated.

[Update 10/12/2010]: Code updated because of improperly disposed SPSite and SPWeb objects. Thanks Donald Hessing and Rhys for pointing it out.

 
Code used in the feature receiver
Now if you activate the feature the code in the feature receiver will run and will apply sample.master to the site. As you can see we change both the MasterUrl and the CustomMasterUrl. The MasterUrl is used on all pages that are not publishing pages. This means it is used on the pages in the sitepages library and on the pages in the _layouts directory like the settings page for instance. The CustomMasterUrl is only used on pages that are stored in the Pages library. This library is created when the SharePoint Server Publishing Infrastructure features is activated on the site collection and the SharePoint Server Publishing feature is activated on the site.
You can also use the SharePoint user interface to apply a new master page. The Site Master Page is the equivalent of the CustomMasterUrl and the System Master Page is what the object model calls the MasterUrl.

Changing the master page using the SharePoint user interface

How to embed the “Connect to Outlook” link in a page

I need to put a link to the "Connect to Outlook" script on a SharePoint 2010 page. I have built the stssync:// URL that triggers the Outlook sync: stssync://sts/?ver=1.1&type=discussions&cmd=add-folder&base-url=[MyServer]&list-url=[MyList]&guid=[MyGUID]&site-name=[MySiteName]&list-name=[MyListName]
Now the link works fine when pasted in IE address bar. However, I can't add it to a Sharepoint page - if I try to use the "insert hyperlink" button Sharepoint will complain about the stssynch:// protocol "URL must be in the form of http://" (or something to that effect).
I have tried to add a regular link that change the URL by editing the page's HTML. However, Sharepoint removes the link on saving the page.
How can I add the stssync:// link to a page? Is there any simple way to somehow "wrap" it in http or something?

Answer :

You can add the following code to a Content Editor webpart or directly onto the page. After adding the CEWP, in the ribbon use Editing Tools > Format Text > Edit HTML source.

 Code:

<script src="http://code.jquery.com/jquery-1.6.1.min.js"></script>
<script language="javascript" type="text/javascript">
  function replaceURL() {
    $('#outlookLink').attr('href','stssync://sts/?ver=1.1&type=discussions&cmd=add-folder&base-url=[MyServer]&list-url=[MyList]&guid=[MyGUID]&site-name=[MySiteName]&list-name=[MyListName]');
  }

  _spBodyOnLoadFunctionNames.push("replaceURL");
</script>
<a id="outlookLink" href="#">Link to Outlook</a>

Here's what it is doing:

1. Includes a reference to the jQuery library, which you can either do as in this example or you can download it to your site and include a reference to that location on your site instead.
2. replaceURL() uses the jQuery .attr() method to change the href attribute of the anchor tag with the url that you want.
3. _spBodyOnLoadFunctionNames.push() will call your function as soon as the page loads.
4. Your link should have an id which you refer to in your function. The href can be anything, since it gets modified by your function as soon as the page loads.

NOTE: You could use straight javascript instead of jQuery, but the syntax is more complicated.

How Themes work in SharePoint 2010

Sharepoint 2010 having a different concept of themes then Sharepoint 2007. Sharepoint 2010 having a new theme engine. You can do cool things such as create a theme in PowerPoint, export it and then import it into SharePoint 2010, click apply and you got a custom theme. One aspect that I could not find documented is the definition of when you choose a color for a particular element what is it really changing?

If you modify the theme for a site you can use the picker to choose a OOTB or custom theme. Or you have the ability to choose your own color scheme via the following elements.

My approach: Make each element white to start with and then one by one add color and document the results. Just for kicks here is what it looks like when it is completely white… (not a color scheme I would recommend…) I will be using a red color: #C12345 for testing.

Here are the details for each one of the elements above:

Text/Background- Dark 1 (41 Classes)

• Page Title Hyperlink Text
• Hover Text
• VB Body Text
• Site Action Menu Text
• Left Navigation Links Text
• Site Setting Page Text Headers

Text/Background - Light 1  (37 Classes)

 

• Body Background
• Toolbar Background
• Quick Launch Borders
• Web Part Header Background
• Site Action Menu Background
• Site Action/Welcome Text Color
• Pop-Up Window Background

Text/Background - Dark 2  (43 Classes)

• Top Banner Background
• Left Navigation Header Text
• Recycle Bin/View All Site Content Text
• I Like/Tags Notes Text
• Library Column Text
• Site Action Drop Down Border
• Breadcrumb Current Location Text
• List Description Text

Text/Background - Light 2  (19 Classes)

 

 

• Browse Tab and hover Background
• Title Container Background
• Top Links/Header 2 Background
• Quick Launch Background
• Web Part Adder Background

Accent 1  (10 Classes)

• Quick Launch Hover Text
• Top Link Selected Tab

Accent 2  (4 Classes)

• .ms-error
• Rich Text Colored Heading 2 Text Styles

Accent 3  (9 Classes)

• Rich Text “Caption” Style Text Color

Accent 4  (5 Classes)

• Border Under Web Part Selector

Accent 5  (3 Classes)

• Rich Text Colored Heading 4 Text Styles

Accent 6  (3 Classes)

• Rich Text Highlight background color

Hyperlink  (12 Classes)

• Toolbar Text Color
• VB Body Hyperlink Text
• a:link Class Text Color
• Web Part Title Hyperlink Text (Not Visited)

Followed Hyperlink (2 Classes)

• .ms-WPBody a:visited
• a:visited

I hope this post will help you when you decide to create your own theme via the above elements. If you create your own theme you can simply view the source of the page and there will be a unique CSS style sheet reference “_themes/16/corev4-8digithex.css?ctag=#”.