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?

If you can already get the list you need from the database, that would probably be the best way. Other than that, you could use ResizeArray<_>, which is .NET's System.Collections.Generic.List<_>. usersList currently is an array, and arrays can never be added to in .NET. — TeaDrivenDev
What database are you accessing and is there a specific reason to use ADO? You should try accessing it with a type provider. Either way, instead of an array try getting back a seq (IEnumerable) of Records. — s952163

s952163 s952163 · Accepted Answer · 2016-11-14T02:21:51

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

Populate list with Types

2 Answers