数据库-ADONET-向数据库提交更新

向数据库提交更新

ADONET对于提交更新的功能和控制是前所未有的。但是，如何有效的行使这种控制和能力？

很多人在ADONET中使用CommandBuilder来生成更新逻辑，有时代码段会滴油警告“说应该生成自己的更新逻辑”，但是这些注释不会解释为什么和如何做。

对于如何使用ADONET提交更新了解的越多，那么生成自己的更新逻辑和（或）通过存储过程提交更新就越得心应手。

理解如何使用DataAdapter将DataSet中的挂起更改提交到数据库，学习如何以及什么适合使用工具，来节省时间，而不影响性能或控制。

你可以轻易的创建“非类型化”和“强类型”的DataSet，用来存储由DataAdapter返回的数据。

还可以修改DataSet的内容，产生数据更改。

DataAdapter对象公开了一个方法：“Update”，用它可以向数据库提交“挂起的更改”。

举一个例子：对于Northwind数据库的订单表，可以把订单数据放到一个DataSet实例中，用户做了一些修改，使用DataAdapter提供的Update方法进行提交，代码像这样：

Dim strConn,strSql as String

strConn=”Provider=SQLOLEDB;Data Source=...;Initial Catalog=Northwind;Trused_Connection=Yes;”

strSql=”select OrderID,ProductID,Quantity,UnitPrice from [Order Details] where OrderID=10503
order by ProductID”

Dim da as New OleDBDataAdapter(strSql,strConn)

Dim tbl as New DataTable(“订单明细”)

Da.Fill(tbl)’到这里才算完成了提取数据的工作

‘下面是修改订单代码

Tbl.Rows(0).Delete()

Tbl.Rows(1)(“Quantity”)=Cshort(tbl.Rows(1)(“Quantity”)*2)

Tbl.Rows.Add(new Object(){10503,1,24,18})

‘下面代码提交更新到数据库

Try

Da.Update(tbl)

Console.WriteLine(“提交更新 – 成功！”)

Catch ex as Exception

Console.WriteLine(“调用DataAdapter.Update方法抛出异常：” & vbCrLf & ex.Message)

End Try

这段代码会成功编译，但是，它不会将订单的更改成功的提交给数据库。

你会收到一个异常，提示“更新在传递带有删除的行的DataRow集合时需要有效的DeleteCommand”。

这种异常使人非常困惑，因为，原来的数据访问技术，比如ADO等，都包含自动提交改变的功能，ADONET虽然可以使用DataAdapter提交改变，但是DataAdapter不会包含自动提交更新所需要的逻辑（相关的Command对象）

如何向ADONET的DataAdapter添加必要的更新逻辑呢？有三种基本的选择：

l 编写自己的代码

l 让ADONET生成更新逻辑

l 使用“数据适配器配置向导”，这样的代码生成工具。

1 有关历史

先看一下ADO（ADONET的前身）如何运作这个过程？ADONET不会自动生成更新逻辑；但是，ADO会。

可以通过“查看ADO游标引擎自动提交更新的方式”，了解ADO的运作过程，进而，我们要了解，为什么ADONET要选择采用不同的方式？为什么要强迫开发者编写自己的更新逻辑？

ADO游标，支持与“ADONET的DataSet”类似的功能。

可以使用一个客户端的ADO Recordset，作为脱机数据缓存。另外，Recordset也是ADO向数据库提交更新的机制。

l 代码演示：获取订单的内容，修改订单，向数据库提交挂起（运行环境：经典VB，ADO2.x）

Dim strConn as String ,strSql as String

strConn=”Provider=SQLOLEDB;Data Source=...;Initial Catalog=Northwind;
Trusted_Connection=Yes;”

strSql=”select OrderID,ProductID,Quantity,UnitPrice from [Order Details] where OrderID=10503
order by OrderID”

Dim rs as ADODB.Recordset

Set rs=new ADODB.Recordset

Rs.CursorLocation=adUseClient

Rs.Open strSql,strConn,adOpenStatic,adLockBatchOptimistic,adCmdText

Rs.Delete

Rs.MoveNext

Rs.Fields(“Quantity”)=2* rs.Fields(“Quantity”)

Rs.Update

Rs.AddNew

Rs.Fields(“OrderID”)=10503

Rs.Fields(“ProductID”)=1

Rs.Fields(“Quantity”)=24

Rs.Fields(“UnitPrice”)=18

Rs.Update

Rs.UpdateBatch

Rs.Close

Cn.Close

1.1 使用ADO Recordset提交更新的好处

第一个好处：只需要最少的代码。代码没有更新逻辑，因为，ADO会在运行时自动生成这个逻辑，

这是另一个好处，ADO不需要开发者在程序中提供更新逻辑。可以无需理解并发、锁定或如何生成SQL Update查询，而使用“ADO游标引擎”的更新功能。

1.2 用ADO Recordset提交更新的缺点

缺点就是，“缓慢的性能”和“缺乏控制”，当然，这些问题不是大的不能接受，不过，它们是明显的缺陷。

为了能更好的了解这个问题，首先看一下：“ADO游标引擎”如何向数据库提交更改？

在调用Recordset对象的UpdateBatch方法时，ADO游标引擎扫描Recordset，找出被修改的行，并且将每一个被修改的行中的【更改】转化为对数据库中相应行进行修改的SQL查询。

这就跟，开发者自己生成Update、Insert、Delete方面的SQL语句，是类似的。（不过这里是ADO游标引擎完成的）

要观察这一点，你可以用SQL事件查看器，监视SQL对数据的调用。如果看到“一个对【带参数的批量查询SQL Server sp_executesql存储过程】的调用”，多半就是ADO游标引擎产生的提交查询了。这个存储过程调用的等价代码如下：

DELETE FROM [Order Details] WHERE OrderID=10503 AND ProductID=14

UPDATE [Order Details] SET Quantity=40

WHERE OrderID=10503 AND ProductID=65 AND Quantity=20

