Business Connectivity Services (BCS) – Creating an External Content Type based on a SQL 2008 Table with Sharepoint Designer


Hello again 😉

i am going to talk about a very common scenario: I have a portal, I have some external data, and I want to integrate that data into my portal in a meaningful way.

Business Connectivity Services (BCS) in SharePoint 2010 is all about connecting to external data.  it’s easy to create an external content type with SharePoint Designer, create an external list in SharePoint’s Web user interface and take the list offline into Outlook as a set of contacts. Also, you can make updates to contacts in Outlook that will cause the data in the external system to update as well.

Today i am going to show you how to do it !!!!

In this scenario, this is what we’ll be using:

  • a SharePoint 2010 platform of course 🙂
  • a database. You can use Microsoft’s sample database “AdventureWorks”. You can download them here:http://msftdbprodsamples.codeplex.com – I’ll use the database “AdventureWorks2008R2”.
  • a Site Collection. (i am going to use the one i created in the previous post and you can use the one created by default in SD if you want).

So let Get Started !!! 😉

First of all you need to Install the AdventureWorks Database. here is an MSDN link to walk you through it http://msdn.microsoft.com/en-us/library/aa992075(v=vs.80).aspx , otherwise you can create your own database and work with it.

       1.Create External Content Type in SharePoint Designer 2010:

let’s start by  opening SharePoint Designer 2010 (“SPD”), click Open Site and enter the address to the site for example http://intranet.contoso.com/sites/TeamSite.

After the site opens in, click External Content Types in the left-hand pane. Give SPD a moment to build the list of existing entities, which should be empty at this point.

click the External Content Type button in the New group on the External Content Types tab on the ribbon.

2.Name the External Content Type

In the New External Content Type tab, enter Contact in the Name and Display Name fields.

Enter AdventureWorks in the Namespace field. and then select Contact in the Office Item Type drop-down list. Your entity should look like this:

     3. Discover Your External Data Source

now  Click “Add Connection”

Select “SQL Server” in the drop down “Data Source Type” in the dialog.

On the next dialog enter the Database Server Name, Database Name and the Name of the new connection. Select “Connect with User’s Identity”.

In the SQL Server Connection dialog, use the following and click OK:

a.       SQL Server: ******

b.      Database Name: AdventureWorks

c.       Name (optional): AWConnection

d.      Choose Connect with User’s Identity

your database server name may be .\sqlexpress or the name of the sql server instance that you’re using.

now expand the Connection node and the Tables folder and select the Contact table. (if you are using AdventureWorks).

4.Define Your Operations

Right click the table, and select Create All Operations from the context menu.

This options creates the well-known CRUD operations.

click the Next button.

In the Parameters Configuration dialog, do the following and then click Next:

a.       Select the LastName field from the list of Columns.

b.      In the Office Property drop-down list, select LastName.

c.       Check the Show In Picker checkbox.

d.      Click the Next button.

Click the Finish button to close the wizard.

Again, SPD 2010 will do its thing, and when it finishes, you will see a report of the operations that were successfully.

WELL DONE !!

This leads us straight into the next blog, which will discuss what you do with an ECT once it’s created:

Advertisements

One thought on “Business Connectivity Services (BCS) – Creating an External Content Type based on a SQL 2008 Table with Sharepoint Designer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s