Rheinwerk Computing :: Visual Basic 2008 – 27.2 Manuell gesteuerte Aktualisierungen

27.2 Manuell gesteuerte Aktualisierungen

Nun wollen wir die Aktualisierung manuell codieren, also auf den DbCommandBuilder durchweg verzichten. Im Wesentlichen besteht dann unsere Aufgabe darin, selbst definierte Command-Objekte bereitzustellen, deren SQL-Abfragestatements sich an den aktuellen Forderungen orientieren. Hier können Sie zwei Ansätze verfolgen:

Sie überlassen weiterhin dem DataAdapter die Aufgabe, in der DataTable nach geänderten Datenzeilen zu suchen und diese der Reihe nach an die Datenbank zu übermitteln.

Sie rufen für jede geänderte Datenzeile die ExecuteNonQuery-Methode des Command-Objekts auf. Dazu müssen Sie Code bereitstellen, der alle geänderten Datenzeilen einer DataTable erfasst.

Im Folgenden werde ich Ihnen beide Alternativen vorstellen.

27.2.1 Manuelles Aktualisieren mit dem DataAdapter

Wollen wir der Update-Methode des DataAdapters den Prozess der Aktualisierung überlassen, müssen wir nur die passenden Command-Objekte erzeugen, die die erforderlichen Aktualisierungsbefehle definieren. Diese werden allgemeingültig formuliert, beschreiben also Parameter, die mit den Daten der betroffenen Datenzeile gefüllt werden müssen. Das ist die Logik, die wir codieren müssen.

Die Update-Methode sucht in einer DataTable nach den Datenzeilen, deren DataRowState nicht Unchanged ist. Für jede solche Datenzeile greift sie auf das entsprechende Command-Objekt zurück, weist die entsprechenden Parameter zu und schickt den konkreten Änderungsbefehl an die Datenbank.

Damit ist die Lösung schon skizziert. Wir stellen eigene Command-Objekte zur Verfügung – nennen wir sie updateCommand, deleteCommand und insertCommand – und weisen sie den entsprechenden Eigenschaften des DataAdapters zu:

<DbDataAdapter>.UpdateCommand = updateCommand 
<DbDataAdapter>.InsertCommand = insertCommand 
<DbDataAdapter>.DeleteCommand = deleteCommand

Damit sind alle Forderungen erfüllt, die die Methode Update des DataAdapters stellt. Nun wollen wir uns den Code an einem konkreten Beispiel ansehen.

Beispielprogramm

In einer Konsolenanwendung bezieht der Anwender die Spalten ProductID, ProductName, UnitPrice und Discontinued der Tabelle Products (sie ist in der Beziehung zur Tabelle Order Details die Mastertabelle). Zur Laufzeit ist es möglich, Datenzeilen hinzuzufügen oder zu ändern. Änderungen sollen nur dann konfliktfrei möglich sein, wenn ein zweiter Anwender nicht zwischenzeitlich den Produktnamen geändert hat. Um dieser Forderung zu genügen, muss in der WHERE-Klausel des UPDATE-Statements die Spalte ProductName angegeben werden.

Function CreateUpdateCommand(ByVal con As DbConnection) As DbCommand 
  Dim cmd As DbCommand = New SqlCommand() 
  cmd.CommandText = "UPDATE Products " & _ 
    "SET ProductName=@Name,UnitPrice=@Preis,Discontinued=@Conti " & _ 
    "WHERE ProductID=@ID AND ProductName=@OrgName" 
   cmd.Connection = con

  ' die Parameter der Parameters-Auflistung hinzufügen 
  Dim col As DbParameterCollection = cmd.Parameters 
  col.Add(New SqlParameter("@Name", SqlDbType.VarChar, 40, "ProductName")) 
  col.Add(New SqlParameter("@Preis", SqlDbType.Money, 8, "UnitPrice")) 
  col.Add(New SqlParameter("@Conti", SqlDbType.Bit, 1, "Discontinued")) 
  col.Add(New SqlParameter("@ID", SqlDbType.Int, 4, "ProductID")) 
  Dim param As DbParameter = New SqlParameter( _ 
    "@OrgName", SqlDbType.VarChar, 40, "ProductName") 
  col.Add(param) 
  param.SourceVersion = DataRowVersion.Original 
  Return cmd 
End Function

Im Kern beschreibt die Methode einen SQL-UPDATE-Befehl, der parametrisiert spezifiziert ist. Erst die Update-Methode des DataAdapters trägt die Daten der zu aktualisierenden Datenzeile in die Parameter ein.

