Methods to Get External Data from Sharepoint

This content was original posted on the sharepoint_tech list, and has been slightly edited for repost here. There is some new content in the bullet (last bullet).

Developing applications within sharepoint is largely dependent on getting access to data which isn’t natively within the sharepoint DB to begin with. But accessing data from within Sharepoint isn’t just for web developers anymore. End users can easily make use of external data from within Sharepoint. We’ll explore that later.

Getting access to external data from within Sharepoint is supported via a variety of options, including:

  • Manual import into a sharepoint list. This method assumes that sharepoint will become the authoritative source of this data (unless you manually synchronize). Excel import, Access import, and several other methods can accomplish this. This is a very end user accessible method, but may not be ideal if the data already exists in a sql database. Basically this is good for smaller amounts of data and for adhoc cases.
  • Business Data Catalog (BDC). The BDC is a collection of external data source connections, with definitions of the type of data within those external sources. These connections are defined centrally (this is one of the shared services that many farms can reuse), and then available to every Sharepoint site. By “pre-defining” the data connections once, the external data is widely useful to many people. The BDC allows end users to easily incorporate data into their sharepoint site without requiring those end users to know anything about the external data connection, or even the query language for that data source. Farm administrators setup the BDC connections, but end users get the benefit. This is good for data which has wide usefulness. External sources defined in the BDC can optionally be crawled by the Sharepoint search engine.
  • Define database connections within Sharepoint Designer. These connections can be leveraged with views, tables, and other data controls on that single webpage. This approach requires some technical knowledge of the database, the connection specifics, and the relevant query statement (usually tsql). This might be a common approach for web applications by web developers. This approach doesn’t assume any special privileges with respect to the Sharepoint server (more on this below).
  • Define database connections with Visual Studio. The framework has been commonly available for quite awhile to create connections to external data sources. So it’s no surprise that it can also be used within Sharepoint along with data controls (e.g. gridview) in highly customized ways. This approach requires developer-level knowledge, and would only be required for web applications with very unique requirements. This approach does assume some special privileges with respect to the Sharepoint server. Specifically, this approach requires code-behind, and all pages with code-behind fall into a special category in terms of how they are provisioned. This is because these pages don’t actually live within the Sharepoint DB, but instead live on the file system itself. On a shared Sharepoint server, there may be change management procedures behind deploying these pages.

Obviously there are many points of accessibility here with  options for users of varying sophistication. The bottom line is that Sharepoint makes it very easy to get at external data and use within a web context.

Leave a Reply

Your email address will not be published. Required fields are marked *