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.
·
Open the Schema.xml file.
·
I used the Schema.xml file as is. I did
change the Title and Url attributes at the top but that is it.
·
Here is what it looks like in Visual Studio.
·
Build and deploy the solution. Go to the
sharepoint site and you can see the newly created student list
