Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

MSSQL & NHibernate – mapping week numbers to properties

DZone's Guide to

MSSQL & NHibernate – mapping week numbers to properties

· Database Zone ·
Free Resource

Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.

In one of my applications I have to use week numbers as properties of business classes that are persisted to database using NHibernate. Week numbers are used in UI and users can filter and sort data by week numbers. In this posting I will show you how to make SQL Server deal with week numbers and how to use them in your domain model.

SQL Server computed fields

SQL Server has to good things that makes our task easy:

  1. DATEPART function – use it to get different parts of given date
  2. support for computed columns (deterministic ones can also be persisted to database)

All we have to do is to create computed columns for week numbers to tables where we need week numbers. Although we can create view for that and map data through this view to domain objects I still prefer to use tables.

mssql-week-formulaScreenshot on right shows how my computed column looks like in Management Studio.

NB! When you add new column to table, Management Studio sets its type. You can see that type column is empty on screenshot. Don’t try to clean this column manually – this doesn’t work. Just insert formula like shown on screenshot and Management Studio clears type column automatically.

 

Protecting week number from changes

As a next thing we have to bring week number to NHibernate mappings and business classes. On mapping level everything is simple: we just define new property that is not written back to database when object is saved. This is just protection mechanism.

In business class I define week number property as follows:

public virtual int Week { get; protected set; }

Setter is protected so Week property is read-only for consumer classes and accessible for dynamic proxies created by NHibernate.

Databases should be easy to deploy, easy to use, and easy to scale. If you agree, you should check out CockroachDB, a scalable SQL database built for businesses of every size. Check it out here. 

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}