Walkthrough of creating association between Sharepoint BDC entities using Visual Studio 2010
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.
The TypeDescriptors in BDC Explorer are like this:
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. For detailed information, check out this on MSDN.
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:
Click OK to finish the association creation. On the design surface we get an association CustomerToOrderAssociation which connects two entities with a dotted line.
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.
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.
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.
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.
The second list will display the corresponding orders.
Yanchen Wu