Skip navigation
Menu
Business Connectivity Services Part 2: Advanced Data Access Techniques
Collaboration>Conferencing

Business Connectivity Services Part 2: Advanced Data Access Techniques

How to create a single ECT based on a .NET assembly connector

This article is the second part in a three-part series about SharePoint 2010 Business Connectivity Services (BCS). In "Business Connectivity Services, Part 1" (November 2010), I discussed BCS models, external content types (ECTs), external lists, and Business Data Catalog (BDC) Web Parts and demonstrated how to use them to implement functionality common to line of business (LOB) applications. In this article, I'll walk you through some advanced data access techniques with BCS. Listings are at the end of the article or you can download the code here.

You can use BCS to connect to many types of external data sources. In addition to being able to connect to common data sources such as databases, web services, and Windows Communication Foundation (WCF) services, BCS can connect to any data source that can be accessed via the Microsoft .NET Framework. So, if you can write .NET code to access a data source, you can connect to that data source with BCS. When you think about the flexibility the .NET Framework provides, it's easy to understand that, one way or another, you can connect to virtually any data source.

BCS connectors come in two main flavors:

  • Out-of-the-box connectors, which come with BCS. They include database connectors for connecting to databases and service connectors for connecting to WCF and web services.
  • Connectors that you create using the Connector Framework. These connectors include .NET assembly connectors and custom connectors.

Both .NET assembly and custom connectors are made up of managed code, which is compiled into an assembly. In both cases, the assembly is deployed to the Global Assembly Cache on the SharePoint servers where the BCS service application runs. Because the .NET assembly and custom connectors require an assembly to be deployed to the Global Assembly Cache, they must be deployed with a farm solution. They can't be deployed with a sandbox solution. The managed code in the compiled assemblies performs the Create, Read, Update, Delete, and Query (CRUDQ) operations on the external data sources the connectors are designed to interact with. Just like the out-of-the-box connectors, .NET assembly and custom connectors require a BCS model that contains the metadata describing the connection information and stereotyped operations that are performed against external data sources. A BCS model maps the methods in the managed code assemblies to the stereotyped operations that the BCS runtime defines, as Figure 1 shows.

One important difference between .NET assembly and custom connectors is that a .NET assembly connector is specifically tied to a single ECT, whereas a custom connector can be tied to one or more ECTs. For example, a custom connector can be used to provide BCS connectivity to SAP, while a .NET assembly connector can be used to provide BCS connectivity to a specific SAP installation for a given organization. As this example illustrates, custom connectors are more flexible than .NET assembly connectors. Custom connectors don't need to be recompiled if the schema representing the data in the external system changes. Instead, SharePoint Designer can be used to reconfigure them to handle schema changes. Unlike custom connectors, .NET assembly connectors are tightly bound to the schema representing the data in an external system. If the schema changes, a .NET assembly connector has to be recompiled.

Obviously, .NET assembly and custom connectors are required to connect BCS to external data sources that aren't supported by the database and service connectors. There are also other situations in which using .NET assembly or custom connectors makes sense, such as:

  • When you need to aggregate external data from multiple external data sources and represent the external data within a single ECT.
  • When you need to flatten complex data structures into fields that are compatible with BCS UI components.
  • When you need to convert proprietary data types returned by an external system into .NET Framework data types that the BDC runtime can understand.

Out of these three situations, creating a .NET assembly connector that aggregates external data is the most common, so I'll focus on it.

The Scenario

In this example, I'll discuss how to create a single ECT based on a .NET assembly connector that reads and updates customer data residing in a Microsoft SQL Server database and a text file. Because of space limitations, I can't provide detailed instructions. However, you can find step-by-step instructions on how to create a .NET assembly connector in my blog "HOW TO: Create a Searchable SharePoint 2010 BDC .NET Assembly Connector Which Reads From a Flat File."

