Using SqlBulkCopy with F# to export matrix in SQL

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?

What is the type of your "matrix"? – Stephen Swensen Jan 20 '12 at 13:17 — Stephen Swensen, Jan 20 '12 at 13:17

score 5 · Accepted Answer · edited Jan 23 '12 at 00:48

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)

Thank you Stephen, it is perfectly doing the job. – fabco63 Jan 20 '12 at 14:16 — fabco63, Jan 20 '12 at 14:16

score 1 · Answer 2 · answered Jan 17 '20 at 21:14

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.

Using SqlBulkCopy with F# to export matrix in SQL

2 Answers2

Linked