It's a fairy common requirement and using SharePoint external content types we can easily achieve this.
I am going to assume you have a table of data in mind within a SQL server database that you have permissions to.
I am also assuming you have the Business Data Connectivity Service configured and the Secure Store Service application configured with a target application ID we can use to access the SQL server database.
If you need guidance on configuring the Secure Store - See Here
Open SharePoint Designer 2010 and open your site you wish to create the external content type on.
On the left hand navigation select "External Content Types" .
Click on the top left ribbon icon to create a new content type.
Name the content type
Click on the link highlighted to connect to a data source
Click on “Add Connection”
Select “SQL Server” as we are using a SQL database, you could use a different data source type.
Provide the server name/alias and the database name.
To use "Connect with User's Identity" as I am you will need to have permissions to the database, if you do not you can use the Secure Store Application ID here.
Drill down in to the database and find the table or view you wish to use.
Right click on the table or view and create a "New Read Item Operation"
Click next (you can name the operations in a way that makes sense to you).
Tick "Map to Identifier"
In my example I am only extracting one column of data and that is all that exists in my database view so I can only select that column which is "ContactGroup"
If you have multiple columns you need to decide on an identifer.
Click "Next"
Tick "Map to Identifier" on this screen too and select the identifier.
Now we need to create a second operation, so again right click on the table or view and select "New Read List Operation".
Click next (you can name the operations in a way that makes sense to you).
I am adding no filter at this point as I need all of the data to be displayed, however you can filter the data if required.
Click "Next"
Tick "Map to Identifier" and as in the previous operation and select identifier.
Click "Finish".
Configure the data source connection properties by clicking on the name.
Pay close attention to the "Authentication Mode" on both the default tab and in particular the client tab!
On the client tab add your Secure Store Application ID to allow the client to access the data.
Save the external content type.
Now the content type is created and configured we can use this within a SharePoint list.
From the view all site content type menu, click "Create".
Select "External List"
Name the list and click on the icon in the bottom right hand corner to view external content types.
Select the external content type from the list.
When you now open the list your data from the SQL server database shoud be visible!
I hope this helps with the basic principle of setting up an external content type and using that to display data from a nexternal source in a SharePoint list.
Thanks for reading!
Matt
No comments:
Post a Comment