Safely replacing ArcSDE feature classes using transactions in ArcObjects?

Question

I am using C#, ArcGIS Server 10, MS-SQL Server 2008, and I want to replace an ArcGIS SDE feature class inside of a transaction. After the deletion of the feature class, I have to create a new one with the same name (as a copy of another feature class).

After some research I have implemented the following:

 public void deleteFeatureClassInTransactionTest(string featureClassName) {
 using (WebObject webObj = new WebObject())
 {
 // Connect to server
 AGSServerConnection agsServerConnection = ConnectServer();
 agsServerConnection.Connect();
 // Initialize workspace
 IWorkspace workspace = GetSdeWorkspace();
 webObj.ManageLifetime(workspace);
 // Initialize edit workspace
 IWorkspaceEdit workspaceEdit = (IWorkspaceEdit)workspace;
 IMultiuserWorkspaceEdit muWorkspaceEdit = (IMultiuserWorkspaceEdit)workspace;
 // Initialize feature workspace
 IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspace;
 // Open feature class
 IFeatureClass featureClass = featureWorkspace.OpenFeatureClass(featureClassName);
 webObj.ManageLifetime(featureClass);
 // Start transaction
 muWorkspaceEdit.StartMultiuserEditing(esriMultiuserEditSessionMode.esriMESMNonVersioned);
 workspaceEdit.StartEditOperation();
 try
 {
 // Delete feature class
 ESRI.ArcGIS.Geodatabase.IDataset pdataset;
 pdataset = (ESRI.ArcGIS.Geodatabase.IDataset)featureClass;
 pdataset.Delete();
 // ... CREATE THE NEW FEATURE CLASS ...
 // Simulate exception
 throw new Exception("DEBUG");
 // Commit
 workspaceEdit.StopEditOperation();
 workspaceEdit.StopEditing(true);
 }
 catch (System.Exception ex)
 {
 // Rollback
 workspaceEdit.AbortEditOperation();
 workspaceEdit.StopEditing(false);
 throw ex;
 }
 finally ..

However, the rollback does not work. The table gets deleted immediately.

How can I make this whole process atomic?

• Kirk Kuykendall: I didn't think any major DBMS would let you DROP TABLE within a transaction, then roll it back. Am I mistaken?
• Kirk Kuykendall: You might try using geodatabase replication to accomplish this though.

Answer 1

DDL statements are never part of a transaction, not on MS SQL Server. The same goes for Oracle.

There are however databases, like PostgreSQL, which allow transactional DDL to certain extent.

Anyway, in a geodatabase, manipulating the data model will never be "safe". Access to feature datasets, tables, feature classes etc. acquires locks which prevent modifications to the data model under user's hands.

It also seems you are misunderstanding the concept of edit sessions and edit operations: their purpose is to manage data editing, not altering the data model.

In any case, I strongly suggest that you rethink your design. Altering the data model as part of "normal" application flow is not inherently flawed (in specific scenarios), but the need to do so in a transparent way within a transaction should in my opinion almost never arise.

• Ragi Yaser Burhum: Even if the underlying database (like PostgreSQL) supports transactional DDL, ArcObjects does not. DLL operations will either fail, or cause a commit of the transaction.
• Christian: Thank you guys. I wasn't aware of the differences between DDL and DML regarding transaction, perhaps because I have been working with PostgreSQL before starting to work on this project. I'll try to redesign the process.