When it comes to displaying SQL data (really any data set), there are two main approaches to take to get that data in your SharePoint Sites. The first is the SPGridView, and the Second is the DataFormWebPart. Both approaches have very clear strengths and weaknesses, and this is what I intend to discuss in this post.
DataFormWebPart
The DataFormWebPart is most commonly seen and used in conjunction with SharePoint Designer. It comes with Designer right out of box, and is exceptionally convenient and an easy way to build your own web parts to display data from various sources. What is great for some people, is that you never need to write a single line of code, however, I often get to a place where I need to customize the auto-generated XSL to meet custom business needs, but I don't consider that programming (just finessing J ).
In fact, the DataFormWebPart is the one of only two reasons I ever use Designer for anything (the other being workflows). I would say that I build three quarters of all my web parts as DataFormWebParts because of the flexibility, speed of creation, and their ease of use. However, the problem poised to developers that use the DataFormWebPart is that connections to SQL databases has been disabled by Microsoft. It was enabled in FrontPage 2003 with V2 of SharePoint, but many speculate that because of security concerns, they pulled the plug just prior to the release of V3. This however, you can circumvent. Read my post on writing your own customer DataFormWebPart with C# rather than SharePoint designer, to learn how to connect a DataFormWebPart programmatically to a custom web service that returns SQL data as XML, and is in turn transformed to HTML via XSL. This process is a lot more overhead than it once was, however, there are clear benefits to taking these extra steps:
Advantages
- Very Flexible Presentation – With XSL you can do almost anything. The base GridView control, conversely, is rather limited in how the DataSet that is bound to the control can be rendered. With a DataFormWebPart, all that data comes back as XML and can be transformed any way you like. See the Approaches in Action section to view some examples.
-
Sorting, Filtering, and Grouping with the DataFormWebPart is a no-brainer, it is all done for you, automatically! The only drawback is that it forces a post back, so if your requirements need AJAX, you're out of luck.
Disadvantages
- A lot of overhead required to get a DataFormWebPart to connect to a SQL resource. However, this "overhead" is about the same as the other approach, so it is more of a mute point. I point it out though, just to say it is not your typical SharePoint Designer experience that you may be used to.
-
No debugging. You can't debug XSL (step through XSL), so this leaves you needing to take the Pac-Man approach to development. Build a little. Test a lot. Rinse, lather, and repeat.
Main Business Driver
Your usability requirements will usually cause you to choose the DataFormWebPart over the SPGridView. The XSL is INCREDIBLY flexible, allowing you to do all kinds of cool things that you may not be able to do with a GridView control, for example.
SPGridView
The SPGridView is fundamentally a 'jazzed-up" GridView control. You take a DataSet, possibly from a stored procedure, and you set it to the DataSource's property of a DataView object, to whom you bind to a SPGridView. All nice and simple, the way we've always done things even since the beginning of time as we know it (or at least since ADO.NET has been around J).
However, the SPGridView gives us some things that the base GridView doesn't, mainly being the SharePoint "look and feel". All the SharePoint styles will apply themselves, making the SPGridView look very similar to a SharePoint list. This in it of itself isn't an advantage over the DataFormWebPart, because that control does the same thing, but would leave to you conclude that you would rarely ever use just a GridView in a SharePoint environment because most of the time you'd enjoy that consistency.
Advantages
Disadvantages
- Not a very flexible user interface. If you have complicated usability requirements or UI Mock-ups, the SPGridView may not be flexible enough for you.
- A lot more overhead is required for sorting, filtering, and grouping, whereas the DataFormWebPart is seamless in this regard.
Main Business Driver
The relative ease of use of a SPGridView is a large bonus, and what is even bigger is the ability to debug and step through your code. People without a lot of XSL experience will want to go this route as well.
Approaches in Action
Notice how the presentation of the DataFormWebPart is highly customizable (figure 1). The report below is of a number of SharePoint sites, and some meta-data. My usability requirements specified that there needs to be a check box next to each site, and when the user checks the box, the entire row is highlighted. Additionally, at the top of the web part I added some aggregate data. This would not be AS easy with a SPGridView (it is possible), but with the DataFormWebPart's XSL, it was a snap! Lastly, look at the built-in sorting, filtering, and grouping. All that functionality was effortless! Figure 1 really only demonstrates the basic concept of the potential here. Within the XSL, you could input any HTML you want, and in effect the sky is really the limit.
Figure 1
The SPGridView example is a screen shot (figure 2) from my SPLimitedAccessDiscovery tool you can download for free. That tool features a SPGridView that is used to present the results of my report in a grid format (right image, under "Understanding!"). However, notice the how the styling looks almost identical to the SharePoint look and feel (left image, under "Confusion?"). They look almost identical. In fact, I bet SharePoint is using SPGridView through the product.
Figure 2
So there you have it! I would say that I most often pick the SPGridView these days; however, if I know I'm working toward a highly sophisticated interface, the DataFormWebPart remains a very strong approach to presenting SQL data.
Good luck!
Phil