0

I'm developing a program in C# which inserts around a 100,000 rows into a version 4 dbase file (*.dbf) using vfpoledb driver.

The method that return the connection string is as follows,

internal string GetDBaseConnectionString(string path)
{            
  return "Provider=vfpoledb;Data Source=" + path + ";Collating Sequence=general;";            
}

The code segment that does the insert is as follows,

        internal long Execute()
        {
            OleDbConnection con = null;
            OleDbCommand cmd = null;

            try
            {
                con = new OleDbConnection(AppSettings.Current.GetDBaseConnectionString(_dbfPath));
                con.Open();

                cmd = new OleDbCommand(_sql);
                cmd.Connection = con;                
                cmd.CommandTimeout = AppSettings.Current.DefaultCommandTimeOutMinutes;

                long rowIndex = 0;
                int countInBatch = 0;

                for (int i = 0; i < _reader.FieldCount; i++)
                    cmd.Parameters.Add(new OleDbParameter());                

                while (_reader.Read())
                {
                    for (int i = 0; i < cmd.Parameters.Count; i++)
                        cmd.Parameters[i].Value = _reader.GetValue(i);

                    cmd.ExecuteNonQuery();

                    rowIndex += 1;

                    if (_progressChangeRowCount > 0)
                    {
                        countInBatch += 1;

                        if (countInBatch >= _progressChangeRowCount)
                        {
                            countInBatch = 0;

                            ProgressChangedEventArgs args = new ProgressChangedEventArgs(rowIndex);
                            this.OnProgressChanged(args);
                        }
                    }
                }

                _reader.Close();

                con.Close();
                con.Dispose();
                cmd.Dispose();

                return rowIndex;
            }
            catch (Exception ex)
            {                
                if (con != null)
                {
                    con.Close();
                    con.Close();
                }

                if (cmd != null)
                    cmd.Dispose();

                if(_reader!= null)
                    _reader.Close();

                throw ex;
            }
        }

This segment is run in three threads concurrently. So data is inserted into three dbase files simultaneously from 3 SqlDataReaders.

My problem is that my program eats up around 50-100 MB per minute and it only increases until I close the program. Because of this System.OutOfMemoryExceptions are raised in the program and the OS soon shuts it down. I can see the Page File Usage in Task Manager go from 540 MB to 2.2 GB.

I have narrowed it down to the line cmd.ExecuteNonQuery(); If I comment out this line, the program executes with only about 1 or 2 MB of increase in memory.

Hence

  1. Can this be due to a memory leak in the VFPOledb driver? I'm using the latest version 9
  2. If so what can I do to cope with it? (Wrapping this as a seperate process so the OS cleans up any memory leaks on exit sounds tempting but should be a last resort)
  3. Do you see or know of any other fault which might be causing this?
  4. Changing the driver may help but the Jet driver is slow painstakingly slow. Is there any other option for dbase bulk inserts? I have also asked this question here Efficient way to bulk insert into Dbase (.dbf) files

Thanks in advance.

Community
  • 1
  • 1
Harindaka
  • 4,658
  • 8
  • 43
  • 62
  • You can and should start to simplify this with `using() {}` blocks. – H H Apr 25 '11 at 10:35
  • Ad `throw ex;` should be `throw;` – H H Apr 25 '11 at 10:40
  • Please add the _sql text and how/where you create _reader. – H H Apr 25 '11 at 10:41
  • The _reader is created and passed to the constructor of the class where this code segment is. The _sql text is a simple sql insert command built dynamically by looking at the columns returned by the reader. This happens inside the constructor of the class in which this code segment resides hence isnt memory intensive since its not repetitive. – Harindaka Apr 25 '11 at 10:57
  • Commenting out only the cmd.ExecuteNonQuery(); line fixes the memory hog. Hence why I'm convinced that this is a bug in the vfpoledb driver. – Harindaka Apr 25 '11 at 10:59
  • The _reader also connect to the (same?) Db. Try preloading that as a list. – H H Apr 25 '11 at 21:07
  • And the VFP provider is probably in-process. Best tging would be to upgrade to something more modern (Sql Express, Sql CE, ...). Notice you're not getting a lot of Views here. – H H Apr 25 '11 at 21:08
  • Thanks for helping so far Henk. The reader fetches data from an MS SQL Server database. The thing is i'm forced to work with an old POS app which uses dbase files so updating to a new technology is not an option for me. My basic requirement is to export data from MS SQL Server to dbase – Harindaka Apr 26 '11 at 04:15
  • Well, goog luck. Look into `using` anyway, cleaning up your code is always helpful. – H H Apr 26 '11 at 06:14

1 Answers1

1

After many tries I wrapped the piece of code in a separate process so that the OS cleaned up after it exits. This was the best solution I could find.

Harindaka
  • 4,658
  • 8
  • 43
  • 62