lundi 27 avril 2015

SQLITE - Open two connections (read and write) at once

I have a c# program that needs to read record by record from a db, then it makes some operations on two columns and then writes on a new column. So far I did like this:

        using (SQLiteConnection conn_write = new SQLiteConnection("Data Source=" + path + ";PRAGMA journal_mode=WAL;"))
        {
            conn_write.Open();
            SQLiteCommand cmd_write = new SQLiteCommand(conn_write);

            using (SQLiteConnection conn_read = new SQLiteConnection("Data Source=" + path + ";PRAGMA journal_mode=WAL;"))
            {
                conn_read.Open();
                SQLiteCommand cmd_read = new SQLiteCommand(conn_read);
                SQLiteDataReader reader;

                sql_read = "SELECT ID, ETRF2000_FI, ETRF2000_LA FROM Tutto_NonAbolito";
                cmd_read.CommandText = sql_read;
                reader = cmd_read.ExecuteReader();

                while (reader.Read())
                {
                    MobileKat.ALCoord.ETRF200ToLL84(reader.GetDouble(reader.GetOrdinal("ETRF2000_FI")), reader.GetDouble(reader.GetOrdinal("ETRF2000_LA")), ref lon, ref lat);
                    sql_write = "UPDATE Tutto_NonAbolito SET LL84_LON = " + lon + ", LL84_LAT = " + lat + " WHERE " + reader.GetInt32(reader.GetOrdinal("ID")) + " = ID;";
                    cmd_write.CommandText = sql_write;
                    cmd_write.ExecuteReader();
                }
                conn_read.Close();
            }
            conn_write.Close();
        }

I also tried to add PRAGMA but it still tells me that the database file is locked.. Is there a way to do that in this way? I wouldn't like to save the columns in an array and then open another connection I prefer to do it "on-the-run" if it is possible. Thanks!

Aucun commentaire:

Enregistrer un commentaire