INSERT INTO [Order Details] (OrderId,ProductId,Quantity,UnitPrice)

VALUES(10503,1,24,18)

当然了，这是重新检查了代码中最初的查询和对Recordset所做的更改之后，才写出来的。

如果知道了数据的来源，那么将Recordset中的更改解释为SQL查询是相当简单的。

下一问题是，ADO游标引擎是如何发现这些信息的呢？

当ADO游标提取查询的结果时，它会向数据请求额外的元数据。

为了构建如前所示的UPDATE查询，游标引擎需要知道基表和结果表中每一列的列名，以及查询引用的表的主键信息。

“用ADO Field对象的Properties集合查询元数据”的代码如下：

With rs.Fields(“Quantity”)

Debug.Print “BaseTableName = “ & .Properties(“BaseTableName”)

Debug.Print “BaseColumnName= “ & .Properties(“BaseColumnName”)

Debug.print “KeyColumn = “ & .Properties(“KeyColumn”)

End With

由此看出，ADO游标引擎的更新功能的第一大主要缺点就是，性能。

ADO游标引擎，发出的查询（从数据库搜集表、列和主键信息等），造成了显著的性能损失。

而且，（非常不幸）ADO没有能力在代码中提供元数据，因此，必须在每次打开Recordset的时候，向数据库查询这些信息。

ADO光标引擎是一种“黑箱”技术。它，不让你定义自己的更新逻辑。这是第二大缺点。

它对更新逻辑只有很少甚至没有控制（不能选择通过存储过程调用类提交更新）

如果不喜欢ADO游标引擎产生的更新逻辑，只能是自己来编写代码。

2 用ADONET Command对象提交更新

ADO游标引擎是构建了带参查询，用来提交更新。

ADONET也可以构建具有同样功能的参数化查询。

ADONET的Command对象不会像ADO那样动态。

上面的过程，我们要构建：一个Command用于更新，一个Command用于插入，一个Command用于删除，它们的参数化查询语句如下：

UPDATE [Order Details]

SET OrderID=?,ProductID=?,Quantity=?,UnitPrice=?

WHERE OrderID=? AND ProductID=? AND Quantity=? AND UnitPrice=?

INSERT INTO [Order Details] (OrderID,ProductID,Quantity,UnitPrice)

VALUES(?,?,?,?)

DELETE FROM [Order Details]

WHERE OrderID=? AND ProductID=? AND Quantity=? AND UnitPrice=?

注意UPDATE和INSERT查询会为原始查询中的每一列都向数据库提交新值。这些查询在其WHERE语句中引用原始查询中的每一列。这种方式有缺点也有优点。

下面的代码演示构建3个带参Command对象，它们使用名为cn的OleDBConnection对象。

Private Function create_update_command() As OleDbCommand

Dim strsql As String

strsql = "update [Order Details] set OrderID=?,ProductID=?, Quantity=?,UnitPrice=? where OrderID=? and ProductID=? AND Quantity=? AND UnitPrice=?"

Dim cmd As New OleDbCommand(strsql, cn)

Dim pc As OleDbParameterCollection = cmd.Parameters

With pc

.Add("OrderID_new", OleDbType.Integer)

.Add("ProductID_new", OleDbType.Integer)

.Add("Quantity_new", OleDbType.SmallInt)

.Add("UnitPrice_new", OleDbType.Currency)

.Add("OrderID_orig", OleDbType.Integer)

.Add("ProductID_orig", OleDbType.Integer)

.Add("Quantity_orig", OleDbType.SmallInt)

.Add("UnitPrice_orig", OleDbType.Currency)

End With

Return cmd

End Function

Private Function create_Insert_command() As OleDbCommand

Dim strsql As String

strsql = "insert into [Order Details] (OrderID,ProductID,Quantity,UnitPrice VALUES(?,?,?,?)"

Dim cmd As New OleDbCommand(strsql, cn)

Dim pc As OleDbParameterCollection = cmd.Parameters

With pc

.Add("OrderID", OleDbType.Integer)

.Add("ProductID", OleDbType.Integer)

.Add("Quantity", OleDbType.SmallInt)

.Add("UnitPrice", OleDbType.Currency)

End With

Return cmd

End Function

Private Function create_Delete_command() As OleDbCommand

Dim strsql As String

strsql = "delete from [Order Details] where OrderID=? AND ProductID=? AND Quantity=? AND UnitPrice=?"

Dim cmd As New OleDbCommand(strsql, cn)

Dim pc As OleDbParameterCollection = cmd.Parameters

With pc

.Add("OrderID", OleDbType.Integer)

.Add("ProductID", OleDbType.Integer)

.Add("Quantity", OleDbType.SmallInt)

.Add("UnitPrice", OleDbType.Currency)

End With

Return cmd

End Function

使用我们的参数化Command对象，来进行提交更新，是非常直观的。

需要，在DataTable中检查修改的行，并确定存储在第一行中的修改类型（是更新、插入或者删除），然后用行的内容构成适当的命令参数。

在调用Command对象的ExecuteNonQuery方法，执行查询之后，可以用方法的返回值，确定更新是否成功，如果成功，就可以调用DataRow对象的AcceptChange方法；否则，可以设置DataRow对象的RowError属性，来表明更改尝试失败。

‘提交过程

Dim cmdUpdate As OleDbCommand = create_update_command()

Dim cmdInsert As OleDbCommand = create_Insert_command()

Dim cmdDelete As OleDbCommand = create_Delete_command()

Dim row As DataRow

Dim intRowsAffected As Integer

Dim dvrs As DataViewRowState

dvrs = DataViewRowState.ModifiedCurrent Or DataViewRowState.Deleted Or DataViewRowState.Added

'下面取被改变的行，并遍历，进行对应操作

For Each row In tbl.Select("", "", dvrs)

Select Case row.RowState

Case DataRowState.Modified

'submitUpdate方法用来更新一行数据

intRowsAffected = submitUpdate(row, cmdUpdate)

Case DataRowState.Deleted

intRowsAffected = submitDelete(row, cmdDelete)

