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.

                   

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

                   

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.’

                   

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 .

                   

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 .  

            

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 .

              

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 .

                    

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