Expand Minimize

Creating .NET Assemblies That Aggregate Data from Multiple External Systems for Business Connectivity Services in SharePoint Server 2010

SharePoint 2010

Summary:  Create and use a .NET Framework assembly with Business Connectivity Services in SharePoint Server 2010 to aggregate data from two external systems: a SQL Server database and a SharePoint document library. Use external content types to display the data in SharePoint and learn ways to retrieve credentials from the Secure Store Service.

Microsoft Business Connectivity Services (BCS) provides a platform known as the Business Data Connectivity (BDC) service to pull data from external systems into Microsoft SharePoint Server 2010 or Microsoft Office 2010. To do this, BDC provides various connectors for connecting to, and retrieving data from, web services, Windows Communication Foundation (WCF) services, and databases. Another connector uses Microsoft .NET Framework assemblies to consume external data. This is a good option if you must aggregate, calculate, or clean the external data before it can be consumed by SharePoint Server 2010.

The example described in this article is a data aggregation problem in which data that is stored in multiple external systems must be aggregated for consumption. Consider an interview-management application in which an interview candidate's personal information is stored in a database, and the résumé is stored in a SharePoint document library. The example in this article demonstrates how you can retrieve the data from those disparate external systems and display the aggregated data in a Web Part in SharePoint Server 2010.

The goal is to create an external content type that retrieves and aggregates the candidate's personal data from both a Microsoft SQL Server database and a SharePoint document library. The data can then be displayed as an external list in SharePoint Server, and it can be synchronized to Microsoft Outlook 2010 or Microsoft SharePoint Workspace 2010. The résumé is a Microsoft Word 2010 document that is stored in a SharePoint document library. The document is streamed by using BDC so that it can be displayed in the Web Parts on SharePoint Server 2010, together with the corresponding candidate data that is retrieved from the database.

The database is Microsoft SQL Server 2008 and it uses SQL Server Authentication. The example also shows how you can retrieve the credentials that are stored in the Secure Store Service on SharePoint 2010 in the .NET assembly either by using Business Connectivity Services or by directly calling the Secure Store Service by using the object model.

This article describes the following tasks:

  1. Designing and implementing a .NET assembly by using the Business Connectivity Services template in Microsoft Visual Studio 2010.

  2. Using different ways to access credentials that are stored in a Secure Store Service in a .NET assembly.

  3. Streaming BLOB data from external systems into SharePoint Server 2010 by using BDC. In this case, the data consists of Word document files that are stored in a SharePoint document library.

  4. Creating a Web Part to aggregate the candidate's personal data from the SQL Server database and the résumé data that is stored in a SharePoint document library.

    The résumé document is associated with the candidate's personal information and can be opened in the Word file format from the Web Part.

Disclaimers

As you read this article, be aware of the following:

  • The article's purpose is not to present a detailed Business Connectivity Services object model discussion. Instead, it presents how you can quickly start building a solution to which you can add more functionality by using the Business Connectivity Services Runtime object model (see Business Connectivity Services Class Libraries).

  • This article does not discuss configuration and credential management in the Secure Store Service. It is assumed that the reader knows how to configure and set credentials for different business applications in the Secure Store Service.

  • Samples are provided in C# and assume that you are using Microsoft Visual Studio 2010.

  • The article also does not discuss in detail how to develop applications by using Visual Studio 2010 or how to use the various templates that it provides. This article is intended for developers who have experience using Visual Studio and C#.

  • The example in this article assumes that SharePoint Server 2010 and Visual Studio 2010 are installed on the same computer. This article does not include a discussion of how to deploy the BDC solution package (.wsp file) that contains the BDC model and .NET assemblies on a separate production computer by using cmdlet tools.

Prerequisites

To complete the exercises in this article, you must have the following:

  • Microsoft SharePoint Server 2010 installed on your development computer.

    You must have Microsoft SharePoint Server 2010 with Enterprise Client Access License, with Business Connectivity Services and Secure Store Service enabled. The Business Data Connectivity (BDC) service application, Secure Store Service application, and associated proxies are provisioned on the SharePoint Central Administration console.

  • Microsoft Visual Studio 2010 with SharePoint 2010 templates installed.

