topic Re: Is there an efficient way in ArcObjects to delete many tables at once? in ArcObjects SDK Questions

Is there an efficient way in ArcObjects to delete many tables at once?

DuncanHornby — Sat, 11 Dec 2021 19:42:06 GMT

All,

I have a tool that can populate a file geodatabase with hundreds to several thousand tables (some tables can be very small others very large). My current VB code searches the file geodatabase and deletes tables using the following logic:

Dim pEnumDataset As IEnumDataset
pEnumDataset = pWorkspace.Datasets(esriDatasetType.esriDTTable)
pDataSet = pEnumDataset.Next
Do While Not pDataSet Is Nothing
     If pDataSet.CanDelete = True Then
         If Right(pDataSet.BrowseName, 12) = "Upstream_IDs" Then
             pDataSet.Delete()
           End If
      Else
           ' Error
            Return Nothing
    End If
    pDataSet = pEnumDataset.Next
Loop

When there are thousands of tables ending with the suffix "upstream_IDs", this process can be quite slow. I don't want to delete the entire geodatabase as there may be other tables that need to remain.

I was wondering if any ArcObjects Guru out there know of a way to batch delete tables? I was thinking if there was some way of providing a list or an enumerator to some interface that can delete tables efficiently?

Re: Is there an efficient way in ArcObjects to delete many tables at once?

DanPatterson_Retired — Sun, 14 Feb 2016 20:46:11 GMT

how about an arcpy solution ListTables—Help | ArcGIS for Desktop

with perhaps the wildcard being your suffix

It should provide a list, then you can use Delete—Help | ArcGIS for Desktop

to delete the elements from the list.

Re: Is there an efficient way in ArcObjects to delete many tables at once?

DuncanHornby — Mon, 15 Feb 2016 09:41:16 GMT

Thanks for the ideas of using arcpy but I was looking for an ArcObjects solution. I've not found an interface that would do a bulk deletion. Sometimes the Interface can have a very odd name. I have thought that it may be quicker to copy out the tables that should remain, blitz the database and rename the new database?

Re: Is there an efficient way in ArcObjects to delete many tables at once?

nicogis — Mon, 15 Feb 2016 11:56:22 GMT

I don't answer your question directly because the method is IDasaset.Delete. But why you don't use a gdb separate or a scratch gdb for these tables (I guess that are scratch tables) ? So you can delete entire gdb ...

Re: Is there an efficient way in ArcObjects to delete many tables at once?

DuncanHornby — Mon, 15 Feb 2016 12:01:15 GMT

They are not scratch tables. They are topological information of river networks one for each catchment so they need to persist. It's just that occasionally they need refreshing and my current method (code above) works it's just a bit slower than I would like.

Re: Is there an efficient way in ArcObjects to delete many tables at once?

nicogis — Mon, 15 Feb 2016 12:20:12 GMT

Is possible remove only rows and mantain table (use truncate)? http://help.arcgis.com/EN/sdk/10.0/ArcObjects_NET/componenthelp/index.html#//0025000008n5000000

Truncating tables The ITableWrite2.Truncate method provides a way to quickly delete every row in a table (or every feature in a feature class). It is only available for local geodatabase classes and for non-versioned classes in ArcSDE. Caution should be taken when using this method, for two reasons: ##All complex behavior is bypassed when calling this method. Calling it on a dataset that participates in a composite relationship class, participates in a controller dataset such as a topology, or has custom behavior defined (i.e., using a class extension) will likely cause data corruption. This should only be used to truncate simple data. ##This method ignores schema locks. If the dataset is being used by other users they may experience unexpected behavior.

Re: Is there an efficient way in ArcObjects to delete many tables at once?

DuncanHornby — Mon, 15 Feb 2016 12:46:29 GMT

Hmm... interesting! My tables are simple standalone tables so not participating in any other relation.

So are you saying if I truncate the table (thus deleting all contents) then delete the table using pDataSet.Delete() that this will be quicker than simply deleting the table (because it has thousands of rows in it)?

Re: Is there an efficient way in ArcObjects to delete many tables at once?

nicogis — Mon, 15 Feb 2016 13:17:50 GMT

I thought enough you remove all records but if you need also remove tables I don't know if the two operations are quicker that a single operation (IDataset.Delete)

Re: Is there an efficient way in ArcObjects to delete many tables at once?

DuncanHornby — Sat, 11 Dec 2021 19:42:09 GMT

Domenico,

I knocked together the following VBA code and ran it against identical geodatabases using just delete and then truncate and delete, the lines marked with '** I commented out when running a straight delete:

Public Sub test()
    Dim pWorkspaceFactory As IWorkspaceFactory
    Set pWorkspaceFactory = New FileGDBWorkspaceFactory
    
    Dim pWorkspace As IWorkspace
    Set pWorkspace = pWorkspaceFactory.OpenFromFile("C:\Scratch\fGDB_NetworkConnectivity.gdb", 0)
    
    Dim pFeatureWorkspace As IFeatureWorkspace
    Set pFeatureWorkspace = pWorkspace
    
    Dim t1, t2 As Date
    t1 = Now
    Debug.Print t1
    
    ' Loop through workspace deleting Tables
    Dim pTableWrite As ITableWrite2
    Dim pEnumDataset As IEnumDataset
    Set pEnumDataset = pWorkspace.Datasets(esriDatasetType.esriDTTable)
    Dim pDataSet As IDataset
    Set pDataSet = pEnumDataset.Next
    Do While Not pDataSet Is Nothing
        If pDataSet.CanDelete = True Then
            If Right(pDataSet.BrowseName, 12) = "Upstream_IDs" Then
                Set pTableWrite = pFeatureWorkspace.OpenTable(pDataSet.Name) '**
                pTableWrite.Truncate '**
                pDataSet.Delete
            End If
        End If
        Set pDataSet = pEnumDataset.Next
    Loop
    
    t2 = Now
    Debug.Print t2
    Debug.Print DateDiff("S", t1, t2)
End Sub

The result is that it is slower to truncate then delete than to simply just delete. Well it was worth a try...

Re: Is there an efficient way in ArcObjects to delete many tables at once?

GregRieck — Thu, 17 Aug 2017 03:42:30 GMT

I know you are looking for an ArcObjects solution but I wanted to mention this approach.

1) Create a new empty geodatabase

2) In ArcCatalog connect to the source database and select everything except the tables you want to delete.

3) Paste your selection into the new database.

This approach will get you a new database without the tables you wanted removed.