Using Fluent NHibernate With Legacy Databases

I am currently working on a project that has a requirement that it be able to access  data from a legacy SQL Server database.

One feature of this system is the ability to add and store checking accounts. These checking accounts are used to make payments on customer accounts. When making a payment using a checking account the vendor needs two pieces of information: An ABA (American Bankers Association) routing number and a checking account number. An ABA number is found at the bottom of your checks next to your bank account number and can be validated against a list of valid ABA numbers. This is where I encountered a need to validate against a legacy database.

In our legacy system ABA Routing numbers are stored in a database called viplookups with a table called bnkroute

For this feature I created a domain object called BankInfo

  public class BankInfo : BaseEntity
        public virtual string AbaNumber { get; set; }
        public virtual string Name { get; set; }
        public virtual string City{ get; set; }
        public virtual string State{ get; set; }
        public virtual string PhoneNumber { get; set; }


Now I needed to map this entity to our legacy database. Our mapping for this feature is as follows:

    public class BankInfoMap : ClassMap<BankInfo>
        public BankInfoMap()
            Id(x => x.Id).Column(“bnkrouteid”);
            Map(x => x.AbaNumber).Column(“crouting”);
            Map(x => x.Name).Column(“ccompname”);
            Map(x => x.City).Column(“ccity”);
            Map(x => x.State).Column(“cstate”);
            Map(x => x.PhoneNumber).Column(“cphone1″);

So lets talk about the relevant Fluent Nhibernate features:

  • Schema(“viplookups.dbo”);

The first item of interest is the Schema() method. The schema function tells NHibernate to pull this entity from a specified database. In my case this database exists on the same SQL Server. So I didn’t need to try it on another server. If you have knowledge of this working on another server leave a comment here.

  • Table(“bnkroute”);

The next item of interest is the Table() method. This is pretty straight forward Fluent NHibernate and specifies the legacy table to pull your data from.

  • SchemaAction.None();

The next interesting feature is SchemaAction.None(). When developing our applications I have an integration test that is used to build all our default schema. I DONT want these table to be generated in our schema, they are external.  SchemaAction.None() tells NHibernate not to create this entity in the database.

So that’s it. A simple combination of Fluent NHibernate features to access data from a legacy database properly.






This entry was posted in Fluent NHibernate, Legacy Data, NHibernate, SQL Server. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Yonkz

    I successfully use the .Schema() method to use two different SQL servers, but the catch is that they need to be set up as Linked servers. This creates some other interesting issues surrounding MSDTC, and forces you to have two separate instances of SQL Server running in your development environments, due to the fact that Microsoft no longer supports distributed transactions in a loop back scenario.

  • @Yonkz. Thanks for the heads up on that. I forgot about linked servers. Proably because we tried using linked servers in the past and were never happy with the results. Too much flakieness IMO.

  • cloggins

    Am I missing something? Where’s the legacy stuff. As best I can tell, this is just basic ‘use Fluent NHibernate to connect to a database’ stuff.

  • @cloggins The normal NHibernate workflow does not normally specify the Schema() and Table() attributes on a mapping. These are derived from the model. Generally these attributes are only used when accessing pre-existing databases (aka legacy)