Before describing the implementation, this section examines the design that is used to solve the problem. To get data from an external system, the first step is to define the external content types. The Business Connectivity Services platform lets you use BDC to call stereotyped operations, such as Create, Update, Read, Delete, and Query, on the external content type. BDC also supports other stereotyped operations, such as StreamAccessor, for streaming BLOB data into SharePoint 2010 or Microsoft Office 2010. This article discusses in more detail the Read, Query, and StreamAccessor operations.

Based on business needs, the .NET assemblies that BDC consumes must expose public methods that correspond to the stereotype operations. The BDC Runtime executes these public methods to retrieve data from the external system and perform different operations.

The first step is to define the external content type. In this example, the business entity is the Candidate. This entity stores all the personal and contact information of the interview candidate. You can define the external content type in the .NET assemblies as a class (the Entity class), as follows.

namespace InterviewManagement.BdcNetModel
{
Public  partial class Candidate
    {
        public Int32 CandidateId {get;set;}
        public String FirstName { get; set; }
        public String LastName { get; set; }
        public String MiddleName { get; set; }
        public String PrimaryContactNo { get; set; }
        public String SecondaryContactNo { get; set; }
        public String PrimaryEmail { get; set; }
        public String SecondaryEmail { get; set; }       
        public Candidate()        {       }
}

The tool that is used to design and deploy the solution in this example is Visual Studio 2010. Visual Studio 2010 contains a new project template for SharePoint 2010 named Business Data Connectivity Model, as shown in Figure 1.

Figure 1.Visual Studio 2010 template

Visual Studio 2010 template

Create a project by using the Business Data Connectivity Model template. Add the candidate class to the project to define the external content type.

Click the BDC Explorer. It displays a template that has been predefined by the external content type. Click Properties, and change the existing identifier to CandidateId with a type of System.Int32, as shown in Figure 2.

Figure 2. Adding the identifier

Adding the identifier

Next, in the BDC Method Details pane, select the ReadList method. For the parameters, delete the existing template parameters (if any are listed), and add the required parameters, as shown in Table 1.

Table 1. ReadList parameters

Parameters

Direction

TypeDescriptor

Type

username

IN

usernameTypeDescriptor

System.String

password

IN

passwordTypeDescriptor

System.String

returnParameter

OUT

CandidateList

InterviewManagment.BdcModel1.Candidate[], BdcModel1

In the TypeDescriptor column in the method properties, right-click to edit the type of the TypeDescriptor, and then add the type of the type descriptor.

The CandidateList type descriptor is a collection of complex type Candidate. You must define the complex type in the BDC model for BDC to map the return values to the correct fields. To define the child type descriptors, in BDC Explorer, right-click CandidateList, and then select Add a type descriptor. Add the type descriptor Candidate, and then add the child type descriptors for Candidate, as shown in Table 2.

Table 2. Candidate child type descriptors

TypeDescriptor

Type

CandidateId

System.Int32

FirstName

System.String

LastName

System.String

MiddleName

System.String

PrimaryContactNo

System.String

PrimaryEmail

System.String

SecondaryContactNo

System.String

SecondaryEmail

System.String

Figure 3 shows Candidate in the BDC method details pane.

Figure 3.BDC method details pane

BDC method details pane

This public method returns the candidate's information that is stored in the database and matches the Finder stereotype operation of BDC. In the BDC Method Details pane, select Add a Method Instance, and then select Create Finder instance. Save the operation.

The next step is to add a public method that will return the candidate information based on the identifier CandidateId. This method matches the BDC stereotype operation SpecificFinder. Following the steps that you took earlier to add ReadList, add the parameters and type descriptor for the ReadItem method, as shown in Table 3.

Table 3. ReadItem parameters

Parameters

Direction

TypeDescriptor

Type

Id

IN

CandidateId

System.Int32, Identitifier: CandidateId

username

IN

usernameTypeDescriptor

System.String

password

IN

passwordTypeDescriptor

System.String

returnParameter

OUT

Candidate

InterviewManagment.BdcModel1.Candidate, BdcModel1

Add the child type descriptors for Candidate as described in Table 2. Save the properties that are defined in the BDC Explorer for the external content type. This automatically adds the method stubs for the methods that are defined in the BDC Explorer as part of the class.

Now that the methods are defined, you must add the logic to retrieve the data from the external systems. The contact information for the candidate is stored in a SQL Server 2008 database, and the résumé document is stored in SharePoint Server in a document library. The SQL Server database must have a user name and password for authentication. The next section describes how you can store the credentials in SharePoint Server 2010 by using Business Connectivity Services.

To connect to the SQL Server external system, add a class by using the LINQ to SQL template, and add a connection to the external system database. This template helps convert the SQL classes to LINQ to enable you to execute queries against SQL Server. The following code retrieves all the data about all candidates in ReadList.

using System.Linq;
using System.Text;
using InterviewManagement.BdcNetModel;
  private const string connString = @"Data Source= <server>;Initial Catalog=TestDB;User ID={0};Password={1};";
public Candidate[] ReadList(string username, string password)
        {

         // Error checking removed for brevity.
          String conn = String.Format(connString,username,password);
            List<Candidate> candidateList = new List<Candidate>();
            using (DataClasses1DataContext db = new DataClasses1DataContext(conn))
                {
                    var allCandidatesInfo = (from candidateDB in db.GetTable<CandidateInfo>()
                                                  select candidateDB);

                     foreach (CandidateInfo candidateInfo in allCandidatesInfo)
                     {
                         Candidate candidate = new Candidate();
                         FillInDataFromDb(candidate, candidateInfo);
                         candidateList.Add(candidate);
                     }
                }
            

           return candidateList.ToArray();
  }

// FillInDB method takes the Candidate query result and maps it to the candidate object.

Because the SQL Server 2008 database is authenticated by using SQL Server Authentication, the .NET assembly must pass the user name and password. The next section discusses how you can retrieve the credentials and pass them to the .NET assemblies.

SharePoint 2010 offers the Secure Store Service, which helps manage credentials. This article does not intend to explain how to configure and manage the Secure Store Service. It assumes that the reader knows how to manage the Secure Store Service.

Because access to the SQL Server database is authenticated by SQL Server itself, a user name and password are required to connect to the SQL Server external system. These credentials are stored in the Secure Store Service and can be retrieved by BDC at run time to connect to the external system SQL Server database. The credentials are mapped to the user who executes the .NET assembly.

There are different options to access the credentials in the Secure Store Service by using Business Connectivity Services. This section describes the following options:

