使用带有F#的SqlBulkCopy在SQL中导出矩阵 [英] Using SqlBulkCopy with F# to export matrix in SQL

问题描述

我想将大量数据从F#传输到SQL表.基本上，我的F#代码创建一个由三列(UserID, ProductID and price)和N行组成的矩阵.我想将其复制/粘贴"到数据库中 我尝试了几种选择，但最后，从F#传输数据确实很慢(10000行大约需要1个小时).

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).

感谢上一个问题的答案如何在F#中包含存储过程，解决此问题的一种有趣方法是使用SqlBulkCopy.

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的WritetoServer方法需要数据库类型，但是我没有找到任何现有代码或简单的方法将矩阵转换为数据库. 您有什么建议或想法吗?

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 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)

这篇关于使用带有F#的SqlBulkCopy在SQL中导出矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！