5 people like it.

Reading a collection of records from database

This snippet can be used to read all records from a given table and expose them as an IEnumerable (seq)

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
      //Given any of this kind (record)
      type product = 
        {ProductId:int;
         ProductName:string;
         SupplierID:int;
         CategoryID:int;
         QuantityPerUnit:string;
         UnitPrice:decimal;
         UnitsInStock:int;
         UnitsOnOrder:int;
         ReorderLevel:int;
         Discontinued:bool}
         
      
      //Read all records from a table an give an IEnumerable collection as output
      let getAllRecords<'a>(tableName) (conn) =
            use cmd = new SqlCommand(("Select * from " + tableName), conn)
            use reader = cmd.ExecuteReader()
            let recFields = typeof<'a>.GetMembers() |> Array.filter (fun (f:MemberInfo) -> f.MemberType.ToString() = "Property")
            [while reader.Read() do yield (recFields |> Array.map (fun (f:MemberInfo) -> unbox (reader.[f.Name])))] 
            |> List.map (fun oArray -> Activator.CreateInstance(typeof<'a>, oArray))
            |> Seq.ofList |> Seq.map (fun o -> o :?> 'a)        
product.ProductId: int
Multiple items
val int : value:'T -> int (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.int

--------------------
type int = int32

Full name: Microsoft.FSharp.Core.int

--------------------
type int<'Measure> = int

Full name: Microsoft.FSharp.Core.int<_>
product.ProductName: string
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = System.String

Full name: Microsoft.FSharp.Core.string
product.SupplierID: int
product.CategoryID: int
product.QuantityPerUnit: string
product.UnitPrice: decimal
Multiple items
val decimal : value:'T -> decimal (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.decimal

--------------------
type decimal = System.Decimal

Full name: Microsoft.FSharp.Core.decimal

--------------------
type decimal<'Measure> = decimal

Full name: Microsoft.FSharp.Core.decimal<_>
product.UnitsInStock: int
product.UnitsOnOrder: int
product.ReorderLevel: int
product.Discontinued: bool
type bool = System.Boolean

Full name: Microsoft.FSharp.Core.bool
val getAllRecords : tableName:obj -> conn:obj -> seq<'a>

Full name: Script.getAllRecords
val tableName : obj
val conn : obj
val cmd : System.IDisposable
val reader : System.IDisposable
val recFields : System.Reflection.MemberInfo []
val typeof<'T> : System.Type

Full name: Microsoft.FSharp.Core.Operators.typeof
module Array

from Microsoft.FSharp.Collections
val filter : predicate:('T -> bool) -> array:'T [] -> 'T []

Full name: Microsoft.FSharp.Collections.Array.filter
val f : System.Reflection.MemberInfo
property System.Reflection.MemberInfo.MemberType: System.Reflection.MemberTypes
System.Enum.ToString() : string
System.Enum.ToString(format: string) : string
val map : mapping:('T -> 'U) -> array:'T [] -> 'U []

Full name: Microsoft.FSharp.Collections.Array.map
val unbox : value:obj -> 'T

Full name: Microsoft.FSharp.Core.Operators.unbox
property System.Reflection.MemberInfo.Name: string
Multiple items
module List

from Microsoft.FSharp.Collections

--------------------
type List<'T> =
  | ( [] )
  | ( :: ) of Head: 'T * Tail: 'T list
  interface IEnumerable
  interface IEnumerable<'T>
  member Head : 'T
  member IsEmpty : bool
  member Item : index:int -> 'T with get
  member Length : int
  member Tail : 'T list
  static member Cons : head:'T * tail:'T list -> 'T list
  static member Empty : 'T list

Full name: Microsoft.FSharp.Collections.List<_>
val map : mapping:('T -> 'U) -> list:'T list -> 'U list

Full name: Microsoft.FSharp.Collections.List.map
val oArray : obj []
module Seq

from Microsoft.FSharp.Collections
val ofList : source:'T list -> seq<'T>

Full name: Microsoft.FSharp.Collections.Seq.ofList
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.map
val o : obj
Raw view Test code New version

More information

Link:http://fssnip.net/5E
Posted:12 years ago
Author:Eduardo Claudio
Tags: ienumerable , database