Hi,

I am using 1.2.0 Beta 2 with SQL Server 2005.

Each of objects store following fields:

DateCreated (Timestamp, when the object was created)
DateUpdated (Timestamp, when the object was last updated)
DateDeleted (Timestamp, when the object was marked as being deleted)

Are there any best practices or guidelines about implementing these types of fieds? Surely, someone here has already tried this before.

I came up with following for DateCreated. I have set the database column in SQL Server to default value of (GetDate())

<property name="DateCreated" type="Timestamp" insert="false" update="false"/>

This way, the SQL Server database INSERT the correct Timestamp for DateCreated field. Is there any way to achieve the same effect for 'DateUpdated' field?

Many thanks in advance!

Yes, for the DateUpdated field, use the <timestamp...> tag (or the <version...> tag which does the same thing). However, you have to set your database column to Date type, not timestamp type.

NH will automatically update the field and you have the side benefit of optimistic locking on updates and deletes.

As for the DateDeleted field, I don't know. Maybe you can use an interceptor so you don't have to manage this field in the client code.

An alternative is using the Interceptor interfaceprovided by NHibernate.
See http://www.hibernate.org/hib_docs/nhibernate/html/manipulatingdata.html#manipulatingdata-interceptors for details.

Regards
Klaus

Klaus,

Thanks for the link. It shows how to do it quite easily in Interceptor API. I would prefer to keep the Timestamp code in database tier (i.e. implement it in DB side)... that way I don't have to keep our Database and Web server times in sync-with each other. The times should be the same, but just in case...

Greenis,

Wow, this 'timestamp' property is pretty cool! Thanks for pointing me to it. I am curious to see if it uses SqlServer's GetDate() method, or .NET's DateTime. I.e. does it run on middle tier or db tier.... let me find this out...

Thanks again!

I'm pretty sure the timestamp tag is hard-coded to use the .NET DateTime.Now() method in NH. However, you can always submit a feature request.

We use a trigger in the DB to handle this. The advantage here is that the value is coordinated correctly with the create time since both are generated by the db server (which might have a different date/time than the server where the NH application is run).

Something I've always wanted is a way to indicate that the value of a property is set to the value result of calling a DB function like SQL Server's GETDATE() function so you could do this without a trigger. Might be possible, but I haven't figured out how...

Cheers,

Symon.

One thing to watch out for here is that A SQL Timestamp and DateTime are two different beasts. A Timestamp does not translate to a DateTime. A Timestamp is simply a mechanism for indicating the order that events happened inside the database. If you need a Timestamp for some third party utility, you won't be able to switch to DateTime. Just a warning.

We take care of this in the business layer ourselves. We initialize values in our constructors and modify values in our property setters where appropriate. Though I understand why this is not always the ideal approach.

Greenis:

Thanks again. Yeah I found out too that <timestamp> uses .NET DateTime.


Symon:

I agree with you. Having an equivalent of calling SQL Server's built in function would be very helpful in this case. Short of that, the only way to do this in the database is to use Triggers.

Onevenxtchuck:

Modifying values in property constructors is definitely a valid approach. Personally, I like the Interceptor way over setting DateUpdated etc in each property setter. It seems less prone to programming errors.

Thanks!

desiguy,

All a matter of taste.

We code gen our business layer so we don't miss any setters. Most of the devs here myself included are a bit paranoid, probably overly, of putting any logic in the DB. To some, the interceptors cut a little too close to that vein (not to mix metaphores) so we dropped the modify time management in the business layer. Besides, it's one less thing to worry about changing if Management flips out over using *gasp* an open source project in their new web site. =)

Take care

Chuck