2

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 however 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.

Jon
  • 38,814
  • 81
  • 233
  • 382

2 Answers2

5

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

Community
  • 1
  • 1
Jay
  • 56,361
  • 10
  • 99
  • 123
  • 1
    I've found this. Do you think it will work? http://www.google.com/codesearch/p?hl=en#5DDdVc0kQuk/trunk/uNhAddIns/uNhAddIns/UserTypes/TrimString.cs&q=trimstring%20package:http://unhaddins%5C.googlecode%5C.com – Jon Jan 11 '11 at 09:30
  • How is your suggestion of Equals different than that in the link? – Jon Jan 11 '11 at 15:10
  • @Jon Sorry, my whitespace was being lost by the markdown parsing. I just mean you want "value" to be considered equal even if it is padded to the column length. – Jay Jan 11 '11 at 16:01
  • Within the database, the dbms will enforce the fact that 'value ' and ' value ' are not equal. Whether it's safe for application code to assume that those two are equal anyway is application-specific. (Meaning it will be hard or impossible for anyone here to give you very much help answering that question.) I'd be really cautious here if I were you--talk to your DBA. – Mike Sherrill 'Cat Recall' Jan 11 '11 at 17:23
  • The link I posted and have tested on trims when it returns from the database and when it sets it to the database. Doing a search where person name = "John" and not "John " does seem to work ie/it returns the data – Jon Jan 11 '11 at 18:25
3

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185