In this tutorial, you will learn about the pros and cons of creating a custom database table within the CMS database using ServiceStack.OrmLite. Creating custom tables can be a sensitive area for some developers. Some purists frown upon creating a custom table within the CMS database. Often, I have personally found myself to be in this camp. CMS systems can come and go within an organisation. Often companies business logic will long remain after a CMS project has been decommissioned. If you store your application data within your CMS database and you swap CMSs systems later on, what happens?
I really like the micro-service design pattern. Abstracting business data via an API with its own data course. When I work with enterprise-level organizations, this is the approach that I usually recommend. On the smaller scale of the spectrum, if you run a small Umbraco website, with a limited budget, paying for a separate SQL database as well as a potential web app to host an API may not be financially worth it.
Deciding where to store your data is one part of this tutorial, the other is the ORM part. Querying a SQL database using a framework in code can make development a lot more efficient. People who come from a SQL background tend to like keeping everything in SQL. Code that maps to SQL is known to be less efficient. Some people will prefer to have a greater degree of control over what SQL queries get run and the idea of passing this responsibility over to a framework isn't one of pleasure. I like ORMs as they allow me to follow a code-first approach. Code first is really helpful when writing unit testing, as it allows you to unit test your SQL code.
Like any good workman, you need to have numerous tools in your toolbox, will make you a better developer. Having a diverse toolbox will allow a greater degree of success within your projects 🛠⛏🔨🧰.
OrmLite
OrmLite is an ORM framework, that is similar to EntityFramework. It provides a clean, fluent API to map C# POCOs to SQL tables and SPROCS. In terms of development, it means you can do everything in Visual Studio and not worry about SSMS
In Visual Studio, right-click on your solution and select Manage Nuget Packages, search for ServiceStack.OrmLite.SqlServer and install it. Next, we need to define some C# classes. In this example, I want to create a basic commenting system, so my class diagram will look like this:
The first step is write some code that will create a database table based on a class:
If you run this code and look within your Umbraco database, you'll see a new database table:
How simple was that! Now we have a table, you can then use Linq to insert data into the tables and perform queries to get data out of it:
We can also insert data, like so:
As you can see, the code to create tables, query and insert data into a database is super simple. I liked using OrmLite, however, it is less well known and used compared to Entity-framework. EF is a much larger framework compared to OrmLite. Keeping things keep things minimal and bare-metal can simplify your architecutre. In general, I think I would favour EF over ORM, however, as you can see OrmLite is very simple to set up and use. Happy Coding 🤘
