SQLCommand

SQLCommand

这个类用于执行SQL命令。

属性：

CommandText
字符型，用于设置SQL命令。
ConnectionName
字符型，用于指定数据源名称，如果省略，则使用内部数据源。
CommandTimeOut
设置超时时限，默认为30秒，一般不需要设置此属性，除非需要进行非常耗时超过30秒的操作，以至于出现超时错误，此时可适当地加大CommandTimeOut属性的值。

提示：如果使用外部数据源，请记得设置数据源名称

方法：

ExecuteScalar

用于执行返回单个值的命令，例如：

Dim val As Integer
Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Select Sum(数量) From {订单} Where 日期 > #2012/2/21#"
val = cmd.ExecuteScalar()

上面的代码统计2012年2月21日之后的销售总量，并保存在变量val中。

再例如：

Dim ld As Date
Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Select Max(日期) From {订单} Where 产品 = 'PD01'"
ld = cmd.ExecuteScalar()

上面的代码得到最后一次有人订购PD01的日期，并保存在变量ld中。

再例如：

Dim ld As Date
Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "Select top 1 日期 From {订单} Where 产品 = 'PD01' Order by 日期 Desc"
ld = cmd.ExecuteScalar()

上面的代码得到最后一次有人订购PD01的日期，并保存在变量ld中，和前一段代码功能一样，但是方法不同，希望大家体会。

ExecuteScalar经常用来判断是否存在指定条件的数据，例如：

Dim cmd As New SQLCommand
Dim nm As String = "贺辉"
cmd.ConnectionName = "User"
cmd.CommandText ="Select Count(*) From {Users} Where Name = '" & nm & "'"
If cmd.ExecuteScalar > 0 Then
Messagebox.Show("已经存在同名用户","提示",MessageBoxButtons.OK,MessageBoxIcon.Information)
End If

ExecuteValues

用于执行一次返回多个值的命令，例如：

Dim cmd As new SQLCommand
cmd.CommandText = "Select 部门,职务 from {员工} Where 姓名 = '王伟'"
cmd.ConnectionName = "数据源名称"
Dim Values = cmd.ExecuteValues
If Values.Count > 0 Then
Output.show(Values("部门"))
Output.show(Values("职务"))
End If

再例如：

Dim cmd As new SQLCommand
cmd.CommandText = "Select Sum(数量) As 数量, Sum(数量 * 单价) As 金额 from {订单} Where 产品 = 'PD01'"
cmd.ConnectionName = "数据源名称"
Dim Values = cmd.ExecuteValues
If Values.Count > 0 Then
Output.show(Values("数量"))
Output.show(Values("金额"))
End If

ExecuteNonQuery

用于执行不返回任何值的SQL命令，例如DELETE和UPDATE命令。
该方法会返回一个整数，表示受影响的行数。

示例一

Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
Dim Count As Integer
cmd.CommandText = "DELETE FROM {订单} WHERE 产品 = 'PD01'"
Count= cmd.ExecuteNonQuery()
Messagebox.Show( "总共删除" & Count & "行！")

以上代码会删除产品PD01的所有订单，执行速度很快。
但是执行完成后，你会发现订单表中产品PD01订单并没有被删除，这是因为DELETE是直接从后台删除的。
所以我们还需要用Remove方法移除已经删除的行：

Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
Dim Count As Integer
cmd.CommandText = "DELETE FROM {订单} WHERE 产品 = 'PD01'"
Count= cmd.ExecuteNonQuery()
DataTables("订单").RemoveFor("产品 = 'PD01'")
Messagebox.Show( "总共删除" & Count & "行！")

也可以用Load方法重新加载表：

Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "DELETE FROM {订单} WHERE 产品 = 'PD02'"
cmd.ExecuteNonQuery()
DataTables("订单").Load()

用Remove的效率更高，但用Load可以同时刷新数据，可根据需要选择。

当然如果删除的数据之前并没有被加载，那么就不需要在删除后执行Remove或Load方法。

示例二

Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "UPDATE {订单} SET 折扣 = 0.2 WHERE 数量 > 500"
cmd.ExecuteNonQuery()
DataTables ("订单").Load()