UPDATE Products 
SET ProductName=@Name, UnitPrice=@Preis, Discontinued=@Conti 
WHERE ProductID=@ID AND ProductName=@OrgName

Die Parameter @Name, @Preis, @Conti und @ID werden mit den Werten aus DataRowVersion.Current gefüllt. Dahinter verbergen sich die vom Anwender vorgenommenen Änderungen an der betroffenen Datenzeile. Da dies der Standard ist, muss die Version nicht extra angegeben werden.

Etwas anders verhält es sich mit dem Parameter @OrgName. Er ist Teil des Suchmusters, mit dem in der Originaldatenbank nach der zu aktualisierenden Zeile gesucht wird. Ihm muss daher der von der Datenbank bezogene Originalwert übergeben werden, der in DataRowVersion.Original zu finden ist. Dazu stellt uns das DbParameter-Objekt die Eigenschaft SourceVersion zur Verfügung, der wir die zu berücksichtigende Version übergeben.

param.SourceVersion = DataRowVersion.Original

Wie Sie weiter oben schon gesehen haben, weisen wir die Referenz des erzeugten Command-Objekts der Eigenschaft UpdateCommand des DataAdapters zu, der automatisch die Parameter mit konkreten Werten belegt, wenn er auf eine geänderte Datenzeile trifft.

Ähnlich sehen auch die Methoden zum Löschen und Hinzufügen aus.

Function CreateDeleteCommand(ByVal con As DbConnection) As DbCommand 
  Dim cmd As DbCommand = New SqlCommand() 
  cmd.CommandText = "DELETE FROM Products WHERE ProductID=@ID" 
  cmd.Connection = con

  ' die Parameter der Parameters-Auflistung hinzufügen 
  Dim param As DbParameter = _ 
    New SqlParameter("@ID", SqlDbType.Int, 4, "ProductID")

  cmd.Parameters.Add(param) 
  param.SourceVersion = DataRowVersion.Original 
  Return cmd 
End Function

Function CreateInsertCommand(ByVal con As DbConnection) As DbCommand 
  Dim cmd As DbCommand = New SqlCommand() 
  cmd.CommandText = "INSERT INTO Products " & _ 
    "(ProductName,UnitPrice,Discontinued) Values(@Name,@Preis,@Conti)" 
  cmd.Connection = con

  ' die Parameter der Parameters-Auflistung hinzufügen 
  Dim col As DbParameterCollection = cmd.Parameters 
  col.Add(New SqlParameter("@Name", SqlDbType.VarChar, 40, "ProductName")) 
  col.Add(New SqlParameter("@Preis", SqlDbType.Money, 8, "UnitPrice")) 
  col.Add(New SqlParameter("@Conti", SqlDbType.Bit, 1, "Discontinued")) 
  Return cmd 
End Function

Die manuelle Aktualisierung unter Zuhilfenahme des DataAdapters unterscheidet sich kaum von der Formulierung mit dem CommandBuilder. Anstatt den CommandBuilder zu erzeugen, weisen Sie den Eigenschaften InsertCommand, DeleteCommand und UpdateCommand des DataAdapters die passenden Command-Objekte zu und rufen anschließend Update auf. Das ist bereits alles. Das folgende Beispielprogramm zeigt Ihnen die Vorgehensweise. Dabei wird ein Datensatz hinzugefügt und der Artikel Chai umbenannt.

'...\ADO\Aktualisierung\Manuell.vb

Option Strict On 
Imports System.Data.Common, System.Data.SqlClient 
Namespace ADO 
  Module Manuell 
    Sub Update() 
      Dim con As DbConnection = New SqlConnection() 
      con.ConnectionString = "Data Source=(local);" & _ 
          "Initial Catalog=Northwind;Integrated Security=sspi" 
      Dim cmd As DbCommand = New SqlCommand() 
      cmd.CommandText = "SELECT ProductID, ProductName, " & _ 
        "UnitPrice, Discontinued FROM Products" 
      cmd.Connection = con 
      Dim ds As New DataSet() 
      Dim da As DbDataAdapter = New SqlDataAdapter() 
      da.SelectCommand = cmd 
      da.FillSchema(ds, SchemaType.Source) 
      da.Fill(ds)

      'Datenzeilen editieren 
      For Each row As DataRow In ds.Tables(0).Rows 
        If row("ProductName").ToString() = "Chai" Then _ 
          row("ProductName") = "Kräutertee" 
      Next

      ' Datenzeile hinzufügen 
      Dim neu As DataRow = ds.Tables(0).NewRow() 
      neu("ProductName") = "Kuchen" : neu("UnitPrice") = 11.3 
      neu("Discontinued") = 0 
      ds.Tables(0).Rows.Add(neu)

      ' Festlegen der Command-Objekte 
      da.InsertCommand = CreateInsertCommand(con) 
      da.UpdateCommand = CreateUpdateCommand(con) 
      da.DeleteCommand = CreateDeleteCommand(con) 
      da.Update(ds)

      Console.WriteLine("Aktualisierung beendet") 
      Console.ReadLine() 
    End Sub 
    Function CreateUpdateCommand(ByVal con As DbConnection) As DbCommand ... 
    Function CreateDeleteCommand(ByVal con As DbConnection) As DbCommand ... 
    Function CreateInsertCommand(ByVal con As DbConnection) As DbCommand ... 
    ... 
  End Module 
End Namespace

27.2.2 Aktualisieren mit ExecuteNonQuery

Wenn Sie Aktualisierungen unter Zuhilfenahme des DbDataAdapters in die Datenbank zurückschreiben, sorgt dessen Methode Update dafür, dass die Parameter der Aktualisierungsstatements gefüllt werden. Update sucht dazu alle geänderten, hinzugefügten und gelöschten Datenzeilen im DataSet. Alle Änderungen werden ausnahmslos zurückgeschrieben.

Vielleicht möchten Sie aber nicht, dass alle Änderungen in die Datenbank zurückgeschrieben werden. Vielleicht sollen es zunächst nur die hinzugefügten Datenzeilen sein oder die gelöschten. In einem solchen Fall bleibt Ihnen nichts anderes übrig, als die Operationen der Update-Methode selbst zu programmieren.

Dazu müssen Sie zunächst wieder eine Methode bereitstellen, die das passende Command-Objekt erzeugt. Greifen wir dazu wieder auf die Methoden zurück, die wir im Abschnitt zuvor definiert haben.

Da wir auf die Unterstützung der Methode Update verzichten wollen, bleibt uns als Alternative nur die Methode ExecuteNonQuery des DbCommand-Objekts, um unser SQL-Statement gegen die Datenbank abzusetzen. Ehe mit einer Anweisung wie

myCommand.ExecuteNonQuery()

aktualisiert werden kann, müssen allerdings die Parameter mit den Daten der betreffenden Datenzeile im DataSet gefüllt werden. Dazu definieren wir eine eigene Methode. Da nach dem Füllen der Parameter das Command-Objekt vollständig ist, bietet es sich an, schon innerhalb der Methode ExecuteNonQuery aufzurufen. Dazu muss der Methode neben dem von CreateUpdateCommand generierten Command-Objekt nur die zu aktualisierende Datenzeile bekannt sein. Beide übergeben wir der Parameterliste der Methode, die wir SubmitUpdateRow nennen.

Function SubmitUpdatedRow(ByVal cmd As DbCommand, ByVal row As DataRow) _ 
As Integer 
  ' Parameter füllen 
  cmd.Parameters("@Name").Value = row("ProductName") 
  cmd.Parameters("@Preis").Value = row("UnitPrice") 
  cmd.Parameters("@Conti").Value = row("Discontinued") 
  cmd.Parameters("@ID").Value = row("ProductID") 
  cmd.Parameters("@OrgName").Value = _ 
    row("ProductName", DataRowVersion.Original) 
  ' Anzahl der betroffenen Zeilen 
  Return cmd.ExecuteNonQuery() 
End Function

Der Aufruf von ExecuteNonQuery liefert die Anzahl Datenzeilen, die aktualisiert werden konnten. Wir reichen sie durch, um Aktualisierungskonflikte leichter erkennen zu können. Zum Beispiel könnte ein anderer Anwender zwischenzeitlich einen zu aktualisierenden Datensatz gelöscht haben. Der Rückgabewert von ExecuteNonQuery und damit auch unserer benutzerdefinierten Methode wäre in dem Fall 0.

Beim Füllen der Parameter müssen Sie bedenken, welche Datenversion Sie in den jeweiligen Parameter eintragen müssen. Die hinter SET aufgeführten Spalten beschreiben die zu ändernden Spalten und deren neue Werte. Es sind also die, die unter DataRowVersion.Current der entsprechenden Datenzeile für die Spalte zu finden sind. Der Indexer der Zeile greift automatisch auf diese Version zu, row("ProductName") ist also der aktuelle Produktname.

Im WHERE-Kriterium ist die Sachlage anders, da Datensätze in der Datenbank gesucht werden. Zum Vergleich werden Datenbankwerte benötigt, die in DataRowVersion.Original gespeichert sind. Eine Sonderstellung haben Primärschlüsselwerte, da diese nicht verändert werden können. Hier kann alternativ auf DataRowVersion.Current zurückgegriffen werden.

