F# FsSql Repository example

type Weather() =
member val Id = 0 with get,set

member val LocationId = Guid.Empty with get,set
member val DataFrom = DateTimeOffset.MinValue with get,set
member val DataTo = DateTimeOffset.MinValue with get,set
member val Temperature = 0.0m with get,set
member val Humidity = 0.0m with get,set
member val Rain = 0.0m with get,set
member val WindSpeed = 0.0m with get,set
member val WindDirection = 0.0m with get,set
member val Clouds = 0.0m with get,set
member val Pressure = 0.0m with get,set

let private P = Sql.Parameter.make

type selectQueryObject<'T> =
{
query : string;
parameters : Sql.Parameter list;
deserialisation : (IDataRecord -> 'T) option;
}

type changeQueryObject =
{
query : string;
parameters : Sql.Parameter list;
}

type queryPart =
{
where : string;
parameter : Sql.Parameter option
}

let private emptyQueryPart deserial = {query = ""; parameters = []; deserialisation = deserial}

&nbsp;

let private w<'a when 'a : (new : unit -> 'a) and 'a : struct and 'a :> System.ValueType> column parametername operator (value : System.Nullable<'a>) =
match value.HasValue with
| true -> { where = sprintf "%s %s %s" column operator parametername; parameter = Some <| P(parametername, value.Value)}
| false -> {where = ""; parameter = Option<Sql.Parameter>.None}

let private combineAnd<'T> (x : queryPart) (qo : selectQueryObject<'T>) =
match (x.parameter, qo.query) with
| (Some xpart, "") -> { query = x.where; parameters = [x.parameter.Value]; deserialisation = qo.deserialisation}
| (Some xpart, _) -> {query = qo.query + " AND " + x.where; parameters = x.parameter.Value :: qo.parameters; deserialisation = qo.deserialisation}
| (None, _) -> { query = qo.query; parameters = qo.parameters; deserialisation = qo.deserialisation}

let private combineQueryParts<'T> (query :string) (qo :selectQueryObject<'T>) =
let mutable q = query
if (qo.query.Length > 0) then
q <- query + " WHERE " + qo.query

{ query = q; parameters = qo.parameters; deserialisation = qo.deserialisation; }

let executeReader<'T> (queryObj : selectQueryObject<'T> )=
sql.ExecReader queryObj.query queryObj.parameters
|> Seq.ofDataReader
|> Seq.map queryObj.deserialisation.Value

let executeScalar (queryObj : changeQueryObject) =
sql.ExecScalar queryObj.query queryObj.parameters
module weather =
let asWeather (r: IDataRecord) =
new Weather(Id = (r?id).Value, LocationId = (r?LocationId).Value, DataFrom = (r?DataFrom).Value,
DataTo = (r?DataTo).Value, Temperature = (r?Temperature).Value,
Humidity = (r?Humidity).Value, Rain = (r?Rain).Value, WindSpeed = (r?WindSpeed).Value,
WindDirection = (r?WindDirection).Value, Clouds = (r?Clouds).Value, Pressure = (r?Pressure).Value)

let private insert (weather : Weather ) =
{
query = "INSERT INTO \"intersect\".\"Weather\"(
locationid, datafrom, datato, temperature, humidity, rain,
windspeed, winddirection, clouds, pressure)
VALUES (@locationid, @datafrom, @datato, @temperature, @humidity, @rain,
@windspeed, @winddirection, @clouds, @pressure);
RETURNING id;
";
parameters = [
P("@locationid", weather.LocationId);
P("@datafrom", weather.DataFrom);
P("@datato", weather.DataTo);
P("@temperature", weather.Temperature);
P("@humidity", weather.Humidity);
P("@rain", weather.Rain);
P("@windspeed", weather.WindSpeed);
P("@winddirection", weather.WindDirection);
P("@clouds", weather.Clouds);
P("@pressure", weather.Pressure);
]
}

let private update (weather : Weather ) =
{
query = "UPDATE \"intersect\".\"Weather\"
SET locationid=@locationid, datafrom=@datafrom, datato=@datato, temperature=@temperature, humidity=@humidity,
rain=@rain, windspeed=@windspeed, winddirection=@winddirection, clouds=@clouds, pressure=@pressure
WHERE id=@id;
SELECT @id;
";
parameters = [
P("@id", weather.Id);
P("@locationid", weather.LocationId);
P("@datafrom", weather.DataFrom);
P("@datato", weather.DataTo);
P("@temperature", weather.Temperature);
P("@humidity", weather.Humidity);
P("@rain", weather.Rain);
P("@windspeed", weather.WindSpeed);
P("@winddirection", weather.WindDirection);
P("@clouds", weather.Clouds);
P("@pressure", weather.Pressure);
]
}

let save (weather : Weather) =
match weather.Id with
| 0 -> insert weather
| _ -> update weather

let search (fc : WeatherSearchRequest) =
let query = "SELECT id, locationid, datafrom, datato, temperature, humidity, rain, windspeed, winddirection, clouds, pressure FROM \"intersect\".\"Weather\""

let locationIdPara = w "locationid" "@locationid" "=" fc.LocationId
let fomPara = w "datafrom" "@datafrom" ">=" fc.From
let toPara = w "datato" "@datato" "<=" fc.To
let datetimeFromPara = w "datafrom" "@datafrom" ">=" fc.DateTime
let datetimeToPara = w "datato" "@datato" "<=" fc.DateTime

Some asWeather
|> emptyQueryPart
|> combineAnd locationIdPara
|> combineAnd fomPara
|> combineAnd toPara
|> combineAnd datetimeFromPara
|> combineAnd datetimeToPara
|> combineQueryParts query