Accessing the Data in Sharepoint

I’ve been spending some more time investigating on how to develop applications in Sharepoint. As said earlier, one question is always: Where to store the data?

In a standalone Application, I would obviously use a SQL Server or some XML Files, but both do not really seem fit with Sharepoint, as Sharepoint already has a mechanism for data storage – Lists and Libraries.

From here, it is worth looking at the White Paper for Microsoft’s Fantastic 40. This document contains some information on how Microsoft built their templates, but to me, it also looks like Sharepoint is not made for “real” relational data management. I like the part where they use JavaScript to set an ID. Nothing against JavaScript, but that’s not really what i’d like to use for anything important.

Jose Baretto has two interesting blog posts. One deals with Sharepoint Application development in general. For me, Point #2 was especially interesting:

When you develop your application, there’s usually a database (or a few) that goes along with it. In the early stages of your application lifecycle you will define what data you need to store and that will end up in a set of tables somewhere. Your data architecture team will define what the data should be and your database team will eventually define the tables, columns and views you will eventually implement. In the end, your ASP.NET application will be tied to a database server somewhere by something called a connection string.

If you’re using SharePoint, you get a place to store your data. No direct SQL Server access. No connections strings specific to your application. Instead of tables, you get SharePoint lists and libraries. You can create custom lists with custom columns that look a lot like the tables. You can also have links between lists like using lookup fields, which are very similar to a foreign key in a database table. When you deploy your application and create a new instance of it, the data elements are there.

This is nothing really new, just reassuring me that my plans for the data storage are indeed getting a bit more complicated. The only “good” way is to use Sharepoint lists, as they can be easily deployed without creating external dependencies. His second Blog post deals with deployment, and it essentially encourages the use of “no-code” whenever possible.

You should promote the use of no-code SharePoint applications. Those are the templates and solution definitions that are based purely in metadata definition, like columns, lists, views, pages and workflows that carry no custom code. If your package (templates and site definitions) has only XML/ASPX files and no assemblies, that a good indication that your risks are low.

It is true that this is more a security concern than a technical one, but from here I think i’ll look into more ways to achieve my tasks with the built-in means of Sharepoint. This also means: No SQL. Instead, it is possible to use CAML (not to be confused with the programming language) to query data.

For CAML, Microsoft has documentation on MSDN, which i’ll give a read next.

As said earlier, Sharepoint is an Application Platform. It is an interesting technology, even though sometimes I indeed have to question if it’s really suitable for every business application. I’ll guess i’ll know when i’m done with my test project…

Sharepoint Link List #1

As said yesterday, I’m playing around with Application Development in Sharepoint at the moment. I’ve found a variety of good sites and blogs, so here is my first, mostly unsorted link list.

  • SharePoint is not the Holy Grail
    • A good blog posting about the fact that Sharepoint alone will not solve any problems – it’s a tool, not a miracle
  • Development and Engineering practices
    • Before starting any development, this article has some nice information from someone who had to learn it the hard way 🙂
  • How solution deployment has changed development with SharePoint technologies
    • A posting about Solutions in Sharepoint 2007 and how to install them
  • What are Content Types?
    • Content Types are very important if you want to use Lists instead of “real” database Tables. This posting is specifically about Content Types in Document Libraries, but it gives a good general overview.
  • Simulate Parent / Child relations
    • If you use Sharepoint Lists instead of real database tables, this article shows how to simulate Master/Detail relations. Here, Content Types play a Key Role
  • Two Articles about List Events: Article 1, Article 2
    • This seems to be a bit outdated (mentions Beta 2 of MOSS), but still a good read. Essentially, when developing applications, keep in mind that the user might use the Lists.asmx Web Service to create new items. If you have all your application logic in a custom form, this may be the source of problems.

Disclaimer: All the links were valid when I made this post. This is the internet, so links might change over time.

Sharepoint 2007 Application Development

Sharepoint is an interesting product, because it’s hard to understand what it actually is and does. It looks like a File Server with the ability to store Documents, yet it enables server-side Full-Text Search on those. It also looks like a Server Version of Excel or Access (which is actually partially true, it is intended as a Server for Collaboration) with the ability to store informations in Lists. And it also looks like an Application Server, with the ability to host Workflows.

But it’s the Application Server Role that i find somewhat tricky. if you “only” want to share data or do Approvals, Sharepoint is really straight forward. Create a list, write a workflow, done. But what if you want to make something “funky” with Database-Relations? Here, Sharepoint has some limitations – but limits are there to be broken, right?

Chris Johnson looks at some ways to do “real” Application Development with Sharepoint. I find Option 2 and 3 a bit “awkward”, but Options 1 and 4 clearly show that it can be done. (I don’t like Option 3 because the SmartPart does not seem to be well maintained, as Godotnet is Phased out and I could not easily find another download link – certainly not a good promise to base development on). Option 4 is possibly the only one really feasible, even though it looks a little bit like a “hack” to me. But then again, The tree of software development must be refreshed from time to time with the blood of hacks and workarounds.

