Have you considered using stored procedures. I have some columns that are encrypted as well. I use the <loader query-ref="namedQuery" /> for loading single aggregates. As far as child lists of aggregates you can use a similar concept. Take a look at this link. It will provide you with the information on how to call stored procedures with named queries, loaders, and bags/lists of aggregates.
http://ayende.com/Blog/archive/2006/09/18/UsingNHibernateWithStoredProcedures.aspx I would take a look at the named queries and possibly loaders. ________________________________ From: birchoff <[email protected]> To: nhusers <[email protected]> Sent: Wed, November 11, 2009 5:09:51 PM Subject: [nhusers] SQL Server 2005 Column Level encryption I have been playing aroudn with column level encryption and nhibernate and I have found a simple yet incomplete way to leverage SQL Servers column level encryption. IList<Person> people = new List<Person>(); using(var transaction = session.BeginTransaction()) { try { OpenEncryptionKey(session); people = session .CreateCriteria<Person>() .List<Person>(); CloseEncryptionKey(session); transaction.Commit(); } catch (Exception e) { transaction.Rollback(); } } private static void OpenEncryptionKey(ISession session) { var command = session.Connection.CreateCommand(); session.Transaction.Enlist(command); command.CommandType = CommandType.StoredProcedure; command.CommandText = "OpenKey"; command.ExecuteNonQuery(); } private static void CloseEncryptionKey(ISession session) { var command = session.Connection.CreateCommand(); session.Transaction.Enlist(command); command.CommandType = CommandType.StoredProcedure; command.CommandText = "CloseKey"; command.ExecuteNonQuery(); } The code snippet above wraps the call to the DB in a transaction and further wraps queries to the database in calls to open and close the encryption key. The Person entity is mapped to a view that decrypts the encrypted fields. Unfortunately you cannot insert into computed columns on a view so you will need to do all saves/updates/ deletes via stored procedures. Now while this code works in the simplest case. Where it will fail is if you needed to lazy load an entity that has encrypted fields, since the encryption key will be closed at the end of the transaction. In order to make this work I would need the ability to open and close the key immediately before and after commands are sent to the database. I have looked at the event listeners and none of them seem to give me a way of doing this. The PreLoadEventListener looks promising but, since I have no way of delimiting the begining and end of a batch of statements being sent to the database I would essentially be opening and closing the key for every entity loaded from the database. While this could work it is horribly inefficient. What I would love to know is where can I hook into nhibernate just before it sends a list of commands over a connection and after it has received all its results from the connection. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
