Moments in Life: Business Connectivity Services

Create an external list in SharePoint 2010 using SharePoint designer 2010

Following example explains how to create a Business Connectivity Services (BCS) with an external content type using SharePoint Designer. An external list is a list based on data from an external system outside of SharePoint. Here I’m using a table in SQL database.

· Go to SharePoint Designer

· Click on the External Content Types menu on the left pane and click the External Content Types icon on the top.

· The Connection window appears. Click on “Click here to discover external data source and define operations” link

· Click on Add Connection button

· Select Data Source Type as SQL Server

· Type Database Server and Database Name à Click OK

· The database is shown on Data Source Explorer as below. Select a Table and right-clicking Table Name and selecting Create All Operations

· A Parameter Configuration wizard is shown after that. Then select columns which need and click the Finish button to complete and click save button

Create External list

Now it’s time to create the external list using the external content type which is created above using SharePoint designer.

· Go to sharepoint site and g to All site Actions and click on “Create” button

§ If "External List" option doesn't appear in the "Create" menu then need to activate “Team Collaboration Lists”

§ Go to: "Site Actions à Manage Site Features" and activate "Team Collaboration Lists" feature.

· Select external list and click on Create button

· Give a Name and select the External content type created in previous step using SharePoint designer 2010

· Then it will navigate to the list. If it gives following error then follow bellow steps. The error only means that you have not yet assigned access privileges for the current logged-in user.

· Go to the Central Admin Page àApplication Management and select Manage service applications

· Click on the [Business Data Connection Services]

· After clicking on BDC Services, then you can see the external content type which created earlier. Click on the external content type Name to expand the context menu and select Set Permissions:

· Add users and assign access permissions for them and click ok.

· Go to the list and refresh page. If still it gives the Access Denied message, reset IIS. Then it will show the list.

· Now you can Add, Edit, Delete list items. Then it will affect to the table in the given database in the external content type

Create External List using Visual Studio 2010

Following example explains how to create a Business Connectivity Services (BCS) with an external content type using Visual Studio and apply this content type in a SharePoint external list.

Create BDC model.

· Add new BDC model to the project. Right click the project à Add à new Item.

· From SharePoint Templates select Business Data Connectivity Model. Give a name and click Add

· Give the SharePoint site url and select “Deploy as a farm solution” and click ok.

· Now I’m deleting the default entity Entity, Entity1.cs and Entity1Service.cs which are created by Visual Studio 2010. After deleting the entity, we can develop the solution from the scratch.

· Click on “Toolbox” link. Drag and drop the Entity. Rename Entity1 as Student.

· Every entity needs a unique identifier. So right click on Identifier and click on “Add new identifier”. Type “ID”. This should be the same as the “ID” field in student table on SQL Database.

· The type of ID is defined as System.Int32 in the database. So, change its Type Name as System. Int32.

Add Methods

· There are two methods that we required to enable this scenario.

Specific finder method.
Finder method.

Specific finder method

· Go to the BDC model designer and right click on "Methods" >> Click on "Add new Method"

· It will open the window "BDC Method Details". Click on "Add a Method" and click on Create Specific Finder Method.

· It will add a new method “ReadItem”. It has a return parameter (student) and input parameter (ID).

· The return parameter student has the type as String. But it should be an object with fields’ ID and Name. We have to define an object for student. This means that we have to define a class that holds data fields of student.

· Using following method, it will generate a class with fields in it:

Create a class for the return parameter Student

· GO to SQL Server and copy the server name

· Go to VS2010 à Tools à Connect to database à give Server name and database names as shown below.

· In the solution explorer, right click on the project and Add à New Item.

· In “Data”, choose "LINQ to SQL Classes" Give a Name and Click Add button.

· Go to Server Explorer and drag and drop the "Student" table to "StudentClasses.dbml"

· The class which needs for the return parameter Student has been created.

· Now need to change the Type name of return parameter student from string to newly created object

· Go to BDC Explorer àStudentBdcModel à Student à ReadItem à student à Student. Click on Student.

· In Type Name, click on the drop down à Current Project tab à EmployeeBDC àStudent.

· Now, we get our newly created object

Add Type descriptors

· By Adding Descriptors, we are giving visual studio 2010 to create fields using a designer interface.

· Right click Student à Add Type Descriptor

· Now need to change the name and the Type name. The Identifier for the Student is ID. First we deal with ID field. Here need to set the identifier only for the ID as ID.

· Add descriptors for all the fields as given in following table

Name	Type name	Identifier
ID	System. Int32	ID
Name	System.String

Add Code

· Now it’s time to add some code to ReadItem Method.

· In Student entity right click Methods à ReadItem à view code.

· Add following code in ReadItem method:

public static Student ReadItem(Int32 iD)

{

string ServerName = @"CTRSRCDEVSQL";

StudentClassesDataContext dataContext = new StudentClassesDataContext("Data Source=" + ServerName + ";" + "Initial Catalog=TestDB;Integrated Security=True");

Student studentobject = (from studenin dataContext.Students.AsEnumerable().Take(20)

where studen.ID == iD

select studen).Single();

return studentobject;

}

Finder method

· We want to create a finder method which will return a list of Students.

· Go to StudentBdcModel.bdcm à Click <Add a Method> and click on “Create Finder Method”.

· It will add “ReadList” method

· Go to BDCExplorer you can see all the fields are added automatically to the ReadList we do not need to add them manually

· Go to StudentBdcModel.bdcm à Methods à ReadList à view code.

public static IEnumerable<Student> ReadList()

{

string ServerName = @"CTRSRCDEVSQL";

StudentClassesDataContext dataContext = new StudentClassesDataContext("Data Source=" + ServerName + ";" + "Initial Catalog=TestDB;Integrated Security=True");

IEnumerable<Student> student1 =

from student in dataContext.Students.Take(20)

select student;

return student1;

}

· Now build and Deploy the solution.

· Go to SharePoint site and create an External list using the above created external content type.

Deploying an External List via Feature

In the previous step I created an external content type using Visual studio 2010. Now I’m going to create an external list via feature using Visual studio 2010.

Learn from existing solution

· First I’m going to learn how to create an external list by using existing solution.

· First I create an external list using previously created StudentBDC.

· Then save the site as a site template to generate a .wsp file for us. We can use this wsp file to learn the schema of an external list

· Go to Site Settings à Save Site as Template. Fill in the fields and it will save a solution package in your solution gallery of your site collection. Save the .wsp file to disk and we’re ready to begin.

· Open Visual Studio 2010, create a new project, and use the Import SharePoint Solution Package project template.

· You will be prompted for what site to use and where the package file is.

· On the next step, you will be prompted for what you want to import. Here all are selected we need only the Student list instance. So deselect (There isn’t a select all button, so press Ctrl+A, and then unclick a checkbox to deselect everything.) all others and select only the Student list instance.

· It will give a warning about dependencies. Just say yes and your project will import.

· Open the Element.xml file

o The important parts of the List instance are TemplateType = “600” and the DataSource element

· Open the Schema.xml file.

It again uses the Type of 600. There are two new elements XslLink and Method

Create new List Definition

· I create a new project and create a new List Definition. I remove list definition and keep only the instance.

· Open the Element.xml file

· I removed some of the unnecessary attributes such as FeatureId and CustomSchema. I gave it a new Title so that we know this is a different list. Here is what my new elements.xml looks like.