SQLite insert looping error 'SQLiteException: 'database is locked'

Question

I'm trying to insert a DataTable into SQLite table, and i'm using foreach loop for this, but it throw error SQLiteException: 'database is locked.

i tried to googling about this, and try to dispose my connection with using

Here's my code

public void Insert_to_db(DataTable data)
{
   foreach (DataRow row in data.Rows)
   {
           string alertTag = row["Alert Tag"].ToString();
           int group = Convert.ToInt32(row["Group"]);
           int line = Convert.ToInt32(row["Line"]);
           int task = Convert.ToInt32(row["Task"]);

           string sql = string.Format("insert into alert_tag (alert_tag, layer_group, line, task) values ('{0}','{1}','{2}','{3}')", alertTag, group, line, task);

           using (SQLiteConnection dbConn = new SQLiteConnection(Tools.SqliteConnString()))
           {
               using (SQLiteCommand command = new SQLiteCommand(sql, dbConn))
               {
                   dbConn.Open();
                   command.ExecuteNonQuery();
                   dbConn.Close();
               }
           }
   }
}

General note: use placeholders/parameterized queries. string.Format should “never” be used for values in SQL. Search for “SQL Injection”. — user2864740, May 17 '19 at 04:50

jazb · Answer 1 · 2019-05-17T04:44:23.103

You need to change the code so that the looping takes place inside the using dbConn:

    using (SQLiteConnection dbConn = new SQLiteConnection(Tools.SqliteConnString()))
    {
        using (SQLiteCommand command = new SQLiteCommand(sql, dbConn))
        {
            dbConn.Open();

            string alertTag;
            int group;
            int line;
            int task;
            string sql;

            foreach (DataRow row in data.Rows)
            {
                alertTag = row["Alert Tag"].ToString();
                group = Convert.ToInt32(row["Group"]);
                line = Convert.ToInt32(row["Line"]);
                task = Convert.ToInt32(row["Task"]);
                sql = string.Format("insert into alert_tag (alert_tag, layer_group, line, task) values ('{0}','{1}','{2}','{3}')", alertTag, group, line, task);
                command.ExecuteNonQuery();
                dbConn.Close();                    
            }                
        }
    }

you may consider doing some sanity checks on the values you get from the data.Rows before saving. you are wide open for sql injection right now. look at building or using parameters.
i would investigate a good ORM like Dapper or EF to avoid building brittle insert statements

While less than efficient, the loop the way shown in the question should be OK without other concurrent access / threads. The DB is opened and closed sequentially and without external access, is “valid”. There is probably something else in the environment.. — user2864740, May 17 '19 at 04:47
but the `sql` var used in `SQLiteCommand` are declared after. — espifi059 espifi059, May 17 '19 at 05:39

score 0 · Accepted Answer · answered May 17 '19 at 07:15

So as @JhonB suggested, i settled on using dapper but first i have to convert my DataTabe to List of Object

private List<AlertTagModel> convert(DataTable dt)
{
   var convertedData = (from rw in dt.AsEnumerable()
                        select new AlertTagModel() {
                            alert_tag = Convert.ToString(rw["AlertTag"]),
                            layer_group = Convert.ToInt32(rw["Group"]),
                            line = Convert.ToInt32(rw["Line"]),
                            task = Convert.ToInt32(rw["Task"])
                            }).ToList();
   return convertedData;
}

then insert into DB with dapper

public void Insert_to_db(Object data)
{
     using (IDbConnection cnn = new SQLiteConnection(Tools.LoadConnectionString()))
     {
          cnn.Execute("insert into alert_tag (AlertTag, Layer_ID, Line_ID, Task_ID) values (@alert_tag, @layer_group, @line, @task)", data);
     }
}

Solved!

SQLite insert looping error 'SQLiteException: 'database is locked'

2 Answers2