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屋！