PROBLEM CASE

Author an ECT using Visual Studio, and generate actions—View and Edit for individual items. Develop a .NET assembly connector to Employee information in the Employees table in the AdventureWorks database. Enable the ECT to use the .NET assembly connector, and, finally, create an external list using this ECT.

Solution

By the end of this exercise, you will have

• a .NET Type assembly connector and
• an ECT

When you use the Visual Studio BDC template, you automatically obtain the BDC Model and External System along with an ECT. But first, using Listings 4-1 through 4-3, create three stored procedures.

Listing 4-1.  Stored Procedure for Retrieving All Employee Information

USE [AdventureWorks2012]
GO
  

CREATE PROCEDURE [dbo].[uspGetEmployees]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT BusinessEntityID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus,
    Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, ModifiedDate
    FROM
    HumanResources.Employee
    WHERE OrganizationNode.GetAncestor(1) IS NOT NULL
END

GO

Listing 4-2.  Stored Procedure for Retrieving Specific Employee Information Using Employee ID

USE [AdventureWorks2012]
GO
  
CREATE PROCEDURE [dbo].[uspGetEmployeesById]
@BusinessEntityID [int]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT BusinessEntityID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus,
    Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, ModifiedDate
    FROM
    HumanResources.Employee
    WHERE
    BusinessEntityID = @BusinessEntityID

END

GO

Listing 4-3.  Stored Procedure for Updating Specific Employee Information Based on Employee ID

USE [AdventureWorks2012]
GO
  
CREATE PROCEDURE [dbo].[uspSetEmployeesValueById]
@BusinessEntityID [int],
@NationalIDNumber nvarchar(15),
@LoginID nvarchar(256),
@JobTitle nvarchar(50),
@BirthDate DateTime,
@MaritalStatus nchar(1),
@Gender nchar(1),
@HireDate DateTime,
@SalariedFlag bit,
@VacationHours smallint,
@SickLeaveHours smallint,
@CurrentFlag bit,
@ModifiedDate DateTime
AS
BEGIN
  
    UPDATE HumanResources.Employee
    Set NationalIDNumber = @NationalIDNumber,
        LoginID = @LoginID,
        JobTitle = @JobTitle,
        BirthDate = @BirthDate,
        MaritalStatus = @MaritalStatus,
        Gender = @Gender,
        HireDate = @HireDate,
        SalariedFlag = @SalariedFlag,
        VacationHours = @VacationHours,
        SickLeaveHours = @SickLeaveHours,
        CurrentFlag = @CurrentFlag,
        ModifiedDate = @ModifiedDate
    WHERE
        BusinessEntityID = @BusinessEntityID
END

GO

Now that you have the necessary stored procedures, let’s go ahead and create the project in Visual Studio 2012:

1. Open Visual Studio, and create a SharePoint 2013 - Empty Project.
2. Enter the SharePoint site URL to use for debugging, and choose for the solution to be deployed as a farm solution.⁸ Click Finish to continue.
3. Right-click the project, and select Add, New Item, Business Data Connectivity Model (Farm Solution only).
4. After the project is successfully created, it will display windows that should look similar to those in Figure 4-23.

   

   Figure 4-23. BDC model in Visual Studio 2012

    Note  If you don’t see the BDC Explorer window, you can launch it from View Other Windows BDC Explorer.

5. Let’s familiarize ourselves a little with important windows in the project, as indicated in Figure 4-23.
   • a.  BDC Explorer: This allows you to define a model, its entities, and their identifiers and methods. Methods can be configured with in and out parameters.
   • b.  BDC Method Details: This allows you to configure both in and out parameters and also to add new method templates, such as Blank, Creator, Deleter, Finder, Specific Finder, and Updater.
   • c.  BDC Model designer: This is a designer space where you can drag and drop entities and create associations.

   By default, an entity (Entity1) is added to the model as shown in Figure 4-23. You will use it and configure it in this example. Entity1 consists of Identifier1 as its identifier and two methods, namely ReadList (Finder method) and ReadItem (Specific Finder method). While ReadList returns an entire entity collection, ReadItem returns only the specific collection item that matches the input filter parameter.

    Note  Visual Studio lets you add different stereotypes and methods for an entity of an external content type to perform the various operations noted next. All these methods are called by the BDC shared service. As soon as you create these methods, Visual Studio adds corresponding empty and not implemented methods in the entity service class file.

   Blank Method: Create a custom method to perform a specific operation in addition to the following methods.

   Creator Method: Implement a method to create and return a new entity. This method is named Create.

   Deleter Method: Implement a method to delete an existing entity instance. This method is named Delete.

   Finder Method: Implement a method to query and return a list or collection of entities. This method is named ReadList.

   Specific Finder Method: Implement a method to query and return a specific entity. This method is named ReadItem.

   Updater Method: Implement a method to edit an existing entity instance. This method is named Update.

   In the Employees table, BusinessEntityID is the primary key and hence you need to configure the Entity1 Type Descriptor such that it can be mapped to BusinessEntityID. As you might have noticed, Identifier1 in this example is a Type Descriptor of type System.String. You need to set its type to System.Int32 in order to make it work in conjunction with the BusinessEntityID data type.

