Compartilhar via

Walkthrough of creating associations between SharePoint BDC entities using Visual Studio 2010 (Yanchen Wu)

  • Artigo

Up till now, we have published two blogs in this series about SharePoint 2010 external list, Walkthrough of creating a SharePoint 2010 external list using Visual Studio 2010 Beta and Walkthrough of enabling CRUD for SharePoint 2010 external lists using Visual Studio 2010. Now you are able to create external list and add basic CRUD functions. In this blog we will show how to create Association between Sharepoint BDC entities using Visual Studio 2010 and use Business Data Web Parts to display the data and the association in SharePoint page.

The same as our second blog, we will use “Northwind” database as external data source, so if you do not have an existing “Northwind” database available, please download it here and install the sample database following the instruction of the installed package or our last blog here.

If you have not read the first two blog entries, we recommend you read them before going through the following content, since this article is highly relevant with previous ones. The first several parts are very similar with the last blog, so they are simplified to only contain useful information. Please refer to the previous articles for any ambiguity.

Create BDC Project

First of all, let’s create a new C# BDC Model project and rename it “BdcAssociationSample” .

Connect to external data source

Add a LINQ to SQL model and drag-and-drop both Customers and Orders tables of Northwind database from the Server Explorer. Right click on the LINQ to SQL item and click View Code, and insert the following code.

C#:

  1 public partial class CustomersAndOrdersDataContext
 2 {
 3     private const string ConnectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;Pooling=False";
 4 
 5     public CustomerDataContext() :
 6         base(ConnectionString, mappingSource)
 7     {
 8         OnCreated();
 9     }
10 }

VB:

 1 Partial Public Class CustomersAndOrdersDataContext
2     Private Const ConnectionString As String = "Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;Pooling=False"
3     Public Sub New()
4         MyBase.New(ConnectionString, mappingSource)
5         OnCreated()
6     End Sub
7 End Class

Design BDC Model

Design the Customer and Order entities according to the LINQ to SQL model. To simply the problem, we only define Specific Finder method and Finder method for both of the entities.

After this step, we get two entities as below.

clip_image001

The TypeDescriptors in BDC Explorer are like this:

clip_image002clip_image003

Add Association between Customer and Order

Now we can create an association by selecting Association tool in the Visual Studio Toolbox, clicking the first entity Customer (called the source entity) and then clicking the second entity Order (called the destination entity). You can define the details of the association in the popped out Association Editor.

By default, the Association Editor adds an Association Navigation method to the source and destination entities. An Association Navigation method in the source entity enables consumers to retrieve a list of destination entities. An Association Navigation method in the destination entity enables consumers to retrieve the source entity that relates to a destination entity.

You can create two types of associations in the BDC designer: foreign key-based associations and foreign keyless associations. (See Creating an Association Between Entities in the MSDN library.)

Now we are creating a foreign key-based associations. So check Is Foreign Key Association checkbox and find ReadItem.order.Order.CustomerID in the left column of the Identifier Mapping gird, and then select CustomerID from the Source ID column on the right to map the identifier. Now the dialog looks like below:

clip_image004

Click OK to finish the association creation. On the design surface we get an association CustomerToOrderAssociation which connects two entities with a dotted line.

clip_image005

Add code behind to access external data source

In Solution Explorer, find and open CustomerService.cs (.vb) , and then replace the implementation with the following code snippet:

C#:

  1 public static Customer ReadItem(string customerID)
 2 {
 3     CustomersAndOrdersDataContext context = new CustomersAndOrdersDataContext ();
 4     Customer cust = context.Customers.Single(c => c.CustomerID == customerID);
 5 
 6     return cust;
 7 }
 8 
 9 public static IEnumerable<Customer> ReadList()
10 {
11     CustomersAndOrdersDataContext context = new CustomersAndOrdersDataContext ();
12     IEnumerable<Customer> custList = context.Customers;
13     return custList;
14 }
15 
16 public static IEnumerable<Order> CustomerToOrder(string customerID)
17 {
18     CustomersAndOrdersDataContext context = new CustomersAndOrdersDataContext();
19 
20     IEnumerable<Order> orderList = context.Orders.Where(o => o.CustomerID == customerID);
21 
22     return orderList;
23 }

