8 people like it.

Simple sql command helper

Minimalist assistant to read data / execute database command. //I have question //How to Print the read value i.e.Customer.Id and Customer.Name //some sample regarding are available?? //Link For convering c# code to f# would be helpful-reply me at

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
open System.Data.SqlClient

type SqlHelper (connection) =
    let exec bind parametres query = 
        use conn = new SqlConnection (connection)
        conn.Open()
        use cmd = new SqlCommand (query, conn)
        parametres |> List.iteri (fun i p -> 
                        cmd.Parameters.AddWithValue(sprintf "@p%d"  i, box p) |> ignore)
        bind cmd

    member __.Execute = exec <| fun c -> c.ExecuteNonQuery() |> ignore
    member __.Scalar  = exec <| fun c -> c.ExecuteScalar()
    member __.Read f  = exec <| fun c -> [ let read = c.ExecuteReader()
                                           while read.Read() do 
                                               yield f read ]

// usage
type Customer = { Id : int; Name : string }

let sql = new SqlHelper ("some connection")

let getCustomers cityId minAges = 
    sql.Read (fun r -> { Id = unbox r.[0]; Name = unbox r.[1] })
             [cityId; minAges] 
             "select Id, Name 
              from dbo.Customers 
              where CityId = @p0 and Age > @p1"
//I have question 
//How to Print the read value i.e.Customer.Id and Customer.Name
//some sample regarding are available??
//Link For convering c# code to f# would be helpful
namespace System
namespace System.Data
namespace System.Data.SqlClient
Multiple items
type SqlHelper =
  new : connection:string -> SqlHelper
  member Read : f:(SqlDataReader -> 'a) -> ('b list -> string -> 'a list)
  member Execute : (obj list -> string -> unit)
  member Scalar : (obj list -> string -> obj)

Full name: Script.SqlHelper

--------------------
new : connection:string -> SqlHelper
val connection : string
val exec : ((SqlCommand -> 'c) -> 'd list -> string -> 'c)
val bind : (SqlCommand -> 'c)
val parametres : 'd list
val query : string
val conn : SqlConnection
Multiple items
type SqlConnection =
  inherit DbConnection
  new : unit -> SqlConnection + 1 overload
  member BeginTransaction : unit -> SqlTransaction + 3 overloads
  member ChangeDatabase : database:string -> unit
  member Close : unit -> unit
  member ConnectionString : string with get, set
  member ConnectionTimeout : int
  member CreateCommand : unit -> SqlCommand
  member DataSource : string
  member Database : string
  member EnlistDistributedTransaction : transaction:ITransaction -> unit
  ...

Full name: System.Data.SqlClient.SqlConnection

--------------------
SqlConnection() : unit
SqlConnection(connectionString: string) : unit
val cmd : SqlCommand
Multiple items
type SqlCommand =
  inherit DbCommand
  new : unit -> SqlCommand + 3 overloads
  member BeginExecuteNonQuery : unit -> IAsyncResult + 1 overload
  member BeginExecuteReader : unit -> IAsyncResult + 3 overloads
  member BeginExecuteXmlReader : unit -> IAsyncResult + 1 overload
  member Cancel : unit -> unit
  member Clone : unit -> SqlCommand
  member CommandText : string with get, set
  member CommandTimeout : int with get, set
  member CommandType : CommandType with get, set
  member Connection : SqlConnection with get, set
  ...

Full name: System.Data.SqlClient.SqlCommand

--------------------
SqlCommand() : unit
SqlCommand(cmdText: string) : unit
SqlCommand(cmdText: string, connection: SqlConnection) : unit
SqlCommand(cmdText: string, connection: SqlConnection, transaction: SqlTransaction) : unit
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 iteri : action:(int -> 'T -> unit) -> list:'T list -> unit

Full name: Microsoft.FSharp.Collections.List.iteri
val i : int
val p : 'd
property SqlCommand.Parameters: SqlParameterCollection
SqlParameterCollection.AddWithValue(parameterName: string, value: obj) : SqlParameter
val sprintf : format:Printf.StringFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.sprintf
val box : value:'T -> obj

Full name: Microsoft.FSharp.Core.Operators.box
val ignore : value:'T -> unit

Full name: Microsoft.FSharp.Core.Operators.ignore
member SqlHelper.Execute : (obj list -> string -> unit)

Full name: Script.SqlHelper.Execute
val c : SqlCommand
SqlCommand.ExecuteNonQuery() : int
val __ : SqlHelper
member SqlHelper.Scalar : (obj list -> string -> obj)

Full name: Script.SqlHelper.Scalar
SqlCommand.ExecuteScalar() : obj
member SqlHelper.Read : f:(SqlDataReader -> 'a) -> ('b list -> string -> 'a list)

Full name: Script.SqlHelper.Read
val f : (SqlDataReader -> 'a)
val read : SqlDataReader
SqlCommand.ExecuteReader() : SqlDataReader
SqlCommand.ExecuteReader(behavior: System.Data.CommandBehavior) : SqlDataReader
SqlDataReader.Read() : bool
type Customer =
  {Id: int;
   Name: string;}

Full name: Script.Customer
Customer.Id: 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<_>
Customer.Name: 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
val sql : SqlHelper

Full name: Script.sql
val getCustomers : cityId:'a -> minAges:'a -> Customer list

Full name: Script.getCustomers
val cityId : 'a
val minAges : 'a
member SqlHelper.Read : f:(SqlDataReader -> 'a) -> ('b list -> string -> 'a list)
val r : SqlDataReader
val unbox : value:obj -> 'T

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

More information

Link:http://fssnip.net/ac
Posted:12 years ago
Author:S. Kasperovich
Tags: data , functions