6. Select Identifier1 under Entity1 in the designer window. In the properties window, change the Type Name to System.Int32.
7. Expand the BdcModel1 under the BDC Explorer. Go to the ReadItem and ReadList method input and output parameters and change Identifier1 Type Name to System.Int32 as shown in Figure 4-24.

   

   Figure 4-24. Adding Identifier1’s Type Name in BDC Explorer

8. Delete the return parameter Message from both ReadItem and ReadList.

   Because the idea is to retrieve and display columns from the Employees table, you need to create corresponding Type Descriptors related to the columns in the Employees table. Expand returnParameter under the ReadItem method. Right-click on Entity1, and click Add Type Descriptor. In the properties window, set the Type Descriptor Name to the column name you’d like to map and set the Type Name to the corresponding data type of the column. For example, set Name to NationalIDNumber and Type to System.String because the column NationalIDNumber is an nvarchar(15) data type. Make sure you rename the existing return parameter Identifier1 to BusinessEntityID. Perform these steps for all the columns that you’d like to retrieve. Repeat the steps for returnParameter under the ReadList method.

    Note  Columns chosen in this example are BusinessEntityID {int}, NationalIDNumber {nvarchar(15)}, LoginID {nvarchar(256)}, JobTitle {nvarchar(50)}, BirthDate {datetime}, MaritalStatus {nchar(1)}, Gender {nchar(1)}, HireDate {datetime}, SalariedFlag {Flag(bit)}, VacationHours {smallint}, SickLeaveHours {smallint}, CurrentFlag {Flag(bit)} and ModifiedDate {datetime}.

9. Switch to Solution Explorer, open the Entity1.cs file, and remove the lines of code for the Message property declaration.
10. Modify the public properties under Entity1 as shown in Listing 4-4.

    Listing 4-4.  Properties in the Entity1 Class

    public Int32 BusinessEntityID { get; set; }
    public string NationalIDNumber { get; set; }
    public string LoginID { get; set; }
    public string JobTitle { get; set; }
    public DateTime BirthDate { get; set; }
    public char MaritalStatus { get; set; }
    public char Gender { get; set; }
    public DateTime HireDate { get; set; }
    public bool SalariedFlag { get; set; }
    public Int16 VacationHours { get; set; }
    public Int16 SickLeaveHours { get; set; }
    public bool CurrentFlag { get; set; }
    public DateTime ModifiedDate { get; set; }

11. In the Entity1Service.cs file, declare the namespace System.Data.SqlClient:

    using System.Data.SqlClient;

12. Copy the code in Listing 4-5 for the ReadList method into the Entity1Service.cs file.

    Listing 4-5.  The ReadList Method

    List<Entity1> entityList = new List<Entity1>();
    using (SqlConnection conn = new SqlConnection(
    "Data Source=localhost; Integrated Security=SSPI; Initial Catalog=AdventureWorks2012"))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("uspGetEmployees", conn))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                entityList.Add(new Entity1
                {
                    BusinessEntityID = Int32.Parse(reader[0].ToString()),
                    NationalIDNumber = reader.GetString(1),
                    LoginID = reader.GetString(2),
                    JobTitle = reader.GetString(3),
                    BirthDate = DateTime.Parse(reader[4].ToString()),
                    MaritalStatus = char.Parse(reader[5].ToString()),
                    Gender = char.Parse(reader[6].ToString()),
                    HireDate = DateTime.Parse(reader[7].ToString()),
                    SalariedFlag = bool.Parse(reader[8].ToString()),
                    VacationHours = Int16.Parse(reader[9].ToString()),
                    SickLeaveHours = Int16.Parse(reader[10].ToString()),
                    CurrentFlag = bool.Parse(reader[11].ToString()),
                    ModifiedDate = DateTime.Parse(reader[12].ToString())
                });
            }
            reader.Close();
        }
    }
    return entityList;