You begin by a creating new Business Connectivity Service Model project in Visual Studio 2010. After Visual Studio 2010 creates the new project, you can look at the project files in Solution Explorer. Let's take a look at the Entity1.cs, Entity1Service.cs, and BdcModel1.bdcm files shown in Figure 2.

Entity1.cs

When you create a .NET assembly connector, the first thing you need to do is define the entity to return to BCS. The entity can be thought of as the ECT defined by the connector.

By default, the Entity1.cs class defines the entity. You need to customize this class so that each property represents an individual column in the ECT tied to your .NET assembly connector. Listing 1 at the end of the article shows the customized portion of the Entity1.cs class for this example. As you can see, it defines columns from both the database and text file.

Entity1Service.cs

After the entity is defined, you need to create the methods that support the stereotyped BCS operations you want the .NET assembly connector to provide. The Entity1Service.cs class holds these methods. For this example, I customized the Entity1Service.cs class so that it implements the Finder, SpecificFinder, and Updater methods.

The Finder Method

The ReadList method represents the Finder method for the ECT. The ReadList method returns all the records from the data sources. The code in Listing 2 at the end of this article defines this method. As you can see, it returns an IEnumerable list of type Entity1. This is the class defined in Entity1.cs.

In my implementation, the ReadList method uses the GetAllCustomerEntities method to return all the data from the database and text file. I'll walk you through how that code works. As Listing 3 shows at the end of this article, I first define the generic type Entity1 list to be returned, then create a DataTable. The DataTable is used to merge the data from the database and text file.

Next, I use ADO.NET to call a stored procedure named BCS_GetCustomers. Note the BCS_ prefix. If possible, I recommend using a similar prefix to make it easy for you to monitor and debug your BCS solutions. It's easy to do a filtered search for the BCS_ prefix using SQL Server Profiler or another SQL Server tool to quickly identify stored procedures used in your BCS solutions.

The BCS_GetCustomers stored procedure retrieves the customer data from the database and adds it to the DataTable, as Listing 4 at the end of this article shows. Figure 3 shows the data returned by the stored procedure, which includes such information as the customers' first and last names, telephone numbers, and email addresses.

The text file contains additional shipping information for the customers. The data is in the format <CustomerID>,<ShippingCompany>,<ShippingAccountNumber>. Here's an example of what that data looks like:

11000,USPS,78389078

11001,FEDEX,56982358

11002,UPS,45632589

The customer IDs will be used to correlate the data from the database with the data from the text file. Each customer's shipping company and shipping account number will be added to the DataTable.

To prepare for this data, I make two modifications to the DataTable, as Listing 5 at the end of this article shows. First, I mark the DataTable's CustomerID column as the PrimaryKey column. This allows the data from the text file to be merged with the corresponding row in the DataTable. Second, I create two new columns to hold the additional data.

Now that the DataTable is ready for the text file's data, I query the text file for its shipping information and merge it with the corresponding rows in the DataTable, as Listing 6 at the end of this article shows. I then loop through the data in the DataTable and create an instance of the Entity1 class for each row. Finally, I set the properties corresponding to the columns of data in each row and add the instances of the Entity1 class to the generic list of type Entity1, which the ReadList method returns. Listing 7 at the end of this article contains this code.

The SpecificFinder Method

The ReadItem method represents the SpecificFinder method for the ECT. It returns a single record from the data sources. The following code defines this method:

public static Entity1
ReadItem(string id)