Case DataRowState.Added

intRowsAffected = submitInsert(row, cmdInsert)

End Select

If intRowsAffected = 1 Then

row.AcceptChanges()

MsgBox("提交成功!")

Else

row.RowError = "Update attempt failed"

End If

使用DataTable对象的Select方法，用来在修改的行（通过第三个参数取得）中循环遍历。

之所以，不“使用For或For Each循环检查DataRow中的全体Rows集合”是因为，在成功提交“挂起删除”或“调用DataRow的AcceptChanges方法”时，这项DataRow就从其父集合中删除了，而由Select方法返回的DataRow对象数组，实质上包含“被修改的行的指针”，如果从DataTable的Rows集合删除项，代码仍然会成功执行。

下面是应用代码：

‘插入行,并提交的演示

tbl.Rows.Add(New Object() {10248, 70, 100, 10.5})

SubmitChangesByHand()

‘修改行并提交的演示

tbl.PrimaryKey = New DataColumn() {tbl.Columns("OrderID"), tbl.Columns("ProductID")}

Dim row As DataRow = tbl.Rows.Find(New Object() {10248, 70})

row("UnitPrice") = row("UnitPrice") * 2

SubmitChangesByHand()

‘删除行并提交的演示

tbl.PrimaryKey = New DataColumn() {tbl.Columns("OrderID"), tbl.Columns("ProductID")}

Dim row As DataRow = tbl.Rows.Find(New Object() {10248, 70})

row.Delete()

SubmitChangesByHand()

前面的参数化Command对象是专门针对初识查询的。

不过SubmitChangesByHand中的代码是具有一般性的。

实际上，上面只是手动构建了DataAdapter对象所提供的更新功能。

3 使用ADONET DataAdapter对象提交更新

DataAdapter对象，可以将查询的结果存储到DataTable中；还可以，向数据库提交DataSet挂起的更改。

在生成DataAdapter，用于向数据库提交更改的更新逻辑时，有三种选择：

n 用代码手工配置DataAdapter对象

n 在运行时，使用CommandBuilder对象。

n 在设计时，使用“数据适配器配置向导”。

4 手工配置DataAdapter对象

DataAdapter对象公开有，4个包含Command的属性。分别是：

n SelectCommand属性

用来填充数据

n UpdateCommand属性

用来提交数据修改

n InsertCommand属性

用来提交插入

n DeleteCommand属性

用来提交删除

这种体系结构，代表了与ADO对象模型技术相比，主要的更改。这里没有“黑箱”技术，因为DataAdapter中的Command对象都得开发者提供，所以开发者可以控制如何提交挂起的更改。

DataAdapter对象的Update方法是非常灵活的。提供给它的参数可以是：

u 一个DataSet

u 一个DataSet和一个表名

u 一个DataTable

u 一个DataRow数组对象

不管如何调用Update方法（指使用不同的重载版本），DataAdapter都将尝试通过正确的Command提交挂起更改。

前面代码中的SubmitChangesByHand过程中的所有工作，都可以通过调用DataAdapter.Update方法一次完成。

4.1 绑定参数-简介

SubmitChangesByHand过程并不复杂，它把繁琐的工作交给了下面的三个子函数。

这些函数根据此行被修改数据的类型给出相应查询的参数值。

下面，我们将使用DataAdapter对象，以同样的参数化查询方式，提交挂起的更改。

当向“DataAdapter对象的Command对象添加Parameter对象”时，这里要使用专门为DataAdapter更新而设计的ADONET Parameter对象的特殊属性：SourceColumn和SourceVersion。

上述属性，基本上就是将Parameter绑定到DataTable中的DataColumn上。

DataAdapter在执行查询前，使用这些属性，“确定如何填充Parameter对象的Value属性”，这与我们在上面代码中，用代码来完成的功能是类似的。

n 代码演示：下面创建一个参数化Command对象，设置该对象的SourceColumn和SourceVersion属性。SourceVersion属性的默认值是DataRowVersion.Current，所以如果需要将Parameter对象绑定到行的原始值，需要设置这个属性。

Private Function CreateDataAdapterUpdateCommand() As OleDbCommand

Dim strSql As String = "update [Order Details] set OrderID=?,ProductID=?,Quantity=?,UnitPrice=? where OrderID=? AND ProductID=? AND Quantity=? AND UnitPrice=?"

Dim cmd As New OleDbCommand(strSql, conn)

Dim pc As OleDbParameterCollection = cmd.Parameters

pc.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

pc.Add("ProductID_New", OleDbType.Integer, 0, "ProductID")

pc.Add("Quantity_New", OleDbType.SmallInt, 0, "Quantity")

pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice")

Dim p As OleDbParameter

p = pc.Add("Order_Orig", OleDbType.Integer, 0, "OrderID")

p.SourceVersion = DataRowVersion.Original

p = pc.Add("Product_Orig", OleDbType.Integer, 0, "ProductID")

p.SourceVersion = DataRowVersion.Original

p = pc.Add("Quantity_Orig", OleDbType.SmallInt, 0, "Quantity")

p.SourceVersion = DataRowVersion.Original

p = pc.Add("UnitPrice_Orig", OleDbType.Currency, 0, "UnitPrice")

p.SourceVersion = DataRowVersion.Original

Return cmd

End Function

Private Function CreateDataAdapterInsertCommand() As OleDbCommand

Dim str As String = "insert into [Order Details] (OrderID,ProductID,Quantity,UnitPrice) VALUES(?,?,?,?)"

Dim cmd As New OleDbCommand(str, conn)

Dim pc As OleDbParameterCollection = cmd.Parameters

pc.Add("OrderID", OleDbType.Integer, 0, "OrderID")

pc.Add("ProductID", OleDbType.Integer, 0, "ProductID")

pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity")

pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice")

Return cmd

End Function

Private Function CreateDataAdapterDeleteCommand() As OleDbCommand

Dim strSql As String

strSql = "delete from [Order Details] where OrderID=? AND ProductID=? AND Quantity=? AND UnitPrice=?"

