SQL.fsx

#r "System.Data.Linq"
#r "FSharp.Data.TypeProviders"

open Microsoft.FSharp.Linq
open Microsoft.FSharp.Data.TypeProviders

type Company = SqlDataConnection<ConnectionString = @"Server=(localdb)\Projects;Database=Company;Integrated Security=SSPI">

let db = Company.GetDataContext()

let submit (db : Company.ServiceTypes.SimpleDataContextTypes.Company) = db.DataContext.SubmitChanges()

open System.Data
open System.Data.SqlClient

let conn str = new SqlConnection(str)
let local = sprintf @"Server=(localdb)\Projects;Database=%s;Integrated Security=SSPI" >> conn

let execNonQuery cmd (conn: SqlConnection) =
    use conn = conn
    conn.Open()
    use comm = new SqlCommand(cmd, conn, CommandTimeout = 10)
    comm.ExecuteNonQuery () |> ignore

module DB =
    let create = sprintf "create database %s" >> execNonQuery
    let drop = sprintf "drop database %s" >> execNonQuery

module SqlTypes =
    type ColumnType =
        | Int
        | Str of int
        | Date

    let rep = function
        | Int -> "int"
        | Str len -> sprintf "varchar(%d)" len
        | Date -> "datetime"

module Column =
    open SqlTypes

    type Column =
        | Col of string * ColumnType
        | Nul of string * ColumnType
        | PKey of string * ColumnType

    let rec rep col =
        let fmt n t = sprintf "%s %s" n (SqlTypes.rep t)
        match col with
        | Col (n, t) -> sprintf "%s not null" (fmt n t)
        | Nul (n, t) -> fmt n t
        | PKey (n, t) -> sprintf "%s, primary key (%s)" (rep (Col(n, t))) n

    let int name = Col (name, Int)
    let str name length = Col (name, Str length)
    let date name = Col (name, Date)
    let pk name ty = PKey (name, ty)

module Table =
    let create name =
        Seq.map Column.rep
        >> String.concat ", "
        >> sprintf "create table %s (%s)" name
        >> execNonQuery

    let drop = sprintf "drop table %s" >> execNonQuery

module T = Table
module C = Column

local "Company"
|> T.create "Employees" [
        C.pk "EmpId" SqlTypes.Int 
        C.str "FirstName" 50
        C.str "LastName" 50
        C.date "Birthday"
    ]

query {
    for c in db.Employees do
    select c
} |> Seq.toArray

db.Employees.InsertAllOnSubmit <| [
    for x in 1 .. 10 ->
        Company.ServiceTypes.Employees (
                EmpId = x,
                FirstName = "David",
                LastName = "Grenier",
                Birthday = System.DateTime.Now
            )
]