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:
- DATEPART function – use it to get different parts of given date
- 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.
Screenshot above 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.