Searching a Database in SharePoint using BCS

Before we get starting on searching a database, we need to get a sample DB. In this case, I’m gonna use AdventureWorksDW2008R2 database. It can be downloaded from http://msftdbprodsamples.codeplex.com/releases/view/55926

I prefer to use the [DimProductCategory] and the [DimProductSubcategory] tables. Let’s look at the relationship between them.

--------------------------------------------------------------------------

Setup an External Content Type for DB

1. Click Start -> All Programs -> SharePoint -> Microsoft SharePoint Designer 2010

2. Click Open Site, enter your site collection URL and then click OK button

3. Click External Content Types from the left navigation, and then click the External Content Type from the ribbon -> New

4. Click the New external content type, enter a name, press the Tab button, the Display Name should be filled automatically. Finally, click the Click here to discover external data sources and define operations.

5. In the Operations Design View, Click the Add Connection button.

6. In the External Data Source Type Selection dialog, select SQL Server and then click OK button.

7. In the SQL Server Connection dialog, enter your Database Server, Database Name and keep selecting Connect with User’s Identity

8. Expand the AdventureWorksDW2008R2 Database -> Tables, find out the [DimProductCategory] table, right click it and then click the Create all Operations

9. In the All Operations dialog, click Finish button, you will see those operations in External Content Type Operations section.

10. Click Summary View from Views

11. From the Fields List, select the EnglishProductCategoryName and then click the Set as Title from the ribbon -> Field

12. Click the Save button and then close the SharePoint Designer

-------------------------------------------------------------------------------

Setup a content source and then crawl it

1. Go to your SharePoint 2010 Central administration, click Manage service applications -> <Your Content Application> -> Content Sources

2. Click New Content Source to create a new content source

3. Enter a new Name, select the Line of Business Data, select Crawl selected external data source and check the <your external content type>, and then click OK button to close the window

4. Go back to your Central Administration page, click the Manage service applications -> Business Data Connectivity Service

5. Select your new data source and the click Set Object Permissions

6. In the opening window, enter your domain user, click Add button, give all permissions to him/her and then click OK button.

7. Go back to your content source page, click the narrow down button, and then click the Start Full Crawl item

8. After the crawling finished, go to your FAST Search Center site, enter a keyword (Like Bikes) and the press ENTER button

 ----------------------------------------------------------------------

Create a Profile page to look at the query result

If you click the query result, you may get the following error. The reason is IE doesn’t know how to display the data with bdc3 protocol.

Fortunately, the BCS option support a nice feature names Profile Pages which we can use it to look at the query result.

1. Go to one of your site collection, click Site Actions -> New Site

2. Select the Blank Site, enter the title and the URL, then click Create button to create the site. Copy the URL.

3. Go to your SharePoint 2010 Central Administration, click Manage service applications -> Business Data Connectivity Service

4. In the Service Application Information page, click the Configure from the ribbon -> Profile Pages

5. In the opening window, paste the site URL to the text box and then click OK button

6. After the window closed, check your content type, and click the Create/Upgrade button from ribbon -> Profile Pages. Click OK button to finish the action.

7. After the creation completed, click the OK button to close the window.

8. Re-crawl your content source, after it competed, go to your FAST Search Center site, enter a keyword, you will get the result. Click the link, you will see the profile page.

Exception: If you are running into credential issue like:

Please refer to another article to resolve this issue.