13. Copy the code in Listing 4-6 for the ReadItem method into the Entity1Service.cs file.

    Listing 4-6.  The ReadItem Method

    Entity1 entity = new Entity1();
    using (SqlConnection conn = new SqlConnection(
    "Data Source=<DB Server Name>; Integrated Security=SSPI; Initial Catalog=AdventureWorks2012"))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("uspGetEmployeesById", conn))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add("@BusinessEntityID", System.Data.SqlDbType.Int).Value = id;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                entity.BusinessEntityID = Int32.Parse(reader[0].ToString());
                entity.NationalIDNumber = reader.GetString(1);
                entity.LoginID = reader.GetString(2);
                entity.JobTitle = reader.GetString(3);
                entity.BirthDate = DateTime.Parse(reader[4].ToString());
                entity.MaritalStatus = char.Parse(reader[5].ToString());
                entity.Gender = char.Parse(reader[6].ToString());
                entity.HireDate = DateTime.Parse(reader[7].ToString());
                entity.SalariedFlag = bool.Parse(reader[8].ToString());
                entity.VacationHours = Int16.Parse(reader[9].ToString());
                entity.SickLeaveHours = Int16.Parse(reader[10].ToString());
                entity.CurrentFlag = bool.Parse(reader[11].ToString());
                entity.ModifiedDate = DateTime.Parse(reader[12].ToString());
            }
        }
    }
    return entity;

     Note  As a recommended practice, add connection strings to the web.config file in the connectionStrings section instead of including them in the code. Also, make sure to encrypt the connectionStrings element if you have information such as the username and password in the connection string.

14. Return to BDC Explorer, expand Entity1 under the returnParameter of the ReadItem method, and select BusinessEntityID. From the properties window, ensure that the Identifier value is Identifier1. Check this for the ReadList method’s returnParameter as well.

    These steps provide you with the infrastructure for retrieving data as a list or a single item. The next step is to implement update functionality when a single item is selected.

15. In the BDC Explorer window, select Entity1. In the BDC Method Details window, click on “Add a Method” and choose Create Updater Method as shown in Figure 4-25.

    

    Figure 4-25. Adding the Create Updater method

    This step automatically creates an empty Update method in the Entity1Service.cs file. Note that Entity1 would be an In parameter for this method. Add another parameter to serve as the selected row item.

16. Click on Add Parameter, and choose Create Parameter in the Name column.
17. Choose the direction as “In” (in the Direction column).
18. Edit the parameter TypeDescriptor in the Type Descriptor column by clicking on the Edit link on the drop-down menu.
19. In the Type Descriptor Properties window, do the following:
    • a.  Set the Name property to BusinessEntityID and the Type Name as System.Int32. Select the Identifier as Identifier1.
    • b.  Set the Pre-Updater Field value to True. The Pre-Updater Field is a Boolean value that applies only to Type Descriptors and indicates if the TypeDescriptor object stores the latest business application data.

    After you complete this step, your BDC Model window should look similar to the one in Figure 4-26.

    

    Figure 4-26. The BDC model

