Question
I want to transfer a large amount of data from F# to an SQL table. Basically my F# code creates a matrix of three columns (UserID, ProductID and price) and N lines. I would like to "copy/pate it" into a database
I tried several options but at the end, the transfer of data from F# is really slow (around one hour for 10000 lines).
Thanks to answers of a previous question How to include a stored procedure in F#, an interesting way to resolve this problem is to use SqlBulkCopy.
SqlBulkCopy requires a database type for its WritetoServer method but I didn't find any existing code or simple way to convert a matrix into a database.
Do you have any suggestions or ideas?
Accepted Answer
This should get you started (and reference the documentation for more information about SqlBulkCopy):
//you must reference System.Data and System.Xml
open System.Data
open System.Data.SqlClient
let bulkLoadUserPurchases (conn:SqlConnection) (userPurchases: list<int * int * float>) =
use sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, null, BatchSize=500, BulkCopyTimeout=1200, DestinationTableName="YOUR_TABLE_NAME_HERE")
sbc.WriteToServer(
let dt = new DataTable()
["UserID", typeof<int>
"ProductID", typeof<int>
"Price", typeof<float>]
|> List.iter (dt.Columns.Add>>ignore)
for userPurchase in userPurchases do
let userId, productId, price = userPurchase
let dr = dt.NewRow()
dr.["UserID"] <- userId
dr.["ProductID"] <- productId
dr.["Price"] <- price
dt.Rows.Add(dr)
dt)