Back to Questions
6.8k
views1
comment
SQL Workflow
f#

Hi there,

I'm learning F# Workflows and I decided to implement a simple SQL one.

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
33

open System.Data
open System.Data.Common

type SQLBuilder(conn: DbConnection) =
    let getFields (reader: DbDataReader) =
        [| for i in 0 .. reader.FieldCount - 1 -> reader.[ i ] |]

    member b.Connection = conn

    member b.Bind (expr, rest) =
        // get a single row
        use cmd = conn.CreateCommand(CommandText = expr)
        use reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
        rest (getFields reader)

    member b.For (expr, rest) =
        // get all rows
        use cmd = conn.CreateCommand(CommandText = expr)
        use reader = cmd.ExecuteReader()
        while reader.Read() do
            rest (getFields reader)

    member b.Return expr = expr

    member b.Combine (expr1, expr2) =
       expr1
       expr2

    member b.Delay (expr) = expr ()

    member b.Zero = fun _ -> ()

let sql conn = SQLBuilder(conn)

It can be used like this (in the example, I'm using SQLite provider):

1
2
3
4
5
6
7
8
9
10
11

open System.Data.SQLite

let connString = sprintf "Data Source = %s"

let test_sql =
    use c = new SQLiteConnection(connString @"C:\test.db")
    c.Open()
    sql c { for [| a; b; c |] in "SELECT first_name, last_name, age FROM family" do
                printfn "%O, %O -> %O" a b c
            let! [| a |] = "SELECT first_name FROM family WHERE age = 5"   
            return a.ToString() }

What do you think? Can you improve it?

Thanks!

.

Hey, I recently found this and I think it's pretty neat. I added some basic transactional support for sqlite here:

[link:github.com]

What I haven't done yet is support nested transactions with sqlite, but that's pretty easy. Between each delay you can wrap it with a save point.

Anyways, thanks for sharing!

By on 12/21/2012 10:14 AM ()

    Topic tags

    IntelliFactory Offices Copyright (c) 2011-2012 IntelliFactory. All rights reserved.
    Home | Products | Consulting | Trainings | Blogs | Jobs | Contact Us | Terms of Use | Privacy Policy | Cookie Policy     		Built with WebSharper