This post will demonstrate the configuration of a SharePoint 2007 BDC application definition and connection.

Requirements:

  • SharePoint Server 2007

  • SharePoint SDK

  • Adventure Works Database

Instructions:

  1. Install SharePoint SDK.  We will be using Application Definition Designer from the SharePoint SDK to create an ADF file.

Download details- http://www.microsoft.com/downloads/details.aspx?familyid=6D94E307-67D9-41AC-B2D6-0074D6286FA9&displaylang=en

  1. Download the AdventureWorks database. Adventure works is SQL sample database provided by Microsoft.

Download details- http://www.codeplex.com/MSFTDBProdSamples

  1. After installing the SharePoint SDK, browse to the welcome page of the SDK and click on Business Data Catalog

3

  1. Click on Tools\BDC Definition Editor under Microsoft Data catalog samples and utilities. This will open up a folder which will have a Setup file for installing Application Definition Designer.

4_1

4_2

Click on the Setup.exe to install Application Definition Designer.

  1. Install AdventureWorks Database on your SQL Server.
  2. Open Application Definition Designer by clicking Start>Programs>ApplicationDefinitionDesigner.exe

6

  1. Click on Add LOB System

7

  1. Now click on connect to database

8

  1. Here we will need to specify the connection string. For our example, we will be using trusted connection (there are different connection strings available).

9

Replace

myServerAddress with SQL Server Name

myDatabase with AdventureWorks or the database name in case you are using a database other than AdventureWorks.

After replacing these details click on connect

  1. Clicking on connect will take us to the Design Surface. Click on Add Table on the right

10

  1. We can use multiple tables, but for this example, we will only be using the table called "DimProduct". To add a table,click on the table and then drag and drop into the left pane.
    Now click on "OK"

11

    This will prompt you with a screen "Enter the name for the LOB System". Give some meaningful name to your ADF File.

    1. Now we will do a quick test to see if our query to the database is returning results or not.

    12

    Expand the tree structure based on the screen shot above and select Find_[AdventureWorks] and then click on Execute.

    1. This will open up the below screen

    13

    Enter a valid value to see if it pulls up the correct result. I picked the value "1" by looking at the table DimProduct in SQL Management Studio

    1. Click on Execute and a valid ProductKey value will pull all the related value.

    14

    Now we know that our connection and Find functions are working properly.

    1. At this point we need to Export our ADF file.
      Right click on the project name, click Export, and then save it on your local drive

    15

    1. Now we will import the ADF file which we just created.
      To do this Browse to Central administration page > SSP>Business Data Catalog>Import application definition

    16

    1. Give path of the ADF file and then click on Import

    17

    On successful import we will get the message "Application definition Import Successful "

    1. Now we can see the Entities we created in the ADF File

    18

    1. Now to use these BDC columns to create a a BDC column in a Document library
    2. Create a BDC Column by browsing to the document library and clicking Settings> Create Column
    3. Select the type "Business data" and then click on "Browse"

    21

    1. Select the BDC Application and click on "OK"

    22

    1. Once we click on Ok it will populate the list of columns available in the Table.We will select couple of columns to show up

      23

      We have selected EnglishDescription,EnglishProductName,ModleName,ProductAlternateKey,ProductKey and ProductLine.

      The value under "Display this field of the selected type" is the one against which the Check name feature will work

      Click on Ok

      1. Once we upload a file it will show the BDC Column which we created-

      24

      Test- This is our BDC Column.

      1. If we look at the File we just uploaded to the document library, we will see the values corresponding to the ProductKey we entered.

      25

      Complete!  You have now configured a BDC connection from SharePoint to the AdventureWorks database. 

      We can create and develop more complex BDC solutions using various other tools

      For more information about the application definition file itself, please see

      http://www.microsoft.com/resources/msdn/en-us/office/media/MOSS2007CreatingBusinessDataCatalogs.wvx