Dim cmd As New OleDbCommand(strSql, conn)

Dim pc As OleDbParameterCollection = cmd.Parameters

Dim p As OleDbParameter

p = pc.Add("OrderID", OleDbType.Integer, 0, "OrderID")

p.SourceVersion = DataRowVersion.Original

p = pc.Add("ProductID", OleDbType.Integer, 0, "ProductID")

p.SourceVersion = DataRowVersion.Original

p = pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity")

p.SourceVersion = DataRowVersion.Original

p = pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice")

p.SourceVersion = DataRowVersion.Original

Return cmd

End Function

提交的过程就很简单了

da.InsertCommand = CreateDataAdapterInsertCommand()

da.UpdateCommand = CreateDataAdapterUpdateCommand()

da.DeleteCommand = CreateDataAdapterDeleteCommand()

da.Update(ds.Tables("Order Details"))

4.2 用存储过程提交更新

使用ADO，从数据中提交数据的过程中，无法通过Recordset对象的UpdataBatch方法，用存储过程提交更新。

但是，在ADONET对象架构中，“构建自己的Command，然后用DataAdapter来提交挂起的更新”这个过程中可以用存储过程来提交。

首先，需要定义存储过程，比如在Sql Server的Northwind数据库中定义一个给Orderdetails表修改、插入、删除行的存储过程。（用SQL查询分析器或调用名为CreateSprocs的过程）

USE Northwind

CREATE PROCEDURE spUpdateDetails

(@OrderID_New int, @ProductID_New int,

@Quantity_New smallint,@UnitPrice_New money,

@OrderID_Orig int,@ProductID_Orig int,

@Quantity_Orig smallint,@UnitPrice_Orig money)

UPDATE [Order Details]

SET OrderID=@OrderID_New,ProductID=@ProductID_New,

Quantity=@Quantity_New,UnitPrice=@UnitPrice_New

WHERE OrderID=@OrderID_Orig AND ProductID=@ProductID_Orig

AND Quantity=@Quantity_Orig AND UnitPrice=@UnitPrice_Orig

CREATE PROCEDURE spInsertDetail(@OrderID int,@ProductID int,@Quantity smallint,

@UnitPrice money)

INSERT INTO [Order Details]

(OrderID,ProductID,Quantity,UnitPrice)

VALUES ( @OrderID,@ProductID,@Quantity,@UnitPrice)

CREATE PROCEDURE spDeleteDetail

(@OrderID int,@ProductID int,@Quantity smallint,@UnitPrice money)

DELETE FROM [Order Details]

WHERE OrderID=@OrderID AND ProductID=@ProductID AND Quantity=@Quantity AND

UnitPrice = @UnitPrice

将输入的存储过程提交给SqlServer，下面编写Command对象以便在调用DataAdapter对象的Update方法时，自动调用这些存储过程。

Private Sub SubmitChangesViaStoredProcedures()

Da.UpdateCommand=CreateUpdateViaSPCommand()

Da.InsertCommand=CreateInsertViaSPCommand()

Da.DeleteCommand=CreateDeleteViaSPCommand()

Da.Update(tbl)

End Sub

Private Function CreateUpdateViaSPCommand() As OleDbCommand

Dim cmd As New OleDbCommand(“spUpdateDetail”,cn)

cmd.CommandType=CommandType.StoredProcedure

Dim pc as OleDbParameterCollection=cmd.Parameters

Pc.Add(“OrderID_New”,OledbType.Integer,0,”OrderID”)

Pc.Add(“ProductID_New”,OleDbType.Integer,0,”ProductID”)

Pc.Add(“Quantity_New”,OleDbType.SmallInt,0,”Quantity”)

Pc.Add(“UnitPrice_New”,OleDbType.Currency,0,”UnitPrice”)

Dim p As OleDbParameter

P=pc.Add(“OrderID_Orig”,OleDbType.Integer,0,”OrderID”)

p.SourceVersion=DataRowVersion.Original

p=pc.Add(“ProductID_Orig”,OleDbType.Integer,0,”ProductID”)

p.SourceVersion=DataRowVersion.Original

p=pc.Add(‘Quantity_Orig”,OleDbType.SmallInt,0,”Quantity”)

p.SourceVersion=DataRowVersion.Original

