Using an sql-function when reading and writing with NHibernate

Question

Hi, I have the following problem.

I have a special column in my tables (a blob). In order to read and write to that column, I need to call an SQL function on its value - to convert to a string when I read, and to convert from a string to this blob when I write.

The read part is easy - I can use a formula to run the sql function against the column. But formulas are read only. Using IUserType also did not seem to assist - I can get the blob and write my own code to convert it to my own type, but I don't want to do that - I already have a database function that does this work for me.

Any ideas?

Answer 1

You can specify sql to insert and update, see the reference documentation, "Custom SQL for create, update and delete". Here is an example from Ayende which uses stored procedures (which is not the same, just to see how it works).

Or you could write a database trigger which does this transformation.