[ADO.NET]DataGridView实时更新数据库注意事项

本帖最后由 rockage 于 2016-10-28 21:36 编辑

1. 首先，在创建SelectCommand的时候，要将主键包含进来。
如果采用Select Column1，Column2 From Tables 这样的结构，而里面未包含主键列则会报错，简单地，用Select * From Tables这种结构就OK了。

        '用于Select的T-SQL指令
        da.SelectCommand = New MySqlCommand("Select * From ld_products_price where product_id = " & product_id & " order by id")
        da.SelectCommand.Connection = cn

2. 然后要创建与之对应的UpdateCommand，这属于常规操作。

SQL = "UPDATE `ld_products_price` SET `vendor_id`=@vendor_id,`price`=@price WHERE `ID` = @id"
        da.UpdateCommand = New MySqlCommand(Sql)
        da.UpdateCommand.Parameters.Add("@id", MySqlDbType.Int16, 11, "id")
        da.UpdateCommand.Parameters.Add("@product_id", MySqlDbType.Int16, 11, "product_id")
        da.UpdateCommand.Parameters.Add("@vendor_id", MySqlDbType.Int16, 11, "vendor_id")
        da.UpdateCommand.Parameters.Add("@price", MySqlDbType.Int16, 11, "price")
        da.UpdateCommand.Connection = cn

3. 要点是：DataGridView控件对于主键列（比如ID），必须是可见的，如果主键列不包含到DataGridView视图内，则报错:
the UpdateCommand affected 0 of the expected 1 records. 无法更新。
PS：如果你的UI内不想显示主键列（一般而言ID列都是多余的），可以用将Columns的.Visible设为False，即可隐藏。例如：DataGridView1.Columns(0).Visible = False


4. 对于包含外键的列处理，这个稍稍有点复杂，首先从SelectCommand开始：

da.SelectCommand = New MySqlCommand("Select id, 
                                            (SELECT `nickname` from `ld_vendor` where ld_products_price.vendor_id = ld_vendor.id) as class, 
                                            price From ld_products_price where product_id = " & product_id & " order by id")
da.SelectCommand.Connection = cn

这里面我们需要一个Select嵌套，即用外键的Vendor_ID在另一个表里去查询它所代表的“名字”（Nickname），如果不进行这个查询操作，那么将会显示一大堆乱七八糟不知所云的ID编号，在UI里这显然是不可接受的。在这里我们用了一个“as class” ， 即这一列在本地数据库里以“Class”为列名。

然后，在回写进数据库的时候，又需要将我们看到的“名字”还原成它所代表的“编号”（ID），这需要在UpdateCommand里进行操作：

SQL = "UPDATE `ld_products_price` SET `vendor_id`=(SELECT `id` from `ld_vendor` where ld_vendor.nickname = @vendor_nickname),
                                      `price`=@price WHERE `ID` = @id"
        da.UpdateCommand = New MySqlCommand(Sql)
        da.UpdateCommand.Parameters.Add("@id", MySqlDbType.Int16, 11, "id")
        da.UpdateCommand.Parameters.Add("@vendor_nickname", MySqlDbType.VarChar, 11, "class")
        da.UpdateCommand.Parameters.Add("@price", MySqlDbType.Int16, 11, "price")
        da.UpdateCommand.Connection = cn

UpdateCommand里面同样有一个Select嵌套，这一行跟上面那行的作用刚好相反，即将“Nickname”还原为“ID”，注意这里在变量设定上是有一点讲究的，这一行：
da.UpdateCommand.Parameters.Add("@vendor_nickname", MySqlDbType.VarChar, 11, "class")
远程数据库里Vendor_ID是一个INT型数字列，而在本地数据库里他则被转换为一个名叫Class的Varchar类型的字符串列，因此在变量选择上，我们应该跟着本地数据库走，将它设为Varchar类型，并指明这个变量绑定在“class”列上。

最后，常规的调用一下DataAdapter的Update方法，例如： da.Update(ds) ，DataGridView的数据就会实时回写进远程数据库，实现了所谓“所见即所得”的功能。

本帖最后由 rockage 于 2016-12-1 22:18 编辑

[附件]

一张图看懂本地数据库 和 远程数据库的 关系，他们之间通过SQLCommand的 Parameters （参数）来建立的映射关系，
在这个场合，使用SQL参数传入值的方式并不仅仅是为了防止SQL灌注攻击，而是必须要用它来建立远程和本地数据列之间的互动。

当然，两者也可以混用，比如一些参数与本地数据表没有联系，也可以直接将值插入进入，如上图也可以改成这样：

        SQL = "insert into ld_stockin (`id`,`item_id`,`vendor_id`,`price`,`freight`,`sum`,`amount`,`date`) 
                        values 
                    (null," & add_item_id & "," & vid & ",@price,@freight,@sum,@amount,@date)"

请注意item_id 和 vendor_id 这两个参数直接使用了两个本地普通变量，
在本地数据表的列与远程数据表的列不完全一致的时候，
可以采用这种直接的方法。