Category: Business Connectivity Services (BCS)

BCS (2) Creating an External List from an ECT


Hi 🙂

In the previous blog post – https://sharepointsabrine.wordpress.com/2012/01/29/business-connectivity-services-bcs-creating-an-external-content-type-based-on-a-sql-2008-table-with-sharepoint-designer/ – we created the ECT. Now, we are going to utilize the ECT within the new feature of SharePoint 2010 known as an External List. After we’ve created and viewed our External List, we are going to use the data offline.

So lets Get Started 😉

There are 2 ways to create an external list either via SP designer or via the SP site:

  1. Creating an  External List from SP Designer:
if you have done the first post the you already have you external content type Contacts if not the open SP designer and Click “External Content Types” in the Site Objects Navigation. and select your content type

On the ribbon, click the Create List and Formsbutton and  enter AWContacts in the List Name field, uncheck the Create InfoPath Form and click the OK button.

Wait till the list is created and then Close SharePoint Designer.

           2. Creating an External List from the site:

i will show you another way to create a external List? If you’re not interested you can skip this part ! no problem.

First of all open your site in the browser, then in click Site Actions => More options

then click on Filtered by List and choose external List and Finally Create:

now fill in the blanks 😉

and it’s done.

           3. Configuring the security settings:

Open the browser and navigate to the site collection. You’ll see the previously created list, click on it and you may get the error “Access denied by Business Data Connectivity”

so  we must configure the security settings on the new BCS application we just created:

a.       Open SharePoint 2010 Central Administration

b.      In the Application Management section select Manage service applications

c.       Underneath the Name column click on the Business Data Connectivity Service hyperlink

d.      On the Service Application Information screen, expand the Contact application’s dropdown arrow and select Set Permissions

On the Set Object Permissions dialog box type All Authenticated Users into the first text box and click Add.

then check at least execute:

Try to open the list on the site collection and refresh if needed.

and Voila !!!!! 🙂

           4. Using the Data offline:

Launch SharePoint Workspace 2010. You may have to set up an account when you first start the application. (use your User information pulled from Active Directory)

after launching the SP Workspace return to the list in SharePoint and click the List tab on the ribbon.

Click the Sync to SharePoint Workspace button to open the list in the SharePoint Workspace. (this will take a while.)

When the process finishes, click the Open Workspace button to close the wizard.

and We’re Done !!

So, in this post i created an external content type, the associated forms and even sync’d the list with SharePoint Workspace 2010 for offline use.

Advertisements

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: