[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID", nwindConn)
' The Update command checks for optimistic concurrency violations in the WHERE clause.
custDA.UpdateCommand = New SqlCommand("UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " & _
"WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", nwindConn)
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 30, "CompanyName")
' Pass the original values to the WHERE clause parameters.
Dim myParm As SqlParameter
myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
myParm = custDA.UpdateCommand.Parameters.Add("@oldCompanyName", SqlDbType.NVarChar, 30, "CompanyName")
myParm.SourceVersion = DataRowVersion.Original
' Add the RowUpdated event handler.
AddHandler custDA.RowUpdated, New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
Dim custDS As DataSet = New DataSet()
custDA.Fill(custDS, "Customers")
' Modify the DataSet contents.
custDA.Update(custDS, "Customers")
Dim myRow As DataRow
For Each myRow In custDS.Tables("Customers").Rows
If myRow.HasErrors Then Console.WriteLine(myRow(0) & vbCrLf & myRow.RowError)
Next
Private Shared Sub OnRowUpdated(sender As object, args As SqlRowUpdatedEventArgs)
If args.RecordsAffected = 0
args.Row.RowError = "Optimistic Concurrency Violation Encountered"
args.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID", nwindConn);
// The Update command checks for optimistic concurrency violations in the WHERE clause.
custDA.UpdateCommand = new SqlCommand("UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " +
"WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", nwindConn);
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 30, "CompanyName");
// Pass the original values to the WHERE clause parameters.
SqlParameter myParm;
myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;
myParm = custDA.UpdateCommand.Parameters.Add("@oldCompanyName", SqlDbType.NVarChar, 30, "CompanyName");
myParm.SourceVersion = DataRowVersion.Original;
// Add the RowUpdated event handler.
custDA.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
// Modify the DataSet contents.
custDA.Update(custDS, "Customers");
foreach (DataRow myRow in custDS.Tables["Customers"].Rows)
{
if (myRow.HasErrors)
Console.WriteLine(myRow[0] + "\n" + myRow.RowError);
}
UPDATE Table1 Set Col1 = @NewCol1Value,
Set Col2 = @NewCol2Value,
Set Col3 = @NewCol3Value
WHERE Col1 = @OldCol1Value AND
Col2 = @OldCol2Value AND
Col3 = @OldCol3Value
只要初始值匹配数据库中的值,就会执行更新。如果已修改某个值,由于 WHERE 子句找不到匹配项,更新将不会修改该行。
如果数据源中的列允许空值,则可能需要扩展 WHERE 子句,以查找本地表和数据源中的匹配空引用。例如,以下 UPDATE 语句验证本地行中的空引用仍匹配数据源中的空引用,或者本地行中的值匹配数据源中的值。
UPDATE Table1 Set Col1 = @NewVal1
WHERE (@OldVal1 IS NULL AND Col1 IS NULL) OR Col1 = @OldVal1
当使用开放式并发模型时,也可以选择应用限制较少的条件。例如,如果只在 WHERE 子句中使用主键列,那么无论自最后一次查询以来是否已更新其他列,数据都将被改写。也可以只将 WHERE 子句应用于特定列,除非自最后一次查询特定字段以来已将其更新,否则数据也会被改写。