How to obtain a table's differences with transactional or historical versions


This document was published with and applies to ArcGIS 9.3.
A 10 version also exists. A 9.2 version also exists.
Summary This topic shows how to obtain a table's differences between two transactional or two historical versions.

Development licensing Deployment licensing
ArcView ArcView
ArcEditor ArcEditor
ArcInfo ArcInfo
Engine Developer Kit Engine Runtime

To use the code in this topic, reference the following namespace via the using (C#) or Imports (VB .NET) statements. It is also necessary to add the corresponding reference to the project to gain access to the application programming interface (API).

In this topic


Obtaining a table's differences

By utilizing the geodatabase API, it is possible to interrogate a table opened in two different versions and obtain a list of the differences based on a difference type. This can be performed when using transactional or historical versions. This topic briefly discusses the process for obtaining various difference types between two versions programmatically.
 
Establishing references to the versions
When determining differences between two transactional versions, the first step is to establish references to the versions. See the following code example:
 

[C#]
// Get references to the child and parent versions.
IVersionedWorkspace versionedWorkspace = (IVersionedWorkspace)workspace;
IVersion childVersion = versionedWorkspace.FindVersion("ChildVersion");
IVersion parentVersion = versionedWorkspace.FindVersion("ParentVersion");

[VB.NET]
' Get references to the child and parent versions.
Dim versionedWorkspace As IVersionedWorkspace = CType(workspace, IVersionedWorkspace)
Dim childVersion As IVersion = versionedWorkspace.FindVersion(childVersionName)
Dim parentVersion As IVersion = versionedWorkspace.FindVersion(parentVersionName)
 
Detecting the differences
Once the versions are obtained, open the tables that will be compared for differences. In this example, the objective is to discover all edits (inserts, updates, and deletes) for the Parcels table in the child version. Use the common ancestor version to accomplish this step.
 
The common ancestor version is when both versions are identical, referencing the same internal state of the database. If the objective is to determine what features are in conflict between two versions (difference types—UpdateUpdate, UpdateDelete, and DeleteUpdate), open the difference table from the parent version and not the common ancestor version. See the following code example:
 

[C#]
// Cast to the IVersion2 interface to find the common ancestor.
IVersion2 childVersion2 = (IVersion2)childVersion;
IVersion commonAncestorVersion = childVersion2.GetCommonAncestor(parentVersion);

// Cast the child version to IFeatureWorkspace and open the table.
IFeatureWorkspace childFWS = (IFeatureWorkspace)childVersion;
ITable childTable = childFWS.OpenTable("Parcels");

// Cast the common ancestor version to IFeatureWorkspace and open the table.
IFeatureWorkspace commonAncestorFWS = (IFeatureWorkspace)commonAncestorVersion;
ITable commonAncestorTable = commonAncestorFWS.OpenTable("Parcels");

[VB.NET]
' Cast to the IVersion2 interface to find the common ancestor.
Dim childVersion2 As IVersion2 = CType(childVersion, IVersion2)
Dim commonAncestorVersion As IVersion = childVersion2.GetCommonAncestor(parentVersion)

' Cast the child version to IFeatureWorkspace and open the table.
Dim childFWS As IFeatureWorkspace = CType(childVersion, IFeatureWorkspace)
Dim childTable As ITable = childFWS.OpenTable(tableName)

' Cast the common ancestor version to IFeatureWorkspace and open the table.
Dim commonAncestorFWS As IFeatureWorkspace = CType(commonAncestorVersion, IFeatureWorkspace)
Dim commonAncestorTable As ITable = commonAncestorFWS.OpenTable(tableName)
 
Obtaining a table reference
With the Parcels table opened in both versions of the workspace, the next step is to obtain a reference to the IVersionedTable interface. Since the objective is to discover all the differences for the Parcels table between the child version and the common ancestor version, cast the child version's table to the IVersionedTable interface. Finally, by calling the IVersionedTable.Differences method, obtain a difference cursor for all the updates performed on the Parcels table in the child version. See the following code example:
 

[C#]
// Cast to the IVersionedTable interface to create a difference cursor.
IVersionedTable versionedTable = (IVersionedTable)childTable;
IDifferenceCursor differenceCursor = versionedTable.Differences
  (commonAncestorTable, esriDifferenceType.esriDifferenceTypeUpdateNoChange,
  null);

[VB.NET]
' Cast to the IVersionedTable interface to create a difference cursor.
Dim versionedTable As IVersionedTable = CType(childTable, IVersionedTable)
Dim differenceCursor As IDifferenceCursor = versionedTable.Differences(commonAncestorTable, differenceType, Nothing)
 
The difference cursor result set contains all updates performed on the Parcel table in the child version. The modified rows can then be accessed with the following code example:
 

[C#]
// Create output variables for the IDifferenceCursor.Next method.
IRow differenceRow = null;
int objectID =  - 1;

// Step through the cursor, showing the ID of each modified row.
differenceCursor.Next(out objectID, out differenceRow);
while (objectID !=  - 1)
{
  Console.WriteLine("OID of Updated Row: {0}", objectID);
  differenceCursor.Next(out objectID, out differenceRow);
}

[VB.NET]
' Create output variables for the IDifferenceCursor.Next method and a FID set.
Dim fidSet As IFIDSet = New FIDSetClass()
Dim differenceRow As IRow = Nothing
Dim objectID As Integer = -1

' Step through the cursor, showing the ID of each modified row.
differenceCursor.Next(objectID, differenceRow)
Do While objectID <> -1
    fidSet.Add(objectID)
    differenceCursor.Next(objectID, differenceRow)
Loop
When iterating through a difference cursor, use the object ID output parameter to check for the end of the result set (as shown in the previous code example), instead of testing whether the row is null, as is typical with a regular cursor. When obtaining deletions, Object IDs are returned, but the returned rows are null.
 
 
Obtaining differences between historical versions
It is also possible to obtain the differences between two historical versions. This is done by opening the two tables from the historical versions. The following code example finds the differences in a table between a historical version (Year End 2004) and the default version:
 

[C#]
// Cast to the IHistoricalWorkspace interface and get the 2004 and default versions.
IHistoricalWorkspace historicalWorkspace = (IHistoricalWorkspace)workspace;
IHistoricalVersion defaultVersion =
  historicalWorkspace.FindHistoricalVersionByName
  (historicalWorkspace.DefaultMarkerName);
IHistoricalVersion historicalVersion =
  historicalWorkspace.FindHistoricalVersionByName("Year End 2004");

[VB.NET]
' Cast to the IHistoricalWorkspace interface and get the 2004 and default versions.
Dim historicalWorkspace As IHistoricalWorkspace = CType(workspace, IHistoricalWorkspace)
Dim defaultVersion As IHistoricalVersion = historicalWorkspace.FindHistoricalVersionByName(historicalWorkspace.DefaultMarkerName)
Dim historicalVersion As IHistoricalVersion = historicalWorkspace.FindHistoricalVersionByName(historicalMarkerName)
 
The code is then similar to obtaining transactional version differences. The following code example shows how to find rows that were added to the default version following the creation of the historical marker:
 

[C#]
// Cast both versions to IFeatureWorkspace and open the table from each.
IFeatureWorkspace defaultFWS = (IFeatureWorkspace)defaultVersion;
IFeatureWorkspace historicalFWS = (IFeatureWorkspace)historicalVersion;
ITable defaultTable = defaultFWS.OpenTable("Parcels");
ITable historicalTable = historicalFWS.OpenTable("Parcels");

// Create a difference cursor and output variables for the IDifferenceCursor.Next method.
IVersionedTable versionedTable = (IVersionedTable)defaultTable;
IDifferenceCursor differenceCursor = versionedTable.Differences(historicalTable,
  esriDifferenceType.esriDifferenceTypeInsert, null);
IRow differenceRow = null;
int objectID =  - 1;

// Step through the cursor, showing the ID of each modified row.
differenceCursor.Next(out objectID, out differenceRow);
while (objectID !=  - 1)
{
  Console.WriteLine("OID of Inserted Row: {0}", objectID);
  differenceCursor.Next(out objectID, out differenceRow);
}

[VB.NET]
' Cast both versions to IFeatureWorkspace and open the table from each.
Dim defaultFWS As IFeatureWorkspace = CType(defaultVersion, IFeatureWorkspace)
Dim historicalFWS As IFeatureWorkspace = CType(historicalVersion, IFeatureWorkspace)
Dim defaultTable As ITable = defaultFWS.OpenTable(tableName)
Dim historicalTable As ITable = historicalFWS.OpenTable(tableName)

' Create a difference cursor.
Dim versionedTable As IVersionedTable = CType(defaultTable, IVersionedTable)
Dim differenceCursor As IDifferenceCursor = versionedTable.Differences(historicalTable, differenceType, Nothing)

' Create output variables for the IDifferenceCursor.Next method and a FID set.
Dim fidSet As IFIDSet = New FIDSetClass()
Dim differenceRow As IRow = Nothing
Dim objectID As Integer = -1

' Step through the cursor, showing the ID of each modified row.
differenceCursor.Next(objectID, differenceRow)
Do While objectID <> -1
    fidSet.Add(objectID)
    differenceCursor.Next(objectID, differenceRow)
Loop

Complete code examples

The following complete code example finds the differences between two versions.  As previously stated, if the objective is to find differences that may result in a conflict, open the difference table from the parent version and not the common ancestor version (as in the following example).

[C#]
public IFIDSet FindVersionDifferences(IWorkspace workspace, String
  childVersionName, String parentVersionName, String tableName,
  esriDifferenceType differenceType)
{
  // Get references to the child and parent versions.
  IVersionedWorkspace versionedWorkspace = (IVersionedWorkspace)workspace;
  IVersion childVersion = versionedWorkspace.FindVersion(childVersionName);
  IVersion parentVersion = versionedWorkspace.FindVersion(parentVersionName);

  // Cast to the IVersion2 interface to find the common ancestor.
  IVersion2 childVersion2 = (IVersion2)childVersion;
  IVersion commonAncestorVersion = childVersion2.GetCommonAncestor
    (parentVersion);

  // Cast the child version to IFeatureWorkspace and open the table.
  IFeatureWorkspace childFWS = (IFeatureWorkspace)childVersion;
  ITable childTable = childFWS.OpenTable(tableName);

  // Cast the common ancestor version to IFeatureWorkspace and open the table.
  IFeatureWorkspace commonAncestorFWS = (IFeatureWorkspace)
    commonAncestorVersion;
  ITable commonAncestorTable = commonAncestorFWS.OpenTable(tableName);

  // Cast to the IVersionedTable interface to create a difference cursor.
  IVersionedTable versionedTable = (IVersionedTable)childTable;
  IDifferenceCursor differenceCursor = versionedTable.Differences
    (commonAncestorTable, differenceType, null);

  // Create output variables for the IDifferenceCursor.Next method and a FID set.
  IFIDSet fidSet = new FIDSetClass();
  IRow differenceRow = null;
  int objectID =  - 1;

  // Step through the cursor, showing the ID of each modified row.
  differenceCursor.Next(out objectID, out differenceRow);
  while (objectID !=  - 1)
  {
    fidSet.Add(objectID);
    differenceCursor.Next(out objectID, out differenceRow);
  }

  fidSet.Reset();
  return fidSet;
}

[VB.NET]
Public Function FindVersionDifferences(ByVal workspace As IWorkspace, ByVal childVersionName As String, ByVal parentVersionName As String, ByVal tableName As String, ByVal differenceType As esriDifferenceType) As IFIDSet
    
    ' Get references to the child and parent versions.
    Dim versionedWorkspace As IVersionedWorkspace = CType(workspace, IVersionedWorkspace)
    Dim childVersion As IVersion = versionedWorkspace.FindVersion(childVersionName)
    Dim parentVersion As IVersion = versionedWorkspace.FindVersion(parentVersionName)
    
    ' Cast to the IVersion2 interface to find the common ancestor.
    Dim childVersion2 As IVersion2 = CType(childVersion, IVersion2)
    Dim commonAncestorVersion As IVersion = childVersion2.GetCommonAncestor(parentVersion)
    
    ' Cast the child version to IFeatureWorkspace and open the table.
    Dim childFWS As IFeatureWorkspace = CType(childVersion, IFeatureWorkspace)
    Dim childTable As ITable = childFWS.OpenTable(tableName)
    
    ' Cast the common ancestor version to IFeatureWorkspace and open the table.
    Dim commonAncestorFWS As IFeatureWorkspace = CType(commonAncestorVersion, IFeatureWorkspace)
    Dim commonAncestorTable As ITable = commonAncestorFWS.OpenTable(tableName)
    
    ' Cast to the IVersionedTable interface to create a difference cursor.
    Dim versionedTable As IVersionedTable = CType(childTable, IVersionedTable)
    Dim differenceCursor As IDifferenceCursor = versionedTable.Differences(commonAncestorTable, differenceType, Nothing)
    
    ' Create output variables for the IDifferenceCursor.Next method and a FID set.
    Dim fidSet As IFIDSet = New FIDSetClass()
    Dim differenceRow As IRow = Nothing
    Dim objectID As Integer = -1
    
    ' Step through the cursor, showing the ID of each modified row.
    differenceCursor.Next(objectID, differenceRow)
    Do While objectID <> -1
        fidSet.Add(objectID)
        differenceCursor.Next(objectID, differenceRow)
    Loop
    
    fidSet.Reset()
    
    Return fidSet
    
End Function
 
The following code example finds the differences between a historical and default versions of a table:
 

[C#]
public IFIDSet FindHistoricalDifferences(IWorkspace workspace, String
  historicalMarkerName, String tableName, esriDifferenceType differenceType)
{
  // Cast to the IHistoricalWorkspace interface and get the 2004 and default versions.
  IHistoricalWorkspace historicalWorkspace = (IHistoricalWorkspace)workspace;
  IHistoricalVersion defaultVersion =
    historicalWorkspace.FindHistoricalVersionByName
    (historicalWorkspace.DefaultMarkerName);
  IHistoricalVersion historicalVersion =
    historicalWorkspace.FindHistoricalVersionByName(historicalMarkerName);

  // Cast both versions to IFeatureWorkspace and open the table from each.
  IFeatureWorkspace defaultFWS = (IFeatureWorkspace)defaultVersion;
  IFeatureWorkspace historicalFWS = (IFeatureWorkspace)historicalVersion;
  ITable defaultTable = defaultFWS.OpenTable(tableName);
  ITable historicalTable = historicalFWS.OpenTable(tableName);

  // Create a difference cursor.
  IVersionedTable versionedTable = (IVersionedTable)defaultTable;
  IDifferenceCursor differenceCursor = versionedTable.Differences
    (historicalTable, differenceType, null);

  // Create output variables for the IDifferenceCursor.Next method and a FID set.
  IFIDSet fidSet = new FIDSetClass();
  IRow differenceRow = null;
  int objectID =  - 1;

  // Step through the cursor, showing the ID of each modified row.
  differenceCursor.Next(out objectID, out differenceRow);
  while (objectID !=  - 1)
  {
    fidSet.Add(objectID);
    differenceCursor.Next(out objectID, out differenceRow);
  }

  fidSet.Reset();
  return fidSet;
}

[VB.NET]
Public Function FindHistoricalDifferences(ByVal workspace As IWorkspace, ByVal historicalMarkerName As String, ByVal tableName As String, ByVal differenceType As esriDifferenceType) As IFIDSet
    
    ' Cast to the IHistoricalWorkspace interface and get the 2004 and default versions.
    Dim historicalWorkspace As IHistoricalWorkspace = CType(workspace, IHistoricalWorkspace)
    Dim defaultVersion As IHistoricalVersion = historicalWorkspace.FindHistoricalVersionByName(historicalWorkspace.DefaultMarkerName)
    Dim historicalVersion As IHistoricalVersion = historicalWorkspace.FindHistoricalVersionByName(historicalMarkerName)
    
    ' Cast both versions to IFeatureWorkspace and open the table from each.
    Dim defaultFWS As IFeatureWorkspace = CType(defaultVersion, IFeatureWorkspace)
    Dim historicalFWS As IFeatureWorkspace = CType(historicalVersion, IFeatureWorkspace)
    Dim defaultTable As ITable = defaultFWS.OpenTable(tableName)
    Dim historicalTable As ITable = historicalFWS.OpenTable(tableName)
    
    ' Create a difference cursor.
    Dim versionedTable As IVersionedTable = CType(defaultTable, IVersionedTable)
    Dim differenceCursor As IDifferenceCursor = versionedTable.Differences(historicalTable, differenceType, Nothing)
    
    ' Create output variables for the IDifferenceCursor.Next method and a FID set.
    Dim fidSet As IFIDSet = New FIDSetClass()
    Dim differenceRow As IRow = Nothing
    Dim objectID As Integer = -1
    
    ' Step through the cursor, showing the ID of each modified row.
    differenceCursor.Next(objectID, differenceRow)
    Do While objectID <> -1
        fidSet.Add(objectID)
        differenceCursor.Next(objectID, differenceRow)
    Loop
    
    fidSet.Reset()
    
    Return fidSet
    
End Function