  • Using system filters

  • Directly invoking the Secure Store Service APIs in the .NET assembly

Using Filters

Business Connectivity Services offers many filter types (system and user input) that enable the user to filter the data that is retrieved from the external system. It is a way by which BDC captures user or system input, and plumbs the complex data by using the BDC API invocation. UsernameFilter and PasswordFilter are two system filters that Business Connectivity Services supports, which enable you to filter the data from the external system, based on the user.

The UsernameFilter and PasswordFilter filters in Business Connectivity Services enable you to retrieve credentials from the Secure Store Service and map them to the input parameters that are associated with the filter descriptors of the calling method.

The ReadList method has a user name and password as input parameters. Before associating the filters with the inputs, you must define the filter descriptor. In the Visual Studio 2010 project, open the BDC Explorer, and then open the BDC Method Details pane. One of the properties that you can set on the method is the FilterDescriptor property. Select Add a FilterDescriptor, select the filter type Username, and name it userFilterDescriptor. After you define the filter descriptor, select the username parameter in ReadList, and edit its properties. Select the Associated Filter property and type the filter descriptor that is defined—userFilterDescriptor. Similarly, add the filter Password, and associate it with the password input for ReadList.

Table 4. Filter in BDC associated with input parameters

Input parameters

FilterDescriptors

username

username

password

password

Figure 4 shows the filter descriptors for ReadList.

Figure 4. Adding filter descriptors to the ReadList method

Adding the filter descriptors to ReadList method

In the Secure Store Service, every credential is stored and associated with the business application that is known as the target application. To retrieve the credentials, BDC requires information about the Secure Store Service provider that SharePoint is using, and the target application from which it will retrieve the credentials. You must provide this information at the LobSystemInstance level as properties. To do this, open the BDC Explorer. Select the LobSystemInstance property, and then in the Properties pane, click Custom Properties. In the Property Editor dialog box, enter the properties, the kind of property, and the values that are associated with the properties. The target application is mapped to a property named SecondarySsoApplicationId, and the provider information is mapped to a property named SsoProviderImplementation.

Enter the properties and values that are shown in Table 5.

Table 5. LobSystemInstance properties for the Secure Store Service

Secure Store Service property

Property

Type

Value

Target Application

SecondarySsoApplicationId

System.String

DBCredentials

SSS Provider Details

SsoProviderImplementationDetails

System.String

Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c

The properties and values are shown in Figure 5.

Figure 5. Assigning the LobSystemInstance properties in BDC Explorer

Assigning the LobSystemInstance properties in BDC

At run time, BDC retrieves the stored credentials for the executing user of the application from the Secure Store Service, based on the target application specified as SecondarySsoApplicationId on LobSystemInstance. It maps the credentials to the inputs that are associated with the UsernameFilter and PasswordFilter filters. At run time, the credentials are automatically populated in the input parameters and can be added to the connection string to connect to the external system.

The following example retrieves the user name and password.

