8 people like it.
Like the snippet!

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:

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"
erewrew

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 -> 'a) -> 'b list -> string -> 'a)

val bind : (SqlCommand -> 'a)

val parametres : 'b 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 : 'b

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

Previous Version Next Version Raw view Test code New version

More information

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