Now that we can read from our database, the next step is to save our price data using FluentNHibernate. The first thing, we need to do is to add a simple table to store our stock price data:
CREATE TABLE [STOCK_PRICE] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[COMPANY_ID] [int] NOT NULL ,
[CLOSING_PRICE] [float] NOT NULL ,
[VOLUME] [float] NOT NULL ,
[THE_DATE] [datetime] NOT NULL ,
CONSTRAINT [PK_STOCK_PRICES] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_STOCK_PRICES_COMPANY] FOREIGN KEY
(
[COMPANY_ID]
) REFERENCES [COMPANY] (
[Id]
)
) ON [PRIMARY]
The only interesting thing of note here is the foreign key to the company table. Next we need to add a STOCK_PRICE object. Because we want to use FluentNHibernate, the class objects have to mirror our database:
-------------------------------------
VB
-------------------------------------
Option Explicit On
Namespace StockPrices
Public Class STOCK_PRICE
Private _ID As Integer
Private _COMPANY_ID As Integer
Private _VOLUME As Double
Private _CLOSING_PRICE As Double
Private _THE_DATE As Date
Public Overridable Property Id() As Integer
Get
Id = _ID
End Get
Set(ByVal value As Integer)
_ID = value
End Set
End Property
Public Overridable Property COMPANY_ID() As Integer
Get
COMPANY_ID = _COMPANY_ID
End Get
Set(ByVal value As Integer)
_COMPANY_ID = value
End Set
End Property
Public Overridable Property VOLUME() As Double
Get
VOLUME = _VOLUME
End Get
Set(ByVal value As Double)
_VOLUME = value
End Set
End Property
Public Overridable Property CLOSING_PRICE() As Double
Get
CLOSING_PRICE = _CLOSING_PRICE
End Get
Set(ByVal value As Double)
_CLOSING_PRICE = value
End Set
End Property
Public Overridable Property THE_DATE() As Date
Get
THE_DATE = _THE_DATE
End Get
Set(ByVal value As Date)
_THE_DATE = value
End Set
End Property
Public Sub New(ByVal CompanyId As Integer, ByVal aDate As String, ByVal aPrice As Double, ByVal aVolume As Double)
COMPANY_ID = CompanyId
THE_DATE = CDate(aDate)
CLOSING_PRICE = aPrice
VOLUME = aVolume
End Sub
Public Sub New()
End Sub
End Class
Public Class COMPANY
Private _ID As Integer
Private _CompanyName As String
Private _CompanyTicker As String
Private _StockPrices As IList(Of STOCK_PRICE)
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
Public Overridable Property Id() As Integer
Get
Id = _ID
End Get
Set(ByVal value As Integer)
_ID = value
End Set
End Property
Public Overridable Property COMPANY_NAME() As String
Get
COMPANY_NAME = _CompanyName
End Get
Set(ByVal value As String)
_CompanyName = value
End Set
End Property
Public Overridable Property COMPANY_TICKER() As String
Get
COMPANY_TICKER = _CompanyTicker
End Get
Set(ByVal value As String)
_CompanyTicker = value
End Set
End Property
Sub New()
_StockPrices = New List(Of STOCK_PRICE)
End Sub
End Class
End Namespace
-------------------------------------
(1) The properties must be tagged as Overridable in order to make them equivalent to virtual properties in C#
(2) The STOCK_PRICES IList in the COMPANY_CLASS must be of type IList -- List will not work. It took some work to sort that out.
(3) The STOCK_PRICES IList:
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
will hold all our stock price data for a given company (ticker). In setting these objects up to be isomorphic with our database, we can take advantage of FluentNHIbernate's automappings. This allows us load all a company's stock prices at the same time we load the company. This saves us the hassle of loading up a company, getting its Id, and the using that Id to query the database for the company's stock prices. FluentNHIbernate will do it all for you. I will show this below.
-----------------------------------------------------------
Here are the classes in C# for those who can't stomach VB
-----------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace StockPrices
{
public class STOCK_PRICE {
public virtual int Id { get; set; }
public virtual int COMPANY_ID { get; set; }
public virtual double VOLUME { get; set; }
public virtual double CLOSING_PRICE { get; set; }
public virtual DateTime THE_DATE { get; set; }
public virtual void New(int CompanyId, string aDate, double aPrice, double aVolume)
{
THE_DATE = Convert.ToDateTime(aDate);
CLOSING_PRICE = aPrice;
VOLUME = aVolume;
}
public virtual void New() {
}
}
public class COMPANY {
public virtual int Id { get; set; }
public virtual string COMPANY_NAME { get; set; }
public virtual string COMPANY_TICKER { get; set; }
public virtual IList<STOCK_PRICE> STOCK_PRICES { get; set; }
public virtual void New()
{
STOCK_PRICES = new List<STOCK_PRICE>();
}
}
}
-----------------------------------------------------------
As an aside, about the only difference between using VB and C# I have run into has to do with the following statement:
let autoMappings = (AutoPersistenceModel.MapEntitiesFromAssemblyOf<StockPrices.COMPANY>()).Where(fun t -> if t.Namespace = "StockPrices.StockPrices")
This statement is used setup your auto mappings in FluentNHibernate.
In VB, you need both the Assembly Name and the Namespace.
In C#, you just need the Namespace.
Why the difference. That's way beyond me. I suppose I will figure out how to write my class objects in F# some time.
Any how, let's now write the simple program to save our stock price data...
Here it is:
----------------------------------------------------------
#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=XXXXXX"
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 companyList = aSession.CreateCriteria(typeof<StockPrices.COMPANY>).List()
for someObj in companyList do
let xCo = someObj :?> StockPrices.COMPANY
printfn "Company Name: %s, Ticker: %s" xCo.COMPANY_NAME xCo.COMPANY_TICKER
let prices = GetPrices.getStockPrices xCo.COMPANY_TICKER 2009 1 1 2009 5 31
for aObs : GetPrices.Observation in prices do
let aPrice (aObs : GetPrices.Observation) =
match aObs.Event with
| GetPrices.Event.StkPrice(p) -> GetPrices.toFloat(p.Close)
| GetPrices.Event.Dividend(d) -> GetPrices.toFloat(d)
| _ -> -999.0
printfn "Here's %s: %s %10.5f" xCo.COMPANY_NAME (aObs.Date.ToShortDateString()) (aPrice(aObs))
let xPrice = new StockPrices.STOCK_PRICE(xCo.Id, aObs.Date.ToShortDateString(), aPrice(aObs), 0.0)
let id = aSession.Save(xPrice)
()
aSession.Transaction.Commit()
aSession.Close()
------------------------------------------------
I pretty much covered everything here in my last post, with the exception of the two lines in aquamarine.
The first line instantiates a new price object and passes it its values when it is created. Note above that I have overloaded the New() function.
The second statement:
let id = aSession.Save(xPrice)
does all the heavy lifting. It tells NHibernate to save (insert) my xPrice object. And what is really cool, is that it returns the Id of the new row (I am using Identity columns for my Id's). I don't use it here, but if I had other related records, it could come in very handy.
I do have to commit my data to actually save it. The relevant line is:
aSession.Transaction.Commit()
Piece of cake.
The next chore is to read what we've written.