上述代码将订购数量超过500的订单的折扣设为0.2，同样在代码的最后调用了Load方法来显示最新数据。
如果要更新的行之前并没有被加载，那么就不需要在更新后调用Load方法。
UPDATE本身的执行速度很快，而且无需保存即刻生效，但是同样需要重新加载表，才能看到更新后的数据。
所以只有在需要更新大量的行，或者要更新没有加载的数据，才可以考虑用UPDATE命令

ExecuteReader

ExecuteReader用于从后台提取数据生成一个临时的DataTable，而这个DataTable只存在于代码的运行过程中，不会以表的形式呈现给用户。

示例一

同样，假定窗口中有一个组合框(ComboBox)，希望这个窗口能够列出订单表所有的客户名称，如果订单表采用动态加载，或者根本没有加载，那么显然无法从订单表中获得所有客户名称，只能利用ExecuteReader直接从后台提取：

Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
Dim dt As DataTable
Dim cmb As WinForm.ComboBox
cmd.CommandText = "SELECT DISTINCT 客户 From {订单}"
dt = cmd.ExecuteReader()
cmb = e.Form.Controls("ComboBox1")
cmb.ComboList= dt.GetComboListString("客户")

上述代码可以设置在窗口的AfterLoad事件中。

示例二

同样有了ExecuteReader,就可以直接利用后台数据给列生成下拉目录树：

'生成数据表
Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
Dim dt As DataTable
cmd.CommandText = "SELECT DISTINCT 省,市县,区号,邮编 From {行政区域}"
dt = cmd.ExecuteReader()
'生成目录树
Dim tb As New DropTreeBuilder
tb.SourceTable = dt
tb.TreeCols = "省|市县"
tb.SourceCols = "省|市县|区号|邮编"
tb.ReceiveCols = "省|市县|区号|邮编"
Tables("客户").Cols("省").DropTree = tb.Build()

示例三

同样利用ExecuteReader，可以直接从后台提取数据合并到某个表中：

Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
Dim dt As DataTable
Dim Cols1() As String = {"来源列一","来源列二","来源列三"}
Dim Cols2() As String = {"接收列一","接收列二","接收列三"}
cmd.CommandText = "SELECT * From {表A} Where 条件语句"
dt = cmd.ExecuteReader()
For Each dr1 As DataRow In dt.DataRows
    Dim dr2 As DataRow = DataTables("表B").AddNew()
    For i As Integer = 0 To Cols1.Length -1
        dr2(Cols2(i)) = dr1(Cols1(i))
    Next
Next

示例四

前面提到ExecuteReader生成的DataTable会在代码运行结束后自动销毁。
如果希望DataTable在代码结束后能够继续存在，可以使用Public变量引用这个DataTable。

例如首先在全局代码中定义一个DataTable类型的Public变量：

Public dt As DataTable

然后在项目事件AfterOpenProject中设置代码，将生成的DataTable赋值给此变量：

Dim cmd As New SQLCommand
cmd.ConnectionName = "数据源名称"
cmd.CommandText = "SELECT DISTINCT 客户 From {订单}"
dt = cmd.ExecuteReader()

这样就可以在任何位置的代码引用此变量：

Dim cmb As WinForm.ComboBox
cmb = e.Form.Controls("ComboBox1")
cmb.ComboList= dt.GetComboListString("客户")

示例五

ExecuteReader有一个可选参数，如果设置为True，那么生成的DataTable不仅可以增加、删除和修改数据，还可以保存。
新建一个文件，在命令窗口测试下面的代码，会在A的第一行的第一列写入123，并删除第二行：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
Dim dt As DataTable
cmd.CommandText = "Select * From {表A}"
dt = cmd.ExecuteReader(True) '记得将参数设置为True
dt.DataRows(0)("第一列") = 123
dt.DataRows(1).Delete()
dt.Save()
DataTables("表A").Load() '重新加载表A,看看值是否已经变化

有了这个特性，很多时候，我们不再需要合成Update语句来更新没有加载的后台数据了。