For me, there are still a few open questions. Like: Is it better to use lists to store the data, even though if the data is strictly application-internal? Or should a “real” database be used, thus adding a second database layer? I would prefer option 1 if feasible, because then the deployment is easier, and i don’t have “hidden” data somewhere – absolutely everything is stored in one site. I lose a lot of what makes databases though, as in Foreign-Keys or most other Constaints. In terms ob Query/Join, i cannot use SQL anymore, but XML or CAML would work as a substitute in most cases. And what about deployment – is it possible to wrap such an application together as a Solution/Feature or as a Site Template?

I think i’ll spend the next days with fiddling around with this. Too bad you can’t use “real” versioning properly, but using the FileVersion will have to be sufficient then…

Souvenance – Open Source Photo Album Software

As mentioned earlier, my search for an Photo Album software was not very successful. My needs were rather basic: I want to be able to just upload my pictures to a folder without having to go through 20 Steps of uploading and adding them to some sort of Album. I want to be able to share Photos with my family and friends, without having to spend hours installing, configuring and managing the Album. I don’t need a fancy Plugin-System that allows people to click on a Photo and order T-Shirts with that Photo printed on it. And I certainly don’t need a database-backed system just to share a few Photos.

On the other Hand, static Pages are also not optimal. Sure, Adobe Photoshop Elements has a nice Export for a Flash Presentation, but adding/removing Photos is still a lot of work on static pages.

I found something for PHP a while back, phpGraphy. This was exactly what I wanted: I just upload my Pictures, and they are displayed. I can also create Users and restrict my Albums. No SQL Database, no complicated Setup, just straight-forward Photosharing.

Starting from the impression that phpGraphy gave me and looking at the available Gallery Software, I immediately knew that I would write something on my own. The result is Souvenance, an Photo Album Software that can be set up in 10 Minutes and does not require any database or any complicated configuration. I released the first Beta a few days ago, and someone already mentioned it in a list of 49 open Source projects. I have to say that i’m honored to be in a list with software like Audacity or xine. (The posting was also mirrored by a chinese Linux site and another Blog).

Anyway, if you are searching for a “zero-configuration” Photo Album, give Souvenance a try. There is even a User’s Manual available now! I’ll also write more about the Development History later, first I have to finish Version 1.0 🙂

Open Source and .net – not really an ideal partnership

Open Source Software is one of the cornerstones of the modern internet, not only thanks to the Apache Web Server, but also (and in my opinion, mainly) through the vast amounts of Open Source Software that runs on these sites. Forums, Blogs, Photo Galleries – there are dozens of good Open Source programs. Yet arguably, most of them are nowadays written in PHP, including this very Blog which runs on WordPress.

There is nothing wrong with PHP (ok, actually there is, but that does not matter here), but I recently switched to for work purposes. I love C# and, among other things also because of the very strong built-in class library. So it felt natural to look for an Blog Software and Photo Album to run an website on.

Given the fact that this Blog is hosted on WordPress, you may have guessed that the search was not very successful. As Jeff Atwood puts it, Open Source is often a second-class citizen in the .net ecosystem.

There are indeed plenty of Blog Systems – SubText, Community Server,, to name a few. I tried about 6 or 7 different ones, and I decided to stay with WordPress. Why? Because it is much more mature. With PHP, installation is normally a 3-step process:

  • Upload all files to your web server
  • run the install script
  • use the applications

With the Blog Systems i’ve tried, installation is a process for the system administrator. Manually building Connection Strings and putting them in the web.config file? Having to create a separate IIS Application for the Admin Panel? Requiring dbcreate rights in the database? Asking for FullTrust? That’s not how it is supposed to be!

I don’t know if .net Developers in general consider themselves “Elite” or “Enterprise Developers”, but you can clearly see a different philosophy. Warning: I am generalizing here, there are always exceptions, and this is only my personal opinion.

.net is obviously associated with Microsoft, so I see a lot more “commercial” attitude. The installation is a lot more complicated, you have all sorts of weird requirements, and the amount of “free” information that can be dug up by google is rather sparse. PHP is associated with your typical Linux Hacker, which is why there is even the acronym “LAMP” to describe your standard Setup of Linux, Apache, MySQL and PHP. PHP does not seem to be as polished as .net, it looks a lot more like “organic growth”, with all it’s pros and cons. But the available PHP Applications seem a lot more polished and it is no problem to find a wealth of good PHP Code and Tips by Google. (Of course, you can also find a lot really really bad PHP Code on Google)

It feels a bit awkward to associate Microsoft with Open Source, even though they now seem to be a bit more serious about it. But that should not prevent anyone from making great Open Source Applications with .net Technology. is one example of great .net Open Source Software.

Coming back to my original plan to find an Blog Software and Photo Album, I have given up finding one. Instead, I started to make my own now. My Photoalbum – Souvenance – is now in Beta and should be released soon. I will then continue to work on my own Blog Software. More information on that will follow later.