How To Use C# Classes To Create Custom Database Tables and Repositories With Umbraco

Today, I'm going to talk about a sensitive area for some developers. Creating custom tables within your CMS database. Custom tables within your CMS database are frowned upon by a lot of purists. In a lot of situations, I consider 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 the 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:

public class Comments
{
public Guid ID { get; set; }

public string Name { get; set; }

public string Email { get; set; }

public string Comment { get; set; }
}

Next, we need to write some code that will create a database table based on the class:

var dbFactory = new OrmLiteConnectionFactory(
    System.Configuration.ConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString;,  
    SqlServerDialect.Provider);

using (var db = dbFactory.Open())
{
   db.CreateTableIfNotExists<Comments>())
}
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:
  using (var db = connection.Open())
            {
                var comments = db.Select<Comments>(x => x.Email == "[email protected]");
                var comment = db.Single<Comments>(x => x.Email == "[email protected]");
            }
We can also insert, like so:
            using (var db = connection.OpenDbConnection())
            {
                db.Insert(new Comments { ID = Guid.NewGuid(),Email = "email", Name="name" } );
            }
AS you can see creating tables, querying data and inserting data into it, is super simple with OrmLite. It's a lot more weight weight than entity and in the right instances, it's a really quick and powerful way. If you want to create an Umbraco package for example, this is a simple way to create a data store for it.


Jon D Jones

Software Architect, Programmer and Technologist Jon Jones is founder and CEO of London-based tech firm Digital Prompt. He has been working in the field for nearly a decade, specializing in new technologies and technical solution research in the web business. A passionate blogger by heart , speaker & consultant from England.. always on the hunt for the next challenge


Back to top