Problem using BulkMerge() method adding at runtime a new column into destination table

[AndreaA86]

Hi,
I have a problem using the BulkMerge() method when I add a new column into the destination table while the application is running. In this case occurs the following error:

"Message: An error occured while resolving mapping by name. See the inner exception for details"
InnerException: "Missing Colum : On Table : [Destination table name]"

Here I report the related code:

using (var bulkMerge = new BulkOperation(conn))
               {
                   bulkMerge.UseCompile = true;
                   bulkMerge.Transaction = trans;
                   bulkMerge.BatchSize = 10000;
                   bulkMerge.DestinationTableName = "BM_Items";

                   bulkMerge.ColumnMappings.Add("IdERP", "IdERP", true);
                   bulkMerge.ColumnMappings.Add("IdVariant", "IdVariant", true);
                   bulkMerge.ColumnMappings.Add("Name", "Name", false);
                   bulkMerge.ColumnMappings.Add("Nature", "Nature", false);
                   if (!skipUoM) bulkMerge.ColumnMappings.Add("IdERPUoM", "IdERPUoM", false);

                   bulkMerge.AllowDuplicateKeys = true;

                   bulkMerge.BulkMerge(dataTable);

                   dataTable.Dispose();

                   trans.Commit();
                   conn.Close();
               }

I'm using the table BM_Items where to export data retrieved from a DataTable (The first time the IdERPUoM column is not added into the table).
After that the first export has been executed (the app is still running) I add the new column "IdERPUoM" and I reinvoke the BulkMerge. In this case occurs the above error:

Message: An error occured while resolving mapping by name. See the inner exception for details"
InnerException: "Missing Colum : [IdERPUoM] On Table : [BM_Items]

If I rerun the application the error doesn't occurs and the export works correctly.

Potential bug:
From what I understand the error depends on the fact that if I do at least an export with BulkMerge in the destination table, if I then make changes on this table, while the application is still running, the Bulk library does not detect the new column. If then restart the app the problem is solved.

I need an urgent answer. I would like to know if it's a bug or if I'm missing something in the configuration of the BulkOperation object.

Thanks in advance.

[JonathanMagnan]

Hello @AndreaA86 ,

I would like to know if it's a bug or if I'm missing something in the configuration of the BulkOperation object.

I would say that's the option C, currently working as intended.

The first time our library access a table, it caches the information retrieved from the Database to don't execute query related to it over and over.

However, in your case, the schema changes which cause this problem.

One way to fix it, is to create a new cache.

Creating a new cache

int schemaVersion = 0;

// every time the schema is modifed
schemaVersion++;
BulkOperationManager.Cache = new MemoryCache("schema_version_" + schemaVersion);

bulk.BulkMerge(dt);

Let me know if that solution work for you or you would like a better one.

Best Regards,

Jonathan

[AndreaA86]

I tried to change BulkOperationManager.Cache as above or using a guid but the error remains.

                BulkOperationManager.Cache = new MemoryCache("schema_version_" + Guid.NewGuid().ToString().Replace("-", "_"));
                using (var bulkMerge = new BulkOperation(conn))
                {
                    bulkMerge.UseCompile = true;
                    bulkMerge.Recompile();

                    bulkMerge.Transaction = trans;
                    bulkMerge.Resolution = ResolutionType.Smart; // .TemporaryTable;

                    bulkMerge.BatchSize = 10000;
                    bulkMerge.DestinationTableName = "BM_Items";

                    bulkMerge.ColumnMappings.Add("IdERP", "IdERP", true);
                    bulkMerge.ColumnMappings.Add("IdVariant", "IdVariant", true);
                    bulkMerge.ColumnMappings.Add("Name", "Name", false);
                    bulkMerge.ColumnMappings.Add("Nature", "Nature", false);
                    if (!skipUoM) bulkMerge.ColumnMappings.Add("IdERPUnitOfMeasure", "IdERPUoM", false);

                    bulkMerge.AllowDuplicateKeys = true;
                    
                    bulkMerge.BulkMerge(dataTable);

                    dataTable.Dispose();

                    trans.Commit();
                    conn.Close();
                    conn.Dispose();
                }

Each time I instance the class containing the code above. I still tried to dispose the object after the using block.

((MemoryCache)BulkOperationManager.Cache).Dispose();

[JonathanMagnan]

Hello @AndreaA86 ,

I'm very sorry to have misleading you. You are right, that will not work.

We already have a built-in method to clear the cache

InformationSchemaManager.ClearInformationSchemaTable();

We obviously testing it correctly this time and it should work as expected.

Best Regards,

Jonathan

[AndreaA86]

Thanks. It works.