How to safely replace ArcGIS SDE feature classes using transactions

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?

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.