[RESOLVED] Sqlite and db locking? 2 programs, 1 writing, 1 reading...

[detlion1643]

I am using sqlite in my programs. I did some reading and noticed that sqlite supports writing to the db file from one place and other places can read from the same db file (if they attempt to write, the writing isn't executed).

So, with that in mind, I created this:

using (SqliteConnection sqlConn = new SqliteConnection(@"Data Source=" + pathgoeshere + ";Pooling=false;synchronous=0;temp_store=2;count_changes=0"))
            {
                //command
                using (SqliteCommand command = sqlConn.CreateCommand())
                {
			command.CommandText = ""; //Creation of Tables
			command.ExecuteNonQuery();
			SqliteTransaction sqltrans = sqlConn.BeginTransaction();
			command.Transaction = sqltrans;
			//open StreamReader
			using (StreamReader sr = new StreamReader(pathgoeshere + ".txt")))
                    	{
				while ((line = sr.ReadLine()) != null)
                        	{
					command.CommandText = ""; //Insert or Ignore, also updating if need to, from 10,000+ records to 2.1 million records (varies every time it runs)
					command.ExecuteNonQuery();
				}
			}
			sqltrans.Commit();
		}
	    }

Then, in another program I have this (didn't even bother writing my query yet for it):

using(SqliteConnection con = new SqliteConnection(@"Data Source=" + pathgoeshere + ";Pooling=false;synchronous=0;temp_store=2;count_changes=0"))
					{
						if(con.State != System.Data.ConnectionState.Open)
							con.Open();
						using(SqliteCommand command = new SqliteCommand("SELECT Field1,Field2 FROM Table",con))
						{
							using(SqliteDataReader reader = command.ExecuteReader())
							{
								if(reader.HasRows)
								{
									
								}
							}
						}
					}

The problem is the 2nd portion of code. Apparently anytime the first program is running through the sqlite code and the 2nd program attempts to run its sqlite code, the 2nd program crashes because the db file is locked. Maybe it's because of the transactions in the first code, but without it, the first code takes too long to run.

The 2nd part is also only going to attempt a simple select.

Does anybody have any suggestions on the best way to handle this?