(Although this code wraps here, you'd enter it all on one line in Visual Studio 2010.)

The ReadItem method differs from the ReadList method in three ways:

  • The ReadItem method calls a different stored procedure named BCS_GetCustomer. This stored procedure retrieves a single row of data from the database. The id parameter passed into ReadItem is converted to an integer data type to match the data type in the database. As you can see in Listing 8 at the end of this article, .NET assembly connectors let you easily convert between different data types when working with external data sources.
  • Unlike the ReadList method, the ReadItem method checks for the corresponding customer record while iterating through the records in the text file, as Listing 9 at the end of this article shows.
  • Instead of returning multiple rows, the ReadItem method returns a single entity representing the customer whose ID matched the id parameter. Listing 10 at the end of this article shows this code.

The Updater Method

The UpdateItem method represents the Updater method for the ECT. It updates a single record in the external data sources. The following code defines this method:

public static void
UpdateItem(Entity1 Entity1, string id)

(Although this code wraps here, you'd enter it all on one line in Visual Studio 2010.)

The Entity1 parameter represents the entity (i.e., a customer in this case) that's being modified in an external list or another BCS UI component. The id parameter represents the identifier corresponding to the entity. The code in Listing 11 at the end of this article shows how the UpdateItem method updates the customer's data in the text file. There are more efficient ways to update values in a comma-separated text file, but they're harder to read so I used this approach.

After the values in the text file are updated, the UpdateItem method calls the BCS_UpdateCustomerRecord stored procedure to update the customer information in the database, as shown in Listing 12 at the end of this article. The Entity1 parameter's properties are used to set the parameters that the stored procedure requires.

BdcModel1.bdcm

After the methods are created in the Entity1Service.cs class, the BCS model (BdcModel1.bdcm) needs to be edited to map the methods to stereotyped BCS operations. You use the BCS Model design surface and the BDC Method Details window (see Figure 4) to add the methods and their parameters. The BDC Explorer, which Figure 5 shows, is where you create the TypeDescriptors corresponding to the properties (i.e., columns) defined in the Entity1 class.

When you're finished editing the BCS model, you can deploy it to your SharePoint server by simply pressing F5. After the SharePoint solution package is deployed, you can create an external list based on the ECT defined in the BCS model. Figure 6 shows the external list for the sample BCS model I discussed here. The columns highlighted with the red box come from the text file. The rest of the columns come from the database.

When the UpdateItem method is defined in a BCS model, you can easily edit an item in the external list. Selecting the Edit Item option for an item opens an edit form like the one in Figure 7. Changing the values in the edit form and clicking the Save button fires the UpdateItem method in the .NET assembly connector, which in turn updates the data in the database and the text file.

Powerful Yet Straightforward

A .NET assembly connector that pulls data from multiple data sources can provide powerful functionality in many LOB applications, and creating one is rather straightforward. In this example, I combined data from a SQL Server database and a text file. Other possible data source combinations include a database and a web or WCF service; multiple web or WCF services; or even multiple RESTful services.

In the last article in this series, I'll demonstrate how to build custom UI components that work with BCS data. I'll also discuss how to work around BCS limitations in the sandbox.

Listing 1: The Customized Portion of the Entity1.cs File
 

public partial class Entity1
{
  //Identifier column (exists in both data sources)
  public string Identifier1 { get; set; }
  //Columns that come from the database
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public string Phone { get; set; }
  public string EmailAddress { get; set; }
  public string Address { get; set; }
  public string City { get; set; }
  public string State { get; set; }
  public string PostalCode { get; set; }
  public string Country { get; set; }
  //Columns that come from the text file
  public string ShippingCompany { get; set; }
  public string ShippingAccountNumber { get; set; }
}


 

Listing 2: Code That Defines the ReadList Method
 

public static IEnumerable<Entity1> ReadList()

\\{

  return GetAllCustomerEntities().ToArray();

\\}

 

 

Listing 3: ReadList Code That Creates the DataTable
 

public static List<Entity1> GetAllCustomerEntities()

\\{

  List<Entity1> customerEntityList = new List<Entity1>();

  DataTable customerEntityDataTable = new DataTable();

 

Listing 4: ReadList Code That Adds the Database's Information to the DataTable

 

using (SqlConnection connection = new

  SqlConnection(connectionString))

\\{

  connection.Open();

  SqlCommand sqlComm = new SqlCommand("BCS_GetCustomers", connection);

  sqlComm.CommandType = CommandType.StoredProcedure;

  SqlDataReader reader = sqlComm.ExecuteReader();

  customerEntityDataTable.Load(reader);

  reader.Close();

\\}

 

Listing 5: ReadList Code That Prepares the DataTable for the Text File's Information
 

DataColumn\\[\\] primaryKeys = new DataColumn\\[1\\];

primaryKeys\\[0\\] =

  customerEntityDataTable.Columns\\["CustomerID"\\];

customerEntityDataTable.PrimaryKey = primaryKeys;

DataColumn ShippingCompanyColumn = new DataColumn();

ShippingCompanyColumn.DataType =

  System.Type.GetType("System.String");

ShippingCompanyColumn.ColumnName = "ShippingCompany";

  customerEntityDataTable.Columns.Add(ShippingCompanyColumn);

DataColumn ShippingAccountNumberColumn = new DataColumn();

ShippingAccountNumberColumn.DataType =

  System.Type.GetType("System.String");

ShippingAccountNumberColumn.ColumnName =

  "ShippingAccountNumber";

  customerEntityDataTable.Columns.Add(ShippingAccountNumberColumn);


Listing 6: ReadList Code That Adds the Text File's Information to the DataTable
 

TextReader textReader = new StreamReader(textFilePath);

string row;

while ((row = textReader.ReadLine()) != null)

\\{

  string\\[\\] customerData = row.Split(',');

  //Find corresponding row in DataTable and update it.

  DataRow customerRow =

    customerEntityDataTable.Rows.Find(customerData\\[0\\]);

  if (customerRow != null)

  \\{

    customerRow\\["ShippingCompany"\\] = customerData\\[1\\];

    customerRow\\["ShippingAccountNumber"\\] =

      customerData\\[2\\];

  \\}

\\}

textReader.Close();

 

Listing 7: ReadList Code That Returns All the Records in the DataTable
 

foreach (DataRow customer in customerEntityDataTable.Rows)

\\{

  Entity1 customerEntity = new Entity1();

  customerEntity.Identifier1 = customer\\["CustomerID"\\].ToString();

  customerEntity.FirstName = customer\\["FirstName"\\].ToString();

  customerEntity.LastName = customer\\["LastName"\\].ToString();

  customerEntity.Phone = customer\\["Phone"\\].ToString();

  customerEntity.EmailAddress = customer\\["EmailAddress"\\].ToString();

  customerEntity.Address = customer\\["AddressLine1"\\].ToString();

  customerEntity.City = customer\\["City"\\].ToString();

  customerEntity.State = customer\\["StateProvinceName"\\].ToString();

  customerEntity.PostalCode = customer\\["PostalCode"\\].ToString();

  customerEntity.Country = customer\\["CountryRegionName"\\].ToString();

  customerEntity.ShippingCompany =

     customer\\["ShippingCompany"\\].ToString();

  customerEntity.ShippingAccountNumber =

    customer\\["ShippingAccountNumber"\\].ToString();

customerEntityList.Add(customerEntity);

\\}

return customerEntityList;

 

Listing 8: ReadItem Code That Calls the BCS_GetCustomer Stored Procedure
 

SqlCommand sqlComm = new SqlCommand("BCS_GetCustomer", connection);

sqlComm.Parameters.Add(new SqlParameter("@CustomerID",

  Convert.ToInt32(id)));


 

Listing 9: ReadItem Code That Checks for the Corresponding Customer Record
 

while ((row = textReader.ReadLine()) != null)

\\{

  string\\[\\] customerData = row.Split(',');

  if (customerData\\[0\\]

id)

  \\{

    customerData\\[1\\] = Entity1.ShippingCompany;

    customerData\\[2\\] = Entity1.ShippingAccountNumber;

  \\}

    stringBuilder.AppendLine(customerData\\[0\\] + "," +

      customerData\\[1\\] + "," + customerData\\[2\\]);

\\}

textReader.Close();

using (StreamWriter updatedFile = new StreamWriter(textFilePath))

\\{

  updatedFile.Write(stringBuilder.ToString());

\\}

Listing 10: ReadItem Code That Returns a Single Record from the DataTable

DataRow customer = customerEntityDataTable.Rows[0];
Entity1 customerEntity = new Entity1();

customerEntity.Identifier1 = customer["CustomerID"].ToString();
customerEntity.FirstName = customer["FirstName"].ToString();
customerEntity.LastName = customer["LastName"].ToString();
customerEntity.Phone = customer["Phone"].ToString();
customerEntity.EmailAddress = customer["EmailAddress"].ToString();
customerEntity.Address = customer["AddressLine1"].ToString();
customerEntity.City = customer["City"].ToString();
customerEntity.State = customer["StateProvinceName"].ToString();
customerEntity.PostalCode = customer["PostalCode"].ToString();
customerEntity.Country = customer["CountryRegionName"].ToString();
customerEntity.ShippingCompany = 
  customer["ShippingCompany"].ToString();
customerEntity.ShippingAccountNumber =
  customer["ShippingAccountNumber"].ToString();

return customerEntity;

 

Listing 11: UpdateItem Code That Updates Customer Information in the Text File

TextReader textReader = new StreamReader(textFilePath);
StringBuilder stringBuilder = new StringBuilder();
string textRow;

while ((textRow = textReader.ReadLine()) != null)
{
string[] customerData = textRow.Split(',');
  if (customerData[0] == id)
  {
    customerData[1] = Entity1.ShippingCompany;
    customerData[2] = Entity1.ShippingAccountNumber;
  }
    stringBuilder.AppendLine(customerData[0] + "," + 
      customerData[1] + "," + customerData[2]);
}

textReader.Close();

using (StreamWriter updatedFile = new StreamWriter(textFilePath))
{
  updatedFile.Write(stringBuilder.ToString());
}

Listing 12: UpdateItem Code That Updates Customer Information in the Database
 

using (SqlConnection connection = new SqlConnection(connectionString))

\\{

  connection.Open();

  using (SqlCommand Sqlcommand = connection.CreateCommand())

  \\{

    Sqlcommand.CommandType = CommandType.StoredProcedure;

    Sqlcommand.CommandText = "BCS_UpdateCustomerRecord";

    Sqlcommand.Parameters.Add("@CustomerID",

      SqlDbType.Int).Value = Entity1.Identifier1;

    Sqlcommand.Parameters.Add("@FirstName",

      SqlDbType.NVarChar).Value = Entity1.FirstName;

    Sqlcommand.Parameters.Add("@LastName",

      SqlDbType.NVarChar).Value = Entity1.LastName;

    Sqlcommand.Parameters.Add("@Phone",

      SqlDbType.NVarChar).Value = Entity1.Phone;

    Sqlcommand.Parameters.Add("@EmailAddress",

      SqlDbType.NVarChar).Value = Entity1.EmailAddress;

    Sqlcommand.Parameters.Add("@AddressLine1",

      SqlDbType.NVarChar).Value = Entity1.Address;

    Sqlcommand.Parameters.Add("@City",

      SqlDbType.NVarChar).Value = Entity1.City;

    Sqlcommand.Parameters.Add("@PostalCode",

      SqlDbType.NVarChar).Value = Entity1.PostalCode;

    Sqlcommand.ExecuteReader();

  \\}

connection.Close();

\\}

 

Hide comments

More information about text formats

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish
Recommended Reading
SharePoint Developers Must Know Business Connectivity Services
Jan 21, 2011
Business Connectivity Services, Part I
Nov 01, 2010
Business Connectivity Services Quick Dive: How to Surface Business Data in Outlook
Jan 25, 2011
Top 10 FAQs for Admins about BCS
Top 10 FAQs for Admins about BCS
Jan 21, 2011