参数化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()


本页地址:http://www.foxtable.com/webhelp/topics/3266.htm