Search code examples
c#exceptionmemorydata-migrationmemory-management

DBF to SQL Migration Utility - Best way to tackle solution

I have a very long winded question. I have recently been given a task by my employer where I am to create a custom DBF to SQL migration utility in C# from 117 *.dbf files. Each table has approximately 40-50 columns and over 100 rows. (Eg. Property Management Database)

How I am tackling it is the following:

  1. Convert a dbf file into a DataTable object.
  2. Add the DataTable object to a List<DataTable> which is contained in the model object.
  3. Bind list to a DataGridView for column viewing purposes.

This is all done in a background worker which works fine.

The next thing I need to do is allow the user to convert and save this list into a very large *.sql file (or optionally, migrate it directly to SQL Express). Again, this I attempt to do in a background thread.

This is where I run into problems. I have a method that accepts a DataTable object and returns a string. In it, I have a stringbuilder object which concatenates all the columns into a 'create table' statement and attaches the associated insert statements in order to include the data.

This method is executed in a loop while passing an instance of each DataTable from List<DataTable> stored in the model object.

Now this works fine up until about the fourth or fifth DataTable before an 'Out of Memory' exception is thrown. I am sure to initiate and dispose of any objects I am not using. I have even went as far as to change all my string concatenation to stringbuilder append logic to take advantage of the stringbuilders better memory management.

I am pretty sure that all my objects are deallocated and garbage collected, so I am assuming that the problem lies in the fact that I am storing all the 117 tables in a list of the model object. Whenever I need to access this list, I simply pass a reference to the model object. As soon as I start building an sql statement for all the tables, the combination between the DataTables list and the Stringbuilder object, it's running out of memory.

I neglect to inform that I am new to the industry as I am fresh out of college. I have been programming for many years only until recently have I been following 'best practice'. So my question is to all of you, am I tackling this project the wrong way? Is there a better way to do it and if so, could you help shed some light as to what you would do in my place?

Solution

  • Alright, I did what made sense. I simply wrote the SQL directly to file rather than write it to a string and then write that string to file. This appears to have done the trick. Not sure why I didn't think of it earlier.