20. Switch to Solution Explorer, and open the Entity1Service.cs file. Replace the code with the Update method code in Listing 4-7.

    Listing 4-7.  The Update Method

    int ret = 0;
    using (SqlConnection conn = new SqlConnection(
    "Data Source=localhost; Integrated Security=SSPI; Initial Catalog=AdventureWorks2012"))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("uspSetEmployeesValueById", conn))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add("@BusinessEntityID", System.Data.SqlDbType.Int).Value = parameter;
            cmd.Parameters.Add("@NationalIDNumber", System.Data.SqlDbType.NVarChar).Value =
    entity1.NationalIDNumber;
            cmd.Parameters.Add("@LoginID", System.Data.SqlDbType.NVarChar).Value = entity1.LoginID;
            cmd.Parameters.Add("@JobTitle", System.Data.SqlDbType.NVarChar).Value = entity1.JobTitle;
            cmd.Parameters.Add("@BirthDate", System.Data.SqlDbType.DateTime).Value = entity1.BirthDate;
            cmd.Parameters.Add("@MaritalStatus", System.Data.SqlDbType.NChar).Value = entity1.MaritalStatus;
            cmd.Parameters.Add("@Gender", System.Data.SqlDbType.NChar).Value = entity1.Gender;
            cmd.Parameters.Add("@HireDate", System.Data.SqlDbType.DateTime).Value = entity1.HireDate;
            cmd.Parameters.Add("@SalariedFlag", System.Data.SqlDbType.Bit).Value = entity1.SalariedFlag;
            cmd.Parameters.Add("@VacationHours", System.Data.SqlDbType.SmallInt).Value =
    entity1.VacationHours;
            cmd.Parameters.Add("@SickLeaveHours", System.Data.SqlDbType.SmallInt).Value =
    entity1.SickLeaveHours;
            cmd.Parameters.Add("@CurrentFlag", System.Data.SqlDbType.Bit).Value = entity1.CurrentFlag;
            cmd.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value =
    entity1.ModifiedDate;
            ret = cmd.ExecuteNonQuery();
        }
    }

    Your Solution Explorer should look like what’s shown in Figure 4-27.

    

    Figure 4-27. The  .NET Connector Assembly in Solution Explorer

21. Finally, ensure that all classes and projects have the same namespace defined. If the namespace or assembly name is incorrect, you’ll run into errors.

Deploy the Solution

Now it’s time to build, package, and deploy the solution to your SharePoint site. Right-click on the project and then click on Deploy. This essentially builds the project and deploys the package (.wsp) to your SharePoint site.

As mentioned earlier, the Business Data Connectivity Model template delivers two major pieces:

• The BDC Model, which contains one or more entities
• An external content type that actually contains metadata information related to the external system

1. Open your Central Administration web application, and go to Application Management Service Applications Manage service applications Business Data Connectivity Service.
2. Observe the different views by choosing them from the drop-down menu in the View section of the ribbon, as shown in Figure 4-28.

   

   Figure 4-28. ECT deployed to BDC Service Application

3. From the list of available ECTs, select the previously created and deployed ECT from the Visual Studio project.
4. From the ECB menu on the entity, select Set Permissions. Enter a user account, and click the Add button. You can add more than one user account and set object permissions.
5. Select the added user, set any permission (Edit, Execute, Selectable In Clients, Set Permissions) or all permissions, and click OK.
6. Open your SharePoint site. From Site Actions More Options, select External List and click Create. In the Create New window, provide a Name and Description and choose whether or not to display the external list on the Quick launch.
7. In the “Data source configuration” section, pick the ECT you created earlier by clicking on the Select External Content Type icon as shown in Figure 4-29. Then click on Create.

   

   Figure 4-29. Choose the external content type through an external list

An external list will be created as shown in Figure 4-30. As you’ll see, it is similar to the list you created using SharePoint Designer earlier in this chapter. In this case, however, the list is populated with data from the external data source using .NET assembly connector code.

Not only is the external list populated, you can also select an item to view and edit (Figure 4-31). When the View option is selected, the ReadItem method will be triggered and the selected identifier id (item id) is passed to the function as a parameter. When the Edit option is selected, the Update method is called and the identifier id (item id) and the entity (row data) are passed as parameters.

You must have noticed that an external list looks similar to a normal SharePoint list, but it’s not exactly the same. Table 4-1 provides a comparison.

In this exercise, the solution you created earlier using .NET code and deployed to SharePoint is deployed globally and will add the BDC model to the BDC Service Application. This means any Web application in the farm can access this ECT, from which you can create an external list. You will see this ECT under SharePoint Designer as a .NET type. We will see how to create an external list from it.

PROBLEM CASE

Using the BDC runtime, access the ReadList method from the previously deployed ECT Entity1 and retrieve the Employee LoginID and Job Title.

Solution

