Controlling output

//Connection and query definition are shared for most of the examples below

[<Literal>]
let productsSql = " 
    SELECT TOP (@top) Name AS ProductName, SellStartDate, Size
    FROM Production.Product 
    WHERE SellStartDate > @SellStartDate
"

type QueryProductAsRecords = SqlCommandProvider<productsSql, connectionString>
let queryProductAsRecords = new QueryProductAsRecords(connectionString)

let records = 
    queryProductAsRecords.AsyncExecute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01")
    |> Async.RunSynchronously 
    |> List.ofSeq

records |> Seq.iter (printfn "%A")

type QueryProductSync = SqlCommandProvider<productsSql, connectionString, ResultType = ResultType.Tuples>

do
    use cmd = new QueryProductSync(connectionString)
    let tuples = cmd.Execute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01") 

    for productName, sellStartDate, size in tuples do
        printfn "Product name: %s. Sells start date %A, size: %A" productName sellStartDate size

do 
    use cmd = new SqlCommandProvider<productsSql, connectionString, ResultType.DataTable>(connectionString)
    let table = cmd.Execute(top = 7L, SellStartDate = System.DateTime.Parse "2002-06-01") 
    for row in table.Rows do
        printfn "Product name: %s. Sells start date %O, size: %A" row.ProductName row.SellStartDate row.Size

type QueryPersonInfoSingletoneAsRecords = 
    SqlCommandProvider<"SELECT * FROM dbo.ufnGetContactInformation(@PersonId)"
                        , connectionString
                        , SingleRow = true>

let singleton = new QueryPersonInfoSingletoneAsRecords(connectionString)

let person = singleton.AsyncExecute(PersonId = 2) |> Async.RunSynchronously |> Option.get
match person.FirstName, person.LastName with
| Some first, Some last -> printfn "Person id: %i, name: %s %s" person.PersonID first last 
| _ -> printfn "What's your name %i?" person.PersonID

[<Literal>]
let queryPersonInfoSingletoneQuery = 
    "SELECT PersonID, FirstName, LastName FROM dbo.ufnGetContactInformation(@PersonId)"

type QueryPersonInfoSingletoneTuples = 
    SqlCommandProvider<queryPersonInfoSingletoneQuery, connectionString, ResultType.Tuples, SingleRow=true>

QueryPersonInfoSingletoneTuples
    .Create(connectionString)
    .Execute(PersonId = 2).Value
    |> (function
        | id, Some first, Some last -> printfn "Person id: %i, name: %s %s" person.PersonID first last 
        | id, _, _ -> printfn "What's your name %i?" person.PersonID
    ) 

type QueryPersonInfoSingletoneDataTable = 
    SqlCommandProvider<
        "SELECT * FROM dbo.ufnGetContactInformation(@PersonId)", 
        connectionString, 
        ResultType = ResultType.DataTable>

do 
    use cmd = new QueryPersonInfoSingletoneDataTable(connectionString)
    let table = cmd .AsyncExecute(PersonId = 2) |> Async.RunSynchronously 
    for row in table.Rows do
        printfn "Person info:Id - %i,FirstName - %O,LastName - %O" row.PersonID row.FirstName row.LastName 

// you can refer to the table type
let table2 : QueryPersonInfoSingletoneDataTable.Table = 
    let cmd = new QueryPersonInfoSingletoneDataTable(connectionString)
    cmd.Execute(PersonId = 2)

// you can refer to the row type
for row : QueryPersonInfoSingletoneDataTable.Table.Row in table2.Rows do
    printfn "Person info:Id - %i,FirstName - %O,LastName - %O" row.PersonID row.FirstName row.LastName 

type AdventureWorks2012 = SqlProgrammabilityProvider<connectionString>
do
    use cmd = new AdventureWorks2012.dbo.ufnGetContactInformation(connectionString)
    cmd.ExecuteSingle(1) //opt-in for explicit call to 
    |> Option.iter(fun x ->  
        printfn "Person info:Id - %i,FirstName - %O,LastName - %O" x.PersonID x.FirstName x.LastName 
    )

type QueryPersonInfoSingleValue = 
    SqlCommandProvider<
        "SELECT FirstName + ' '  + LastName FROM dbo.ufnGetContactInformation(@PersonId)", 
        connectionString, 
        SingleRow=true>

do 
    let personId = 2
    use cmd = new QueryPersonInfoSingleValue(connectionString)
    cmd.Execute( personId)
    |> Option.iter (fun name -> printf "Person with id %i has name %s" personId name.Value)

type GetServerTime = 
    SqlCommandProvider<
        "IF @IsUtc = CAST(1 AS BIT) SELECT GETUTCDATE() ELSE SELECT GETDATE()", 
        connectionString, 
        SingleRow=true>

let getSrvTime = new GetServerTime(connectionString)

getSrvTime.AsyncExecute(IsUtc = true) |> Async.RunSynchronously |> printfn "%A"
getSrvTime.Execute(IsUtc = false) |> printfn "%A"

[<Literal>]
let invokeSp = "
    EXEC HumanResources.uspUpdateEmployeePersonalInfo 
        @BusinessEntityID, 
        @NationalIDNumber,
        @BirthDate, 
        @MaritalStatus, 
        @Gender
"
type UpdateEmplInfoCommand = SqlCommandProvider<invokeSp, connectionString>
let nonQuery = new UpdateEmplInfoCommand(connectionString)
let rowsAffected = 
    nonQuery.Execute(
        BusinessEntityID = 2, NationalIDNumber = "245797967", 
        BirthDate = System.DateTime(1965, 09, 01), MaritalStatus = "S", Gender = "F") 

open System
open System.Data
open Microsoft.SqlServer.Types

do 
    use cmd = new AdventureWorks2012.HumanResources.uspUpdateEmployeeLogin(connectionString)
    let hierarchyId = SqlHierarchyId.Parse(SqlTypes.SqlString("/1/4/2/"))
    cmd.Execute(
        BusinessEntityID = 291, 
        CurrentFlag = true, 
        HireDate = DateTime(2013,1,1), 
        JobTitle = "gatekeeper", 
        LoginID = "adventure-works\gat0", 
        OrganizationNode = hierarchyId
    )
    |> printfn "Records afftected: %i"

type Get42 = SqlCommandProvider<"SELECT * FROM (VALUES (42), (43)) AS T(N)", connectionString>
let xs = (new Get42(connectionString)).Execute() |> Seq.cache 
printfn "#1: %i " <| Seq.nth 0 xs 
printfn "#2: %i " <| Seq.nth 1 xs //see it fails here if result is not piped into Seq.cache 

module SqlDataReader =  
    open System.Data.SqlClient
    let toMaps (reader: SqlDataReader) = 
        seq {
            use __ = reader
            while reader.Read() do
                yield [
                    for i = 0 to reader.FieldCount - 1 do
                        if not( reader.IsDBNull(i)) 
                        then yield reader.GetName(i), reader.GetValue(i)
                ] |> Map.ofList 
        }