Saturday, February 22, 2014

SharePoint as a Relational Database

Every web application has a database, right? If you're developing an ASP.NET application, your first choice is probably SQL Server, Microsoft's relational database product.

What if you're building an application on top of SharePoint? If you come from an ASP.NET background, you might reflexively reach for SQL Server and go nuts creating your tables, columns, views, etc.

If you're starting fresh, with no existing database to integrate with, I suggest that you consider taking advantage of SharePoint's out-of-the-box capabilities to be your data store. There are some serious advantages to this approach that I'll get into later. You may not need (or want) to reach for SQL Server after all.


If You Squint Hard Enough


SharePoint can start to take on the look of a web-based SQL Server Management Studio if you cock your head at a certain angle. I'm going to reference the venerable Northwind database to illustrate the similarities between SharePoint and a relational database like SQL Server. 

The world famous Northwind database


Table ⇔ List

We need a Customers table? How about a Customers list?

The Customers table becomes the Customers list


Column ⇔ Column/Field

SharePoint lists have columns (called "fields" in the API) just like database tables have columns. The Customers table in the Northwind database has a ContactName column with a data type of nvarchar(30). Let's add a column to our Customers list. To fit the requirements, we'll specify that the column is a "Single line of text" and the maximum number of characters is 30.

Adding the Contact Name column to the Customers list

Limit the number of characters in the column just like an nvarchar column in SQL Server

SharePoint columns can accommodate any data you could throw at them. I'll discuss the different data types in more detail in a future post.

You often want a primary key column on your table that auto-increments, right? SharePoint automatically includes a column in every list called ID, that does exactly that. I'll add the ID column to my view (more on those in a minute).

SharePoint includes an auto-incrementing primary key column called ID on every list

The Customers table has a CompanyName column. I might want to put some constraints on it. It would probably make sense to require a value for that column, as what good is a Customers record without the customer's name? Just as SQL Server allows you to decide if a column accepts nulls or not, in SharePoint, we can set "Require that this column contains information" to "Yes".

You might also want to put a uniqueness constraint on the CompanyName column. In SharePoint, we have the "Enforce unique values" setting.

We might even want to specify a default value for the Country field, perhaps defaulting it to "USA" if our customers are primarily American companies. SQL Server has default constraints, and SharePoint has the "Default value" setting.

SharePoint columns can have constraints familiar to SQL Server users

For performance reasons, you can even index a column.

SharePoint columns can be indexed


Foreign Key ⇔ Lookup

The Northwind database has an Orders table with a foreign key to the Customers table because each order has a corresponding customer that placed the order.

SharePoint's answer to the foreign key is the lookup column. Since tables are like lists, we could create an Orders list and add a lookup column.

Adding a lookup column on the Orders list
Just as the Orders table has a foreign key to the Customers table, the Orders list has a lookup to the Customers list

You can even enforce referential integrity through cascade constraints. We could tell SharePoint that we want it to delete any orders that go with a customer we've just deleted.

You can set up cascading delete behavior, just like in SQL Server


View ⇔ View

SharePoint lists can have views, analogous to the views you'd define in SQL Server.

Adding a view to the Customers list

These views can have sorts, filters, group bys and other features you'd expect (even joins).


Trigger ⇔ Event Receiver

In SQL Server, you might want to attach a trigger to a database table to run some custom code when a record is being inserted, updated, or deleted.

SharePoint's answer to the trigger is the event receiver. You can run custom .NET code when certain events occur. For example, when an item is being added, updated, or deleted in a list.

Event receivers have so many uses that I plan to elaborate on them in a separate blog post.


SQL ⇔ CAML

When it's time to write some queries against your SQL Server database, you turn to its namesake, SQL. SharePoint has its own language for expressing queries called CAML.

Being a dialect of XML, CAML is quite verbose and can be unwieldy to write by hand. There are tools that can make CAML easier to manage, and I discuss them in my post Taming CAML.


SQL Server Management Studio ⇔ SharePoint Manager

When you want to inspect your SQL Server databases, you fire up SQL Server Management Studio and crack open the Object Explorer. It lays out the whole structure of your SQL Server installation and lets you view the data in your tables.

Browsing your tables in SQL Server Management Studio

There's an indispensable open source project called SharePoint Manager that accomplishes roughly the same objectives in the SharePoint world. SharePoint Manager provides a nice GUI for browsing the site collections in your SharePoint farm, and inspecting every list, column, view, event receiver, and every other SharePoint object that exists.

Browsing your lists in SharePoint Manager



Querying Data from Code


If you've been in the .NET world for long enough, or if you're working with a legacy code base, you may be familiar with using "raw" ADO.NET classes to query for data in your database. If you're working in a .NET code base of a more recent vintage, you're probably familiar with object-relational mappers, like LINQ to SQL, that provide a nice strongly-typed object model for your database interactions.