p=pc.Add(“UnitPrice_Orig",OleDbType.Currency,0,”UnitPrice”)

p.SourceVersion=DataRowVersion.Original

Return cmd

End Function

Private Function CreateInsertViaSPCommand() AS OleDbCommand

Dim cmd as New OleDbCommand(“spInsertDetail”,cn)

cmd.CommandType=CommandType.StoredProcedure

Dim pc as OleDbParameterCollection=cmd.Parameters

Pc.Add(“OrderID”,OleDbType.Integer,0,”OrderID”)

Pc.Add(“ProductID”,OleDbType.Integer,0,”ProductID”)

Pc.Add(“Quantity”,OleDbType.SmallInt,0,”Quantity”)

Pc.Add(“UnitPrice”,OleDbType.Currency,0,”UnitPrice”)

Return cmd

End Function

Private Function CreateDeleteViaSPCommand() As OleDbCommand

Dim cmd as New OleDbCommand(“spDeleteDetail”,cn)

cmd.CommandType=CommandType.StoreProcedure

Dim pc As OleDbParameterCollection=cmd.Parameters

Pc.Add(“OrderID”,OleDbType.Integer,0,”OrderID”)

Pc.Add(“ProductID”,OleDbType.Integer,0,”ProductUD”)

Pc.Add(“Quantity”,OleDbType.SmallInt,0,”Quantity”)

Pc.Add(“UnitPrice”,OleDbType.Currency,0,”UnitPrice”)

Return cmd

End Function

下面展示，用代码创建存储过程：

Private Sub CreateSprocs()

Dim cmd As OleDbCommand=cn.CreateCommand

Dim strSql as String

strSql=”create procedure spUpdateDetail ” & vbCrLf &_

“ (@OrderID_New int, @ProductID_New int, “ & vbCrLf & _

“@Quantity_New SmallInt, “ & vbCrLf &_

“@UnitPrice_New money, “ & vbCrLf &_

“@OrderID_Orig int, “ & vbCrLf &_

“@ProductID_Orig int, “ & vbCrLf &_

“@Quantity_Orig smallint, “ & vbCrLf &_

“@UnitPrice_Orig money) “ & vbCrLf &_

“AS “ & vbCrLf &_

“UPDATE [Order Details] “ & vbCrLf &_

“ SET OrderID=@OrderID_New, “ & vbCrLf &_

“ ProductID=@ProductID_New, “ & vbCrLf &_

“ Quantity=@Quantity_New, “ & vbCrLf &_

“ UnitPrice=@UnitPrice_New “ & vbCrLf &_

“ WHERE OrderID=@OrderID_Orig AND “ & vbCrLf &_

“ ProductID=@ProductID_Orig AND “ & vbCrLf &_

“ Quantity=@Quantity_Orig AND “ & vbCrLf &_

“ UnitPrice=@UnitPrice_Orig“

cmd.CommandText=strSql

cmd.ExecuteNonQuery()

strSql=”CREATE PROCEDURE spInsertDetail “ & vbCrLf & _

“ (@OrderID int,@ProductID int, @Quantity smallint,@UnitPrice money) “ & vbCrLf & _

“AS “ & vbCrLf & _

“ INSERT INTO [Order Details] “ & vbCrLf & _

“ (OrderID,ProductID,Quantity,UnitPrice) “ &vbCrLf & _

“ VALUES (@OrderID,@ProductID,@Quantity,@UnitPrice) ”

cmd.CommandText=strSql

cmd.ExecuteNonQuery()

strSql=”CREATE PROCEDURE spDeleteDetail “ & vbCrLf &_

“ (@OrderID int,@ProductID int, “ & vbCrLf &_

“ @Quantity smallint,@UnitPrice money) “ & vbCrLf &_

“ AS “ & vbCrLf &_

“ DELETE FROM [Order Details] “& vbCrLf &_

“ WHERE OrderID=@OrderID AND “ & vbCrLf &_

“ ProductID=@ProductID AND “ & vbCrLf &_

“ Quantity=@Quantity AND UnitPrice=@UnitPrice”

cmd.CommandText=strSql

cmd.ExecuteNonQuery()

End Sub

4.3 提供自己的更新逻辑

比较：“在代码中——提供自己的更新逻辑的【优点】和【缺点】“

优点：

最大的两个优点是：控制和性能。

ADONET的DataAdapter对象为开发者提供了比任何的Microsoft数据访问技术，更多的对更新逻辑的控制。从此不再局限于直接向表提交更新，开发者可以最终以RAD方式利用存储过程。

此外，因为不再依赖数据访问技术确定数据来源，所以可以认为所有的结果集都是可以更新的。

相对的，通过ADO游标引擎访问数据的时候，如果游标引擎不能收集到向数据库返回提交更改所需的元数据，那么就没办法在程序中提供这种信息。而使用ADONET技术，可以利用存储过程调用、对临时表的查询或联合的多查询的结果填充DataSet-或任何其他您认为合适的方式填充它——并且仍然可以向数据库提交更改。

在代码中提交更新逻辑可以改进应用程序的性能。使用ADO游标引擎提交更新的代码段包含的代码行数更少，但它需要ADO游标引擎查询数据以得到源表名、源列名和源表的主键信息。查询数据库系统表以获得元数据，再用这些元数据产生更新逻辑要比只是从本地代码中加载它要花费更多时间。

缺点

提供自己的更新逻辑的缺点，与ADO游标引擎方法的优点一样。

首先，提供自己的更新逻辑，需要大量代码。编写这些代码是很花时间的，并且相当繁琐。

另外，很多人不习惯编写自己的更新逻辑。比如：需要在查询中分隔表名？应该使用什么样的参数类型标记？哪一列应该出现在UpdateCommand和DeleteCommand的CommandText的WHERE语句中？对于包含日期、时间值的参数，正确的OleDbType设置是什么？

5 使用CommandBuilder对象生成更新逻辑

ADONET对象模型不只让你定义自己的更新逻辑，而且还用CommandBuilder对象提供了与ADO游标引擎类似的动态更新逻辑产生机制。

如果，实例化一个CommandBuilder对象，并将它与，一个DataAdapter对象相关联，那么CommandBuilder将尝试根据在DataAdapter对象的SelectCommand中包含的查询，来生成更新逻辑。

为了演示CommandBuilder如何工作？下面用它类为查询订单明细表的示例代码产生更新逻辑。

Imports System.Data

Imports System.Data.OleDb

Public Class Form1

Dim da As OleDbDataAdapter

Dim cb As OleDbCommandBuilder

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim str_sql As String = "select OrderID,ProductID,Quantity,UnitPrice from [Order Details] where OrderID=10503 order by ProductID"

da = New OleDbDataAdapter(str_sql, conn)

cb = New OleDbCommandBuilder(da)

Label1.Text = cb.GetInsertCommand.CommandText

Label2.Text = cb.GetDeleteCommand.CommandText

Label3.Text = cb.GetUpdateCommand.CommandText

End Sub

End Class

运行的结果就是，能够显示由CommandBuilder对象产生的，插入、删除、更新语句。

生成的CommandText语句，看起来与前面构建的提交查询很类似。

5.1 CommandBuilder如何生成更新逻辑

CommandBuilder用于生成Update、Insert、Delete查询的逻辑。

生成过程并不复杂，与ADO游标引擎类似。

CommandBuilder会查询数据库，以确定查询的结果的基表、列名以及关键信息。

CommandBuilder自动生成更新逻辑，需要满足以下条件：

u 查询只返回一个表中的数据

u 这个表有一个主键

u 主键包括在查询的结果中。

主键，确保CommandBuilder所生成的基于更新查询，最多只能更新一行。

CommandBuilder对象使用DataAdapter对象的SelectCommand属性，获取更新逻辑所需要的元数据。Command对象的ExecuteReader方法可以连同查询结果一起请求这种类型的元数据。

Dim str_sql As String = "select OrderID,ProductID,Quantity,UnitPrice from [Order Details] where OrderID=10503 order by ProductID"

Dim cmd As New OleDbCommand(str_sql, conn)

conn.Open()

Dim rdr As OleDbDataReader

rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo)

