As SharePoint becomes more and more central to a business for their information management the amount of data that is required to be stored in SharePoint lists and libraries grows to, in some instances, very large amounts. Large volumes of data in SharePoint lists can lead to performance issues as and when the pages are rendered, even if views are maintained properly within SharePoint.
As users want to be able to navigate around the site without the constant accompaniment of the egg-timer it is sometimes best to store data in external data stores or even retrieve data from the data store of another system.
Using external data stores in SharePoint is not as difficult as it might sound as there are many ways that it can be accessed.
Custom Web Parts and Workflow Actions
Web parts and custom workflow actions do require some coding but they open up the ability to 'read from' and 'write to' all sorts of data stores - in essence if the data can be connected to it can be used.
As web parts and workflow actions are created using Visual Studio API’s and Web Services for applications (such as Sage), they can easily be added as a reference into your visual studio project and then with the namespace declared in your code you are good to go…
One of the most common external data stores used is SQL server. SQL databases can be accessed by using the namespace Microsoft.Data.SqlClient in a project (this will also require a reference to be added to System.Data if you do not have this in your project already).
Here is an example (using C#) of how to read SQL data:
//Create an SQL connection and open the connection
SqlConnection sqlConnect = new SqlConnection("server=SERVER1;Trusted_Connection=yes;databaseDATABASE_NAME; connection timeout=30 ");
sqlConnect.Open();
//Create an SQL Query
string strSQL = “SELECT * FROM tblCustomers”;
//Create an SQL command and reader for receiving the data
SqlCommand sqlCommand = new SqlCommand(strSQL, sqlConnect);
SqlDataReader oReader = null;
//Populate the reader object with data from the SQL command
oReader = sqlCommand.ExecuteReader();
//Loop through the reader’s records (the rows in the database) and access the information
while(oReader.Read())
{
//Access current record data like this: oReader[“Customer Name”].ToString();
}
Data Connections in InfoPath forms
InfoPath supports a variety of connections to external data. These range from data within SharePoint to web services and SQL databases, and what is more these data connections are made through wizards which means there is no need to spend any lengthy time using code.
The wizard for adding and amending data connections in InfoPath 2007 and 2010 can be accessed at the bottom of the Fields panel by clicking the ‘Manage Data Connections…’ link.
For more in-depth and dynamic access to external data in InfoPath .NET code can be used to access web services, SQL databases and other databases - on the click of a button or the selection of a drop down box.
Business Data Catalogue / Bamboo MashPoint
The Business Data Catalogue (BDC) is a feature of MOSS 2007, SharePoint Foundation 2010 and SharePoint Server 2010. It allows connections to be created to external data stores and third party systems which can then be used within SharePoint lists as a drop down field or within data viewer web parts. This is a quick and simple way to connect to external data and make it readily available in SharePoint as though it is actual SharePoint data.
In Windows SharePoint Services (WSS 3.0) the Business Data Catalogue is not available, however thanks to the good people at Bamboo Solutions there is a free alternative called Bamboo MashPoint. MashPoint can be used on both WSS3.0 and MOSS2007 as it does provide more connectivity to external data stores than the Business Data Catalogue does so it still shows a considerable improvement in MOSS2007. To read more information about MashPoint click here.
External Content Types (ECT's) - SharePoint 2010 onwards
External content types are a great new feature that has been brought to SharePoint 2010 and are nice and easy to set up using a wizard within Microsoft SharePoint Designer 2010.
ECT’s will allow a connection to line of business applications and static data stores (i.e. SQL server) and are used within SharePoint, just like a normal content type on a list. These content types can also have permissions set against them within the central administration site which will allow different access levels (such as read and write) to users who are accessing/using the ECT.
One of the most powerful abilities of ECT’s is that when the information is amended within the SharePoint environment it will automatically update the changes back to the external data store, for example if you have your customers in a line of business application and a customer’s address is updated via an ECT within SharePoint, the customers address will also be updated in the external data store, and therefore in line of business application as well. Be conscious of permissions when doing this - a whole separate subject!
So hopefully you have found this useful and you can go forth and connect lots of external data. I welcome your comments and questions to this blog and keep calling back to see what other cool stuff I have for you on SharePoint and its uses… Cheers!