MOSS 2007: Displaying data from SQL Server Database

Question

I have a requirement to select some data from a table in a database (SQL Server) and display it on a page in a SharePoint site (MOSS 2007).

I have a little bit of experience of MOSS, so I know I have a couple of options:

• BDC
• SSRS
• or possibily even a custom ASPX page.

I've never really worked with any of these. Can anyone advise on the +/-'s of each?

Answer 1

It all depends on what functionality is required once the table is displayed in SharePoint. Do you need extra bells and whistles or is a dump of the data adequate? (Think about the future as well.) Here's some thoughts...

Business Data Catalog (BDC)

If you want to nicely integrate the SharePoint look and feel with your data as well as get some cool functionality, BDC is a good solution. There is no need to program anything and these are the features you get. However if this data will only be used once in one way then it is probably overkill.

You would need to roll your own XML description of the data so SharePoint knows how to pull it from the database (or use something third party like Lightning Tools). As Colin mentioned it requires the Enterprise license of MOSS.

SQL Server Reporting Services (SSRS)

If Reporting Services is already available for use then this is the quickest and simplest solution. It works well but can get painful as more advanced features are required in your reports.

You should be able to use the Report Designer wizard to point to your table and set everything up for you. There are SSRS web parts that can be used within SharePoint, or indeed you can run SSRS inside SharePoint using SharePoint integration mode.

Application Page

This method Steven mentioned lets you integrate an ASP.NET application into SharePoint (such that it can be accessible by a URL within your SharePoint site). This allows anything that ASP.NET does, and the code-behind will be aware of SharePoint so you can tap into that if needed as well. The cons here are code access security and deployment.

See this webcast for how to do it.

Web Part

Similar to developing an application page but much more integrated with SharePoint. A custom web part is very powerful and allows you to integrate ASP.NET code on a SharePoint page. As you would know web parts can be dragged to whatever location and give a consistent properties pane. Again, code access security and deployment needs to be considered.

If you like designer view in Visual Studio then be aware this isn't the approach that Microsoft push. You can however use something like SmartPart where you would develop your custom app as a user control and the SmartPart wraps it into a web part. I'm used to doing it the MS way now and actually prefer the additional control this gives, however it can take more time.

Page Viewer Web Part

If you don't want to touch your SharePoint installation at all then have a look at this web part. It takes a URL as parameter and displays an IFrame containing the page that would be your report, either in SSRS or standard ASP.NET.

The downside from this lack of integration is problems with sizing the IFrame, and possible security warnings from the browser if accessing the URL in a different domain.

Answer 2

Don't forget the DataView Webpart. It has a pretty small developement footprint and works really well as a first "cut" if you are unsure as to how hardcore you need the final result to be.

It renders using XSLT, giving you full control of the final HTML, so it can do some pretty things with Javascript and co.

For a very simple example of charting.