Raw ADO.NET ⇔ SPListItem, SPQuery, etc.

The SharePoint API contains classes for getting data out of lists in a weakly-typed fashion. If you're familiar with classes from ADO.NET like SqlCommand and SqlDataReader, then SharePoint API classes like SPQuery and SPListItem will seem very familiar.


LINQ to SQL ⇔ LINQ to SharePoint

Object-relational mappers like LINQ to SQL provide a much more natural, object-oriented interface to your data. LINQ to SQL includes a tool called SqlMetal that can be run against an existing SQL Server database and will generate C# or VB classes that represent the tables in your database.

LINQ to SharePoint has SPMetal. You point SPMetal at your SharePoint site and it generates C# or VB classes that represent the lists in that site. LINQ to SharePoint has some drawbacks to consider, which I discuss in my post Taming CAML.



What's in It for Me?


Sure, SharePoint can be the relational database for your application. But why should it be?


Scaffolding for Free

Speaking from my own experience, I can say that there's nothing more tedious than writing the CRUD forms that wrap a database table.

When Ruby on Rails came along, one of the things that blew me away was this feature it had called scaffolding. Once you had your database schema in place, Rails would automatically create working forms for you to create, read, update, and delete records in your database. This was revolutionary! I hated writing that stuff by hand! And it took up so much time.


If you're storing your data in SharePoint lists, you've got something that looks an awful lot like scaffolding. SharePoint automatically provides forms for shuffling around the data in your list. Once we defined our Customers list above, SharePoint magically gave us all the forms for manipulating our customer records. Sweet!

SharePoint automatically generates all the forms needed to perform CRUD actions on our Customer data

This auto-magic CRUD functionality in SharePoint is deep, and I plan to elaborate on it in another blog post.


Deep Security Integration

SharePoint has a deeply integrated security model. There's a built-in security system for applying permissions to your lists and even individual records in your lists. Once you have your permissions configured, SharePoint automatically trims its UI based on the particular logged in user, and even restricts actions performed by methods in its API.

SharePoint's screen for declaring the permissions on a list

For example, you could configure the security on your Customers list so that certain groups or users only have read permissions. In this case, SharePoint will automatically hide any of its UI elements that involve adding an item to the Customers list when these certain users were logged in. Even if you're running custom code that calls into SharePoint's API to add an item to the Customers list, it will pick up the currently logged in user's permissions and refuse to add the item.

SharePoint's security model is extremely deep and is woven into every aspect of the product. I plan to cover it in depth in a future blog post.


Easy Schema Changes

One of the biggest headaches that comes with depending on a custom database is the difficulty of pushing out schema changes.

Developer A needs to add a new column to a table. He fires up SQL Server Management Studio on his local machine and adds the column. Now what? Developers B, C, D, E, and F all need that column added on their machines, too. So Developer A might shoot an email to the other developers telling them what the column is called, its data type, whether it allows nulls, etc. and ask them to manually make the change on their machines. If he's a nice guy, he might even write a snippet of T-SQL they can run and send that over. 

But you also have a production server, a staging server, and a QA server. So now someone has to log into those machines and take the same manual steps to get that new column added. What if there are new tables or new views? This method of keeping your database schema up to date across machines quickly becomes a time-consuming and error-prone nightmare.

SharePoint has a comprehensive API for "schema" changes. Want to add a column to a list? Add a new list? Add a view

SharePoint features provide the opportunity to make your schema changes in code and have them automatically applied when your application is deployed or upgraded. This is another deep topic, and one I elaborate on in my post A Model for Upgradeable SharePoint Sites.



Closing

In my career, I've seen teams working on SharePoint projects reflexively reach for a custom SQL Server database when it was time to store some data. I hope that in this post I was able to show that SharePoint can be your database, and there are definite advantages to using it in this way.

As I've noted throughout, several of the topics require elaboration. When I cover them in future posts, I'll come back here and link them up as appropriate.

See you next time.

6 comments:

  1. Hi,

    Thanks for this article, but I would mention that this approach is applicable only for very simple applications : SharePoint implies also a lot of limitations. So for big real world business application, this is not relevant :Multiple Foreign Keys, Transactions, and much more... won't be supported.

    If you still want SharePoint integration, you should look after Provider or Auto Hosted App model.

    ReplyDelete
  2. Thanks for laying out this great overview and showing what is possible. Sharepoint has its limitations to be sure however there are some strong advantages as well.

    ReplyDelete
  3. Thanks for this article, I am trying to push sharepoint as data location and hopefully will be able to do it so!

    ReplyDelete
  4. Nice article. I was weighing my options and reading this has come at a good time to choose this over .net application, and actually can integrate .net custom code as things get complex.

    ReplyDelete
  5. Great article .. i shall be using it to design an application data and forms . Will provide my feedback as I go with it.

    ReplyDelete