如何在F#中包含存储过程

本文介绍了如何在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.