System.Data.SQLite

I came across this post on making bulk inserts more performant:

http://sqlite.phxsoftware.com/forums/t/134.aspx

I've tried to follow the parameterized insert example using TransactionScope instead of IDbTransaction.  The basic structure I am using is:

            using (TransactionScope transaction = new TransactionScope())
            {
                // The Connection parameter creates and opens a connection
                using (IDbCommand command = Connection.CreateCommand())
                {
                    // Set command.CommandText
                    // Create and add the parameters

                    foreach (string line in lines)
                    {
                        // Set the parameter's value
                        command.ExecuteNonQuery();
                    }
                }

                transaction.Complete();
            }

I am running into two problems with this.  The first is that it performs no better than the old individual insert way.  The second problem is that in one instance, the largest set of data, I am also getting a timeout error.

Does anyone have any ideas on what I am doing wrong?

Thanks,
-Mont

 OK, I may have solved my own problem, but I don't understand the solution.

 I changed the code to have the following structure:

        using (DbConnection connection = new SQLiteConnection(SQLiteConnectionString(false)))
        {
            using (TransactionScope transaction = new TransactionScope())
            {
                connection.Open();

                using (IDbCommand command = connection.CreateCommand())
                {
                    // Set command.CommandText
                    // Create and add the parameters

                    foreach (string line in lines)
                    {
                        // Set the parameter's value
                        command.ExecuteNonQuery();
                    }
                }

                connection.Open();



                transaction.Complete();
            }
        }

I had seen references to this structure earlier but I had also come across this post which seemed to imply that creating the connection before the TransactionScope was no longer necessary.

http://sqlite.phxsoftware.com/forums/p/482/2044.aspx#2044

Also, this only seemed to be a problem with my larger data sets, smaller ones didn't matter.

Does anyone have an explanation for what I have observed?

Oh, and I forgot to mention I am using v1.0.65.0.

Thanks,
-Mont