例如要将订购数量超过100的订单的折扣，统一设置为0.05，代码为：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
Dim dt As DataTable
cmd.CommandText = "Select [_Identify],折扣 From {订单} Where 数量 >= 500"
dt = cmd.ExecuteReader(True) '记得将参数设置为True
For Each dr As DataRow In dt.DataRows
dr("折扣") = 0.05
Next
dt.Save()

提示：

1、Select语句只加载需要的列，这样可以提高运行速度。
2、如果只选择部分列，那么必须包括主键列，否则保存的时候会出错！

示例六

通过ExecuteReader生成的表也可以增加数据并保存。
如果生成的表仅用于增加数据，那么就没有必要加载数据，生成一个空表即可，例如：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
Dim dt As DataTable
cmd.CommandText = "Select * From {表A} Where [_Identify] Is null" '生成空表
dt = cmd.ExecuteReader(True) '记得将参数设置为True
Dim dr As DataRow = dt.AddNew()
dr("第一列") = "Foxtable"
dt.Save()
DataTables("表A").Load() '重新加载表A,看看值是否已经变化

假定出库表和出库明细表通过出库单编号建立关联，下面的代码演示了如何不用SQL语句直接在后台增加一个出库单及对应的两条出库明细：

Dim cmd As new SQLCommand
cmd.ConnectionName = "数据源名称"
Dim ckd As DataTable
Dim ckmx As DataTable
cmd.CommandText = "Select * From {出库} Where [_Identify] Is null" '生成一个空出库表
ckd = cmd.ExecuteReader(True) '记得将参数设置为True
Dim dr As DataRow = ckd.AddNew() '增加出库单
dr("出库日期") = Date.today
dr("出库单编号") = "2020-0517-101"
cmd.CommandText = "Select * From {出库明细} Where [_Identify] Is null" '生成空出库明细表
ckmx = cmd.ExecuteReader(True) '记得将参数设置为True
dr = ckmx.AddNew() '增加一个出库明细
dr("出库单编号") = "2020-0517-101"
dr("商品代码") = "HC00101"
dr("数量") = 50
dr = ckmx.AddNew() '再增加一个出库明细
dr("出库单编号") = "2020-0517-101"
dr("商品代码") = "HC00102"
dr("数量") = 100
ckd.Save()
ckmx.Save()

示例七

假定出库表的主键是出库单号列，而且是自动增量型，那么上述代码是无法正常运行的，因为作为自动增量列，出库单编号是由系统自动生成的，而且是在保存之后生成。
所以我们在保存出库单之后，必须想办法获取自动生成的出库单编号，并赋值给随后新增的出库单明细的出库单编号列，否则二者之间将失去关联。

Foxtable处理这种问题非常简单，因为每次保存新增行之后，Foxtable都会自动访问后台数据库，获取新增行的主键值并写入到主键列中。
修改后的代码如下，留意加粗显示的代码：

Dim cmd As new SQLCommand
cmd.ConnectionName = "orders"
Dim ckd As DataTable
Dim ckmx As DataTable
cmd.CommandText = "Select * From {出库} Where [出库单编号] Is null" '生成一个空出库表
ckd = cmd.ExecuteReader(True) '记得将参数设置为True
Dim cr As DataRow = ckd.AddNew() '增加出库单
cr("出库日期") = Date.today
cr("库管员") = "张三"
ckd.Save() '必须提前保存出库单,以获取自动生成的出库单编号.
cmd.CommandText = "Select * From {出库明细} Where [出库单编号] Is null" '生成空出库明细表
ckmx = cmd.ExecuteReader(True) '记得将参数设置为True
Dim mr As DataRow = ckmx.AddNew() '增加一个出库明细
mr("出库单编号") = cr("出库单编号") '将自动生成的出库单编号赋值后新增的出库明细.
mr("商品代码") = "HC00101"
mr("数量") = 50
mr = ckmx.AddNew() '再增加一个出库明细
mr("出库单编号") = cr("出库单编号") '将自动生成的出库单编号赋值后新增的出库明细.
mr("商品代码") = "HC00102"
mr("数量") = 100
ckd.Save()
ckmx.Save()

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