With SharePoint Designer 2010, you can easy to connect external data such as SQL Server to display on Sharepoint and allow user to modify as well. In this article, I would like to introduce step by step how to retrieve Customer table from SQL Server 2008 and show to a site page just by manually and without coding.

As below table, I have a Customer table with primary key is [Customer ID] and some data

My SharePoint 2010 server is running local IP 172.16.1.71 and port 8888, using SharePoint Designer 2010, open SharePoint web application http://172.16.1.71:8888 then create database connection by click on Database Connection ribbon button as below.

From Data Source Properties window, Create a connection by follow Configure Database Connection wizard. In this wizard, fill out my database information such as Server name, user name and password… If you have connection string, you can use it by checking Use Custom connection string and provide your connection string.

In next screen, the wizard will connect to SQL Server and list out all existed Table or View from my database and allow to select one of them. In this case, I just have only one Customer table, select it and click Finish to complete the wizard.

Next step, I create an aspx file to show Customer data to this page. To create aspx file, select Site Page from left navigation and then click on ASP.NET ribbon button and select Aspx page, created page will be store in my Site Page folder. You can store the aspx file to any page folder in your SharePoint site. After create Aspx page, create datasource by click on Data Source ribbon button and select database connection as below screenshot

Then, Right click on created data source and select Configure Datasource, keep default in 2 first step of wizard, in Configure the Select Statement screen, choose which columns will be display on aspx page and click Advance, in Advanced SQL Generation Options popup, check on Generate INSERT, UPDATE and DELETE statements. Click OK and Finish the wizard.

An important thing here is this option only available when SQL Table has Primary key.

After finish setup for datasource, continue to insert a Grid View to display data. Come back to aspx file, from Ribbon, Click Insert tab, select ASP.NET button, and select Grid View. Navigate to grid view, select context menu and set datasource as below picture. Don’t forget to check on Enable for paging, Editing, Deleting,…

Now you have the page shows SQL Data and editable. But we should make it compatible with SharePoint interface by set master page for this page. From Style tab, click on Attach button and select v4.master or your customized master page. If you have problems when set master page, don’t worry, just close and re-open this aspx page and attach master page again.

Finally, just save and publish your page. The result should be like this:

Hoang Nhut Nguyen

Email: nhutcmos@gmail.com

Skype: hoangnhut.nguyen