Database is locked sqCommand.ExecuteNonQuery()?

问题:

I have the following code:

string sql = String.Format("insert into {0}({1}) values({2});", tableName, columns, values);
SQLiteConnection myConn = null;
try 
{
    myConn = new SQLiteConnection(dbConnection);
    myConn.Open();

    using(SQLiteCommand sqCommand = new SQLiteCommand(sql, myConn))
    {
        sqCommand.CommandText = sql;
        int rows = sqCommand.ExecuteNonQuery();
        return !(rows == 0);
    }
} 
finally 
{
    myConn.Close();
}

It returns always an error: Database is locked in line:

int rows = sqCommand.ExecuteNonQuery();

Whu does it happen?

回答1:

I bet the database connection is left open somewhere when sqCommand.ExecuteNonQuery() method executed (which the lock still apply for previous connection), hence you need to refactor by wrapping every SQLiteConnection instance with using statement block like this:

string sql = String.Format("insert into {0}({1}) values({2});", tableName, columns, values);
using (SQLiteConnection myConn = new SQLiteConnection(dbConnection))
{
    try
    {
        myConn.Open();

        using (SQLiteCommand sqCommand = new SQLiteCommand(sql, myConn))
        {
            sqCommand.CommandText = sql;
            int rows = sqCommand.ExecuteNonQuery();
            return !(rows == 0);
        }
    }
    catch (Exception e)
    {
        // do exception handling
    }
}

Reference:

Writing to a database file