VB:

  1 Public Shared Function ReadItem(ByVal customerID As String) As Customer
 2     Dim context As New CustomersAndOrdersDataContext
 3 
 4     Dim cust = (From c In context.Customers _
 5                Where c.CustomerID = customerID _
 6                Select c).Single()
 7     Return cust
 8 End Function
 9 
10 Public Shared Function ReadList() As IEnumerable(Of Customer)
11     Dim context As New CustomersAndOrdersDataContext
12 
13     Return context.Customers
14 End Function
15 
16 Public Shared Function CustomerToOrder(ByVal customerID As String) As IEnumerable(Of Order)
17     Dim context As New CustomersAndOrdersDataContext
18 
19     Dim orderList = From o In context.Orders _
20                     Where o.CustomerID = customerID _
21                     Select o
22     Return orderList
23 End Function

Then open and do the same for OrderService.cs (.vb).

C#:

  1 public static Order ReadItem(int orderID)
 2 {
 3     CustomersAndOrdersDataContext context = new CustomersAndOrdersDataContext();
 4 
 5     Order order = context.Orders.Single(o => o.OrderID == orderID);
 6 
 7     return order;
 8 }
 9 
10 public static IEnumerable<Order> ReadList()
11 {
12     CustomersAndOrdersDataContext context = new CustomersAndOrdersDataContext();
13 
14     IEnumerable<Order> orderList = context.Orders;
15 
16     return orderList;
17 }
18 
19 public static IEnumerable<Customer> OrderToCustomer(int orderID)
20 {
21     CustomersAndOrdersDataContext context = new CustomersAndOrdersDataContext();
22 
23     string customerID = context.Orders.Single(o => o.OrderID == orderID).CustomerID;
24 
25     IEnumerable<Customer> custList = context.Customers.Where(c => c.CustomerID == customerID);
26 
27     return custList;
28 }

VB:

  1 Public Shared Function ReadItem(ByVal orderID As Integer) As Order
 2     Dim context As New CustomersAndOrdersDataContext
 3 
 4     Dim order = (From o In context.Orders _
 5                Where o.OrderID = orderID _
 6                Select o).Single()
 7     Return order
 8 End Function
 9 
10 Public Shared Function ReadList() As IEnumerable(Of Order)
11     Dim context As New CustomersAndOrdersDataContext
12 
13     Return context.Orders
14 End Function
15 
16 Public Shared Function OrderToCustomer(ByVal orderID As Integer) As IEnumerable(Of Customer)
17     Dim context As New CustomersAndOrdersDataContext
18 
19     Dim customerID = (From o In context.Orders _
20                Where o.OrderID = orderID _
21                Select o).Single().CustomerID
22 
23     Dim custList = From c In context.Customers _
24                     Where c.CustomerID = customerID _
25                     Select c
26     Return custList
27 End Function

Deploy the solution and create new page to see the associated data lists

1. Go to the homepage of your SharePoint site. Typically https://localhost.

2. On the top left corner of the site, select Site Actions -> New Page, name the new page BdcAssociationSample.

3. On the ribbon, select Insert -> Web Part, and click on Business Data from the Categories panel, select Business Data List from the Web Parts panel. Click Add.

clip_image006

4. Repeat the step above, select Business Data Related List and click Add.

5. Click Open the tool pane on Business Data List, then select Customer from External Content Type picker and click OK. Now you should able to see the customer information from the database is displayed in the list.

clip_image007

clip_image008

6. Repeat the step above, select Order on Business Data Related List.

7. On the top-right corner of the Business Data Related List, click on the drop down menu and select Edit Web Part. After the page refreshed, from the same drop down menu, select Connections -> Get Related Item From -> Customer. Click OK.

clip_image009 clip_image010

8. Click Save & Close on the ribbon of the new page.

Now you can see a list of Customers on the SharePoint page, when you select a row in the Customer table, a list of orders related with this customer will showed in the second table.

Select CustomerID ALFKI by clicking the small icon in the front of each line.

clip_image011

The second list will display the corresponding orders.

 clip_image012

Yanchen Wu

Comments

  • Anonymous
    September 05, 2013
    Very clear walkthrough on creating assocations between SP BDC entities. I have not seen it layed out this nicely before! Thanks!