[sqlite] Using the SQLite.Net ADO wrapper for sqlite3

[Michael Evenson]

Hi all,

        I am trying to use the transaction object in SQLite.NET to speed up
a process that needs to move over 100 million rows from a MSSQL 2K bcp file
to a SQLite 3 database. It certainly does speed up the process (by 6 times a
couple orders of magnitude). The problem is that Disposing of the
IDbTransaction object does not release the memory. Could someone who has
done this please look at the snippet below and tell me what I am doing
wrong. The code works, it just has a HUGE memory leek.

NOTE:   1. The reader object is a bcp file reader that I wrote.
        2. The cmd string is pre built and the parameters already defined.
        3. The bcp format file and the bcp data are generated from the same
script

<snip>

IDbTransaction trnInsert = Conn.BeginTransaction ();    // Declare and
Create the Transaction Object
cmdInsert.Transaction = trnInsert;                              // Assign it
to the Command object

while (reader.Read()) 
{
        for (i = 0; i < reader.fmtThisFile.m_nNumberOfColumns; i++)
        {
                CFormatFileEntry clsFormatFileEntry = (CFormatFileEntry)
reader.fmtThisFile.m_alEntries[i];
                if (clsFormatFileEntry.m_strDataType == "SQLINT")
                {
                        p =
(IDbDataParameter)cmdInsert.Parameters[clsFormatFileEntry.m_nFieldOrder -
1];
                        p.Value = reader.GetInt32
(clsFormatFileEntry.m_nFieldOrder - 1);
                        if (clsFormatFileEntry.m_nFieldOrder == 1)
                        {
                                lUID = (long) p.Value;
                        }
                }
                else
                {
                        p =
(IDbDataParameter)cmdInsert.Parameters[clsFormatFileEntry.m_nFieldOrder -
1];
                        p.Value = reader.GetString
(clsFormatFileEntry.m_nFieldOrder - 1);
                }
        }
        lUIDCount++;

        if ((lUIDCount % 10000) == 0)
        {
                trnInsert.Commit ();                            // commit
the transaction
                trnInsert.Dispose ();                           // dispose
of it

                trnInsert = Conn.BeginTransaction ();   // re-create a new
transaction start
                cmdInsert.Transaction = trnInsert;              // assign it
to the Command object
        }

        cmdInsert.ExecuteNonQuery ();
}

// commit the last transaction

trnInsert.Commit ();
trnInsert.Dispose ();

<snip>