Go NoSQL Schemaless with DNN and SQL Server

Go NoSQL Schemaless with DNN and SQL Server

We’ve learned many lessons through the noSQL movement but one of the most useful ones is having a flexible schema which allows you the develop software faster. Best of all, you don’t have to convert to MongoDB in order to take advantage of these concepts.

I use the schemaless term loosely because the truth is you absolutely do have a schema. The schema has simply moved from the database to the business logic layer.

Standard Entities

This is how you used to do it, you started with a standard entity the created the manual database field mappings:

What if you want to add another property? Your process looks something like this:

Add/Modify the property to your entity

Add/Modify column to your database table

Modify the SQL CRUD (select, insert, update) statements to access that new field

Pretty mundane and long winded! Gets old, quick, right?

Using PetaPoco With Your Entities

With DNN this process gets a little easier with the addition of the DAL2 and PetaPoco. You can skip the requirement of updating any SQL CRUD because it is now generated by PetaPoco as parameterized SQL.

Now that’s not to bad when you know what fields you want to add, but what happens when you don’t know? The ability for the user to dynamically add additional properties with their own configurations is a requirement for many in today’s day and age.

Unfortunately PetaPoco only maps to standard types such as string, int, boolean etc, so we lose the ability to map to complex type objects such as List<T>.

Adapting PetaPoco For Unlimited Properties

With just a couple of field attributes and an additional property we can achieve adding properties solely within the application business logic layer, as follows:

We’ve abstracted out the field into it’s own class and added a property Fields which is what is used within the application. It is decorated with PetaPoco’s [IgnoreColumn] attribute because this field is not going to be saved to the database. The string field FieldsAsXml which serializes the list into XML is store in the database instead

The addition of the [ColumnName("Fields")] is just for cleanliness when saving the property to the database

The [XmlIgnore, JsonIgnore] is to ensure that this property is not included when serialized and sent to the client through WebAPI

The user can now dynamically add fields to the profile where Name is “First Name” and Value is their name, for instance.

The Benefits

The primary benefit of this method is that we can now can simply add properties to our Field class and have it automatically get stored into the database without having to create additional database objects or CRUD every time.

A secondary benefit of this method is that our data is still queryable using SQL’s XML selectors. If another module or utility wishes to utilize the data it is trivial to do so. For example, if you wanted to create a SQL view that gets all the user’s email addresses, you could do just that:

select profileFields.value('(/ArrayOfFieldValue/FieldValue/Value/node())[1]','nvarchar(50)') as Email from MyCustomTable
where profileFields.value('(/ArrayOfFieldValue/FieldValue/Name/node())[1]','nvarchar(50)') ='Email'

As usual, there are always many ways to “skin a cat”, and this is just one of them. We wanted to talk about PetaPoco (and not Entity Framework, for instance) because it is a framework already baked into DNN and therefore no additional configurations are required to get up and coding.

Tags:

Jonathan Sheely

Sr Software Engineer at Maxiom Technology. Jonathan is an out of the box thinker who has over 10 years experience building and supporting web application software and infrastructure. Jon specializes in ASP.NET C#, Javascript and CSS but is willing to learn anything that gets the job done.

No Comments

Sorry, the comment form is closed at this time.

About Us

We turn your ideas into reality. We design and develop web, cloud, mobile and enterprise software products with an unrelenting commitment to quality.