如何在F#中包含存储过程 [英] How to include a stored procedure in F#

问题描述

在以下代码中，我想通过F#在SQL表中插入或更新行. 它采用表示用户的元组矩阵和相关的分数(usrID，分数)，这是一些F#计算的结果.
现在，我想更新一个名为UserScoresTable的SQL表.
我编写的代码可以正常运行，但是速度很慢.

In the following code, I would like to insert or update rows in an SQL table through F#. It takes a matrix of tuple representing users and an associated scores (usrID,score) which are the results of some F# calculations.
Now I want to update a SQL table called UserScoresTable.
The code I wrote is working but is very slow.

let cnn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings.Item("Database").ConnectionString)
cnn.Open()

// Definition d'une fonction qui execute une demande sous MySQL
let execNonQuery s =
    let comm = new System.Data.SqlClient.SqlCommand(s, cnn, CommandTimeout = 10)
    comm.ExecuteNonQuery() |> ignore

// Definition d'une fonction qui utilise les éléments d'une matrice pour updater une table dans MySQL
let updateMySQLTable (m : Matrix<float * float>) =
    for j in 0 .. (snd m.Dimensions) - 1 do
        for i in 1 .. (fst m.Dimensions) - 1 do
        // first check if the user and score and date do not exist
            sprintf "IF NOT EXISTS (SELECT * FROM ProductScores WHERE UserID = %f AND ProductID = %f) INSERT INTO ProductScores (UserID, Score, Date) VALUES (%f, %f,CURRENT_TIMESTAMP)"  (fst m.[i, j]) (snd m.[i, j])
            |>  execNonQuery
       // otherwise update the row
            sprintf "UPDATE ProductScores SET Score = %f, Date = CURRENT_TIMESTAMP WHERE UserID = %f " (snd m.[i, j]) (fst m.[i, j])
            |>  execNonQuery

我想通过使用诸如p

CREATE PROCEDURE updateScoreByUsers 
    -- Add the parameters for the stored procedure here
    @UserID int, 
    @Score  float

AS
BEGIN

    IF NOT EXISTS 
    (SELECT * FROM ProductScores WHERE UserID = @UserID ) 
        INSERT INTO 
        ProductScores (UserID, Score, Date) VALUES (@UserID,@Score,CURRENT_TIMESTAMP)
    ELSE

    UPDATE ProductScores 
        SET Score = @Score, Date = CURRENT_TIMESTAMP 
        WHERE UserID = @UserID 
END
GO

但是我不知道如何在F#中调用SQL存储过程.
如何用F#调用它?
您还看到其他任何改进方法吗?

But I don't know how to call a SQL stored procedure in F#.
How can I call it with F#?
DO you see any other way of improvements?

推荐答案

将SqlCommand.CommandText设置为存储过程的名称，并将CommandType设置为StoredProcedure.例如

Set SqlCommand.CommandText to the name of the stored procedure and set CommandType to StoredProcedure. For example

use cmd = new SqlCommand("MyStoredProcedure", con)
cmd.CommandType <- CommandType.StoredProcedure
cmd.ExecuteNonQuery()

如果您使用的是2008年版本，则可能需要查看 MERGE 语句.

If you're on 2008 you may want to look into the MERGE statement.

要显着提高性能，您需要避免对每个项目进行数据库调用.也许使用SqlBulkCopy一次将所有数据加载到服务器(使用临时表或指定的登台表)，然后使用基于集合的操作(UPDATE/INSERT/MERGE)与目标合并桌子.

To significantly improve performance you need to avoid making a database call for each item. Perhaps use SqlBulkCopy to load all the data to the server at once (use a temp table or designated staging table), then use set-based operations (UPDATE/INSERT/MERGE) to merge with your target table.

这篇关于如何在F#中包含存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！