SQlite connection closes itself before execution

Question

I encountered a strange issue and I can't seem to be able to find an answer to...

I have a piece of code that Inserts data into the SQlite DB using transactions and parameters. It's taking values from passed list of object List<T>. There are 74 parameters in total... My code looks like this

            dbConnection.Open();

            
            using (SqliteTransaction transaction = dbConnection.BeginTransaction())
            {
                foreach (CardScryfall _card in _cards)
                {
                    SqliteCommand com = dbConnection.CreateCommand();
                    com.CommandText = sql;
                    com.Parameters.Add("@dbid", SqliteType.Integer); //Mandatory
                                                                     //Assign values to Parameters
                    com.Parameters[0].Value = GetNextDBID();
                    //Assign card properties to parameters
                    com.Parameters.Add("@objectName", SqliteType.Text);
                    com.Parameters[com.Parameters.IndexOf("@objectName")].Value = _card.objectName;
                    
                    //Next 72 parameters are created the same way.
                    

                    //Replace NULL with DBNull.Value
                    foreach (SqliteParameter par in com.Parameters)
                    {
                        if (par.Value == null)
                        {
                            par.Value = DBNull.Value;
                        }
                    }

                    //Execute query
                    
                    com.Prepare();
                    com.ExecuteNonQuery();
                    transaction.Commit();
                    com.Dispose();
                    
                }
            }
            dbConnection.Close();

Now... When I remove the using transaction section the code works fine it's just tremendously slow... So I added wanted to wrap it into one transaction so boost the performance.

The problem now is somewhere in the middle of adding parameters (different every time I step through the code) the dbconnection.State changes from open to close so when the code reaches to com.Prepare() for first insert and I get error that connection is closed... I have 10k+ records to insert

I tried setting the dbConnection.DefaultTimeout = 0 but that didn't help...

Can you help???

Thanks

UPDATE:

I found my issue... Function GetNextDBID() is retrieving the last recordID which opens and CLOSES the DBconnection... So I changed the code around and now it works fine

Answer 1

No need to create a command in a loop. You should create it once and then only assign new values to the parameters.

Similarly, you don't need to commit a transaction at each iteration of the loop. Do this once at the end.

using (var dbConnection = new SqliteConnection(_connectionString))
{
    dbConnection.Open();

    using (SqliteTransaction transaction = dbConnection.BeginTransaction())
    using (SqliteCommand com = dbConnection.CreateCommand())
    {
        com.CommandText = sql;

        com.Parameters.Add("@dbid", SqliteType.Integer);
        com.Parameters.Add("@objectName", SqliteType.Text);
        // ...

        com.Prepare();

        foreach (CardScryfall _card in _cards)
        {
            com.Parameters["@dbid"].Value = GetNextDBID();
            com.Parameters["@objectName"].Value = (object)_card.objectName ?? DBNull.Value;
            // ...

            _ = com.ExecuteNonQuery();
        }
        transaction.Commit();
    }
}