Dim tbl As DataTable = rdr.GetSchemaTable

rdr.Close()

conn.Close()

Dim r As DataRow

Dim c As DataColumn

For Each r In tbl.Rows

For Each c In tbl.Columns

ListBox1.Items.Add(c.ColumnName & " - " & r(c).ToString)

ListBox1.Items.Add("")

运行上面的代码，你会看到，CommandBuilder为了生成更新逻辑，需要取得的所有数据。

列名、基表和列的基列名、列是否是基表的主键的一部分、列是否包含一个长的数据类型（大文本或二进制）、浮点列的范围和精度，等等。

5.2 使用CommandBuilder的优点和缺点

将“由CommandBuilder生成的代码”与“自己产生更新逻辑的代码”进行比较，发现“使用CommandBuilder对象”有两个主要的优点。

u 使用CommandBuilder对象需要的代码更少。

u 在生成更新逻辑时，不需要对SQL的Update、Insert和Delete查询语法有深入的了解。

如果“在生成自己的更新逻辑”时遇到问题，CommandBuilder也可以提供帮助。——先利用CommandBuilder成功的生成更新逻辑，通过检查其Command对象的CommandText属性的值或其所构建的Parameter对象的不同属性，重新设计自己的更新逻辑。

在所有需要支持更新但是，设计时不知道查询结构的应用程序中，CommandBuilder都是很有用的。

与ADO游标引擎一样，CommandBuilder在运行时，自动生成更新逻辑。

因此，CommandBuilder也具有与ADO游标引擎的同样的问题和局限性。

CommandBuilder不能提供最好的运行时性能。解决办法是，可以在代码中提供自己的更新逻辑，这比CommandBuilder请求并处理生成类似更新逻辑所需要的时间少的多。

CommandBuilder不提供对“它自己产生的语句”进行控制。

不能指定想要使用的开放式并发的类型。

CommandBuilder也不能使用存储过程提交更新。

6 使用【数据适配器配置向导】生成更新逻辑

【数据适配器配置向导】可以在，设计时快速、高效地，产生更新逻辑。

6.1 检查DataAdapter的结构

举例说明：加入新的DataAdpater组件之后，进入属性窗口，深入其DeleteCommand属性，现在CommandText属性，单击右边的按钮，可以出现相应的【查询生成器】。

6.2 构建更新逻辑的选择

【向导】的【生成SQL语句】窗口，有一个【高级选项】按钮，单击它显示【高级SQL生成选项】对话框。

其中的选项有三个：

u 生成Insert、Update和Delete语句

不生成此三种语句（只用到提取数据功能），能够节省一些设计和运行的时间

u 使用开放式并发

在默认情况下，向导将所有非BLOB列添加到提交挂起更新和删除的查询的WHERE语句，如果取消选择【使用开放式并发】，向导在查询的WHERE语句中只包括主键列。

u 刷新数据库

一些数据库，如SQL Server，支持可以返回几行数据的成批查询。如果使用【向导】构建与此种数据库通讯的DataAdapter，那么【刷新数据集】是启用并选中的。选中此项，向导会产生，在提交改变后立即提取修改的行的内容的查询，这样新的服务器产生的值，如时间戳和自动递增值，将会在调用DataAdapter.Update之后出现在DataSet中。

6.3 使用存储过程提交更新

【向导】还可以帮助构建用存储过程向Sql Server数据库提交更新的DataAdapter对象。

在向导的【选择查询类型】页面，有【使用现有存储过程】选项。选中后，下一步可以为每一个Command对象选择存储过程。

注意：向导没有提供设置Parameter对象的SourceVersion属性的选项。因此，此属性默认值为Current。

企业版的Visual Studio.NET提供SQL查询，在【向导】中会为DataAdapter对象的各个Command产生新的存储过程。（在【向导】的【选中查询类型】页面选【创建新存储过程】）

6.4 使用向导的优缺点

向导比CommandBuilder提供更多选项，还能生成开发者不愿编写的繁琐代码。

尽管“向导产生更新逻辑，需要数据库的架构信息”与“CommandBuilder”一样，但它只在设计时请求一次信息，然后将新逻辑保存在代码中。因此，应用程序避免了使用CommandBuilder所带来的性能损失。

向导只提供了，OLEDB和SQL客户端.NET数据提供程序。

7 关于更新的其他注意事项

你需要了解更多的知识：比如，如何处理并发，以便不会意外覆盖另一位用户的更改？如何处理并发检查中的空值？在提交更新时，DataAdapter的TableMappings集合起什么作用？

7.1 开放式并发选项

“构建使用开放式并发向数据库提交更新的多用户数据库程序”时，开放式并发检查，很重要。

假设，两个用户请求同一行数据，然后都尝试更新同一行数据，接下来会发生什么？（这取决于开发者如何构造更新查询）

在SQL更新查询中有四种基本的开放式并发选项。

7.1.1 只包含主键列

在SQL的Update和Delete查询中，可以只包括【主键列】，这会创建出“后来居上”的更新方案。

上述，两个更新尝试都会成功。并且，最后一个更新会覆盖前一个更改。

方案的简单过程如下：

用户A提取一行

用户B提取一行

用户B修改此行并提交更改

用户A修改此行，并提交更改，覆盖了B的更改。

用户A甚至意识不到数据库这一行的内容，在最初查询和用户提交更改这段时间，已经发生改变。

CommandBuilder对象不提供这种开放式并发选择

【数据适配器配置向导】则提供。在【高级选项】中，取消选择【使用开放式并发】

