F # insert list in SQL Server

Question

I am stuck at the moment trying to find a way to insert into SQL Server from F #.

I have an F # function that iterates through all the files inside the folder following the user template. I can then use the returned data to add to the list or (ideally) insert into the database.

I already have a working insert-in-sql function that works correctly:

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 am trying to make this method work correctly:

 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 only Seq.toList and call the function as shown below, it works:

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

Another thing I don't understand is how you can create a working insert command that takes a string for the value. For instance:

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

Answer 1

The best way to pass parameters to the query is to use SqlCeParameter . This is simpler than composing strings (because you do not need to encode strings and escape quotes), and it is also safer because you avoid SQL injection attack . Here is a basic example:

 let sqlInsertString value = // Create and open connection ('use' makes sure it gets closed at the end) use conn = new SqlCeConnection("..."); conn.Open() // Create a command with a parameter named '@str' let cmd = new SqlCeCommand("INSERT INTO MyTable (MyColumn) values (@str)", conn) // Create parameter '@str' with string value 'value' and add it to the command let param = new SqlCeParameter("@str", SqlDbType.NVarChar, value) cmd.Parameters.Add(param) // Now run the command (exception handling omitted) cmd.ExecuteNonQuery() |> ignore 

Using this function, you should now use Seq.iter . The function takes a string to be inserted and returns unit (no value), so it can be passed to Seq.iter :

 let getApplications (dir : string) (extension : string) = getAllFiles dir extension |> Seq.iter (fun s -> sqlInsertString s) 

Alternatively, you can write the last line in the same way as |> Seq.iter sqlInsertString . If you do this, you basically say that the argument s should be directly passed to the sqlInsertString function.

Answer 2

You are almost there. The sqlInsertString task returns a string that is not legal for use in Seq.iter .

What you do with sqlInsertString is creating a string using string formats. It goes well with the sprintf function:

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

Now you can use execNonQuery from sqlInsertString to actually insert data into the database. Since execNonQuery returns unit , it can 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 the annotation of the type is invalidated, your code can be rewritten in a more idiomatic way:

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