sqlite - any improvements for this attach code (running multiple sql commands transactionally in sqlite)

Question

Hi,

Is this code solid? I've tried to use "using" etc. Basically a method to pass as sequenced list of SQL commands to be run against a Sqlite database.

I assume it is true that in sqlite by default all commands run in a single connection are handled transactionally? Is this true? i.e. I should not have to (and haven't got in the code at the moment) a BeginTransaction, or CommitTransaction.

It's using http://sqlite.phxsoftware.com/ as the sqlite ADO.net database provider.

1st TRY

private int ExecuteNonQueryTransactionally(List<string> sqlList)
{
    int totalRowsUpdated = 0;

    using (var conn = new SQLiteConnection(_connectionString))
    {
        // Open connection (one connection so should be transactional - confirm)
        conn.Open();

        // Apply each SQL statement passed in to sqlList
        foreach (string s in sqlList)
        {
            using (var cmd = new SQLiteCommand(conn))
            {
                cmd.CommandText = s;
                totalRowsUpdated = totalRowsUpdated + cmd.ExecuteNonQuery();
            }
        }
    }

    return totalRowsUpdated;
}

3rd TRY

How is this?

private int ExecuteNonQueryTransactionally(List<string> sqlList)
{
    int totalRowsUpdated = 0;

    using (var conn = new SQLiteConnection(_connectionString))
    {
        conn.Open();
        using (var trans = conn.BeginTransaction())
        {

            try
            {
                // Apply each SQL statement passed in to sqlList
                foreach (string s in sqlList)
                {
                    using (var cmd = new SQLiteCommand(conn))
                    {
                        cmd.CommandText = s;
                        totalRowsUpdated = totalRowsUpdated + cmd.ExecuteNonQuery();
                    }
                }

                trans.Commit();
            }
            catch (SQLiteException ex)
            {
                trans.Rollback();
                throw;
            }


        }
    }
    return totalRowsUpdated;
}

thanks

Answer 1

Yes, it's true, each SQLite unnested command is nested in a transaction. So that if you need to run several queries, without fetching the result, there is much gain is explicitly starting a transaction, doing your queries, and committing.