One of the features of SharePoint is connecting with
External Data like SQL, Oracle, Siebel, SAP etc. SharePoint provides two ways
to do so. First one is an easier, code less way through SharePoint Designer which provides basic CRUD operations on
external data. Second way is by developing a Business Data Connectivity Model through Visual Studio which
enables us to write complex business logic.
First, let’s explore how to connect with external data
through SharePoint Designer.
consider, we have a custom table named Orders in our SQL Server 2008 DB (TestDB).
Open the SharePoint Designer and click on Open Site.
A dialog will appear which will allow you to select your
SharePoint Site. In my case, I have a SharePoint site at http://homestation/.
Select the site and click Open. 
SharePoint Designer will open the site for you. On the left hand side under Site Objects, click on External Content Types.
Click on External
Content Type ribbon button from the top.
Click on Add
Connection button to specify a new external data source connection.
A dialog will appear allowing you to select the Data Source Type. Select SQL Server as Data Source as we are going to connect with SQL Server 2008.
Specify your SQL Server Database Server and Database Name. In
my case, its HomeStation and TestDB respectively.
Click on Ok button. Designer will populate all DB Objects for you to select in Data Source Explorer pane.
Right click on Orders table and then click Create All Operation from
the menu.
A dialog will appear which will allow us to create basic Create, Read Item, Update, Delete and Read List
functions.
After clicking next Paremeters Configuration dialog will appear. Select each field one by one and click on Show in Picker from the Properties panel.
After clicking Next, Filter Parameters Configuration
dialog will appear. Filters are
important performance factor to consider when we handle large volume of external
data.
For now lets skip this step
and click Finish.
Click on Save button.
SharePoint Designer will take some time to save the changes.
Click on Create Lists
& Forms ribbon button. A Dialog will appear as below. Provide the List
Name as Orders and click OK button.
Navigate to All Site
Content page of your SharePoint site. SQL table Orders will be available for you as a Custom List (Orders).
Click on Orders
List. You can see the data in the list populating from external SQL Orders table.
Add a New Item in
the list and provide details.
Go to SQL Server and refresh the Orders Table. You can
see the Item inserted from SharePoint Orders List is available in the table. Similarly
you can perform Update and Delete actions on the list and changes will take
effect directly on the table.
SharePoint Designer is a quick and easy way to explore SharePoint Business Data Connectivity features but in real world scenarios we have more complex logic.
In my next blog, I will explore Business Data Connectivity Model through Visual Studio which enables us to write custom business logic while
integrating with external data.
