Populate list with Types

Question

Im trying to populate list with my own type.

let getUsers =
    use connection = openConnection()
    let getString = "select * from Accounts"
    use sqlCommand = new SqlCommand(getString, connection)
    try
        let usersList = [||]
        use reader = sqlCommand.ExecuteReader()
        while reader.Read() do
            let floresID = reader.GetString 0
            let exName = reader.GetString 1
            let exPass = reader.GetString 2
            let user = [floresID=floresID; exName=exName; exPass=exPass]
            // what here?
            ()
    with
        | :? SqlException as e -> printfn "Došlo k chybě úrovni připojení:\n %s" e.Message
        | _ -> printfn "Neznámá výjimka."

In C# I would just add new object into userList. How can I add new user into list? Or is it better approach to get some sort of list with data from database?

Answer 1

Easiest way to do this is with a type provider, so you can abstract away the database. You can use SqlDataConnection for SQLServer, SqlProvider for everything (incl. SQLServer), and also SQLClient for SQLServer.

Here is an example with postgres's dvdrental (sample) database for SQLProvider:

#r @"..\packages\SQLProvider.1.0.33\lib\FSharp.Data.SqlProvider.dll"
#r @"..\packages\Npgsql.3.1.8\lib\net451\Npgsql.dll"

open System
open FSharp.Data.Sql
open Npgsql
open NpgsqlTypes
open System.Linq
open System.Xml
open System.IO
open System.Data

let [<Literal>] dbVendor = Common.DatabaseProviderTypes.POSTGRESQL
let [<Literal>] connString1  = @"Server=localhost;Database=dvdrental;User Id=postgres;Password=root"
let [<Literal>] resPath = @"C:\Users\userName\Documents\Visual Studio 2015\Projects\Postgre2\packages\Npgsql.3.1.8\lib\net451"
let [<Literal>] indivAmount = 1000
let [<Literal>] useOptTypes  = true

//create the type for the database, based on the connection string, etc. parameters
type sql =  SqlDataProvider<dbVendor,connString1,"",resPath,indivAmount,useOptTypes>
//set up the datacontext, ideally you would use `use` here :-)
let ctx = sql.GetDataContext()
let actorTbl = ctx.Public.Actor //alias the table

//set up the type, in this case  Records:
type ActorName = {
    firstName:string
    lastName:string}

//extract the data with a query expression, this gives you type safety and intellisense over SQL (but also see the SqlClient type provider above):
let qry = query {
            for row in actorTbl do
            select ({firstName=row.FirstName;lastName=row.LastName})
                } 
//seq is lazy so do all kinds of transformations if necessary then manifest it into a list or array:
qry  |> Seq.toArray

The two important parts are defining the Actor record, and then in the query extracting the fields into a sequence of Actor records. You can then manifest into a list or array if necessary.

But you can also stick to your original solution. In that case just wrap the .Read() into a seq:

First define the type:

type User = {
    floresID: string
    exName: string 
    exPass: string
}

Then extract the data:

let recs = cmd.ExecuteReader() // execute the SQL Command
//extract the users into a sequence of records:
let users = 
    seq {
         while recs.Read() do
             yield {floresID=recs.[0].ToString()
                    exName=recs.[1].ToString()
                    exPass=recs.[2].ToString()
                   }
        } |> Seq.toArray

Answer 2

Taking your code, you can use list expression:

let getUsers =
    use connection = openConnection()
    let getString = "select * from Accounts"
    use sqlCommand = new SqlCommand(getString, connection)
    try
        [
            use reader = sqlCommand.ExecuteReader()
            while reader.Read() do
                let floresID = reader.GetString 0
                let exName = reader.GetString 1
                let exPass = reader.GetString 2
                let user = [floresID=floresID; exName=exName; exPass=exPass]
                yield user
        ]
    with
        | :? SqlException as e -> failwithf "Došlo k chybě úrovni připojení:\n %s" e.Message
        | _ -> failwithf "Neznámá výjimka."

That being said, I'd use FSharp.Data.SqlClient library so all of that boiler plate becomes a single line with added benefit of type safety (if you change the query, the code will have compile time error which are obvious to fix).