First, take a look at the ECT we previously developed and deployed using Visual Studio—Entity1. After it is deployed, you can locate Entity1 in CA Application Management Service Applications Manage service applications Business Data Connectivity Service. (Refer back to Figure 4-33.)

Before you begin, you will need a few values from the ECT, as well as the code from which it has been deployed. Follow these steps:

1. Note the Name of the ECT and the corresponding namespace because you’ll be using them in the BDC runtime (Figure 4-35).

   

   Figure 4-35. Entity1 in the BDC Service Application

2. Next, because the goal is to access the Read List method, you need to obtain the exact method name from the code. If you followed previous example, you know it is named ReadList.
3. Finally, you need the column names for the Login ID and job title, which are LoginID and JobTitle.

Launch Visual Studio. From File New Project, select the Visual C# Console Application template and ensure that you selected .NET Framework 4.5. Provide the Name, Location, and Solution Name, and click OK. Add references to the following assemblies:

• C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15ISAPIMicrosoft.BusinessData.dll
• C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15ISAPIMicrosoft.SharePoint.dll
• C:WindowsMicrosoft.NETassemblyGAC_MSILMicrosoft.SharePoint.BusinessData.Administration.Clientv4.0_15.0.0.0__71e9bce111e9429cMicrosoft.SharePoint.BusinessData.Administration.Client.dll
• System.Web

Replace the code in Program.cs with that of Listing 4-8. Ensure that you change the SharePoint site URL correctly to your own. Also, if the method names, ECT name, or namespace are different, make sure to change them accordingly in the code.

Listing 4-8.  Code for the BDC Runtime

using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.BusinessData.SharedService;
using Microsoft.SharePoint.BusinessData.MetadataModel;
using Microsoft.BusinessData.MetadataModel;
using Microsoft.BusinessData.Runtime;
namespace BCSRunTime // Set namespace according to your project
{
    class Program // Set class name as per your project
    {
        static void Main(string[] args)
        {
            ExecuteBcsEctMethods(@" http://localhost:81/ "); // set your site URL
        }
        static void ExecuteBcsEctMethods(string siteUrl)
        {
            using (SPSite site = new SPSite(siteUrl))
            {
                using (new SPServiceContextScope(SPServiceContext.GetContext(site)))
                {
                    BdcServiceApplicationProxy proxy = (BdcServiceApplicationProxy)SPServiceContext.Current.GetDefaultProxy(
typeof(BdcServiceApplicationProxy));
                    DatabaseBackedMetadataCatalog model = proxy.GetDatabaseBackedMetadataCatalog();
  
                    // Namespace, Entity name
                    IEntity entity = model.GetEntity("EmployeeEntityModel.BdcModel1", "Entity1");
  
                    ILobSystemInstance lobSystemInstance =
entity.GetLobSystem().GetLobSystemInstances()[0].Value;
  
                    // Finder method name
                    IMethodInstance method = entity.GetMethodInstance("ReadList", MethodInstanceType.Finder);
                    IView view = entity.GetFinderView(method.Name);
  
                    IFilterCollection filterCollection = entity.GetDefaultFinderFilters();
                    IEntityInstanceEnumerator entityInstanceEnumerator =
                    entity.FindFiltered(filterCollection, method.Name, lobSystemInstance, OperationMode.Online);
                    Console.WriteLine("Employee Login ID | Job Title");
                    while (entityInstanceEnumerator.MoveNext())
                    {
                        // Column names
                        Console.WriteLine(entityInstanceEnumerator.Current["LoginID"].ToString() +
                                                       " - " + entityInstanceEnumerator.Current["JobTitle"].ToString());
  
                    }
                    Console.ReadLine();
                }
            }
        }
    }
}

Save the file, and then build and run the code. You should see output similar to what’s shown in Figure 4-36.

What you achieved just now is this: you are able to execute a finder method in the ECT and retrieve the data from the external system. Similarly, you can execute any method that you define in the ECT, whether it’s for updating the data in a default view or reading a specific item.

Note that the logic in the code is unaware of the external system. All it cares about is the ECT name, its finder method, and the namespace. As long as these don’t change, your logic runs forever.

The BCS comes with many more features. You can use it with SharePoint and also integrate it with other Office applications, and you can work offline using SharePoint Workspace. In the next section, we will discuss some of these capabilities.