c# - UPDATE faster in SQLite + BEGIN TRANSACTION -

this 1 related spatilite (not sqlite)

i have file database (xyz.db) using sqliteconnection (sqliteconnection extends spatialite).

i have many records needs update database.

                (int y = 0; y < castarraylist.count; y++)                 {                     string s = convert.tostring(castarraylist[y]);                      string[] h = s.split(':');                      sqlitecommand sqlqctsql4 = new sqlitecommand("update temp2 set geom = " + h[0] + "where " + dtsqlquery2.columns[0] + "=" + h[1] + "", con);                     sqlqctsql4.executenonquery();                      x = x + 1;                 } 

at above logic castarraylist arraylist contains value need process database.

when checked above code updating around 400 records in 1 minute.

is there way can able improve performance ?

note :: (file database not thread-safe)

2. begin transaction

let's suppose run 2 (or millions) update statement single transaction in spatialite.. possible ?

i read online , prepare below statement me (but not success)

begin transaction; update builtuparea_luxbel set admin_level = 6 pk_uid = 2; update builtuparea_luxbel set admin_level = 6 pk_uid = 3; commit transaction;  

above statement not updating records in database. sqlite not support begin transaction ? there missing ?

and if need run individual statement it's taking time update said above...

sqlite support transaction, can try below code.

using (var cmd = new sqlitecommand(conn)) using (var transaction = conn.begintransaction()) {     (int y = 0; y < castarraylist.count; y++)     {         //add query here.         cmd.commandtext = "insert table (field1,field2) values ('a', 'b');";         cmd.executenonquery();     }     transaction.commit(); }