F# insert a list into SQL Server

Question

I'm stuck at the moment trying to figure out a method of inserting into SQL Server from F#.

I have an F# function that iterates through all files inside a folder following a user-defined pattern. Then I can use the returned data to put in a list or (ideally) insert into a database.

I already have a working insert-into-sql function that works properly:

let execNonQuery conn s =
let comm = 
    new SqlCeCommand(s, conn)
try 
    comm.ExecuteNonQuery() |> ignore
with e ->
    printf "Error : %A\n" e

let string = "insert into MyTable (MyColumn) values ('test .. again')"
execNonQuery conn string; // works

I'm trying to get this method to work properly:

let rec getAllFiles dir pattern =
    seq { yield! Directory.EnumerateFiles(dir, pattern)
          for d in Directory.EnumerateDirectories(dir) do
              yield! getAllFiles d pattern }

let getApplications (dir : string) (extension : string) = 
    getAllFiles  dir extension
    //|> Seq.toList // If I need to create a list of returned values
    |> Seq.iter (fun s -> SQLInsertString s) // This does not work as it complains about the function not being of type unit

If I use Seq.toList only and call the function as per below, it works:

getApplications "C:\Admin" "*.txt" // works

The other thing I don't understand is how can you create a working insert command that takes in a string for Value. For example:

let SQLInsertString s = "insert into MyTable (MyColumn) values (%s)" //does not work

Answer 1

You're almost there. The problem is sqlInsertString returns string which is not legal to use in Seq.iter.

What you're doing with sqlInsertString is to create a string using string formats. It fits nicely with sprintf function:

let sqlInsertString s = 
    sprintf "insert into MyTable (MyColumn) values (%s)" s

Now you can use execNonQuery on results of sqlInsertString to actually insert data into database. Since execNonQuery returns unit, it could be easily used in Seq.iter:

// Assuming conn is a global and already defined variable.
let getApplications (dir : string) (extension : string) = 
    getAllFiles  dir extension
    |> Seq.iter (fun s -> execNonQuery conn (sqlInsertString s))

Since type annotation is redundant, your code could be rewritten in a more idiomatic way:

let getApplications dir extension conn = 
    getAllFiles dir extension
    |> Seq.iter (sqlInsertString >> execNonQuery conn)