The Mad Economist

In my last post, we wrote a bunch of stock prices for four companies to our database.

We now want to read them back.  Also we want to figure out how to use Fluent NHibernate's auto loading of child collections.  That is, how to load up a company's prices and the same time we load a company.

Here is the base program I have come up with:

----------------------------------------------

#light
open System
open System.Collections.Generic
open System.IO
open StockPrices

open FluentNHibernate.AutoMap
open FluentNHibernate

let properties = new Dictionary<string, string>()

let connString = "server='BIG_ROCK\LOGGERSEDGE';Initial Catalog=SMDATA;User ID=sa;Password=XXXXX"

properties.Add("connection.provider", "NHibernate.Connection.DriverConnectionProvider")
properties.Add("dialect", "NHibernate.Dialect.MsSql2000Dialect")
properties.Add("connection.driver_class", "NHibernate.Driver.SqlClientDriver")
properties.Add("show_sql", "true")
properties.Add("connection.connection_string", connString)

let autoMappings = (AutoPersistenceModel.MapEntitiesFromAssemblyOf<StockPrices.COMPANY>()).Where(fun t -> if t.Namespace = "StockPrices.StockPrices" then true else false)

let aConfig = (new NHibernate.Cfg.Configuration()).AddProperties(properties).AddAutoMappings(autoMappings)

let sessionFactory = aConfig.BuildSessionFactory()

let aSession = sessionFactory.OpenSession()

aSession.BeginTransaction()

let coID = 100

let xCo = aSession.Load(typeof<StockPrices.COMPANY>, coID) :?> StockPrices.COMPANY

//
//  Everything is Good
//
printfn "Company Name: %s,  Ticker: %s" xCo.COMPANY_NAME xCo.COMPANY_TICKER

let prices = xCo.STOCK_PRICES

let n = prices.Count
printfn "Price count: %i, " n

for xp in prices do
    printfn "Company Name: %s,  Price: %10.2f" xCo.COMPANY_NAME xp.CLOSING_PRICE
    ()   

let userresp = Console.ReadLine()

----------------------------------------------

This program loads the company AT&T - it has an Id of 100 in my COMPANY table.  The totally cool thing about Fluent NHibernate is that the statement:

let xCo = aSession.Load(typeof<StockPrices.COMPANY>, coID) :?> StockPrices.COMPANY

loads the company (AT&T) into the xCo company object.

The stock prices are loaded into the AT&T's STOCK_PRICES List object when that object is referenced a couple of lines later:

let prices = xCo.STOCK_PRICES

We can then access the prices directly and do whatever we want them.  In my dorky little program, I just write them to the console.

My biggest hurdle in getting this to work was to make sure that my STOCK_PRICES list object was defined as:

Public Overridable Property STOCK_PRICES() As IList(Of STOCK_PRICE)

    Get
       STOCK_PRICES = _StockPrices
    End Get

    Set(ByVal value As IList(Of STOCK_PRICE))
       _StockPrices = value
    End Set

End Property

It needs to defined as an IList object.  It needs both Get & Set methods.

Kudos to James Gregory for helping me figure this out.

Now let's look at the console output.  The most interesting part of the output are the NHibernate SQL statements.  These are generated when you have the show_sql property turned on in your configuration (properties.Add("show_sql", "true").  The first sql statement shows the SELECT statement that is generated behind the scenes to load the company with Id 100 (AT&T).

The second sql statements show the SELECT statement for grabbing the stock prices.  You can see that it knows the Company Id to use in the where class Id is 100.

-----------------------------------------------------------------------

NHibernate: SELECT company0_.Id as Id1_0_, company0_.COMPANY_NAME as COMPANY2_1_0_, company0_.COMPANY_TICKER as COMPANY3_1_0_ FROM [COMPANY] company0_ WHERE company0_.Id=@p0; @p0 = '100'

Company Name: AT&T,  Ticker: T

NHibernate: SELECT stock_pric0_.COMPANY_id as COMPANY2_1_, stock_pric0_.Id as Id1_, stock_pric0_.Id as Id0_0_, stock_pric0_.COMPANY_ID as COMPANY2_0_0_, stock_pric0_.VOLUME as VOLUME0_0_, stock_pric0_.CLOSING_PRICE as CLOSING4_0_0_, stock_pric0_.THE_DATE as THE5_0_0_ FROM [STOCK_PRICE] stock_pric0_ WHERE stock_pric0_.COMPANY_id=@p0; @p0 = '100'

Price count: 102,
 
Company Name: AT&T,  Price:      29.42
Company Name: AT&T,  Price:      28.43
Company Name: AT&T,  Price:      28.30
Company Name: AT&T,  Price:      27.21
Company Name: AT&T,  Price:      27.18
Company Name: AT&T,  Price:      26.68
and so on.....

-----------------------------------------------------------------------

OK, so far.  Let's now see if we can load up the stocks for all four of the companies in the database.

I have replaced all the code after the 'aSession.BeginTransaction()' statement with:

 -----------------------------------------------------------------------

let companyList = aSession.CreateCriteria(typeof<StockPrices.COMPANY>).List()

// Let's Try to read some data
for someObj in companyList do

    let xCo = someObj :?> StockPrices.COMPANY
    printfn "Company Name: %s,  Ticker: %s" xCo.COMPANY_NAME xCo.COMPANY_TICKER
   
    //Loads the prices for the current company
    let prices = xCo.STOCK_PRICES
   
    for xp in prices do
        printfn "Company Name: %s,  Price: %10.2f" xCo.COMPANY_NAME xp.CLOSING_PRICE
        ()   
    let userresp = Console.ReadLine()
    ()