I recently evaluated the MOSS BDC Design Studio tool to configure basic Business Data Catalog (BDC) connections.

Let me define basic first: I need to allow my SharePoint users to search Products within the AdventureWorks database using SharePoint search. Further, I want to allow users to search and view Products in the Business Data List web part.

As database administrator, I want to control access to the data. Therefore, I'll expose the data from the Line of Business (LOB) application via stored procedures. This has some other advantages such as enabling me to tune SQL statements used to support the BDC outside of SharePoint, and it allows BDC configuration to be performed by resources that may not have expertise with SQL or the particulars of the LOB entity.

I created three stored procedures to achieve my goals (script is listed at the end of this post):

• usp_Select_Products – to perform a search across all products filtering by Name and Product Number.
• usp_Select_Product – to select out a specific Product by Product Id
• usp_Select_ProductIds – to select out all product ids and the last modified date from the Product table.

These match up with the BDC method types Finder, Specific Finder, and Id Enumerator respectively. Now that the SQL tasks are out of way, let's crack open the MOSS BDC Design Studio.

First, choose Connect Data Source to define how to reach the database.

Define the user and password. I am using the bdcuser account to which I have only granted exec rights to the three stored procedures listed above. One quirk, the error message "invalid connection string" may mean the password was entered incorrectly.

This leads me to the design view of the editor where I can drag any one of the stored procedures on to the Design surface.

The Entity Wizard appears and guides us first to name the entity. Let's name it Product.

The Entity Wizard requires that we define the stored procedures for use as Finder, Specific Finder, and IdEnumerator for this entity.

Next, identify the column used to uniquely identify our entity.

Last step in the wizard, define the column which will be used to select a specific entity when presented in the Business Data List web part.

The newly defined Product entity now appears in the Design space.

Choose Edit Properties and define the Product Id as the primary key of the entity and the Name as the Title Column. Yes, the Entity Wizard did ask for this already.

Choose the Methods tab, select the Get_Product method, and define the input parameter as uniquely identifying the Product entity.

Set the default values for the input parameters of the Finder method to % (the SQL wildcard character).

Next, we must define how MOSS will connect to the LOB application. From the top menu, choose Edit-Properties.

Choose Build-Build BDC Xml to generate the Xml configuration file.

One optional step, we can inform the BDC as to which column may be used to determine the last time an entity was modified. This field enables the incremental crawl feature to skip entity records which have not been modified since the last crawl.

Choose File-Save BDC Xml to save the XML to file. Navigate to the Shared Services Administration page and choose Import application definition.

Once the import completes successfully, you can add a Business Data List web part to your web page and search for products by Name or Product Number.

The final step is to add the new LOB as a content source for use in SharePoint search. From Shared Services Administration, choose Configure Search Settings, Content Sources and Crawl Schedules, and add a new Content Source being sure to choose Business Data as the Content Source Type. Last, be sure to check Start full crawl of this content source before clicking ok.

And just for completeness, here is the script for the stored procedures. You have to create a bdcuser account and add that user to the AdventureWorks database before running this script.

go
create procedure dbo.usp_Select_Products (@Name nvarchar (50), @ProductNumber nvarchar(25))
as
begin
    select
        ProductID,
        Name,
        ProductNumber,
        MakeFlag,
        FinishedGoodsFlag,
        Color,
        SafetyStockLevel,
        SafetyStockLevel as SafetyStockLevel1,
        StandardCost,
        ListPrice,
        Size,
        SizeUnitMeasureCode
    from
        Production.Product
    where
        Name like @Name and ProductNumber like @ProductNumber
end
go
create procedure dbo.usp_Select_Product (@ProductID int)
as
begin
    select
        ProductID,
        Name,
        ProductNumber,
        MakeFlag,
        FinishedGoodsFlag,
        Color,
        SafetyStockLevel,
        SafetyStockLevel as SafetyStockLevel1,
        StandardCost,
        ListPrice,
        Size,
        SizeUnitMeasureCode
    from
        Production.Product
    where
        ProductID = @ProductID
end
go
create procedure dbo.usp_Select_ProductIDs as
begin
    select
        ProductID,
        ModifiedDate
    from
        Production.Product
end
go
grant exec on dbo.usp_Select_ProductIDs to BDCUser
grant exec on dbo.usp_Select_Product to BDCUser
grant exec on dbo.usp_Select_Products to BDCUser