mardi 26 mai 2015

SQLite 'The database file is locked' on INSERT

I have a WEB API constructor that gets data from SQL Server and put's it into a SQLite local database.

public class ForemanController : ApiController
{
    static Foreman[] allForeman;
    static SqlConnectionStringBuilder connectionString = new SqlConnectionStringBuilder();
    static string SQLiteConnectionString = "Data Source=C:\\Users\\tunnelld\\Desktop\\customers.sqlite;Version=3;";
    public ForemanController()
    {
        connectionString.DataSource = "dxdb0212";
        connectionString.InitialCatalog = "dbname";
        connectionString.UserID = "un";
        connectionString.Password = "pw";

        string queryString = "SELECT * FROM [QTRAXAdmin].[vwQT_Foreman]";

        List<Foreman> list;
        // Creates a SQL connection
        using (var connection = new SqlConnection(connectionString.ToString()))
        {
            using (var command = new SqlCommand(queryString, connection))
            {
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    list = new List<Foreman>();
                    while (reader.Read())
                    {
                        list.Add(new Foreman { ForeBadge = reader.GetString(0), ForeName = reader.GetString(1) });
                    }
                }
            }
            connection.Close();
            allForeman = list.ToArray();
        }
        string deleteSQL = "DELETE FROM Foreman;";
        using (var connection = new SQLiteConnection(SQLiteConnectionString))
        {
            using (var command = new SQLiteCommand(deleteSQL, connection))
            {
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
                SQLiteCommand cmd2 = connection.CreateCommand();
                foreach (Foreman row in allForeman)
                {
                    cmd2.CommandText = "INSERT INTO Foreman (ForeBadge, ForeName) VALUES (@param1, @param2);";
                    cmd2.Parameters.Add(new SQLiteParameter("@param1", row.ForeBadge));
                    cmd2.Parameters.Add(new SQLiteParameter("@param2", row.ForeName));
                    cmd2.ExecuteNonQuery();
                }
        }
    }

I'm getting this error and I don't understand why:

enter image description here

An exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll but was not handled in user code

Additional information: The database file is locked

How do I resolve this?

Aucun commentaire:

Enregistrer un commentaire