c# - SQLite database is locked when inserting but good when using select

Question

I have two different c# projects. One is the main and the other is just a class of m SQLite and I make it a .dll file and import it to the main. Everything is good when my script is just Select. But when Im about to insert it gives me error and says "Database is Locked". My SQLite class code is this:

public class SQLite
{
    public Finisar.SQLite.SQLiteConnection myConn;
    public SQLiteCommand myComm;
    public SQLiteDataReader myReader;
    public SQLiteDataAdapter myAdapter;
    public string SQLiteDatabaseName = "";

    public void ExecuteQuery(string query)
    {
        myConn = new Finisar.SQLite.SQLiteConnection("Data Source=" + SQLiteDatabaseName + ".db;Version=3;");
        myConn.Open();
        myComm = myConn.CreateCommand();
        myComm.CommandText = query;
        myComm.ExecuteReader();
    }
}

I search about this before and found this code:

 using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
    c.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
    {
        using (SQLiteDataReader rdr = cmd.ExecuteReader())
        {
            ...
        }
    }
}

I haven't tried it yet because I dont think it will work on me. Because I have code in my Main class where I put the myReader.Read() inside a while statement to read all rows and I dont think I can do that if I put in using statement.

In my previous project everything is fine when im using MySQL but I just want to have a project that is database is just in a single file like SQLite. below is my code when using MySQL:

class connectionToMySQL
{
    string constring = "datasource=localhost;port=3306;username=root;password=root";


    public MySqlConnection myConn;
    public MySqlCommand myComm;
    public MySqlDataReader myReader;


  public void connectToMySQL(string query)
    {
        myConn = new MySqlConnection(constring);
        myComm = new MySqlCommand(query, myConn);

        try
        {
            myConn.Open();
            myReader = myComm.ExecuteReader();
        }

        catch (Exception ex)
        {

        }
    }
}

This works really fine on me. In my main project there's no single .Close() or .Dispose() line because I dont think I need one coz it never give me error about database is locked.

Please help me how can I solve my problem in my SQLite what should I do about the "database is locked" error. But the solution must not restrict me to do a read data inside a while loop. Thanks in advance.

That is how SQLite works. It's not meant to handle modifications from multiple clients. You can enalbe concurrent changes by enabling [WAL mode](http://www.sqlite.org/wal.html) with `PRAGMA journal_mode=WAL;` in the connection string. This writes changes to a transaction log that *eventually* gets applied to the data files. This means that you can read dirty data if you SELECT before the changes are applied — Panagiotis Kanavos, May 30 '17 at 10:26
Hi can you answer this for me please? In my 2nd batch of code in the top, how can I read data using while loop? Because the dataReader variable is created in Using, in my main program how am I able to read data using while loop? — Haime Reyes, May 30 '17 at 14:35
I still have no idea how to fix my problem. All of the "duplicate" in stack is not same as my problem. Please can you help me? — Haime Reyes, May 30 '17 at 14:48
You asked about locking. The duplicate answers that. Using a DataReader is covered by tutorials. It's also answered in several SO questions, [like this](https://stackoverflow.com/questions/8370927/how-do-i-loop-through-rows-with-a-data-reader-in-c) — Panagiotis Kanavos, May 30 '17 at 15:00
I think I got it. I used my first code in the top, but instead, I move the "myConn" 2 line code in my main class. Is my error here is that I always Open the connection everytime I execute a script? — Haime Reyes, May 30 '17 at 15:31

c# - SQLite database is locked when inserting but good when using select

0 Answers0