F#使用参数设置SQLCommand的最佳方法 [英] F# best way to set up a SQLCommand with parameters
问题描述
我的F#程序需要与SQL Server对话.在某一部分中,我有这样的东西:
My F# program needs to talk to SQL Server. In one part I have something like this:
let workFlowDetailRuncommand = new SqlCommand(query, econnection)
workFlowDetailRuncommand.CommandTimeout <- 100000
workFlowDetailRuncommand.Parameters.Add("@1", SqlDbType.Int).Value <- 42
workFlowDetailRuncommand.Parameters.Add("@2", SqlDbType.VarChar).Value <- "answer"
workFlowDetailRuncommand.Parameters.Add("@3", SqlDbType.VarChar).Value <- mydate.ToString("yyyy.MM.dd")
workFlowDetailRuncommand.Parameters.Add("@4", SqlDbType.VarChar).Value <- "D. Adams"
workFlowDetailRuncommand.Parameters.Add("@5", SqlDbType.DateTime).Value <- DateTime.Now
workFlowDetailRuncommand.Parameters.Add("@6", SqlDbType.Text).Value <- filename
是否有一种更理想的方式(只需更少的输入!),而无需一次设置一个这样的参数.
Is there a more idomatic way to do this (with less typing!) without setting the parameters one at a time like this.
推荐答案
我认为Bent的答案为您提供了用于构建标准SqlCommand
对象的非常不错的DSL.可能正是您所需要的-如果您只想使用更好的语法来创建几个命令,它将很好地工作.
I think the answer from Bent gives you very nice DSL for constructing the standard SqlCommand
objects. That might very well be just what you need - if you just want nicer syntax for creating a couple of commands, it will work perfectly.
如果您想使用SQL命令做更多的事情,那么DSL有一个局限性,那就是它仍然基于潜在的可变SqlCommand
类型-它看起来很有效,但是它掩盖了对象,这可能会给您带来麻烦.
If you wanted to do more things with your SQL commands, then the DSL has one limitation, which is that it is still based on the underlying mutable SqlCommand
type - it looks functional, but it mutates objects under the cover, which might get you in trouble.
更全面的选择是定义您自己的功能类型以捕获域-即您要运行的查询类型:
A more comprehensive option would be to define your own functional types to capture the domain - that is, the kind of queries you want to run:
type Parameter =
| Int of int
| VarChar of string
| Text of string
| DateTime of System.DateTime
type Command =
{ Query : string
Timeout : int
Parameters : (string * Parameter) list }
然后,您可以使用普通的F#类型构造查询(甚至可以像上面建议的Bent一样实现DSL,同时仍然保持不变):
Then you can construct queries using normal F# types (and you could even implement a DSL like the one Bent suggested on top of this, while still keeping things immutable):
let cmd =
{ Query = query
Timeout = 100000
Parameters =
[ "@1", Int 42
"@2", VarChar "answer"
"@3", VarChar (mydate.ToString("yyyy.MM.dd"))
"@4", VarChar "D. Adams"
"@5", DateTime DateTime.Now
"@6", Text filename ] }
最后一位是编写一个接受命令和连接并将其转换为SqlCommand
的函数:
The last bit would be to write a function that takes command and a connection and turns it into SqlCommand
:
let createSqlCommand cmd connection =
let sql = new SqlCommand(cmd.Query, connection)
sql.CommandTimeout <- cmd.Timeout
for name, par in cmd.Parameters do
let sqlTyp, value =
match par with
| Int n -> SqlDbType.Int, box n
| VarChar s -> SqlDbType.VarChar, box s
| Text s -> SqlDbType.Text, box s
| DateTime dt -> SqlDbType.DateTime, box dt
sql.Parameters.Add(name, sqlTyp).Value <- value
sql
什么是最佳方法,将取决于您的用例-与数据库进行交互本质上是不纯的,因此也许将事物隔离和不纯是完全可以的.尽管我想将其显示为可能的选项,如果您想发挥更大的功能并专注于域(使用F#强大的域驱动建模方面!).
What is the best approach will depend on your use case - and interacting with databases is inherently impure, so perhaps keeping things isolated and impure is perfectly fine. Though I wanted to show this as a possible option if you wanted to be more functional and focus on the domain (using the strong domain-driven modelling side of F#!).
这篇关于F#使用参数设置SQLCommand的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!