'''' 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);

  }

protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)

{

上一页  [1] [2] [3] [4] [5]  下一页