Over a million developers have joined DZone.

MSSQL & NHibernate – mapping week numbers to properties

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

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.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:

Published at DZone with permission of Gunnar Peipman, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}