In ähnlicher Weise können Sie auch Command-Objekte bereitstellen, die gelöschte und hinzugefügte Datenzeilen beschreiben.

Function SubmitDeletedRow(ByVal cmd As DbCommand, ByVal row As DataRow) _ 
As Integer 
  ' Parameter füllen 
  cmd.Parameters("@ID").Value = row("ProductID") 
  ' Anzahl der betroffenen Zeilen 
  Return cmd.ExecuteNonQuery() 
End Function

Function SubmitAddedRow(ByVal cmd As DbCommand, ByVal row As DataRow) _ 
As Integer 
  ' Parameter füllen 
  cmd.Parameters("@Name").Value = row("ProductName") 
  cmd.Parameters("@Preis").Value = row("UnitPrice") 
  cmd.Parameters("@Conti").Value = row("Discontinued") 
  ' Anzahl der betroffenen Zeilen 
  Return cmd.ExecuteNonQuery() 
End Function

Datenquelle synchronisieren

Nun wollen wir die drei Methodenpaare kombinieren. Mit einer zur Aktualisierung anstehenden Datenzeile row könnte das wie folgt aussehen:

Dim cmdUpdate As DbCommand = CreateUpdateCommand(con) 
Dim countDS As Integer = SubmitUpdatedRow(cmdUpdate, row) 
If countDS = 1 Then 
  ' Aktualisierung ist gelungen 
End If

Aber wie kommen wir an die Referenz einer geänderten DataRow?

Liegt ein DataSet mit unveränderten, geänderten, hinzugefügten und gelöschten Datenzeilen vor, müssen wir die verschiedenen (lokalen) Zeilenzustände auseinanderhalten. Dazu bietet sich eine Überladung der Methode Select der DataTable an, die im letzten Kapitel schon behandelt worden ist.

Function Select(filterExpression As String, sort As String, _ 
  recordStates As DataViewRowState) As DataRow()

Der erste Parameter beschreibt eine Zeichenfolge zum Filtern der Datenzeilen, der zweite eine Zeichenfolge für die Sortierung. Im dritten Parameter vom Typ DataRowViewState können wir angeben, nach welchen Datenzeilenversionen wir suchen wollen. Handelt es sich um gelöschte, hinzugefügte und editierte Datenzeilen, fassen wir das Suchkriterium in einer Variablen zusammen:

Dim drvs As DataViewRowState = DataViewRowState.Added Or _ 
  DataViewRowState.Deleted Or DataViewRowState.ModifiedCurrent

Select liefert dann ein Array von den Datenzeilen, die sich in irgendeiner Weise von dem Original unterscheiden, das von der Datenbank bezogen wurde. Das Array durchlaufen wir elementweise, und wir prüfen dabei jeweils die Eigenschaft RowState der aktuellen DataRow. Je nachdem, ob es sich um eine gelöschte, editierte oder hinzugefügte Zeile handelt, reagieren wir mit dem Aufruf einer unserer drei Submit-Methoden:

For Each row As DataRow In ds.Tables(0).Select("", "", drvs) 
  Select Case row.RowState 
    Case DataRowState.Added 
      countDS = SubmitAddedRow(cmdInsert, row) 
    Case DataRowState.Deleted 
      countDS = SubmitDeletedRow(cmdDelete, row) 
    Case DataRowState.Modified 
      countDS = SubmitUpdatedRow(cmdUpdate, row) 
  End Select 
Next

DataSet synchronisieren

Der Aufruf der Methode ExecuteNonQuery liefert uns als Ergebnis des Aufrufs eine Zahl, die die Anzahl der betroffenen Datensätze angibt. Da wir für jede einzelne Datenzeile die Methode aufrufen, signalisiert uns die Zahl 1, dass die Originaldatenquelle geändert wurde. Die Prüfung auf erfolgreiche Synchronisierung ist wichtig, denn wir haben den Aktualisierungsprozess selbst in die Hand genommen und sollten ihn auch erfolgreich beenden.

Die Update-Methode des DataAdapters sorgt automatisch dafür, dass das DataSet nach erfolgreicher Aktualisierung auf den aktuellen Stand gebracht wird. Daten, die unter DataRowVersion.Original zu finden sind, werden durch Daten in DataRowVersion.Current ersetzt. Zeilen, deren RowState entweder Added oder Modified ist, werden auf Unchanged gesetzt. Gelöschte Zeilen werden endgültig aus dem DataSet entfernt.