  private const string connString = @"Data Source=<server>;Initial Catalog=TestDB;User ID={0};Password={1};";
public Candidate[] ReadList(string username, string password)
        {
         // Error checking removed for brevity.
          String conn = String.Format(connString,username,password);
      }

Invoking the Secure Store Service APIs in a .NET Assembly

The second option that you can use to retrieve credentials from the Secure Store Service is to directly invoke the Secure Store Service APIs in your .NET assembly. This is a full code approach in which the Secure Store Service APIs are called directly in the code in the .NET assembly.

Although this option is not used in this example to retrieve the back-end credentials, it is described here as a different approach to retrieve credentials from the Secure Store Service. One approach is to have the target application and provider information defined as properties at the LobSystemInstance level instead of adding this information as part of the code. This gives you more flexibility to configure these properties as required, and it is consistent with the way that other Business Connectivity Services connectors retrieve credentials from the Secure Store Service for authentication.

How do you access the LobSystemInstance properties in a .NET assembly? The answer is to implement the IContextProperty interface, which enables access to the metadata model properties.

The following example shows how to retrieve the user name and password.

using Microsoft.BusinessData.SystemSpecific;
public class CandidateService : IContextProperty   
#IContext properties
 public IMethodInstance MethodInstance

// Get the methodinstances properties.
 public  ILobSystemInstance LobSystemInstance

// Get the lobsysteminstance properties.
 public  IExecutionContext ExecutionContext

// Get the execution context.

When the .NET assembly class implements the IContextProperty interface, the BDC runtime automatically initializes IContextProperty properties. At run time, the method that is implemented in the .NET assembly can read the metadata model properties because the code has the context (MethodInstance and LobSystemInstance) in which it executes.

As discussed, the SecondarySsoApplicationID property and the SsoProviderImplementation property are defined at the LobSystemInstance property level. By using IContextProperty, you can read these properties, instantiate the correct Secure Store Service provider, and call the correct target application. All Secure Store Service providers implement the ISecureStoreProvider interface. The first step is to instantiate the correct Secure Store Service provider type object. By using an ISecureStoreProvider object, you can call the GetCredentials API, which provides a collection of credentials of type SecureStoreCredentialCollection for a particular target application for the executing user. Loop through the collection to get an object of type ISecureStoreCredential, which contains the credential type and value. This code example calls the Secure Store Service and retrieves the credentials.

using Microsoft.BusinessData.Infrastructure.SecureStore;
using System.Runtime.InteropServices;
private string[] GetCredentialsFromSSS()
        {

// Error checking removed for brevity.
            string[] Credentials = new String[2];             

            // Get the provider information.
             string ssoProvider = this.LobSystemInstance.GetProperties()["SsoProviderImplementation"] as string;
                Type providerType = Type.GetType(ssoProvider);          
            ISecureStoreProvider provider =  (ISecureStoreProvider)Activator.CreateInstance(providerType);           

            // Get the credentials.
            string appTargetName = this.LobSystemInstance.GetProperties()["SecondarySsoApplicationId"] as string;      

// If the credentials need to be cached , do not use the using block.
           using (SecureStoreCredentialCollection credentials = provider.GetCredentials(appTargetName))
            {
                foreach (ISecureStoreCredential cred in credentials)
                {
                    if (cred.CredentialType == SecureStoreCredentialType.UserName)
                    {
                        Credentials[0] = GetString(cred.Credential);
                    }
                    else if (cred.CredentialType == SecureStoreCredentialType.Password)
                    {
                        Credentials[1] = GetString(cred.Credential);
                    }
                }
            }
            return Credentials;
        }

// Secure Store Service returns the credentials as SecureString. 
// Need to convert the SecureString into cleartext //string.
        private string GetString(System.Security.SecureString secureString)
        {
            string str = null;
            IntPtr pStr = IntPtr.Zero;
            try
            {   pStr = Marshal.SecureStringToBSTR(secureString);
                str = Marshal.PtrToStringBSTR(pStr);        }
            finally
            { Marshal.FreeBSTR(pStr);  }
            return str;
        }

After you retrieve the back-end user name and password, you can replace it in the connection string and connect to the SQL Server table to retrieve the candidate information.

Using the same methods that were mentioned earlier, you can add the ReadItem method in the BDC Explorer and associate it with the SpecificFinder instance. This method also has a user name and password as input and associates the filters Username and Password with the input parameters. The following is the code for the ReadItem method.

public Candidate ReadItem(int id, string username, string password)
        {
// Error check is removed for brevity.
            Candidate candidate = new Candidate();                    
            CandidateInfo candidateInfo;
            String conn = String.Format(connString, username, password);
            candidate.CandidateId = id;
            using (DataClasses1DataContext db = new DataClasses1DataContext(conn))
                { candidateInfo = (from candidateDB in db.GetTable<CandidateInfo>()
                                     where candidateDB.CandidateId == candidate.CandidateId
                                     select candidateDB).SingleOrDefault();
                    FillInDataFromDb(candidate, candidateInfo);
                }            
            return candidate;
  }

// FillInDB method takes the Candidate query result and maps it to the candidate object.

You have the candidate's personal information from the SQL Server table. But the candidate's résumé is stored in the document library in SharePoint Server 2010, and it must be accessed and then aggregated with the personal information. This section describes how you can use Business Connectivity Services to stream the résumé, which is a Word document, into the SharePoint 2010 Business Data Web Part, and then combine it with the candidate's personal information.

BDC provides a MethodInstance (see MethodInstance Element in MethodInstances (BDCMetadata Schema)) named StreamAccessor to access Stream data. By default, the project template in Visual Studio 2010 supports only the basic stereotype operations of Create, Update, Read, Delete, and Query. However, the user can add other stereotypes that are supported by Business Connectivity Services in BDC Explorer.

To do this, switch to BDC Explorer view, and add a new method named GetResumeDetails with the parameters shown in Table 6.

Table 6. GetResumeDetails method parameters

Parameters

Parameter Direction

Type

TypeDescriptor

candidateId

In

System.Int32

Candidate Identifier:CandidateId

stream

Out

System.IO.Stream

Stream

Add the MethodInstance of type StreamAccessor, as shown in Figure 6.

Figure 6. Configuring StreamAccessor in BDC Explorer

Configuring StreamAccessor in BDC Explorer

Because the résumé is stored as a Word document, you might want to stream and access the document in the same format. To enable this feature, Business Connectivity Services supports additional properties on the MethodInstanceStreamAccessor. Table 7 shows the properties that are supported to open the document in the required MIME type.

Table 7. Properties to specify the MIME type to read the stream

Properties

Functionality

Value

MIMEType

Specifies the MIME type for displaying the document.

application/vnd.openxmlformats-officedocument.wordprocessingml.document

FileName

Specifies the file name that BDC uses to display the file.

ResumeFile.docx

The StreamAccessor method is defined in the BDC Explorer. The assumption here is that the folder is located on the same site where the Web Part is executed. Similar to the Secure Store Service properties, it would be good to have a property at the LobSystemInstance level to which you can set the folder name. This gives you the flexibility to have a consistent folder name, but it must be changed at the model level. To enable this, define an additional property at the LobSystemInstance level, as shown in Table 8.

Table 8. Additional property at the LobSystemInstance level

Property

Type

Value

FolderName

System.String

<Name of the folder on SharePoint 2010>

The method properties are defined in the BDC Explorer. Save the model properties. The next step is to implement the code to access the document and stream the data into SharePoint Server 2010.

An earlier section described the IContext property and how it enables you to access the LobSystemInstance properties. By using the IContext property, you can access the FolderName in the method.

The first step is to obtain the context of the HTTP request from which to get access, and information about the associated website, document library, and folder object. The class is SPServiceContext. Because the calling application is a Web Part (HTTP request), you can use it to obtain the current context. By using the SPContext object, you can get the website object of type SPWeb. By using the SPWeb object, you can access the folder object on the SharePoint website of type SPFolder, using the FolderName that was defined as a property on the LobSystemInstance level in the BDC model. Using the SPFolder object, you can access the document file in the folder of type SPFile and open the BinaryStream to read the contents of the document, which can be mapped to the return parameter of the method.

The following example shows the GetResumeDetails method.

Using Microsoft.SharePoint;
public Stream GetResumeDetails(Int32 candidateId)
        {

// Error handling removed for brevity.             
             String foldername = this.LobSystemInstance.GetProperties()["FolderName"] as string;
            SPContext context = GetSPContext();
            SPWeb site = context.Web;
            string fileName = "Candidate" + candidateId +".docx";
           SPFolder resumeFolder = site.GetFolder(foldername);
           SPFile file = resumeFolder.Files[fileName];
           Stream resumeStream = file.OpenBinaryStream(SPOpenBinaryOptions.SkipVirusScan);
            return resumeStream;
    } 
private SPContext GetSPContext()
      {
            SPContext spContext = SPContext.Current;
          return spContext;      
  }

After you complete this implementation, build the project. This example assumes that you are deploying the BDC artifacts on SharePoint Server 2010, installed on the same computer. In the Visual Studio project, select Build, and then select Deploy Solution. This deploys the BDC package on a server that is running SharePoint Server.

NoteNote

If you want to deploy the BDC package on a different computer, take the solution package (.wsp file) that was generated by the project, and deploy it by using SharePoint cmdlets on SharePoint Server 2010.

The package is deployed, which means that the BDC model is imported into SharePoint Server 2010, and the associated .NET assemblies are also imported into SharePoint Server and associated with the correct LobSystem in the BDC Metadata Store. The next step is to execute the Web Part and retrieve the data from the external system database and document library in SharePoint Server 2010.

SharePoint Server 2010 has several business data Web Parts, such as the Business Data Item and the Business Data List. You can select any Web Part, and then select the specific MethodInstance type (Finder or SpecificFinder) based on the kind of Web Part that was selected for the external content type Candidate.

To create a Business Data Web Part

  1. In SharePoint Server, select the Business Data Item Web Part.

  2. To edit it, click Open the tool pane.

  3. In the Web Part Designer, click the Entity Picker, and then select the LobSystemInstance that is associated with the external content type, Candidate.

  4. Using the Item picker, add the default item to display the data for the Web Part, and then click Apply.

At run time, for each External Item that is returned by the SpecificFinder or Finder, Business Connectivity Services associates the stream based on the identifier, combines the data, and shows it in the Business Data Web Part, as shown in Figure 7.

Figure 7. Business Data Web Part associated with the Stream Accessor

Business Data Web Part associated with the Stream

Because the MIME type that is specified in the BDC model is of Word type, the stream is opened to view as a Word document.

This article describes a quick, four-step process for creating a .NET assembly that Business Connectivity Services can use to retrieve external data for Microsoft SharePoint Server 2010 by using Microsoft Visual Studio 2010. It also discussed how to obtain credentials from the Secure Store Service, and how to stream BLOB data into SharePoint Server 2010 by using Business Connectivity Services.

Show:
© 2014 Microsoft