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:	13 years ago
Author:	Eduardo Claudio
Tags:	ienumerable , database