SharePoint List From SQL Server Table (External Content Type with SharePoint Designer)

  • [アーティクル]

Many time we need to show custom data in a SharePoint list and you do not have an option to store data in SharePoint list .In most of the cases your data source can be used in some other legacy system and you have centralized data in SQL server table. Now challenge is to integrate this data in SharePoint so that a SharePoint user can view ,modify and delete this data from a SharePoint list .

To handle this situation SharePoint provides External Content Types . We will see how to configure ECT with SharePoint Designer and show a list from SQL server table .

Let’s start we have following customer table in our data base with some dummy values . We will make this table shown as a SharePoint list in few clicks with the help of SharePoint designer.

                    image

Step 1 . To start with open your site in SP Designer .To do this either click on Site Action –> Edit in SharePoint Designer option from your site or open SP Designer 2010 and browse for your site .

Step 2. Select External Content Type from Site Objects and then click on the first icon in the new group of the ribbon i.e External Content Type

                    image

Step 3. This will open a ‘ New External Content Type’ window , change the Name and Display Name in the External Content Type Information tab of the newly opened window.

Step 4. Click on the link ‘Click here to discover external data sources and define operations.’

                    image

Step 5. This will open a new window with the name that you have provided in last screen for external content type , Click on ‘Add Connection’. In the External Data Source Type Selection choose SQL Sever. Fill the details for Database Server ,Database Name and user identity to connect with database .

                    image

Step 6 . If you have provided the correct details , now you can see your database in the ’Data Source Explorer’ tab. Expand your database –> navigate till the table which you want to display as SharePoint List . Right click on the table and select ‘Create All Operations’ . With this option you can also edit and delete items from SharePoint list . The changes will be atomically reflect in SQL server after any operation in the SharePoint list .  

             image

Step 7. It will open up ‘All Operations’ window . Click Next and it will display all the fields of the selected table , change any properties if you want otherwise just click Next. The next section is for filter parameter configuration, just click Finish.Save your external content type in SP Designer.

Step 8. Now our external content type is ready and we just need a list . Select ‘List and Libraries’ from ‘Site Objects’ tab and then click on ‘External List’ command from the ribbon .

               image

Step 9. You will now see a ‘External Content Types Picker ‘ window . Select your external content type which you have just created and then click Ok. Provide name and description for the list in ‘Create External List’ window , Click oaky and you are done .Save everything in SP Designer go to your site and you can can find newly created list under Lists section .

                     image

You can also edit , delete and add new items from SharePoint List to the SQL server database .

Comments

  • Anonymous
    May 02, 2013
    You left out a lot of important details related to security.  Most enterprise environments will have to use the secure store, for which you need Central Admin access.  Then there is no mention of filters.  Large queries can drag down the performance of the SharePoint environment.

  • Anonymous
    September 12, 2013
    Agreed, I'm running into "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'" whenever I try to use the list. I'm also not able to give this user access. Critical information has been left out of this article otherwise it would be a fantastic resource.

  • Anonymous
    July 27, 2014
    Got this working after looking at this link as well: blogs.msdn.com/.../access-denied-by-business-data-connectivity.aspx

  • Anonymous
    October 09, 2014
    Does this method also work with SQL Azure databases created with an Access 2013 Web App?

  • Anonymous
    January 21, 2015
    Can I have column in that list containing the number of customers who have last name = "Pandey", for example??