[ADO.NET]为什么我们要使用SQLCommand

本帖最后由 rockage 于 2016-10-28 22:37 编辑

在以前，我们对数据库进行增删改操作并不是一件很难的事情，几条T-SQL语句传送到服务器去执行即可搞定，操作结果直接作用于远程数据库，很容易理解。这种操作方式对于小的数据库来说看不出有什么问题，但是当数据库庞大到一定程度时，大量而且频繁的触发将导致效率变得低下。

为了解决上述问题，微软在ADO.NET上创建了一套新的数据库操作体系，主要由Dataset、DataAdapter、DataTable这三大模块来构建，其目的是尽可能将数据操作移到本机处理，然后将结果一次性批量更新，尽量减少频繁而少量的远程数据库触发，从而实现提高效率的目的。

当然，在ADO.NET中纯SQL语句操纵远程数据库的方式也并没有被淘汰，你依然可以用传统的方法，甚至可以完全绕开ADO.NET，或者混合使用，都是允许的。

下面我们举一个例子说明ADO.NET的一般性操作流程：

1. 首先，将数据库（或者数据库的一部分）从远程服务器上转移到本机内存。
2. 然后，在本机内存中，针对这块数据进行各种加工处理。
3. 最后，将处理好的数据一次性回写到服务器。

理解了大体的操作结构后，来看看三大部件起的作用：

1. Dataset ，运行在本机内存的数据容器，简单地理解，就和我们以前理解的“数据库”差不多的意思。
2. DataAdapter ，这是一个以前没有的新概念，其作用是同步本机数据和远程数据，简单地说就是本地电脑和远程服务器之间的搬运工。
3. DataTable , 运行在本机内存的数据表，就是平常我们理解的“表”，但它只是临时运行在内存里。

下面说说具体的操作方法(以MySQL为例，其他数据库基本一样)：

1. 从服务器上转移到本机：

   Dim myConnectionString As String = "server=服务器IP;uid=用户名;pwd=密码;database=数据库名;charset=utf8;Pooling=True"
    Dim cn As New MySqlClient.MySqlConnection
    Dim da As New MySqlDataAdapter("SELECT * From Table", cn) '用Select决定要搬运哪些数据
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim db As New MySqlCommandBuilder(da) '这条指令现在不讲，放到后面叙述

    cn.ConnectionString = myConnectionString
    cn.Open()

    da.Fill(ds, "Table") '从服务器上将数据搬运到本机，表名随意，可以和远程服务器一致，也可以不一致
    dt = ds.Tables("Table") '将远程数据表Table的值赋给本地内存数据表dt

2. 接下来，就是对本地数据表dt的操作了：

A：增加一行

    Dim aRow As DataRow = dt.NewRow '首先克隆aRow的结构，即列的结构
    aRow("列1") = 1
    aRow("列2") = "abc"
    dt.Rows.Add(aRow) '执行新增操作

B：删除一行

     Dim aRow() As DataRow
     aRow = dt.Select("ID = 1") '指定要删除哪行
     aRow(0).Delete() '执行删除

C：修改一行

        Dim aRow() As DataRow = dt.Select("ID = 1") '指定要修改哪行
        aRow(0)("列1") = "abcdefg" '执行修改

注意：以上是最简单的增删改操作，但是需要理解的是，这些操作仅仅作用于本地内存，执行结束后并不会影响远程服务器上的数据，这就是微软这套体系的关键，大部分操作都放在本机完成了，当所有操作完成之后，只需要将结果回传到服务器即可，大大减轻了服务器的负担。

3. 接下来，就需要将上面的改动回写到服务器了：

da.Update(ds, "Table")

很简单，就一条Update指令，表示将本地数据库ds里的"Table"表传回到服务器，完成最终修改。

对于一个单表来说，理解以上的内容就足够了，但是如果同时要处理好几个数据表，情况会变得稍稍有点复杂，下面针对几个问题再单独说一下：
1. 多表传入：

        Dim myConnectionString As String = "server=服务器IP;uid=用户名;pwd=密码;database=数据库名;charset=utf8;Pooling=True"
        Dim cn As New MySqlClient.MySqlConnection
        Dim da As New MySqlDataAdapter("SELECT * From Table1;SELECT * From Table2", cn) '这是多表的Select语句，中间用分号隔开
        Dim ds As New DataSet
        Dim dt1 As New DataTable
        Dim dt2 As New DataTable
        cn.ConnectionString = myConnectionString
        cn.Open()
        da.Fill(database, "Table") '从服务器上将数据搬运到本机，表名随意，可以和远程服务器一致，也可以不一致
        dt1 = ds.Tables("Table") '第1个表
        dt2 = ds.Tables("Table1") '第2个表

备注：请注意这里，由于我们现在是从服务器一次性转移了两个表下来，表的命名方式有点诡异：第一个表名不变，第二个表名在后面加1，第三个表加2，以此类推，总之请记住，用于本机的表名和远程服务器上的表名没有必然联系，可以设置成一样，也可以不一样，在多表的时候Select语句用分号隔开，在搬到本机后的表名后面加1,2,3...n对应即可。

2. 下面就要讲一下最开始那个例子里有一行“不讲”的语句了：

Dim db As New MySqlCommandBuilder(da)

这个MySqlCommandBuilder到底是个什么东西？
从头到尾只见它被定义了一次，也没见什么地方使用过它，它有何作用？去掉它又会发生什么？

