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 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?
This should get you started (and reference the documentation for more information about
//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)
If you're okay with third party libraries, fastmember can be really useful here.
module DB = open System.Data open System.Data.SqlClient open FastMember // val bulk : conn:SqlConnection -> table:string -> columns:seq<string*string> -> items:seq<a'> let bulk conn table columns items = use bcp = new SqlBulkCopy(connection = conn) bcp.EnableStreaming <- true bcp.DestinationTableName <- table for n,v in columns do bcp.ColumnMappings.Add(new SqlBulkCopyColumnMapping(sourceColumn = n, destinationColumn = v)) |> ignore bcp.WriteToServer(items |> ObjectReader.Create)
This leaves you with a generic method that you can just funnel any
seq<a'> into. Along with some config parameters, which you can of course configure to your needs.