Chapter 14
Advanced Business Connectivity Services
WHAT’S IN THIS CHAPTER?
- Creating BCS connectors
- Searching BCS systems
- Using the BCS object model
- Using BCS with SharePoint apps
WROX.COM CODE DOWNLOADS FOR THIS CHAPTER
The wrox.com code downloads for this chapter are found at www.wrox.com/remtitle.cgi?isbn=1118495829 on the Download Code tab. The code is in the chapter 14 download and individually named according to the names throughout the chapter.
Chapter 13, “Introducing Business Connectivity Services,” discussed creating Business Connectivity Services (BCS) solutions with little coding. This chapter explores custom coding in BCS by presenting .NET Assembly Connectors and the BDC server object model, which lays the foundation for custom BCS solutions.
CREATING .NET ASSEMBLY CONNECTORS
A .NET Assembly Connector associates a custom assembly with an external content type (ECT) so that you can precisely control how information is accessed, processed, and returned from external systems. You create a .NET Assembly Connector in Visual Studio 2012 by adding the Business Data Connectivity Model (BDCM) item. The Visual Studio BDCM item is a complete working sample. After it is created, you can press F5 and make external lists in SharePoint. This is a good start because the tooling can be somewhat confusing when you first use it; a complete working sample helps guide you to success. The detailed examination of the project item in the next section can help you understand.
Understanding the Project Tooling
When you work with the Business Data Connectivity Model item, there are three explorers/designers available: the BDC Model Explorer, the Entity Design Surface, and the Method Details pane. The BDC Model Explorer is used to navigate the nodes of the BDC Metadata Model. The Entity Design Surface is used to design the ECT that will be associated with the .NET Assembly Connector. The Method Details pane is used to create the function signatures for ECT operations. Along with these three elements, the Business Data Connectivity Model project template also gives you the standard windows such as the Solution Explorer and the Properties pane. Figure 14-1 shows the tooling in Visual Studio 2012.
Although the Visual Studio tooling is helpful, there are times when you must access the underlying BDC Metadata Model as XML either for direct editing or simply to verify the work you have done using the tools. The BDC Metadata Model can be found in the Solution Explorer as the file with the .bdcm extension. You can open this file as XML by right-clicking it and selecting Open With from the context menu. From the Open With dialog box, open the file with the XML Editor.
To be successful with the tooling, you must understand how the various explorers and designers relate to the underlying model XML. Furthermore, you must understand which elements of the project are affected as you make changes. In particular the BDC Model Explorer and the Method Details pane can be confusing if their relationships to the underlying XML are not well understood.
Not all of the underlying BDC Metadata Model can be represented in the BDC Model Explorer. In particular, the BDC Model Explorer shows methods, but not method instances. Methods are used in the BDC Metadata model as prototypes, which are subsequently implemented by method instances. The Method Details pane provides the interface necessary to define the method instances.
The Entity Design Surface is also used to edit the underlying BDC Metadata Model. However, it is focused on the creation of entities. Using this tool you can create new entities, assign the Identifier, and create new methods.
Regardless of which tool you use, you can use the Properties pane to edit the selected node. The Properties pane lists the type of node and its attributes. Although the nodes have many attributes, most of them are not required. It is not always clear, however, which attributes are required to implement any given node. The better you understand the model, the more likely you are to create the one you need.
For the most part, the tooling is designed to edit the underlying BDC Metadata Model, with one exception. A class module is used to implement the method instances that you define in the model. This class module is created automatically and is always given the name of the entity followed by the word Service. This class is known as the service class. If you change the name of the entity in your model, the service class name is updated automatically. If you delete the service class from the project, it is re-created the next time you make any changes to the method definitions.
The methods implemented in the service class have types defined by the input and return parameters in the BDC Metadata Model. These types can be simple types or classes. Typically, however, the Finder and SpecificFinder methods return classes that represent the ECT associated with the .NET Assembly Connector. In the Business Connectivity Model item, a class named Entity1.cs is created by default and is returned from the Finder and SpecificFinder methods. These methods are also created by default when you create a project with the Business Connectivity Model item.
Even though the item template includes a class that has the same name as the entity, there is actually no connection between the entity definition and the entity class as far as the tooling is concerned. Changing the name of the entity in the model does not change the name of the class, and the class is never automatically generated. The class is actually just a payload returned from the .NET Assembly Connector. Its name is meaningless, but it is a best practice to keep the name of the class synchronized with the name of the entity it represents. The methods in the service class return instances of the entity class that are passed on to external lists for display. In more advanced scenarios, you may choose to implement the entity classes in a separate project so that they can be easily referenced by custom web parts that display the data.
The tooling is largely focused on defining and implementing methods as opposed to defining the data returned by the entity class. In the default project template, the entity has a data field named Message, which is defined as a TypeDescriptor with a TypeName of System.String. The entity class has a corresponding property whose value is set during the execution of the Finder or SpecificFinder methods. To add or modify data fields for the ECT, you must make changes to the model in the BDC Model Explorer and add new properties to the entity class. This is a manual process — the tooling never automatically generates members for the entity class.
Walking Through the Development Process
The easiest way to become familiar with the Visual Studio Business Data Connectivity Model project is to create a solution. This example walks you through the complete development process for building a .NET Assembly Connector. It makes use of a subset of product and category data from the AdventureWorks database to create a connector that allows full CRUD operations. As a starting point, an object relational model (ORM) was created over the database using the Entity Framework so that the .NET Assembly Connector can simply access the database through LINQ. To learn more about the Entity Framework, you can find complete coverage on MSDN at http://msdn.microsoft.com/en-US/data/jj590134.
Creating a New Project
The first step in developing the connector is to create a new empty SharePoint 2013 solutions project and add a new Business Data Connectivity Model item. For this walkthrough, you use a model named ProductModel that makes use of data from the AdventureWorks database. Although the default model created by the project template is valuable for learning about the tooling, it provides little help in developing a connector. For this reason, it is best simply to delete the default Entity1 entity from the Entity Design Surface. Along with the entity, you should also delete the entity service class and the entity class from the Solution Explorer. This leaves you with a simple BDC Metadata Model that looks like the following XML, which you can view as text directly in Visual Studio:
<?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= "ProductModel ">
<LobSystems>
<LobSystem Name= "ProductSystem" Type= "DotNetAssembly ">
<LobSystemInstances>
<LobSystemInstance Name= "ProductSystemInstance" />
</LobSystemInstances>
</LobSystem>
</LobSystems>
</Model>
The LobSystem element is a critical part of the model. Note how the element indicates that the system will be implemented through an assembly. This syntax differs significantly from that of the examples in previous chapters, which used databases and web services. This element is also used by both SPD and Visual Studio 2012 to determine whether to provide tooling support for a model. SPD does not provide tooling support for .NET Assembly Connectors, and Visual Studio provides tooling support only for .NET Assembly Connectors.
Also notice in the preceding XML that careful attention has been paid to naming the elements. The Model, LobSystem, and LobSystemInstance nodes have all been named appropriately. When you create connectors, naming is critical for maintaining clarity as the BDC Metadata Model becomes more complex. Remember to name elements correctly early in the development process. Renaming elements later can cause problems, as described in the section “Packaging Considerations”.
Creating a New Entity
Because the default entity was deleted, the next step is to add a new entity to the project. You can add entities to the project from the toolbox in Visual Studio, which has an Entity object that can be dragged onto the Entity Design Surface. When you add a new entity, you’ll notice that a new service class is automatically created. In addition, the Properties pane presents several properties that you can set. Here you will at least set the Name property for the entity. In this case, the entity is named Product.
The next step is to add the Identifier for the entity. The Identifier is the primary key by which a unique entity instance can be identified. You can create a new Identifier by right-clicking the entity and selecting Add, Identifier. Using the Properties pane you can set the name and data type for the Identifier. For the walkthrough, an Identifier named ProductID was created, with a data type of System.Int32. The following code shows the BDC Metadata Model for the entity:
<Entities>
<Entity Name="Product" Namespace="ProductConnector.ProductModel"
Version="1.0.0.133">
<Properties>
<Property Name="Class" Type="System.String">
ProductConnector.ProductModel.ProductService,
ProductSystem</Property>
</Properties>
<Identifiers>
<Identifier Name="ProductID" TypeName="System.Int32"/>
</Identifiers>
</Entity>
</Entities>
The next step is to create the entity class that contains the data from the external system. Remember that Visual Studio does not automatically create an entity class, so you must add a new class manually. Within the class you must add properties for each of the data fields you want to return. The following code shows the entity class created for the walkthrough:
namespace ProductConnector.ProductModel
{
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
public string Number { get; set; }
public string Color { get; set; }
public string Description { get; set; }
}
}
Although the preceding entity class is fairly simple, there are a couple of things to point out. First, each of the properties in the class corresponds to a column in an external list. Second, the data is strongly typed; the types defined in the class are returned from the connector.
Creating a Finder Method
The next step is to create the methods for the entity. Returning to the Entity Design Surface, you can create new methods by right-clicking the entity and selecting Add Method. You can also create a new method in the Method Details pane, which is a better idea because Visual Studio defines the model for the stereotype when you start here. Remember that a method is just a stereotype and that you must create a method instance to implement the method. You can create a new method instance by clicking the Add Method Instance link in the Method Details pane. After you create the method instance, you can specify the Type of the method instance in the Properties pane. Typically, your first method will be a Finder method. For the walkthrough, a Finder method named ReadProducts was created.
After the method instance is defined, you must define its parameters. In the case of the default Finder method, you typically define a return parameter only. Other method instances may require input parameters as well as filters. You can create a new parameter by clicking Add a Parameter in the Method Details pane. Using the Properties pane you can then change the parameter name and direction. For the walkthrough, a return parameter named ProductList was created.
When a parameter is defined, Visual Studio automatically creates a TypeDescriptor for the parameter. The TypeDescriptor acts as a mapping between the data types found in the external system and the data types returned by the .NET Assembly Connector. Clicking the TypeDescriptor in the Method Details pane enables you to define the TypeName for the TypeDescriptor. In the case of a Finder method, the TypeDescriptor is typically a collection of entity instances. Therefore, the IsCollection property should be set to True before you select the TypeName. After the TypeDescriptor is designated as a collection, you can open the TypeName picker, click the Current Project tab, and select the Product class. Visual Studio automatically sets the return type to be a collection. Figure 14-2 shows the Type Name picker in Visual Studio.
At this point, you can open the code for the service class and see that Visual Studio has created a method whose signature is based on the method, parameter, and TypeDescriptor settings. However, your work is not yet done because the return TypeDescriptor was designated as a collection. Therefore a new TypeDescriptor must be added to represent the member of the collection. In addition, each field in the collection member must be defined.
To create the additional TypeDescriptors, you work in the BDC Model Explorer. In the Explorer, you can see the TypeDescriptor defining the collection. You may define a collection member by right-clicking the collection TypeDescriptor and selecting Add Type Descriptor from the context menu. This TypeDescriptor has a TypeName of Product but will not be a collection. Finally, you must add a TypeDescriptor for every property of the entity you want to return. Take care to set the Identifier property for the TypeDescriptor that represents the Identifier of the entity (in this case, ProductID) to designate this property as the one containing the Identifier value.
Finally, return to the Method Details pane and select the method instance for the Finder. In the Properties pane, set Return Parameter Name and Return TypeDescriptor to reference the items already created. This completes the definition of the Finder. The following code shows the completed Finder method definition in the BDC Metadata Model.
<Method Name="ReadProducts">
<Parameters>
<Parameter Name="productList" Direction="Return">
<TypeDescriptor Name="ProductList"
TypeName="System.Collections.Generic.IEnumerable`1[
[ProductConnector.ProductModel.Product, ProductSystem]]"
IsCollection="true">
<TypeDescriptors>
<TypeDescriptor Name="Product"
TypeName="ProductConnector.ProductModel.Product, ProductSystem"
IsCollection="false">
<TypeDescriptors>
<TypeDescriptor Name="ProductID" TypeName="System.Int32"
IsCollection="false" IdentifierName="ProductID" ReadOnly="true" />
<TypeDescriptor Name="Name" TypeName="System.String" />
<TypeDescriptor Name="Description" TypeName="System.String" />
<TypeDescriptor Name="Color" TypeName="System.String" />
<TypeDescriptor Name="Number" TypeName="System.String" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="ReadProducts" Type="Finder"
ReturnParameterName="productList" ReturnTypeDescriptorPath="ProductList" />
</MethodInstances>
</Method>
Creating a SpecificFinder Method
Because the minimum requirements for an external list include a Finder and SpecificFinder method, the next step is to create the SpecificFinder method. You do this using the same procedure as for the Finder method, with two exceptions. First, the return type is a single entity instance as opposed to a collection. Second, SpecificFinder requires an input parameter that contains the Identifier of the entity instance to return. You must explicitly designate this input parameter as accepting an identifier by setting the Identifier property.
As with the Finder method, you must also add a TypeDescriptor for every property of the entity you want to return and set the Identifier property for the TypeDescriptor that contains the Identifier value. In this case, however, you can simply copy the TypeDescriptors from the Finder method in the BDC Model Explorer and paste it under the SpecificFinder method.
One last thing you must do is set the Read-Only property to True for the TypeDescriptor that represents the identifier. You must do this because the ProductID is handled as an identity column in the database. Users cannot update the value of this field. Setting the Read-Only property ensures that the auto-generated forms in SharePoint reflect that the field cannot be changed. The following code shows the completed SpecificFinder method definition in the model.
<Method Name="ReadProduct">
<Parameters>
<Parameter Name="product" Direction="Return">
<TypeDescriptor Name="Product"
TypeName="ProductConnector.ProductModel.Product, ProductSystem"
IsCollection="false">
<TypeDescriptors>
<TypeDescriptor Name="ProductID" IdentifierName="ProductID" IsCollection=
"false" TypeName="System.Int32" ReadOnly="true" />
<TypeDescriptor Name="Name" TypeName="System.String" />
<TypeDescriptor Name="Description" TypeName="System.String" />
<TypeDescriptor Name="Color" TypeName="System.String" />
<TypeDescriptor Name="Number" TypeName="System.String" />
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
<Parameter Name="productID" Direction="In">
<TypeDescriptor Name="ProductID" TypeName="System.Int32"
IdentifierEntityName="Product"
IdentifierEntityNamespace="ProductConnector.ProductModel"
IdentifierName="ProductID" />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="ReadProduct" Type="SpecificFinder"
ReturnParameterName="product" ReturnTypeDescriptorPath="Product" />
</MethodInstances>
</Method>
Handling Connection Information
At this point the minimum required methods are defined, and you can turn your attention to implementing them in code. As a first order of business, you must consider how to handle connection information for the external system. The simplest way to store connection information is as a property in the BDC Metadata Model. You can add custom properties to any node in the BDC Metadata Model, and connection information is typically attached to the LobSystemInstance node.
In the BDC Explorer you can select the LobSystemInstance node and then click the Custom Properties ellipsis in the Properties pane. This opens the Property Editor dialog box, where you can add a new custom property to hold the connection string. Figure 14-3 shows the custom property for the walkthrough and the BDC Metadata Model follows.
<LobSystemInstance Name="ProductSystemInstance"> <Properties> <Property Name="AdventureworksCatalog" Type="System.String"> Connection string goes here </Property> </Properties> </LobSystemInstance>
After the custom property is created, the service class can be modified to support reading the connection information. You start this process by setting a reference to the Microsoft.BusinessData.dll assembly located in the ISAPI folder beneath the SharePoint system directory. After the reference is made, the service class must be updated to implement the Microsoft.BusinessData.SystemSpecific.IContextProperty interface.
You don’t need to write any code to implement the interface because the BDC Server run time takes care of managing the properties that must be set. You can, however, now use the interface to retrieve the property previously stored in the model. The following is the interface and connection information retrieval code.
internal string GetConnectionInfo()
{
INamedPropertyDictionary props =
this.LobSystemInstance.GetProperties();
if (props.ContainsKey("AdventureworksCatalog"))
return props["AdventureworksCatalog"].ToString();
else
return string.Empty;
}
public Microsoft.BusinessData.Runtime.IExecutionContext
ExecutionContext
{
get;
set;
}
public Microsoft.BusinessData.MetadataModel.ILobSystemInstance
LobSystemInstance
{
get;
set;
}
public Microsoft.BusinessData.MetadataModel.IMethodInstance
MethodInstance
{
get;
set;
}
Implementing the Methods
Now that the connection information can be stored and retrieved, you can turn your attention to implementing the methods. This is a matter of writing the necessary code to return the data from the external system, but there are two changes that must be made to the service class first.
If you examine the code that Visual Studio generates in the project, you may notice that all the methods are static. This is because the .NET Assembly Connector performs slightly better with static methods. However, after the IContextProperty interface is implemented, the class can no longer use the static methods. Therefore, the static keyword must be removed. In addition, the code generated by Visual Studio also uses IEnumerable<T> as the return type for the Finder method. If you want to open the ECT in the SharePoint Designer, however, this must be changed to IList<T>.
When the changes are made, the code for the methods can finally be added to the connector. After you finish and compile the code, the .NET Assembly Connector can be deployed. At this point, you can create a new external list. The following code shows the complete implementation for the methods, which uses LINQ queries against the Entity Framework layer discussed previously.
namespace ProductConnector.ProductModel
{
public partial class ProductService : IContextProperty
{
public IList<Product> ReadProducts()
{
AdventureworksCatalog catalog =
new AdventureworksCatalog(GetConnectionInfo());
var q = from p in catalog.Products
orderby p.Name
select p;
List<Product> products = new List<Product>();
foreach (var i in q)
{
products.Add(
new Product()
{
ProductID = i.ProductID,
Name = i.Name,
Number = i. Number,
Color = i. Color,
Description = i.Description
});
}
return products;
}
public Product ReadProduct(int ProductID)
{
AdventureworksCatalog catalog =
new AdventureworksCatalog(GetConnectionInfo());
var q = from p in catalog.Products
where p.ProductID == ProductID
select p;
if (q.Count() == 1)
{
return new Product()
{
ProductID = q.First().ProductID,
Name = q.First().Name,
Number = q.First().Number,
Color = q.First().Color,
Description = q.First().Description
};
}
else
return null;
}
}
}
Adding Creator, Updater, and Deleter Methods
For the .NET Assembly Connector to be fully functional, it must have methods to create, update, and delete items. You can create new methods by clicking the Add a Method link in the Method Details pane. As stated previously, when you start from the Method Details pane, Visual Studio generates model elements appropriate for the method.
The Creator method takes an entity instance as input and returns a new entity instance. The input entity instance is simply a container for the new values, with the exception of Identifier, because that value is created in the external system. Each field that contains information necessary for the creation of the new item has a CreatorField property set to True. The following code shows the Creator method model definition.
<Method Name="CreateProduct">
<Parameters>
<Parameter Name="returnProduct" Direction="Return ">
<TypeDescriptor Name="ReturnProduct"
IsCollection="false"
TypeName="ProductConnector.ProductModel.Product, ProductSystem">
<TypeDescriptors>
<TypeDescriptor Name="Color" TypeName="System.String" />
<TypeDescriptor Name="Description" TypeName= "System.String" />
<TypeDescriptor Name="Name" TypeName="System.String" />
<TypeDescriptor Name="Number" TypeName="System.String" />
<TypeDescriptor Name="ProductID" IdentifierName="ProductID"
IsCollection="false" TypeName="System.Int32" />
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
<Parameter Name="newProduct" Direction="In">
<TypeDescriptor Name="NewProduct"
IsCollection="false"
TypeName="ProductConnector.Product, ProductSystem ">
<TypeDescriptors>
<TypeDescriptor Name="Color" TypeName="System.String"
CreatorField= "true" />
<TypeDescriptor Name="Description" TypeName="System.String"
CreatorField="true" />
<TypeDescriptor Name="Name" TypeName="System.String"
CreatorField="true" />
<TypeDescriptor Name="Number" TypeName="System.String"
CreatorField="true " />
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="CreateProduct" Type="Creator"
ReturnParameterName="returnProduct "
ReturnTypeDescriptorPath="ReturnProduct" />
</MethodInstances>
</Method>
The Updater method takes an entity instance as an input. The input entity instance is the entity to update. Each field that contains information necessary to update the item in the external system has an UpdaterField property set to True. The following code shows the Updater method model definition.
<Method Name="UpdateProduct">
<Parameters>
<Parameter Name="product" Direction="In ">
<TypeDescriptor Name="Product" IsCollection="false"
TypeName="ProductConnector.ProductModel.Product, ProductSystem">
<TypeDescriptors>
<TypeDescriptor Name="Color" TypeName="System.String"
UpdaterField="true" />
<TypeDescriptor Name="Description" TypeName="System.String"
UpdaterField="true" />
<TypeDescriptor Name="Name" TypeName="System.String"
UpdaterField="true" />
<TypeDescriptor Name="Number" TypeName="System.String"
UpdaterField="true" />
<TypeDescriptor Name="ProductID" TypeName="System.Int32"
IsCollection="false" ReadOnly="false" UpdaterField="true" />
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="UpdateProduct" Type="Updater" />
</MethodInstances>
</Method>
Although it is less likely that you will want to allow end users to edit the identifier of an entity instance, you can provide this capability in the Updater method. For the identifier to be updated, the Updater method must accept a separate parameter containing the new value for the identifier. This parameter must have the PreUpdaterField property set to True. The following code shows the BDC Metadata Model for the parameter and the resulting function signature.
<Parameter Name="NewProductID" Direction="In">
<TypeDescriptor Name="ProductID" TypeName="System.Int32"
IsCollection="false" PreUpdaterField="true"
IdentifierName="ProductID" />
</Parameter>
public void UpdateProduct(Product ProductIn, int NewProductID){}
The Deleter method takes an Identifier as an input. The Identifier is the entity to delete. The following code shows the Deleter method model definition.
<Method Name="DeleteProduct ">
<Parameters>
<Parameter Name="productID" Direction="In">
<TypeDescriptor Name="ProductID" TypeName="System.Int32"
IdentifierEntityName="Product"
IdentifierEntityNamespace="ProductConnector.ProductModel"
IdentifierName="ProductID" />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="DeleteProduct" Type="Deleter" />
</MethodInstances>
</Method>
Visual Studio offers you a list of several methods to create and even builds the correct function signature for you. The only modification you must make is to remove the static keyword from the signatures. Then the methods can be implemented with the following code.
public Product CreateProduct(Product ProductIn)
{
AdventureworksCatalog catalog =
new AdventureworksCatalog(GetConnectionInfo());
AdventureworksData.Product newProduct = new AdventureworksData.Product()
{
Name = ProductIn.Name,
Number = ProductIn.Number,
Color = ProductIn.Color,
Description = ProductIn.Description
};
catalog.AddToProducts(newProduct);
catalog.SaveChanges();
ProductIn.ProductID = newProduct.ProductID;
return ProductIn;
}
public void UpdateProduct(Product ProductIn)
{
AdventureworksCatalog catalog =
new AdventureworksCatalog(GetConnectionInfo());
AdventureworksData.Product product =
catalog.Products.First(p => p.ProductID == ProductIn.ProductID);
product.Name = ProductIn.Name;
product.Number = ProductIn.Number;
product.Color = ProductIn.Color;
product.Description = ProductIn.Description;
catalog.SaveChanges();
}
public void DeleteProduct(int ProductID)
{
AdventureworksCatalog catalog =
new AdventureworksCatalog(GetConnectionInfo());
AdventureworksData.Product product =
catalog.Products.First(p => p.ProductID == ProductID);
catalog.DeleteObject(product);
catalog.SaveChanges();
}
Adding a StreamAccessor Method
The .NET Assembly Connector supports far more method stereotypes than SPD. Because of this support, .NET Assembly Connectors are often written solely to implement stereotypes not available in SPD. A good example of one of these additional methods is the StreamAccessor. The StreamAccessor method is used to return a stream from the .NET Assembly Connector typically associated with a file. In the walkthrough, the external system contained a photo for each product. You can use StreamAccessor to return the photo. The input parameter is the identifier and the output parameter is the stream. The following code shows the BDC Metadata Model for the StreamAccessor method definition.
<Method Name="ReadPhoto">
<Parameters>
<Parameter Name="ProductID" Direction="In">
<TypeDescriptor Name="ProductID" TypeName="System.Int32"
IdentifierName="ProductID" IsCollection="false" />
</Parameter>
<Parameter Name="Photo" Direction="Return">
<TypeDescriptor Name="PhotoTypeDescriptor" TypeName="System.Stream" />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="ReadPhotoInstance" Type="StreamAccessor"
ReturnParameterName="Photo"
ReturnTypeDescriptorPath="PhotoTypeDescriptor" />
</MethodInstances>
</Method>
Implementing StreamAccessor is a matter of reading the file contents and returning them as a stream. In this case, the file is kept as a BLOB in the database. The following code shows how the method is implemented in the walkthrough.
public Stream ReadPhoto(int ProductID)
{
AdventureworksCatalog catalog =
new AdventureworksCatalog(GetConnectionInfo());
var q = from p in catalog.Products
where p.ProductID == ProductID
select p;
if (q.Count() == 1)
{
byte[] buffer = q.First().ProductPhoto;
return new MemoryStream(buffer);
}
else
return null;
}
StreamAccessor methods are not supported in external lists, but they are supported in the External Data Web Parts. Of course, you can also use the BDC runtime API to call the method in your own custom code as well. Entities that expose StreamAccessor show a hyperlink in the External Data Web Part that will allow a download of the file. This hyperlink opens the DownloadExternalData.aspx page, sending a set of query string parameters to invoke the StreamAccessor method for the correct entity instance. The MIMETypeField and MIMEType properties of the MethodInstance element can be used to specify the MIME type of the ECT, which determines which application is used to open the document.
Creating Associations Among Entities
In production systems you will undoubtedly define multiple ECTs, and these ECTs will have relationships among themselves. In the walkthrough, each product was assigned to a category, so it makes sense that there should be a new ECT to represent the category, and that it should be related to the Product. To start, a new entity named Category was created, along with the Finder and SpecificFinder methods. The process of creating the new ECT is identical to the process of creating the Product entity.
In addition to the new Category entity being created, the Product entity must be updated to contain the CategoryID of the associated category. The model, entity class, and service class will all require changes to support the new CategoryID field. The changes, however, are straightforward and similar to those required by the other fields defined in the entity.
After the entities are defined, you can define an association using the Association item in the toolbox. This item works a little differently than most toolbox items. Instead of dragging and dropping the shape, you must click the shape in the toolbox. Then you can click the one (parent) entity and drag an association to the many (child) entity.
When you create the association, Visual Studio presents the Association Editor dialog box. In this dialog box, you must map each TypeDescriptor that represents the foreign key from the many (child) entity to the one (parent) entity. In the walkthrough, each of the TypeDescriptors representing the CategoryID in the Product entity was mapped to the CategoryID Identifier in the Category entity.
The Association Editor creates a one-to-many association and a reverse association by default. The one-to-many association returns all child entity instances for a given parent, and the reverse association returns the parent entity instance for a given child. In the case of the walkthrough, only the one-to-many association was retained; the reverse association was deleted.
When the Association Editor dialog box is closed, the underlying model is updated with AssociationNavigator methods. These methods pass in an identifier and return associated entities. In the walkthrough, Category Identifier was passed in, and multiple Product entity instances were returned. Function stubs are created in the service class for each AssociationNavigator method. Implementing these methods requires executing the necessary code to return the required entities. The following code shows the implementation for the walkthrough.
public IList<Product> CategoryToProduct(int categoryID)
{
AdventureworksCatalog catalog =
new AdventureworksCatalog(GetConnectionInfo());
var q = from p in catalog.Products
where p.CategoryID == categoryID
orderby p.Name
select p;
List<Product> products = new List<Product>();
foreach (var i in q)
{
products.Add(
new Product()
{
ProductID = i.ProductID,
Name = i.Name,
Number = i.Number,
Description = i.Description,
Color = i.Color,
CategoryID = i.CategoryID
});
}
return products;
}
Understanding Nonforeign Key Relationships
Although it is common to have associations between entities through foreign keys, that is not always the case. The situation in which you’re most likely to find a nonforeign key relationship is that of a many-to-many relationship. A database may be designed, for example, to keep the relationships in a separate table so that they are not directly available through a foreign key.
In addition to the AssociationNavigator methods, you might need to include Associator and Disassociator methods. These are intended to modify the data in the external system that manages the relationship. You can use the methods to modify the table that contains a many-to-many relationship in the database.
As a quick sidebar example, consider an external system that relates people to action items. A table named Resources maintains information about the people, whereas a table named ActionItems maintains tasks. In the system design, many tasks can be assigned to a single resource, and many resources can be assigned to a single task. Your application should show the tasks assigned to a given resource and show the resources assigned to a given task. In this case you use the Association Editor, but uncheck the Is Foreign Key Association check box. In addition, add Associator and Disassociator methods.
The Associator and Disassociator methods have two input parameters. These are the identifiers of the entity instances to associate or disassociate. In code you can use these values to modify the table defining the many-to-many relationship. The following code shows the BDC Metadata Model for the Associator method.
<Method Name="AssociateResourceToTask"> <Parameters> <Parameter Name="resourceID" Direction="In"> <TypeDescriptor Name="ResourceID" TypeName="System.Int32" IdentifierEntityName="Resource" IdentifierEntityNamespace="ActionItems.ActionItemsModel" IdentifierName="ResourceID" ForeignIdentifierAssociationEntityName="Resource" ForeignIdentifierAssociationEntityNamespace="ActionItemsModel" ForeignIdentifierAssociationName="AssociateResourceToTaskAssociator" /> </Parameter> <Parameter Name="taskID" Direction="In"> <TypeDescriptor Name="TaskID" TypeName="System.Int32" IdentifierEntityName="Task" IdentifierEntityNamespace="ActionItemsModel" IdentifierName="TaskID" /> </Parameter> </Parameters> <MethodInstances> <Association Name="AssociateResourceToTaskAssociator" Type="Associator"> <SourceEntity Name="Resource" Namespace="ActionItemsModel" /> <DestinationEntity Name="Task" Namespace="ActionItemsModel" /> </Association> </MethodInstances> </Method>
Testing the Connector
When the .NET Assembly Connector is complete, you can deploy and test it. Using the connector developed in the walkthrough, you should be able to create external lists and use the External Data Web Parts. The associations between the entities should result in appropriate pickers appearing when entity instances are created or edited. As with all features created in Visual Studio 2012, you can easily debug the .NET Assembly Connector by setting breakpoints in the code and pressing F5.
Handling Runtime and Validation Errors
Handling runtime and validation errors in connectors is straightforward because unhandled errors are simply bubbled back up to the browser and displayed in the external list. The general approach is to handle any errors within the connector code when it makes sense to do so, but if the error needs to be returned to the user, a Microsoft.BusinessData.Runtime.RuntimeException should be thrown instead. The RuntimeException class has several derivations that are useful for connectors, but it is easiest to just throw a LobBusinessErrorException, which is the most generic derivation.
PACKAGING CONSIDERATIONS
As with all SharePoint projects, Business Data Connectivity Model projects are packaged for deployment as cabinet files with a .wsp extension. When projects containing BDC Metadata Models are packaged in WSP files, special attention must be given to the values set for the feature properties. Feature properties are set within Visual Studio 2012 and appear as Property elements within the Feature.xml file of the project. Business Data Connectivity Model projects have four key feature properties, as shown in the following code.
<?xml version="1.0" encoding="utf-8"?>
<Feature xmlns="http://schemas.microsoft.com/sharepoint/"
Description="A .NET Assembly Connector"
Id="bc975901-3142-4fbf-9e3c-1f124b6c890d"
ReceiverAssembly=". . ."
ReceiverClass=". . ."
Scope="Farm"
Title="My Connector">
<Properties>
<Property Key="MyModel"
Value="BdcAssembliesMyConnector.dll" />
<Property Key="IncrementalUpdate" Value="true" />
<Property Key="ModelFileName" Value="MyModelMyModel.bdcm" />
<Property Key="SiteUrl" Value="http://awserver/bcs/" />
</Properties>
<ElementManifests>
<ElementFile Location="MyModelMyModel.bdcm" />
<ElementFile Location="BdcAssembliesMyConnector.dll" />
</ElementManifests>
</Feature>
Most of the properties in the file are set by Visual Studio and do not require any editing. However, these properties should always be verified before packaging. Renaming elements during development and the particulars of the target SharePoint environment may necessitate changes to the values.
The first property uses the name of the LobSystem as the Key. The Value references the assembly that implements the operations defined in the model. This property is set by Visual Studio and generally does not need to be changed. In some scenarios, this property may be set incorrectly if you rename LobSystem after creating operations in the BDC Metadata Model.
The second property is the IncrementalUpdate property, and it supports modifying parts of the BDC Metadata Model. This property is set by Visual Studio and also does not need to be changed.
The third property is ModelFileName. This property references the BDCM file that contains the model. This property is set by Visual Studio and generally does not need to be changed. In some scenarios, this property may be set incorrectly if you rename the model during development.
The fourth property is SiteUrl. This property is used to identify the BDC Service Application where the BDC Metadata Model should be deployed. The SiteUrl property is not present by default in the Business Data Connectivity Model project. When the property is not present, the deployment assumes a value for the property of http://localhost:80. This means that the BDC Metadata Model will be deployed to the BDC Service Application associated with the site located at http://localhost:80. If, however, no site exists at http://localhost:80, the deployment fails. In this case, you must explicitly set the SiteUrl value to reference a site associated with the correct BDC Service Application.
You can review and modify the Feature properties directly in Visual Studio 2012. First, select the BDC Metadata Model project item in the Solution Explorer. Second, select Feature Properties from the Properties window, which opens a dialog box. Finally, set the property values in the dialog box.
ENABLING SEARCH SUPPORT
When creating BCS solutions, you are required to define at least Finder and SpecificFinder methods to support the creation of external lists. In addition, these methods are used to support indexing and search. To enable SharePoint to search an external system through the BDC Metadata Model, you must include a RootFinder property in a Finder method instance. The following code shows how the property is used.
<MethodInstances> <MethodInstance Type="Finder" ReturnParameterName="Read List" Default="true" Name="Read List" DefaultDisplayName="Product Read List"> <Properties> <Property Name="RootFinder" Type="System.String"></Property> </Properties> </MethodInstance> </MethodInstances>
Notice that the RootFinder property has no value. If you include a value for this property, it is simply ignored. Sometimes you may see a value of x in the property, but this is simply a style choice. The presence of the property is enough to designate the Finder method as the RootFinder.
Although the RootFinder property is the only one necessary to support indexing the external system, an additional property is required to allow the ECT to be selected as a content source in the search service application (SSA). The ShowInSearchUI property must be present on the LobSystemInstance for the ECT to be selectable as a content source. The following code shows the property.
<LobSystemInstances>
<LobSystemInstance Name="AWProducts">
<Properties>
<Property Name="AuthenticationMode" Type="System.String">
PassThrough
</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">
SqlServer
</Property>
<Property Name="RdbConnection Data Source" Type="System.String">
AWSERVER
</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">
AdventureworksProducts
</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">
SSPI
</Property>
<Property Name="RdbConnection Pooling" Type="System.String">
True
</Property>
<Property Name="ShowInSearchUI" Type="System.String"></Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
Just like the RootFinder property, the ShowInSearchUI property does not require a value. Its presence is enough to allow the ECT to appear as a content source in the Search Service Application (SSA). Again, as with the RootFinder property, you may occasionally see examples in which this property has a value of x.
When the RootFinder and ShowInSearchUI properties are added to the model, you can configure the ECT as a content source. Simply create a new content source in the SSA and select Line of Business Data as the type. All the ECTs with the ShowInSearchUI property appear next to check boxes. Check the ECT and you can immediately begin a full crawl of the external system, provided the account performing the crawl has permission to use the BDC Metadata Model and can access the external system. Figure 14-4 shows the Add Content Source page with some ECTs visible.
In addition to full crawls, BCS solutions can also support incremental crawls with the LastModifiedTimeStampField property. This property has a value that refers to a DateTime field, which indicates the last time the item was modified. If the item has not been modified since the last incremental crawl, it isn’t included in the current incremental crawl. The following code shows an example of the property mapping to a field in the ECT named ChangedDateTime. This mapping specifies that the ChangedDateTime field in the external system will be used to determine whether the row of data has changed since the last crawl.
<MethodInstances>
<MethodInstance Type="Finder" ReturnParameterName="Read List"
Default="true" Name="Read List" DefaultDisplayName="Product Read List">
<Properties>
<Property Name="RootFinder" Type="System.String"></Property>
<Property Name="LastModifiedTimeStampField" Type="System.String">
ChangedDateTime
<Property Name="UseClientCachingForSearch"
Type="System.String"></Property>
</Properties>
</Property>
</Properties>
</MethodInstance>
</MethodInstances>
Along with the LastModifiedTimeStampField property, note the use of the UseClientCachingForSearch property in the Metadata Model. The presence of this property indicates that the RootFinder is capable of returning all content for an entity instance within a 30 KB data block. This tells the crawler that it does not need to make a subsequent call to the SpecificFinder method because all required data was returned from the RootFinder. Note that the cache size is fixed and works on a per-item basis, so only those items that cannot return all required data result in an additional call. If the UseClientCachingForSearch property is not present, the LastModifiedTimeStampField property must also be included under the SpecificFinder method instance because it will be called for each item crawled. It is generally a good idea to use the UseClientCachingForSearch property whenever possible because it makes crawling more efficient.
You can map the Title field in the search results by using the Title property under the Entity element in the Metadata Model. The value of the Title property refers to the name of the ECT field that should be used for the Title field. The Title field will then be used as the header for each entity instance in the search results. The following code maps the Name field of the Entity to the Title field of the search results.
<Entity Namespace="http://aw/bcs" Version="1.0.0.0" EstimatedInstanceCount="10000" Name="AWProduct" DefaultDisplayName="AWProduct"> <Properties> <Property Name="Title" Type="System.String">Name</Property> </Properties> </Entity>
The Author, Description, and Link fields in the search results are mapped through the AuthorField, DescriptionField, and DisplayUriField properties, respectively. The values of these properties map to fields in the ECT. The following code shows a sample.
<MethodInstances>
<MethodInstance Name="ReadAllItems" Type="Finder"
ReturnParameterName="documentList"
ReturnTypeDescriptorPath="DocumentList"
DefaultDisplayName="Read All Items" Default="true">
<Properties>
<Property Name="RootFinder" Type="System.String"></Property>
<Property Name="LastModifiedTimeStampField" Type="System.String">
Modified
</Property>
<Property Name="DisplayUriField" Type="System.String">
Url
</Property>
<Property Name="DescriptionField" Type="System.String">
Description
</Property>
<Property Name="AuthorField" Type="System.String">
Author
</Property>
<Property Name="UseClientCachingForSearch"
Type="System.String"></Property> </Properties>
</MethodInstance>
</MethodInstances>
If the UseClientCachingForSearch property is present, the AuthorField, DescriptionField, and DisplayUriField properties need be defined only beneath the RootFinder. If the UseClientCachingForSearch property is not present, these properties must also be defined under the SpecificFinder method instance.
WORKING WITH THE BDC SERVER RUNTIME OBJECT MODEL
The BDC Server Runtime object model is the API used to write custom solutions that run on the SharePoint 2013 server and utilize BCS artifacts. Using the object model is fairly straightforward, but it gives you significant control over how and when operations are performed. In addition, using the object model allows you to create custom user interface elements such as console applications and web parts.
The BDC Server Runtime object model is contained in the Microsoft.SharePoint.dll and Microsoft.BusinessData.dll assemblies. Both of these assemblies are located in the ISAPI directory. Any solution you create needs a reference to both of these assemblies, which contain many namespaces. The following code shows the typical using statements.
//Reference to Microsoft.SharePoint.dll using Microsoft.SharePoint; using Microsoft.SharePoint.Administration; using Microsoft.SharePoint.BusinessData; using Microsoft.SharePoint.BusinessData.Runtime; using Microsoft.SharePoint.BusinessData.SharedService; using Microsoft.SharePoint.BusinessData.MetadataModel; //Reference to Microsoft.BusinessData.dll using Microsoft.BusinessData; using Microsoft.BusinessData.MetadataModel; using Microsoft.BusinessData.Runtime; using Microsoft.BusinessData.MetadataModel.Collections;
Connecting to the Metadata Catalog
To execute code against BCS solutions using the BDC Server Runtime, you must establish a connection to the metadata catalog where the ECTs are stored. The first step in this process is to make a connection to the BDC Service Application. You establish this connection in different ways, depending upon whether your code is running within a SharePoint context or is simply on the SharePoint server. In either case, however, you’ll use the Microsoft.SharePoint.SPServiceContext class.
The SPServiceContext class enables your code to communicate with SharePoint service applications. When your code runs inside of a SharePoint context (for example, a custom web part), you can use the Current property to retrieve the current service context. The GetDefaultProxy() method may then subsequently be used to get the service proxy for any service. If your code runs outside of a SharePoint context (for example, a console application), the context must be explicitly set using a SPSite object. In either case, you then use the Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplicationProxy class to get a reference to the BDC Service Application proxy. The GetDefaultProxy() method of the SPServiceContext class returns the default service application proxy for the type specified. The GetProxies() method returns all available service application proxies for the type specified. The following code shows first how to get the default BdcServiceApplicationProxy object from within a SharePoint context and then from outside of a SharePoint context.
//Within SharePoint Context
BdcServiceApplicationProxy proxy =
(BdcServiceApplicationProxy)SPServiceContext.Current.
GetDefaultProxy(typeof(BdcServiceApplicationProxy));
//Outside SharePoint Context
using (SPSite site = new SPSite(siteCollectionUrl))
{
BdcServiceApplicationProxy proxy =
(BdcServiceApplicationProxy)SPServiceContext.GetContext(site).
GetDefaultProxy(typeof(BdcServiceApplicationProxy));
}
In addition to using the BdcServiceApplicationProxy object to establish context, you can also use the Microsoft.SharePoint.BusinessData.SharedService.BdcService class. The BdcService class is an abstraction of the BDC Service Application, which is useful for determining whether a BDC Service Application is available in the farm. The following code shows how to check the availability of the BDC Service Application in a farm.
BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
if (service == null)
throw new Exception("No BDC Service Application found.");
After you establish a context, you can connect to the metadata catalog in the BDC Service Application. The metadata catalog on the server is represented by the Microsoft.SharePoint.BusinessData.MetadataModel.DatabaseBackedMetadataCatalog class. Both the BdcServiceApplicationProxy object and the BdcService object can return a DatabaseBackedMetadataCatalog object. The following code shows both approaches.
//Using BdcServiceApplicationProxy DatabaseBackedMetadataCatalog catalog = proxy.GetDatabaseBackedMetadataCatalog(); //Using BdcService DatabaseBackedMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog( SPServiceContext.GetContext(site));
Retrieving Model Elements
When you establish a connection to the metadata catalog, you can retrieve elements of the BDC Metadata Models. This includes ECTs, systems, and operations. The purpose of retrieving these items is to execute the defined method instances against the defined external system. The DatabaseBackedMetadataCatalog class has five methods for retrieving model elements: GetEntity(), GetEntities(), GetLobSystem(), GetLobSystems(), and GetById().
Typically, your solution will start by retrieving a reference to the ECTs that represent the data you want to use. The simplest way to retrieve an ECT is to use the GetEntity() method passing in the name and namespace for the desired entity. The method returns a Microsoft.BusinessData.MetadataModel.IEntity interface representing the ECT, as shown in the following code.
IEntity ect = catalog.GetEntity("MyNamespace", "MyEntity");
Although the code for retrieving an ECT is simple, it is not the most efficient. Although the BDC Server Runtime object model generally interacts with cached metadata models, the GetEntity(), GetEntities(), GetLobSystem(), and GetLobSystems() methods do not. Instead, they call directly to the Metadata Catalog database, which is less efficient. The solution to this problem is to use the GetById() method to retrieve the ECTs and LobSystems. The GetById() method takes the ID and type of the element to return and executes against the cached model. The challenge in using the GetById() method is determining the ID of the wanted element. The best approach is to use the GetEntity() method on the first call and save the ID of the element for future use. The following code shows the relationship between the GetEntity() method and the GetById() method.
//Get Entity the easy way on first call IEntity ect = catalog.GetEntity(entityNamespace, entityName); //Save Entity data uint ectId = Convert.ToUInt32(ect.Id); Type ectType = ect.GetType(); //Get Entity the fast way on subsequent calls ect = (IEntity)catalog.GetById(ectId, ectType);
Along with retrieving ECTs and LobSystems from the DatabaseBackedMetadataCatalog object, many objects have methods for retrieving related objects. For example, the GetLobSystem() method of the IEntity interface returns the related LobSystem for the ECT. In this way, you can retrieve whatever model elements are required for your application.
Executing Operations
The whole point of connecting to the metadata catalog and retrieving the ECT is to allow for the execution of the operations defined as method instances within the BDC Metadata Model. With full access to the operations, you can create complete custom applications. These applications can use any of the available method stereotypes, thus leaving behind any of the limitations found in the SharePoint Designer and the Business Data web parts.
The approach used to execute an operation varies slightly from stereotype to stereotype. In the case of the Finder methods, for example, you must first retrieve the method instance before executing. This is because a BDC Metadata Model may define multiple Finder methods. In the case of the Creator, Updater, and Deleter methods, the object model provides a more direct approach because only a single method instance of these stereotypes can exist in the model for any entity.
Along with specific support for various stereotypes, the object model also provides generic support to execute any method instance. The following code shows a console application that uses the Execute() method of the IEntity to execute a Finder method and display the results. This code works for any model and any Finder method based on the arguments passed to it.
static void Main(string[] args)
{
try
{
if (args.Count() != 5)
throw new Exception("Usage: ExecuteFinder.exe
SiteCollectionUrl,
LobSystemInstance,
EntityName,
EntityNamespace,
FinderMethodInstance");
string siteCollectionUrl = args[0];
string lobSystemInstance = args[1];
string entityName = args[2];
string entityNamespace = args[3];
string finderMethodInstance = args[4];
using (SPSite site = new SPSite(siteCollectionUrl))
{
//Connect to the BDC Service Application proxy
BdcService service =
SPFarm.Local.Services.GetValue<BdcService>();
if (service == null)
throw new Exception("No BDC Service Application found.");
//Connect to metadata catalog
DatabaseBackedMetadataCatalog catalog =
service.GetDatabaseBackedMetadataCatalog(
SPServiceContext.GetContext(site));
//Get Entity
IEntity ect = catalog.GetEntity(entityNamespace, entityName);
//Get LobSystem
ILobSystem lob = ect.GetLobSystem();
//Get LobSystemInstance
ILobSystemInstance lobi =
lob.GetLobSystemInstances()[lobSystemInstance];
//Get Method Instance
IMethodInstance mi =
ect.GetMethodInstance(finderMethodInstance,
MethodInstanceType.Finder);
//Execute
IEnumerable items = (IEnumerable)ect.Execute(mi, lobi);
//Display
foreach (Object item in items)
{
PropertyInfo[] props = item.GetType().GetProperties();
foreach (PropertyInfo prop in props)
{
Console.WriteLine(prop.GetValue(item, null));
}
}
}
}
catch (Exception x)
{
Console.WriteLine(x.Message);
}
}
When executing methods using the generic approach provided by the Execute() method, you often need to pass in parameters such as when you execute a SpecificFinder method. In these cases, you must retrieve the required parameters from the method and set them. The following code snippet shows how to do this for a SpecificFinder method associated with a BDC Metadata Model that uses the SQL connector.
//Get Method Instance
IMethodInstance mi = ect.GetMethodInstance(specificFinderMethodInstance,
MethodInstanceType.SpecificFinder);
//Get Parameters
IParameterCollection parameters = mi.GetMethod().GetParameters();
//Set Parameters
object[] arguments = new object[parameters.Count];
arguments[0] = entityInstanceIdentifier;
//Execute
ect.Execute(mi, lobi, ref arguments);
//Display
PropertyInfo[] props = arguments[1].GetType().GetProperties();
PropertyInfo prop = props[0];
SqlDataReader reader = (SqlDataReader)(prop.GetValue(arguments[1], null));
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine(reader.GetString(3) + " " + reader.GetString(5));
}
}
In the code, note how the arguments are passed by reference to the Execute() method. This is required because the Return parameter is placed in the array during execution. You can then read the Return parameter and cast it to an appropriate type for display. In the code sample, the Return parameter is cast to a SqlDataReader, which is the type returned from methods that use the SQL connector.
Although CRUD operations are certainly the most common in BCS solutions, accessing documents through streams is often a critical part of any SharePoint solution. Therefore, the StreamAccessor stereotype stands out as important. The Execute() method can be used to invoke a StreamAccessor method and return a stream for downloading. The following code shows a typical StreamAccessor method defined in a BDC Metadata Model.
<Method Name="ReadContents" DefaultDisplayName="Read Contents">
<Parameters>
<Parameter Name="id" Direction="In">
<TypeDescriptor Name="ID" IdentifierName="ID"
TypeName="System.Int32" IsCollection="false" />
</Parameter>
<Parameter Name="contents" Direction="Return">
<TypeDescriptor Name="Contents" TypeName="System.IO.Stream" />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="ReadContents" Type="StreamAccessor"
ReturnParameterName="contents" ReturnTypeDescriptorPath="Contents"
DefaultDisplayName="ReadContents">
</MethodInstance>
</MethodInstances>
</Method>
The method instance returns a System.IO.Stream object based on a System.Int32 value. You can use the Execute() method to invoke the StreamAccessor. The following code shows how the method instance can be invoked and the stream downloaded to the client based on query string parameters passed into an ASPX page.
//Connect to server-side BCS
BdcServiceApplicationProxy proxy =
(BdcServiceApplicationProxy)SPServiceContext.
Current.GetDefaultProxy(typeof(BdcServiceApplicationProxy));
DatabaseBackedMetadataCatalog catalog =
proxy.GetDatabaseBackedMetadataCatalog();
IEntity ect = catalog.GetEntity("MyNamespace", "DocumentECT");
ILobSystem lob = ect.GetLobSystem();
ILobSystemInstance lobi = lob.GetLobSystemInstances()["MyDMSInstance"];
IMethodInstance mi =
ect.GetMethodInstance("ReadContents",
MethodInstanceType.StreamAccessor);
//Call BCS to get stream
object[] args = { int.Parse(Request.QueryString["DocumentId"]), null };
ect.Execute(mi, lobi, ref args);
byte[] buffer = ((MemoryStream)args[1]).ToArray();
//Download
this.Page.Response.Clear();
this.Page.Response.ClearHeaders();
this.Page.Response.AddHeader("Content-Disposition",
"attachment; filename="" + Request.QueryString["fileName"] + """);
this.Page.Response.AddHeader("Content-Length", buffer.Length.ToString());
this.Page.Response.BinaryWrite(buffer);
this.Page.Response.Flush();
this.Page.Response.End();
Although the Execute() method provides good functionality for executing any method instance, most often the application code is tailored for the specific stereotype being invoked. The following sections detail the support provided by the BDC Server Runtime object model for invoking specific method stereotypes.
Executing Finder Methods
Finder methods are the backbone of any custom BCS application. To invoke a Finder method instance, you use the FindFiltered() method of the IEntity. The FindFiltered() method returns entity instances from a Finder method using filter criteria.
If the method is the default Finder method, its name does not need to be provided. If the Finder method to execute is not the default, its name is provided as a String value to the FindFiltered() method. Be careful to use the name of the method instance as defined in the BDC Metadata Model and not the name of the method.
If the Finder method defines filters (such as a limit, wildcard, or page filter), these values must be provided in the call to the FindFiltered() method. An IFilterCollection can be returned by calling the GetFilters() method of the IMethodInstance. The values for the filters can then be set. The following code shows how to get the filter collection and set values.
IMethodInstance mi = ect.GetMethodInstance(FinderMethodInstanceName,
MethodInstanceType.Finder);
IFilterCollection filters = mi.GetFilters();
(filters[0] as LimitFilter).Value = 10;
(filters[1] as PageNumberFilter).Value = 2;
(filters[3] as WildcardFilter).Value = "Bike";
(filters[4] as ComparisonFilter).Value = "CN123720";
In most applications, you already know which filters the method instance is expecting. In these cases, you can set the filters directly, as shown in the preceding code. If, however, you do not know which filters are expected, you can determine this dynamically by iterating through the collection of filters, as shown in the following code.
foreach (IFilter filter in filters)
{
Console.WriteLine("Filter Type: " +
filter.FilterDescriptor.FilterType.ToString());
Console.WriteLine("Filter Field: " +
filter.FilterDescriptor.FilterField);
}
When executing the FindFiltered() method, you may optionally specify an OperationMode for the call that allows data to be read from a cache. However, the OperationMode has no effect on server-side operations. The presence of the OperationMode is solely to maintain complementary signatures between the BDC Client and BDC Server APIs. Remember that the server never caches data — only model elements. The OperationMode has meaning only on the client.
The FindFiltered() method returns a Microsoft.BusinessData.Runtime.IEntityInstanceEnumerator. The IEntityInstanceEnumerator object provides a forward-only collection of entity instances that you can read. After reading the entity instances from the collection, the Close() method must be called to release the resources used to access the external system. The following code shows the basic approach.
//Connect to BDC Service Application
BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
if (service != null)
{
//Get Metadata elements
DatabaseBackedMetadataCatalog catalog =
service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
IEntity ect = catalog.GetEntity(EntityNamespace, EntityName);
ILobSystem lob = ect.GetLobSystem();
ILobSystemInstance lobi =
lob.GetLobSystemInstances()[LobSystemInstanceName];
}
IMethodInstance mi = ect.GetMethodInstance(FinderMethodInstanceName,
MethodInstanceType.Finder);
IFilterCollection filters = mi.GetFilters();
IEntityInstanceEnumerator items =
ect.FindFiltered(filters, FinderMethodInstanceName);
while (items.MoveNext())
{
Console.WriteLine(items.Current[FieldName].ToString());
}
items.Close();
In addition to enumerating entity instances, you can also return entity instances in a System.Data.DataTable. You can use a DataTable to return entity instances by calling the CreateDataTable() method of the Microsoft.BusinessData.Runtime.IRuntimeHelper interface. You can obtain this interface through the Helper property of the DatabaseBackedMetadataCatalog object. The CreateDataTable() method takes an IEntityInstanceEnumerator object and builds a DataTable from it. The CreateDataTable() method can make it easier to work with entity instances because the DataTable is a familiar and flexible object. In addition, the CreateDataTable() method supports options that allow for paging through entity instances. The following code shows an example of the CreateDataTable() method.
//Connect to BDC Service Application
BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
if (service != null)
{
//Get Metadata elements
DatabaseBackedMetadataCatalog catalog =
service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
IEntity ect = catalog.GetEntity(EntityNamespace, EntityName);
ILobSystem lob = ect.GetLobSystem();
ILobSystemInstance lobi =
lob.GetLobSystemInstances()[LobSystemInstanceName];
}
IMethodInstance mi = ect.GetMethodInstance(FinderMethodInstanceName,
MethodInstanceType.Finder);
IFilterCollection filters = mi.GetFilters();
IEntityInstanceEnumerator items =
ect.FindFiltered(filters, FinderMethodInstanceName);
DataTable dt = ect.Catalog.Helper.CreateDataTable(items);
Executing SpecificFinder Methods
To invoke a SpecificFinder method, you use the FindSpecific() method of the IEntity interface. The FindSpecific() method returns an entity IEntityInstance from a SpecificFinder method given an Identifier.
If the method is the default SpecificFinder method, its name does not need to be provided. If the SpecificFinder method to execute is not the default, its name is provided as a String value to the FindSpecific() method. Be careful to use the name of the method instance as defined in the BDC Metadata Model and not the name of the method.
When calling the FindSpecific() method, you will always provide an Identity object, which represents the Identifier for the wanted entity instance. Simply create a new Identity object using the appropriate value and pass the object as an argument. Identity objects can be created with any data type, but be aware that string values are case-sensitive when used as identifiers. The following code shows how to call the FindSpecific() method.
//Connect to BDC Service Application
BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
if (service != null)
{
//Get Metadata elements
DatabaseBackedMetadataCatalog catalog =
service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
IEntity ect = catalog.GetEntity(EntityNamespace, EntityName);
ILobSystem lob = ect.GetLobSystem();
ILobSystemInstance lobi =
lob.GetLobSystemInstances()[LobSystemInstanceName];
}
//Execute SpecificFinder
int id = 5;
IMethodInstance mi =
ect.GetMethodInstance(SpecificFinderMethodInstanceName,
MethodInstanceType.SpecificFinder);
IEntityInstance item =
ect.FindSpecific(new Identity(id),
SpecificFinderMethodInstanceName,
lobi, true);
Executing Updater Methods
To invoke an Updater method, you first use the FindSpecific() method to return the entity to update. The value of the fields associated with the return entity may then be modified, and those modifications are committed through the Update() method of the IEntityInstance interface. The following code shows how to use the Update() method.
//Connect to BDC Service Application
BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
if (service != null)
{
//Get Metadata elements
DatabaseBackedMetadataCatalog catalog =
service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
IEntity ect = catalog.GetEntity(EntityNamespace, EntityName);
ILobSystem lob = ect.GetLobSystem();
ILobSystemInstance lobi =
lob.GetLobSystemInstances()[LobSystemInstanceName];
}
//Execute SpecificFinder
int id = 5;
IMethodInstance mi =
ect.GetMethodInstance(SpecificFinderMethodInstanceName,
MethodInstanceType.SpecificFinder);
IEntityInstance item =
ect.FindSpecific(new Identity(id),
SpecificFinderMethodInstanceName,
lobi, true);
//Update entity instance
item["Title"] = "My Item";
item["Description"] = "An updated item";
item.Update();
Executing Creator Methods
To invoke a Creator method, you use the Create() method of the IEntity interface. The Create() method returns an Identity for the new entity instance.
When calling the Create() method, you pass the values for the new entity instance in a Microsoft.BusinessData.Runtime.IFieldValueDictionary. The IFieldValueDictionary can be created from a Microsoft.BusinessData.MetadataModel.IView interface. This interface represents all of the fields that are associated with a given method instance. After obtaining the IFieldValueDictionary object, you can either set values for the new entity instance or use the default values, as shown in the following code.
//Connect to BDC Service Application
BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
if (service != null)
{
//Get Metadata elements
DatabaseBackedMetadataCatalog catalog =
service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
IEntity ect = catalog.GetEntity(EntityNamespace, EntityName);
ILobSystem lob = ect.GetLobSystem();
ILobSystemInstance lobi =
lob.GetLobSystemInstances()[LobSystemInstanceName];
}
//Create new entity instance with default values
IView createView = ect.GetCreatorView(CreatorMethodInstanceName);
IFieldValueDictionary fieldValueDictionary = createView.GetDefaultValues();
ect.Create(fieldValueDictionary, lobi);
Executing Deleter Methods
To invoke a Deleter method, you first use the FindSpecific() method to return the entity instance to delete. You can then delete the entity instance using the Delete() method of the IEntityInstance interface. The following code shows how to use the Delete() method.
//Connect to BDC Service Application
BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
if (service != null)
{
//Get Metadata elements
DatabaseBackedMetadataCatalog catalog =
service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
IEntity ect = catalog.GetEntity(EntityNamespace, EntityName);
ILobSystem lob = ect.GetLobSystem();
ILobSystemInstance lobi =
lob.GetLobSystemInstances()[LobSystemInstanceName];
}
//Execute SpecificFinder
int id = 5;
IMethodInstance mi =
ect.GetMethodInstance(SpecificFinderMethodInstanceName,
MethodInstanceType.SpecificFinder);
IEntityInstance item =
ect.FindSpecific(new Identity(id),
SpecificFinderMethodInstanceName,
lobi, true);
//Delete entity instance
item.Delete();
USING ECTS IN SHAREPOINT APPS
Along with most of the SharePoint 2013 workloads, Business Connectivity Services has been updated to work with the app model. App support enhancements in SharePoint 2013 include support for OData sources, app-level External Content Types (ECTs), and a new client-side object model. Together, these enable you to create apps for either on-premises installation or SharePoint online.
Understanding App-Level ECTs
With SharePoint 2013, BCS supports the use of OData sources as the basis for defining an ECT. Because OData is emerging as an accepted standard for exposing data sources in the cloud, these services are an ideal source for developing BCS-based apps. When creating a SharePoint app, you can easily add an ECT based on an OData source by selecting Add ⇒ Content Types for an External Data Source from the project context menu. This action starts a wizard that will prompt you for the endpoint of the OData source. Figure 14-5 shows the wizard referring to the publicly available Northwind data source.
After connecting to the source, the wizard prompts you to select from the available entities exposed by the service. All you have to do is select one or more entities and click Finish. Visual Studio then generates a BDC Metadata Model and an associated external list within your app project. The BDC Metadata Model and the external list definition are packaged and deployed with the app, which uses the model at run time to connect to the source and fill the external list. Figure 14-6 shows the basic app architecture for rendering accessing the OData source.
Under normal circumstances, BDC Metadata Models are stored in the BDC Service Application associated with the farm. Such an architecture would be unacceptable for apps, however, because apps are supposed to be isolated from the other farm elements. In the case of an app, the model is loaded in an in-memory BDC catalog known as the FileBackedMetadataCatalog. The BDC Metadata Model deployed with the app is stored in a special document library and subsequently loaded into the FileBackedMetadataCatalog. When loaded, the model is used to determine the connection to the OData source and what data to retrieve. The external list is then filled with the data in much the same way as any external list in a SharePoint farm. The only drawback is that the external list will not be immediately visible. This is because the app pages do not include a web part or code for displaying the list. This is where some client-side coding comes into play.
Understanding the BCS Client Object Model
To display the data in the external list, you have two options. The first is to make a RESTful call to the external list. External lists support the same RESTful access to data that standard SharePoint lists support. Therefore, there is nothing new to learn, and you may use standard techniques covered elsewhere in this book. The second option is to utilize the client-side object model (CSOM) through JavaScript.
CSOM access to BCS data follows the same general approach used in the server-side model discussed earlier in this chapter. By keeping the approaches similar, it should be easier for developers to learn the CSOM version. The only big difference is that the CSOM version relies on asynchronous operations, so the developer must handle several callbacks to be successful.
All of the objects necessary to work with BCS in an app are contained in the sp.js library found in the LAYOUTS directory. Development begins by retrieving the standard ClientContext object in JavaScript. When the ClientContext is retrieved, it may be used to access the Web associated with the app and then the in-memory SP.BusinessData.AppBdcCatalog object. When the catalog is accessed, the target SP.BusinessData.Entity object can be retrieved using the entity name and entity namespace, as shown in the following code.
var ctx = SP.ClientContext.get_current(); var ect = ctx.get_web().getAppBdcCatalog().getEntity(entityNamespace, entityName); ctx.load(ect);
When the entity is retrieved, it can be used to execute the basic stereotypes associated with it. The JavaScript methods all have names that are similar to the ones found in the server-side object model, and you can see the complete definition for the methods by examining the sp.debug.js library found in the LAYOUTS directory. As an example, you can walk through executing the Finder method using the findFiltered method.
The findFiltered method requires that you provide the name of the method instance, values for any filters associated with the method instance, and the associated SP.BusinessData.LobSystemInstance object. Retrieving the associated SP.BusinessData.LobSystemInstance object requires a round trip, so that should be done first using the following code, which returns a SP.BusinessData.Collections.LobSystemInstanceCollection object.
var ctx = SP.ClientContext.get_current(); var ect = ctx.get_web().getAppBdcCatalog().getEntity(entityNamespace, entityName); ctx.load(ect); var lob = ect.getLobSystem(); ctx.load(lob); var collection = lob.getLobSystemInstances(); ctx.load(collection); ctx.executeQueryAsync(onLobSystemInstancesSuccess, onLobSystemInstancesError);
After the collection is returned, you can look through it for the associated SP.BusinessData.LobSystemInstance object by name. Then you can retrieve and set any required filters. Finally, you can call the findFiltered method, as shown in the following code.
var ctx = SP.ClientContext.get_current();
for (var i = 0; i < collection.get_count(); i++) {
if (collection.get_item(i).get_name() === lobSystemInstanceName) {
lobi = collection.get_item(i);
break;
}
}
var filters = ect.getFilters(methodInstanceName);
ctx.load(filters);
var results = ect.findFiltered(filters, methodInstanceName, lobi);
ctx.load(results);
ctx.executeQueryAsync(onExecuteFinderSuccess, onExecuteFinderError);
When the results of the operation are returned, then you can loop through them and read the property values. Typically, you will take these values and save them into an object array for use when displaying them in a web page. The following code shows how the resulting records can be read.
for (var i = 0; i < results.get_count() ; i++) {
var entityInstance = results.get_item(i);
var fields = entityInstance.get_fieldValues();
var v1 = fields.ProductID;
var v2 = fields.ProductName;
var v3 = fields.CategoryName;
var v4 = fields.UnitsInStock;
var v5 = fields.ReorderLevel;
}
SUMMARY
Although many BCS solutions can be created with no code, there are many limitations associated with data presentation and functionality that require custom coding to overcome. The .NET Assembly Connector enables significant control over how external systems are accessed by SharePoint and supports indexing of nearly any external system. As your solutions become more complex, custom coding through the BDC Server object model can help to create professional and efficient solutions that can scale along with your SharePoint farm. Finally, the new SharePoint 2013 app model enables the creating of apps with ECTs that utilize OData sources.