Hello,
In this post I'm going to show you how easy it can be to integrate an external application (using any kind of database or even just web services) with SharePoint lists, linking, exporting and importing data to and from a non-SharePoint application.
My demo illustrates the following capabilities:
- Link an external item to SharePoint - we'll be selecting a customer from an external database table and entering sales data for that customer in a SharePoint list
- Exporting data to external DB item - we will then update the SQL table with the amount of the last sale
- Importing data back into SharePoint - changes made to the SQL table are imported into a log list located in SharePoint
I'll be using a SharePoint Foundation site with two custom lists: Sales and Imported Sales. I'll be also using a table in an external MS SQL database called Clients (contains: ClientID (int), Client Name (nvarchar), Phone (nvarchar), Email (nvarchar), LastSale (currency), LastUpdate (datetime)).
Link External Item
We'll be using LOB Item Link Field, it looks more or less like a lookup column, but gets its data from an external source: MS SQL, Oracle, MySQL, web services and so on. We are adding a column of this type to our Sales list, we also add Amount (currency column). The settings for the LOB Item Link Field are as follows:
We are basically providing the connection string and selecting a table and its column. We also chose to place each column into a separate SharePoint column (the additional columns are generated automatically).
That's it, now when you go to register a new sale, we can select a client from the DB:
Once saved, the values go right into SharePoint's columns:
Export to External DB
Once the item is saved, it's going to update the DB item with the latest sale amount. We use Smart Action Pro to execute a stored procedure in the SQL database to update the table row. The stored procedure accept SharePoint column values as parameters:
The actions runs automatically, whenever we add a new item to the Sales list. The stored procedure updates both the LastSale and LastUpdate columns:
Update Clients SET LastSale=@LastSale, LastUpdate=GETDATE() WHERE ClientID=@ClientID
Our sale amount is now registered in the SQL table.
Import into SharePoint
Our next goal is import the latest changes to the Clients table into Imported Sales list in SharePoint, demonstrating the ability to automatically import data. We'll be using Smart Import Pro, our component that can import emails, DB or web service items into SharePoint. A great advantages of this product compared to the alternatives (such as email-enabled lists in SharePoint) are that it can import into any type of list or document library and it can not only create new items, but also update existing items, based on the new data.
We'll set up an import profile. It will run every 15 minutes and import latest changes in our Clients table into Imported Sales list. That's why we needed LastUpdate column in our SQL table, we'll be using it to figure out which items have been updated.
We've completed the whole cycle of updates now, we have information coming to SharePoints, updates pushed back into SQL DB and come back to SharePoint, without any custom development!
Add your comment