要讲清楚这个东西，我觉得还是需要通过它的运行步骤来理解：
在本地数据表dt的操作过程中，有大量的Update或者Insert之类的操作，这些操作最终是要被传送到服务器去执行的，否则就只是在本机晃一下不产生任何作用。那么问题来了，ADO.NET如何知道用哪句T-SQL去通知服务器执行呢？放在以前，很简单，，写一条T-SQL语句让服务器去执行的即可，比如删除一条记录，我们直接发送：

DELETE FROM Table Where ID = 1

但在ADO.NET下，则是：

aRow = dt.Select("ID = 1") '指定要删除哪行
aRow(0).Delete() '执行删除

如何将两者统一，就是MySqlCommandBuilder要做的事情了，它自动将ADO.NET语句翻译成远程MySQL服务器能“听得懂”的T-SQL语句，如果取消这行，直接就报错了。针对不同的数据库，都有它对应的SqlCommandBuilder。如本文所说的MySqlCommandBuilder（MySQL），或者OracleCommandBuilder （Oracle），又或者没有前缀的SqlCommandBuilder（对应MSSQL）。

SQLCommandBuilder，是一个傻瓜化的构建工具，它将ADO.NET的语句翻译成“常规”的T-SQL语句放到服务器上去执行，用户要做的仅仅是加上这行代码即可，其余的增删改命令由系统自动生成，尽管它很方便，但局限性也很大。这在单表情况下基本没有问题，但如果是多表，这种自动构建的方式就又不好使了，下面请看例子：

首先引用前面的代码，传送两个表到Dataset:

        Dim myConnectionString As String = "server=服务器IP;uid=用户名;pwd=密码;database=数据库名;charset=utf8;Pooling=True"
        Dim cn As New MySqlClient.MySqlConnection
        Dim da As New MySqlDataAdapter("SELECT * From Table1;SELECT * From Table2", cn) '这是多表的Select语句，中间用分号隔开
        Dim ds As New DataSet
        Dim dt1 As New DataTable
        Dim dt2 As New DataTable
        cn.ConnectionString = myConnectionString
        cn.Open()
        da.Fill(ds, "Table") '从服务器上将数据搬运到本机，表名随意，可以和远程服务器一致，也可以不一致
        dt1 = ds.Tables("Table") '第1个表
        dt2 = ds.Tables("Table1") '第2个表

然后对dt1或者dt2做一些变化，比如每一个表都删除一条数据：
最后用Update回写到服务器：

     Dim aRow() As DataRow
     aRow = dt1.Select("ID = 1") '删除dt1里的一条数据
     aRow(0).Delete() '执行删除
     da.Update(ds, "Table") '通知服务器更新

没有问题，第一个表（表名Table），成功地删除了一条记录，然后咱们再试着删除第二个表（表名Table1）的一条记录：

     aRow = dt2.Select("ID = 1") '删除dt2里的一条数据
     aRow(0).Delete()
     da.Update(ds, "Table1")

那么问题就来了，系统直接报错：【当传递具有已删除行的 DataRow 集合时，Update 要求有效的 DeleteCommand。】
尽管看上去代码并没有任何问题，但事实上，在执行Update这一行指令的时候，SqlCommandBuilder就开始进行翻译工作了，即向服务器发送了：

Delete From Table Where ID = 3

然而，在执行第二个表的时候，系统并不会“智能”地将第二个表的删除指令自动翻译成：

        Delete From Table1 Where ID = 3

这是因为通过SqlCommandBuilder自动构建的翻译机制，只能傻瓜化地为第一个表服务，当我们存在多个表的时候，它就不知道该怎么工作了。

这时候我们需要手工搭建“T-SQL翻译”平台，即使用SqlCommand。
DataAdapter里面有四个属性与之相关，即:

DataAdapter.SelectCommand
DataAdapter.InsertCommand
DataAdapter.UpdateCommand
DataAdapter.DeleteCommand
分别代表查、增、改、删。
我们可以手工为不同的表搭建不同的T-SQL语句，上述删除例子改成这样就没有问题了：

        Dim delRow() As DataRow
        Dim Command As New MySqlCommand
        Dim Parameter As MySqlParameter = Command.Parameters.Add("@ID", MySqlDbType.Int, 11, "ID") '创建一个T-SQL语句需要用到的条件变量，这里用ID

        Command.Connection = cn

        Command.CommandText = "DELETE FROM Table WHERE id = @ID" '注意这里，这条DELETE语句是针对Table的
        delRow = Table.Select("id = 3")
        delRow(0).Delete()
        da.DeleteCommand = Command 
        da.Update(ds, "Table") '通知服务器更新Table

        Command.CommandText = "DELETE FROM Table1 WHERE id = @ID" '而这条DELETE语句是针对Table1的
        delRow = Table1.Select("id = 3")
        delRow(0).Delete()
        da.DeleteCommand = Command
        da.Update(ds, "Table1")  '通知服务器更新Table1

使用SqlCommand（MySQL下，叫做MySqlCommand，其他数据库名称不同，但用法都一样），就可以针对每一个不同的表手工构建T-SQL语句，这里引用的是删除的例子，同样的可以是Select、Insert或者Update，这个例子非常简单，仅起到一个抛砖引玉的作用，手工构建SqlCommand非常灵活，可以适用在许多复杂场合，尽管傻瓜化的SqlCommandBuilder显得很方便，在调试阶段很不错，但在实际工作中，它的局限性大到几乎没有实用价值。

总的来说，在大多数情况下，我们需要通过SqlCommand来手工构建我们的T-SQL语句。

Written By Rockage

2016.09.27