7.1.2 在WHERE语句中包括所有列

CommandBuilder和向导的默认操作，都是在WHERE语句中包括：所有列。

使用这种逻辑，即可防止覆盖在代码获取行期间（或者代码尝试向数据库提交对这一行所做的挂起改变）内，另一个用户所做的更改。

举个例子：假设用户A和用户B获取同一行客户数据。

用户B对ContactName列做了改变并提交了更改。应用程序在基于查询的更新中的WHERE语句中包括所有列，所以Update查询形式如下：

UPDATE Customers

SET CustomerID=’ABCDE’,CompanyName=’Original Company Name’,

ContactName=’New Contact’,Phone=’800-555-1212’

WHERE CustomerID=’ABCDE’ AND

CompanyName=’Original Comany Name’ AND

ContactName=’Original Contact’ AND

Phone=’800-555-1212’

与此同时，用户A修改了同一行客户数据，改变了CompanyName列的值。

因为用户A在用户B提交之前取得的数据，因此更新语句如下：

UPDATE Customers

SET CustomerID=’ABCDE’,CompanyName=’New Company Name’,

ContactName=’Original Contact’,Phone=’800-555-1212’

WHERE CustomerID=’ABCDE’ AND

CompanyName=’Original Comany Name’ AND

ContactName=’Original Contact’ AND

Phone=’800-555-1212’

因为，A提交时数据库的ContactName值已经改变，表中没有能够满足WHERE语句的数据，因此，数据库不会修改任何行。DataAdapter查询数据（确定被修改行数），发现没有成功修改行，就相应标记DataRow。

这是CommandBuilder对象使用的并发选择。

适配器向导默认会启用这种并发选择。

注意：一般的，数据库不会让你执行对两个BLOB值的比较操作。

因为在BLOB列中可以存储许多兆字节的数据，对它们的比较即使可行，也非常低效。

代码生成工具（CommandBuilder和适配器向导）不会在基于查询的更新语句的？WHERE部分包括BLOB列。

7.1.3 包括主键和时间戳列

使用【时间戳】列，可以简化查询更新的WHERE语句。

SQL SERVER时间戳列，并不真正包含日期和时间信息。它只包含数据库中惟一的二进制数据。

定义SQL SERVER表上的时间戳列，在任何行的内容发生改变之时，SQL SERVER都会修改此行的时间戳值。

向Customers表中添加时间戳列，并修改查询如下：

UPDATE Customers

SET CustomerID=’ABCDE’,CompanyName=’Original Company Name’,

ContactName=’New Contact’,Phone=’800-555-1212’

WHERE CustomerID=’ABCDE’ AND TimestampColumn=0x00000000000CC

因为服务器在【每次更新一行时都会为时间戳产生新值】，因此，在更新语句的WHERE子句中使用【主键和时间戳列】的组合，可以保证不会覆盖另一位用户所做的更改。

大多数数据库系统都支持【类似时间戳】的数据类型。它们，有的使用惟一的二进制值，有的使用日期/时间值。可以通过【检查数据库系统的文档】来确定后端的数据类型，并了解如何迫使数据库在每次修改一行的内容时更新这个值。

目前，CommandBuilder和适配器配置向导，都【不支持】生成使用这种开放式并发策略的更新逻辑。

注意：SQL Server中，rowversion和时间戳数据类型是同义的。Sql Server文档建议你用rowversion关键词代替时间戳。

使用【主键+时间戳】可以得到简单的更新逻辑，并且每次尝试更新时数据库需要检查的列更少。

7.1.4 包括主键和被修改的列

默认情况下，ADO游标引擎在查询语句中的WHERE部分只包括【主键列和修改列的原始值】；在Update语句的SET部分也只包括修改的列。

在使用ADO游标引擎进行此种更新时多用户例子如下：假设用户A用户B同时取得同一行客户数据，他们修改了不同行的数据——用户A改变了CompanyName列，用户B改变了ContactName列。用户B首先提交了对ContactName列的挂起更改，用户B的Update语句如下：

UPDATE Customers

SET ContactName=’New Contact’

WHERE CustomerID=’ABCDE’ AND ContactName=’Original Contact’

然后用户A用下面的Update语句提交更新：

UPDATE Customers

SET CompanyName=’New Company Name’

WHERE CustomerID=’ABCDE’ AND

CompanyName=’Original Company Name’

数据库执行效果：

公司名=‘新公司名’；联系人姓名=‘新联系人’

两个更新都成功了，并且由用户A的更新没有覆盖用户B的更新。

ADONET DataAdapter的结构，不会依靠这种更新策略，因为，它需要根据被修改的列来改变查询的结构，DataAdapter提供了以行为基础的更新语句参数值，但是它不会修改参数查询的实际结构。

理论上，可以编写代码更改相应Command对象结构的代码，并在处理DataAdapter对象的RowUpdating事件时使用这段代码。【这种更新策略有其好处，但是代价过高，超过优点】

7.2 使用空值

Northwind数据库中的Customers表包含了一个Region列，可以接受15个字符的字符串，也可以接受空值（Null）。

一般开发者都会尝试用下面的查询取得空值的行：

Select CustomerID,CompanyName,ContactName,Phone from Customers where Region=NULL

如果在ADONET中使用此种查询，或者在SQL查询分析器中运行它，则将返回零行。

在“数据库世界”中，空值是一种特殊情况，特别是当在查询中比较空值的时候，根据ANSI标准，不能使用等号运算符“=”来比较空值，必须使用IS NULL。

上面的语句需要写成：

Select CustomerID,CompanyName,ContactName,Phone from Customers where Region IS NULL

空值与用DataAdapter向数据库提交更新有什么关系呢？？？

在前面，用于提交Order Details表中被修改行的Command Text是这样：

UPDATE [Order Details]

SET OrderID=?,ProductID=?,Quantity=?,UnitPrice=?

WHERE OrderID=? AND ProductID=? AND Quantity=? AND UnitPrice=?

