Contrary to SharePoint 2007, SharePoint 2010 creates the Business Data Connectivity (BDC) through SharePoint Designer 2010 and not through the Central Administration page. This change can take some getting used to for those who’ve grown accustomed to the SharePoint 2007-model BDC. The BDC Service enables SharePoint to display business data from back-end server applications, Web services, and databases. This walkthrough shows you how to create a model for the BDC service that returns information about contacts in a sample database.

Challenge:

In this post, we will take a look at how to create the BDC service through SharePoint Designer 2010. Especially, we will introduce you to the creation of an Association between External Content Types for BDC. This means that it allows a nest of tables in SQL. I am going to show you how to effect the joining many tables from the SQL server database through SharePoint Designer 2010. The advantage of this is you can use these tables for columns or products for getting data from the BDC to your portal site.

Solution:

First, I will demonstrate how to generate a new key for using BDC on a portal site through SharePoint 2010.

Step 1: Go to Central Administration -> Application Management -> Manage service applications:

Select the Secure Store Service hyperlink to generate new key:

Enter the Pass Phrase (which was created when installing the machine) into the Generate New Key form:

Step 2: Click on the New menu on the Ribbon to create a new key:

Fill the required information into the form as shown below:

Click the Next button to continue:

Step 3: Enter the user(s) who will have permission to use the BDC when logging onto the site.

Click the OK button to complete. Make sure that you see the new key on the page after creating your new key.

Step 4: Click on the context menu and select Set Credentials to set permissions.

Enter the user name and password for the user(s) who should have permission, and click Save to finish:

Next, I will demonstrate how to connect to a portal site, create BDC and its Association between External Content Types through SharePoint 2010.

Step 1: Open the Microsoft SharePoint Designer 2010 program on your machine.

Step 2: Go to the Sites tab and select the Open Site icon.

Enter the URL of the portal site you need to use BDC with into the dialog.

Concurrently, you must enter user name and password as one who has access permission on your portal site into the Windows Security as pictured below:

Step 3: Go to the External Content Types tab and click on the External Content Type menu to connect to the SQL program:

Click on the Add Connection button on the form, and make sure that you created the Northwind database on your SQL program before:

Select the SQL Server option:

Fill in the information regarding Database Server, Database Name, and the Key which was created above into the SQL Server Connection dialog as shown in the following picture:

Concurrently, you must enter user name and password as one who has access permission on your SQL program into the BSC Secure Store dialog as pictured below:

After step 3 has been completed, you will see the Northwind database appear with a new name as Key Name on the Data Source Explorer tab.

Step 4: Select a table which you want to make a parent table. Right-click on it and select the Create All Operations menu:

Click the Next button to continue:

Select the column which differs from the primary key and check on the Show in Picker checkbox. Click on the Next button to continue:

Click on the Finish button to complete:

Make sure that the information in the created table above is shown on the right of the Form, and that no errors ocurred during its creation. Click on the Save button to complete:

Make sure that you see the name of the created table on the left of the form in the SharePoint Designer program as shown below:

Step 5: Turn off SharePoint Designer, then turn on it again.

Select the second table, which you want to make a child table. Make sure that the second table has a reference key which refers to the primary key of the parent table above. Right-click on it and select the Create All Operations menu:

Select the column which differs from the primary key and check on the Show in Picker checkbox. Click on the Next button to continue:

Click on the Finish button to complete. Please do not click on the Save button, as we will continue to create an Association.

Now I will demonstrate how to create an Association between External Content Types for BDC through SharePoint Designer 2010.

Right-click on the second table which was just created, and select the New Association menu:

You can rename the Association Name or Association Display Name in here as you wish, and then click on the Browse button to select the Parent table which the second table needs to refer to.

And then you must select the reference field for the right combo box for the appropriate option.

Click on the Next button to continue.

Select the reference key and check on the Map to Identifier checkbox as pictured below:

Click on the Next button.

Finally, you must click on the Save button in SharePoint Designer to complete the process:

Make sure that you have two tables (parent table and child table) on the left pane after having saved.

After finishing, please remember to check BDC services to see if your configuration has succeeded (or not) going to Central Administration -> Application Management -> Manage service applications -> Business Data Connectivity Service.

Make sure that you see the two table names which were created from SharePoint Designer.

To use BDC on your portal site, please set Permissions for those users who will be logging onto the site to use it.

Click on the context menu for each table to select the Set Permission menu.

Enter the user name and check full permission.

Click on the OK button, and you can now use the BDC on your portal easily.

Notes:

We will use these methods for all columns regarding the BDC such as External Data and Metadata columns on your portal site. In this example, I will introduce some Bamboo products such as Data-Viewer Web Part

First, go to the tool pane of Data-Viewer, select the MashPoint/BDC option, and click on the Book picker.  You will see the two tables which were created from SharePoint Designer 2010 above.

You should select the parent table first and then the child table as shown below:

Next, click on the Apply and OK buttons to complete the action. You will see the data of the two tables on the Web Part with a nest of tables:

See Also:

• SharePoint 2010 Cookbook: How to Create External Lists & Include Databases Using SharePoint Designer 2010 [Bamboo Nation]

---