I am trying to get a solution to this problem and kind find a decent answer.

The issue: multiple (most) database objects must have fields for Created and Modified. The date/time assigned cannot be generated by code or within the service processing, the SQL generated by NHibernate needs to populate the appropriate parameter with the appropriate function for the respective database.

As in (pseudo code):

A sample object like Order should have date/time fields for Created and Modified and should NOT be assigned this way:
order.Created = DateTime.Now;
order.Modified = DateTime.Now;

The reason should be obvious. Some transactions are complex and nested and the date/time actually inserted versus assigned in code (as demonstrated above) would differ.

The appropriate method is to have NHibernate assign the value for the parameter as a function (i.e. GetDate() for SQL sever).


I have seen hints and workarounds and nothing I have found seems right (perhaps I just haven;t found the correct resource).

From my view, I should be able to do something like this in NHibernate (pseudo mapping using Fluent Nhibernate for brevity):

class OrderMap : ClassMap<Order>
{
public OrderMap()
{
WithTable("tb_order");
Id(x => x.ID, "pk_order_id").GeneratedBy.Native();
// other mappings ...
Map(x => x.Created, "created").GenerateDate(Behavior.OnInsert);
Map(x => x.Modified, "modified").GenerateDate(Behavior.Always);
}
}

I understand that NHibernate and Fluent NHibernate are different animals, but that sample should make the point.

I cannot see how this particular scenario is not common and a fundamental requirement; especially, since it is should be so easy to implement.

I should be able to tell NHibernate that the date should be genertated by the database and specify the behavior (on insert or new, or always). NHibernate requires each connection to specify a provider (like MS Sql Server 2005) and it should be rather simple to associate the function that the particular database supports for inserting a database generated date like GetDate() for SQL Server.

With a mapping like above, NHibernate should then allow a mapping for a readonly public property with a private backing field and should auto-inject into the SQL the proper function based on the behavior specified.

If this is not currently supported, the please consider this elementary and a high priority. In my opinion, a professional database should only allow date/times for Created and Modified to be generated by the database as part of the actual transaction. I could list numerous problems associated to faking the date/time by pre-populating the values as I demonstrated in my first pseudo-code example.

So, again:

1) perhaps this is supported and I am not aware. If it is, only post if you have sample code or a link to sample code. I have searched and have not found an answer, so mentioning it without some helpful guidance is not useful.

2) If what I suspect is true: NHibernate does not support this and what I found really are the only options (which, in my honest opinion, seemed to be some really some complex hacks that may not even solve this issue, I.e. not use database generated dates)... if this is true, how does one get support for such a change? I suspect that most every developer would really want this feature and would go "thank you", "thank you", if it was supported similar to how I described.

TIA

Trevor

You can use a trigger for that and set the properties to generated="always". Then hibernate will refresh the values in the object after insert/update.

I expect this functionality when NH generates the database. So, does NH create the trigger in the DB or do I achieve this manually? Do all supported DBs support triggers?

I am looking for a "standard" out-of-the-box solution. My inclination is to belive that the most prudent approach would involve NH simlly adjusting the generated SQL per database provider and applying the equivalent 'GetDate()' function as appropriate.

If, however, all the supported databases support triggers. the I am happy with a trigger-based solution... IF... NH generates the SQL to create the triggers. The date/time fields and in virtually every table within the database. Would you really expect every user who desires Created and Modified dates to manually adjust the database for every table?

A "manual" adjustment is not a solution (in my opinion). Fo example, I perform unit tests. These test auto-create the database, populate values, and verify the results, then the temporary database is deleted afterwards. Requiring manual creation of any feature destroys the ability to properly unit test. In addition, these fields are used often in business logic. They are pervasive throughout the framework.

Thus, since this should be an extrememely common requirement and involves virtually every datbase table, nd involves the ability to properly unit test... I feel it should get high priority and serious consideration.

I am worried... this functionality is ultra-basic and I am surprised its not in-the-box.

How can I help? What can I do?

I don't mean to offend, but the lack of response in the forum is worrisome?

You find the reason here:

https://forum.hibernate.org/viewtopic.php?f=25&t=994308

YOu probably address those kinds of questions/requests on the google group.

Thank you!

That explains it all.

I did get a satisfactory answer.

Here is how to achieve database generates dates via a NH mapping:

You can use custom SQL for insert and update (Using XML mapping for brevity):
<class name="YourEntity">
<id type="int">
<generator class="identity"/>
</id>
<property name="Description"/>
<property name="Created" generated="always"/>
<property name="Modified" generated="always"/>
<sql-insert>INSERT INTO YourEntity (Description, Created) VALUES ( ?,
GetDate()); select SCOPE_IDENTITY()</sql-insert>
<sql-update>UPDATE YourEntity SET Description=?, Modified=GetDate() WHERE
ID=?</sql-update>
</class>

Nice!