Today, you will learn about the pros and cons of creating a custom database table within the CMS database. Creating custom tables can be a sensitive area for some developers. Some purists frown on custom tables stored inside your CMS database.
In some situations, I have definitely found myself to be in this camp. If you build your application logic within your website, you can run into numerous problems later down the line. My usual advice if you need custom tables is to strongly consider using an API and a separate database.
I really like the micro-service design pattern and when I work with a large enterprise-level sized organization, this is the approach you should adopt. On the scale of the spectrum, if you want to run a small Umbraco website, with a limited budget, especially in the cloud, paying for a separate SQL database is not viable. The other group of people who might get up in arms with this post is the people who come from an SQL background. Some people will prefer to have a greater degree of control over what SQL queries get run and the idea of creating and doing all querying of SQL through C# classes isn't one of pleasure. The last group might just ask, why not just use Entity Framework?
The reason why you might want to consider using a different approach mainly comes to deployment. Using a C# class approach allows you to easily and quickly implement a code-first approach. This can be really helpful when unit testing, as it allows you to unit test your SQL code. This gain in unit testing isn't for everyone, but I personally think the information I'll very shortly jump into is really useful in certain projects. Like any good workman, you need to have numerous tools in your toolbox. Having a diverse toolbox will allow a greater degree of success within your projects.
Enough..Let's Get Started
If you are a regular reader, you know I'm a keen advocate of not re-inventing the wheel. If we want to work with C# classes instead of using SQL, then we're going to use a third-party component, in this instance I'll use ServiceStack.OrmLite. As we're using Umbraco, we will need to specifically use 'ServiceStack.OrmLite.SqlServer'.
In Visual Studio, right-click on your solution and select 'Manage Nuget Packages' and select ServiceStack.OrmLite.SqlServer. Next, we need to define some C# classes. In this example, I want to create a basic commenting system, so my class will look like this:
Next, we need to write some code that will create a database table based on the class:
If you run this bit of 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 and perform selects from it:
We can also insert, like so:
Creating tables, querying and inserting data into them is super simple. There are many ways to access data but using an ORM is the best way to go. OrmLite is one option. Entity-framework is another. Enjoy!