参数化SQLCommand

随着Foxtable开始提供OpenQQ和HttpServer等后台服务功能，直接使用SQL语句读写后台数据源的场合会越来越多。

提示：如果需要使用SQL直接读写后台数据，请务必使用外部数据源。

何为参数化

假定你用的是SQL Server数据源，我们在订单表增加一行的SQL语句为：

Insert Into 订单 (客户, 日期, 订单编号) Values('01', '2018-05-01','100')

在Foxtable中编写的代码为：

Dim cid As String = "01"
Dim rqi As Date = Date.Today
Dim oid As String = "100"
Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Insert Into 订单 (客户, 日期, 订单编号) Values('" & cid & "',  '"  & Date.Today & "','" & oid & "')"
cmd.ExecuteNonQuery

是不是有点头晕？ 其实我和你一样头晕，这里仅用了3列作为示例，实际开发时可能有数十列，这样的写法就是职业成员也会崩溃的。

我们可以换一种写法，采用参数化方式，代码为：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Insert Into 订单 (客户, 日期, 订单编号) Values(?,?,?)"
cmd.Parameters.Add("@客户","01")
cmd.Parameters.Add("@日期",Date.Today)
cmd.Parameters.Add("@订单编号",100)
cmd.ExecuteNonQuery

显然这种方式要轻松很多，再多的列也不会让您头晕了。

参数化SQLCommand很简单，将SQL语句中需要动态取值的参数全部用问号(?)代替,然后通过SQLcommand的Parameters集合桉顺序指定各参数的值。

提示：我在编写以上示例代码的时候，单独建立了一个外部表，一开始正常，但很快发现再也无法增加行了，我诧异了几分钟后，才想起来外部表默认只加载10行数据，所以上述代码都正常增加了行，只是没有加载而已，特此提醒，免你犯同样的错误。

重要的是顺序

参数的名称并无意义，重要的是顺序，如果你将代码改为：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Insert Into 订单 (客户, 日期, 订单编号) Values(?,?,?)"
cmd.Parameters.Add("@日期",Date.Today)
cmd.Parameters.Add("@客户","01")
cmd.Parameters.Add("@订单编号",100)
cmd.ExecuteNonQuery

这里只是将日期和客户两个参数的增加顺序调整了一下，看起来不应该有问题，但是运行却会出错，我们来分析一下原因。

下面的代码看起来是给日期列指定值:

cmd.Parameters.Add("@日期",Date.Today)

但其实不然，因为这是第一个增加的参数，所以这个参数会赋值给客户列（第一个需要指定值的列），同样下面的代码：

cmd.Parameters.Add("@客户","01")

会将"01"赋值给日期列（第二个需要指定值的列），因为这是第二个增加的参数。

所以编写代码的时候，必须严格按照SQL语句中的参数顺序赋值。

由于参数是根据顺序而不是名称赋值，所以参数名称 并不会影响代码的实际执行，以下代码完全可以正常运行：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Insert Into 订单 (客户, 日期, 订单编号) Values(?,?,?)"
cmd.Parameters.Add("@日期","01")
cmd.Parameters.Add("@客户",Date.Today)
cmd.Parameters.Add("@p",100)
cmd.ExecuteNonQuery

虽如此，但是给参数指定一个有描述性的名称是必须的，因为这有助于今后的维护。

更多例子

所有的语句都可以使用参数化，例如用Delete语句删除指定编号的订单：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Delete From 订单 Where 订单编号 = ?"
cmd.Parameters.Add("@订单编号",100)
cmd.ExecuteNonQuery

例如用Update语句将数量大于500的订单的折扣设置为0.15：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "UPDATE 订单 SET 折扣 = ? WHERE 数量 > ?"
cmd.Parameters.Add("@折扣",0.15)
cmd.Parameters.Add("@数量",500)
cmd.ExecuteNonQuery

用Select语句获取指定日期范围内的订单：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "SELECT * FROM {订单} WHERE 日期 >= ? AND 日期 <= ?"
cmd.Parameters.Add("@开始日期",#2/1/2018#)
cmd.Parameters.Add("@结束日期",#3/31/2018#)
Dim dt As DataTable = cmd.ExecuteReader()