MOSS 2007: displaying data from a 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 on a SharePoint site (MOSS 2007).

I have a little MOSS experience, so I know that I have several options:

• Bdc
• SSRS
• or maybe even a custom aspx page.

I have never worked with anyone. Can anyone advise +/- everyone?

Answer 1

It all depends on what functionality is required after the table is displayed in SharePoint. Do you need extra bells or whistles or is it a data dump? (Think also of the future.) Here are some thoughts ...

Business Data Catalog (BDC)

If you want to integrate the look and feel of SharePoint with your data, and get great functionality, BDC is a good solution. There is no need to program anything, and these are the functions that you get . However, if this data is used only once in one direction, then this is likely to be redundant.

You need to flip your own XML data description so that SharePoint knows how to pull it out of the database (or use something third like Lightning Tools ). As Colin said, this requires an Enterprise MOSS license.

SQL Server Reporting Services (SSRS)

If Reporting Services is already available for use, this is the fastest and easiest solution. It works well, but it can be painful because your reports require more advanced features.

You should be able to use the Report Designer wizard to point to a table and configure everything for you. There are SSRS Web Parts that you can use in SharePoint, or you can run SSRS inside SharePoint using SharePoint Integration Mode .

Application page

This method allows Stephen to integrate an ASP.NET application into SharePoint (so that it can be accessed at the URL of your SharePoint site). This allows everything ASP.NET does, and the code will know about SharePoint so you can use it if necessary. The disadvantages here are the protection and deployment of code access.

See this webcast for how to do this.

Web part

Similar to developing an application page, but much more integrated with SharePoint. The custom web part is very efficient and allows you to integrate ASP.NET code into a SharePoint page. As you know, web parts can be dragged to any place and create a solid property area. Again, you must consider the security and deployment of access to codes.

If you like the design look in Visual Studio, then be aware that this is not the approach Microsoft takes . However, you can use something like SmartPart , where you developed your custom application as a user control, and SmartPart wraps it in a web part. I'm used to doing it now in MS, and actually prefer the extra control that this gives, however it may take longer.

Web Browsing Web Part

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

The disadvantage of this lack of integration is the issue with the IFrame size and possible browser security warnings when accessing a URL in another domain.

Answer 2

Do not forget DataView Webpart . It has a fairly small development area and works great as a first "cut" if you are not sure how hardcore the final result is needed.

It displays the use of XSLT, giving you full control over the final HTML, so it can do some nice things with Javascript and co.

For a very simple charting example .

Answer 3

Have you ever used the .NET Framework to create web pages? MOSS 2007 uses this structure for master / template files.

Thus, your best bet is to create an .aspx template file to display the results on a web page.

Answer 4

BDC is the way to go if you want to keep it "strictly Sharepoint". The problem is that it is only included in the enterprise version of SharePoint. If you have a standard version, you can apply for SSRS and the SSRS web part or create your own website. This way you can use data mapping anywhere on your site as well as ASP.NET.

Answer 5

As Nat said - if all you need is to display some data from the database on the page - DVWP is a great solution. Simply open your page in SharePoint Designer, add a database connection, and release the web part on your page. You can use any of several pre-formatted layouts or create / customize your own so that data is displayed as needed.

Relatively simple, many online games, and much faster / easier to set up than something like BDC.