How can I trim string property datatypes from database char datatypes in Fluent NHibernate

I have a SQL database and a Oracle database with the same schema.

Therefore I want to use my model classes for the two databases and all I will do is change the database connection string in the Fluent NHibernate configuration.

I have some database char data types for columns but in my model classes I have set them as string ho开发者_开发技巧wever when they are returned from queries they have padded white space.

How do I return them trimmed without causing problems when I query the database using these colums as they will need to match the fixed length specification.

You can create an implementation of NHibernate.UserTypes.IUserType that will trim the whitespace when fetched from the database and re-pad it when going back to the database.

In your fluent mapping, you just add .CustomType<T> where T is your IUserType implementation.

This article is helpful for properly implementing IUserType. Don't get hung up on methods like Assemble, Disassemble, DeepCopy, Replace -- it doesn't look like you'll ever hit those, even. You're most concerned with NullSafeGet, in which you'll trim, and NullSafeSet in which you'll re-pad.

update

Upon further consideration, I'm not sure you'll actually need to re-pad the value when inserting to the database -- the db itself will enforce the fixed length of the column.

In response to the link you provided in your comment, I think that implementation pretty much gets you there, but I do think you might need to modify it a bit. For one thing, you may not want to trim both leading and trailing whitespace, as Trim() will do. For another, in your override of Equals, you'll want

     value

to equal

value

The char data type in SQL databases is padded with whitespace.

If you can modify the database, you can create a view that trims the columns. But a trimmed value won't match the untrimmed value. (In a SQL query, that is.) TRIM() is a standard SQL function, but not all platforms support it. I suppose you might be able to cast it to VARCHAR(); I'm not sure I've ever tried that.

If I were in your shoes, I think I'd cache literally the value the database contains. (That is, I'd store it in a variable of some kind.) I might trim it for display to the user, but any interaction between my application code and the database would be done using the actual values in the database.