Ist die benutzerdefinierte Aktualisierung mit ExecuteNonQuery erfolgreich, müssen wir diesen Mechanismus von Update nachbilden, damit das lokale DataSet den nun aktuellen Zustand der Datenzeilen in der Datenbank widerspiegelt. Die Anpassungen müssen wir jedoch nicht für jede Datenzeile manuell vornehmen: Dies erledigt die Methode AcceptChanges, die auf dem DataSet, einer DataTable oder einer DataRow aufgerufen werden kann. Die Änderungen wirken sich nur auf das Objekt aus, auf dem die Methode aufgerufen wird.

Klappt die Aktualisierung nicht, wäre der Aufruf von AcceptChanges falsch. Zumindest wollen wir aber sicherstellen, dass der Anwender eine Information über den misslungenen Aktualisierungsversuch erhält. Es bietet sich hier an, der Eigenschaft RowError der betreffenden DataRow eine Zeichenfolge zu übergeben, die auf den Fehlversuch hinweist:

If countDS = 1 Then 
  row.AcceptChanges() 
Else 
  row.RowError = "Änderung wurde nicht akzeptiert" 
End If

Beispielprogramm

Zum Schluss wollen wir uns noch das gesamte Beispielprogramm ansehen, in dem die zuvor beschriebenen Methoden genutzt werden.

'...\ADO\Aktualisierung\Manuell.vb

Option Strict On 
Imports System.Data.Common, System.Data.SqlClient 
Namespace ADO 
  Module Manuell 
    ... 
    Function CreateUpdateCommand(ByVal con As DbConnection) As DbCommand ... 
    Function CreateDeleteCommand(ByVal con As DbConnection) As DbCommand ... 
    Function CreateInsertCommand(ByVal con As DbConnection) As DbCommand ... 
    Sub ZuFuss() 
      Dim con As DbConnection = New SqlConnection() 
      con.ConnectionString = "Data Source=(local);" & _ 
          "Initial Catalog=Northwind;Integrated Security=sspi" 
      Dim cmd As DbCommand = New SqlCommand() 
      cmd.CommandText = "SELECT ProductID, ProductName, " & _ 
        "UnitPrice, Discontinued FROM Products" 
      cmd.Connection = con 
      Dim ds As New DataSet() 
      Dim da As DbDataAdapter = New SqlDataAdapter() 
      da.SelectCommand = cmd 
      da.FillSchema(ds, SchemaType.Source) 
      da.Fill(ds)

      'Datenzeilen editieren 
      For Each row As DataRow In ds.Tables(0).Rows 
        If row("ProductName").ToString() = "Chai" Then _ 
          row("ProductName") = "Kräutertee" 
      Next row

      ' Datenzeile hinzufügen 
      Dim neu As DataRow = ds.Tables(0).NewRow() 
      neu("ProductName") = "Kuchen" : neu("UnitPrice") = 11.3 
      neu("Discontinued") = 0 
      ds.Tables(0).Rows.Add(neu)

      ' Festlegen der Command-Objekte 
      Dim cmdInsert As DbCommand = CreateInsertCommand(con) 
      Dim cmdDelete As DbCommand = CreateDeleteCommand(con) 
      Dim cmdUpdate As DbCommand = CreateUpdateCommand(con)

      ' aktualisieren 
      Dim countDS As Integer 
      Dim drvs As DataViewRowState = DataViewRowState.Added Or _ 
        DataViewRowState.Deleted Or DataViewRowState.ModifiedCurrent 
      con.Open() 
      For Each row As DataRow In ds.Tables(0).Select("", "", drvs) 
        Select Case row.RowState 
          Case DataRowState.Added 
            countDS = SubmitAddedRow(cmdInsert, row) 
          Case DataRowState.Deleted 
            countDS = SubmitDeletedRow(cmdDelete, row) 
          Case DataRowState.Modified 
            countDS = SubmitUpdatedRow(cmdUpdate, row) 
        End Select 
        If countDS = 1 Then 
          row.AcceptChanges() 
          Console.WriteLine("Aktualisierung erfolgreich.") 
        Else 
          Console.WriteLine("Aktualisierung fehlgeschlagen") 
        End If 
      Next row

      con.Close() 
      Console.ReadLine() 
    End Sub 
    Function SubmitUpdatedRow(cmd As DbCommand,row As DataRow) As Integer ... 
    Function SubmitDeletedRow(cmd As DbCommand,row As DataRow) As Integer... 
    Function SubmitAddedRow(cmd As DbCommand,row As DataRow) As Integer... 
  End Module 
End Namespace

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.