This one is related to spatilite also (not only SQLite)
I have a file database (xyz.db
) which I am using by SQLiteconnection
(SQLiteconnection
is extends to spatialite).
I have so many records needs to update into database.
for (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
is Arraylist
which contains value which need to process into database.
When I checked above code updating around 400 records in 1 minute.
Is there any way by which I can able to improve performance ?
NOTE :: (File database is not thread-safe)
2. BEGIN TRANSACTION
Let's suppose I like to run two (or millions) update statement with single transaction in Spatialite.. is it possible ?
I read online and prepare below statement for me (but not get success)
BEGIN TRANSACTION;
UPDATE builtuparea_luxbel SET ADMIN_LEVEL = 6 where PK_UID = 2;
UPDATE builtuparea_luxbel SET ADMIN_LEVEL = 6 where PK_UID = 3;
COMMIT TRANSACTION;
Above statement not updating records in my database. is SQLite not support BEGIN TRANSACTION ? is there anything which I missing ?
And If I need to run individual statement then it's taking too much time to update as said above...
SQLite support Transaction, you can try below code.
using (var cmd = new SQLiteCommand(conn))
using (var transaction = conn.BeginTransaction())
{
for (int y = 0; y < castarraylist.Count; y++)
{
//Add your query here.
cmd.CommandText = "INSERT INTO TABLE (Field1,Field2) VALUES ('A', 'B');";
cmd.ExecuteNonQuery();
}
transaction.Commit();
}