如果查询中有引用列接受空值，会怎样？假设，有一行的Quantity列是空值，现在希望改为20。那么，实际的查询语句如下

UPDATE [Order Details]

SET OrderID=12345, ProductID=1, Quantity=20, UnitPrice=18

WHERE OrderID=12345 AND ProductID=1 AND Quantity=Null AND UnitPrice=18

这时，由于WHERE语句中的Quantity=Null，查询会修改零行。（因为，数据库所需行的Quantity列为空，但是Null=Null值为False，因此，数据库不会修改行）

怎么让WHERE语句能够适应【并发检查中的空值】？如果是特定的列接受空值，可以这样修改：

将ColumnName=?

修改为

（ColumnName=？OR （（ColumnName IS NULL） AND （？ IS NULL））

这就使得“当列和参数是相等的非空值”或者“它们都是空值”时，语句的值为True。

如果使用【适配器配置向导】构建更新逻辑，假设ContactName和Phone列可接受空值，你会发现向导产生的查询是带有相应的空值检查的。

总结：

【数据适配器配置向导】在“产生更新逻辑”方面做的很好。即使你要生成自己的逻辑，看一下向导产生的代码对你的工作也有双重检查的好处。

7.3 在事务中提交更新

将更新封装到一个事务中，可以使更新作为整体提交。

但是，DataAdapter未公开Transaction属性。

实际上，DataAdapter并不提交更新，它只是将工作上交给UpdateCommand、InsertCommand和DeleteCommand属性对应的Command对象。Command对象公开了Transaction属性。

用DataAdapter提交带事务的更新，必须设置对应Command对象的Transaction属性。

‘设置时

‘创建新事务

Dim txn As OleDbTransaction=cn.BeginTransaction()

‘设置DataAdapter的Command的Transaction属性

Da.UpdateCommand.Transaction=txn

Da.InsertCommand.Transaction=txn

Da.DeleteCommand.Transaction=txn

‘使用时

try

‘提交更改

Da.Update(tbl)

‘接受更新并关闭连接

Txn.Commit()

Cn.Close()

Catch e as exception

End try

使用CommandBuilder对象生成更新逻辑，由于CommandBuilder在实例化的时候，并没有实际地产生更新语句，再以事务方式提交就比较困难。

在调用DataAdapter.Update的时候，CommandBuilder对象将使用DA的SelectCommand取得数据库的元数据，如果SelectCommand没有跟事务对象关联，CommandBuilder会引发异常。

解决办法就是给SelectCommand也关联事务对象：

Da.SelectCommand.Transaction=txn

不过，这样又使得CommandBuilder提取数据库架构信息也在事务中进行了，一般来说，我们会希望事务【尽可能少的】接触数据库中的数据。怎么办？

更恰当的选择就是，迫使CommandBuilder在开始事务之前就生成更新逻辑，比如，可以先通过调用CommandBuilder的GetUpdateCommand方法，取得Update语句逻辑。

7.4 使用TableMapping集合

DataAdapter的TableMapping集合可以影响其Fill方法填充DataSet的过程。

如下：

Dim strConn,strSql As String

strConn=””

strSql=”select OrderID,ProductID,Quantity,UnitPrice from [Order Details] where OrderID=10503”

Dim da as New OleDbDataAdapter(strSql,strConn)

Dim da as new DataSet()

‘注意这里

Da.TableMappings.Add(“Table”,”Order Details”)

Da.Fill(ds) ’填充的表名直接被指定为Table

在提交更新时，TableMappings集合有类似上面的效果。

如果在DataAdapter对象的Update方法中只提供一个DataSet对象，那么DataAdapter就会依靠其TableMappings集合来确定究竟要检查DataSet中的哪一个DataTable。

‘前面略

...

Dim da as new OleDbDataAdapter(strSql,strConn)

Da.TableMappings.Add(“Table”,”OrderDetails”)

‘定义更新逻辑

Dim ds as new DataSet()

Da.Fill(ds)

‘此处修改一些数据，略

Da.Update(ds)

如果没有填充DataAdapter对象的TableMappings集合，那么你就必须使用【可接受DataSet和一个表名称】的Update方法，或者使用可接受数据表DataTable对象的Update方法。

‘略

Dim da as new OleDbDataAdapter(strSql,strConn)

‘定义更新逻辑

Dim ds as new DataSet()

Da.Fill(ds,”Order Details”)

‘修改内容,略

Da.Update(ds,”Order Details”)

‘第二种

...

Dim da As New OleDbDataAdapter(strSql,strConn)

‘定义更新逻辑

Dim tbl as new DataTable()

Da.Fill(tbl)

‘修改内容，略

Da.Update(tbl)

有一条【基本原则】：“你应该使用相同的逻辑，来控制DataAdapter.Fill和DataAdapter.Update中，所引用的DataTable（数据表）”

7.5 最佳更新方式

ADO.NET为开发者提供了许多提交更改的选择，

可以在运行时使用CommandBuilder来生成更新逻辑

可以在代码中提供自己的更新逻辑，通过Insert、Update、Delete查询或存储过程调用提交更改。

可以在设计时使用【数据适配器配置向导】

哪一种选择更适合你呢？

答案，取决于，应用程序的参数。

你或许可以，通过配置DataAdapter对象，为通过存储过程调用提交更新，得到最佳性能。不过，如果必须使用Access这样不支持存储过程的数据库，这种方案就不适用了。这时，你最好使用Insert、Update、Delete查询。

一般而言，建议在可能的情况下，通过存储过程来提交更新。

但是，如果使用多种后端数据库，则应该使用基于查询的更新。

不管选择哪一种方式，都要生成自己的更新逻辑，注意【避免在运行时生成更新逻辑】。

还有，要记住的一点是，除非绝对必要，否则不要在应用程序里使用CommandBuilder对象。

posted @ 2008-12-18 09:38 怒杀神阅读(750) 评论(0) 编辑收藏举报

刷新页面返回顶部

怒杀神殿

这里是我家

数据库-ADONET-向数据库提交更新

公告