I am using a SQLite Database in my C# program.
The program saves values every second in the DB, and also checks everytime if the tables already exist:
class DataManager : IDataManager
{
private bool machineTableIsCreated = false;
private bool machineAlreadyInDb = false;
private bool machineValueTableAlreadyExists = false;
public DataManager()
{
machineTableIsCreated = CheckIfMachineTableExists();
}
//Writing the Values in a CSV-File
public void WriteInCsv(dynamic value, string name)
{
string strFilePath = @"C:\SVN\" + name + ".csv";
File.AppendAllText(strFilePath, Convert.ToString(value) + "\n");
}
//Writing the Values into the Database
public void WriteInDb(string name, string netId, int port, List<BeckhoffVariable> variables)
{
BeckhoffMachineDto machine = new BeckhoffMachineDto
{
Name = name,
NetId = netId,
Port = port,
Variables = variables
};
if (!machineTableIsCreated)
{
CreateMachineTable();
machineTableIsCreated = true;
}
machineAlreadyInDb = CheckIfMachineIsAlreadyInDb(machine);
if (!machineAlreadyInDb)
{
InsertMachineInDb(machine);
}
machineValueTableAlreadyExists = CheckIfMachineValueTableExists(machine.Name);
if (!machineValueTableAlreadyExists)
{
//Creates Table for the Machine where its values are stored
CreateTable(machine.Name);
}
//Inserts the Value read from the Machine into the DB
InsertData(machine.Name, machine.Variables);
}
private bool CheckIfMachineValueTableExists(string name)
{
//Open connection to DB
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
//Creating Command to execute
using (var command = connection.CreateCommand())
{
command.CommandText = $"SELECT * FROM SQLITE_MASTER WHERE type ='table' AND name ='{name}';";
//read result of command
using (var reader = command.ExecuteReader())
{
//Check if result isnt null
if (reader.HasRows)
{
//connection.Close();
return true;
}
//connection.Close();
return false;
}
}
}
}
private bool CheckIfMachineIsAlreadyInDb(BeckhoffMachineDto machine)
{
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = $"SELECT * FROM machines WHERE Name = '{machine.Name}' AND NetId = '{machine.NetId.Replace('.', '-')}' AND Port = '{machine.Port}';";
command.CommandTimeout = 1;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
//connection.Close();
return true;
}
//connection.Close();
return false;
}
}
}
}
private bool CheckIfMachineTableExists()
{
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='machines';";
command.CommandTimeout = 1;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
//connection.Close();
return true;
}
//connection.Close();
return false;
}
}
}
}
private void InsertMachineInDb(BeckhoffMachineDto machine)
{
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO machines (Name, NetId, Port, VariableCoolingFlow, VariableCoolingIn, VariableCoolingOut, " +
"VariableTempAc, VariableTempDc, VariableTempAcBelow, VariableTempDcBelow) VALUES(@machineName, @machineNetId," +
" @machinePort, @machineVar0, @machineVar1, @machineVar2, @machineVar3, @machineVar4, @machineVar5, @machineVar6); ";
command.CommandType = CommandType.Text;
command.Parameters.Add(new SQLiteParameter("@machineName", machine.Name));
command.Parameters.Add(new SQLiteParameter("@machineNetId", machine.NetId.Replace('.', '-')));
command.Parameters.Add(new SQLiteParameter("@machinePort", machine.Port));
command.Parameters.Add(new SQLiteParameter("@machineVar0", machine.Variables[0].Name.Replace('.', '-')));
command.Parameters.Add(new SQLiteParameter("@machineVar1", machine.Variables[1].Name.Replace('.', '-')));
command.Parameters.Add(new SQLiteParameter("@machineVar2", machine.Variables[2].Name.Replace('.', '-')));
command.Parameters.Add(new SQLiteParameter("@machineVar3", machine.Variables[3].Name.Replace('.', '-')));
command.Parameters.Add(new SQLiteParameter("@machineVar4", machine.Variables[4].Name.Replace('.', '-')));
command.Parameters.Add(new SQLiteParameter("@machineVar5", machine.Variables[5].Name.Replace('.', '-')));
command.Parameters.Add(new SQLiteParameter("@machineVar6", machine.Variables[6].Name.Replace('.', '-')));
command.ExecuteNonQuery();
}
//connection.Close();
}
}
private static void CreateMachineTable()
{
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "CREATE TABLE machines (Name VARCHAR(40), NetId VARCHAR(30), Port VARCHAR(40), VariableCoolingFlow VARCHAR(45)," +
" VariableCoolingIn VARCHAR(45), VariableCoolingOut VARCHAR(45), VariableTempAc VARCHAR(45), VariableTempDc VARCHAR(45)," +
" VariableTempAcBelow VARCHAR(45), VariableTempDcBelow VARCHAR(45));";
command.ExecuteNonQuery();
}
//connection.Close();
}
}
//Creates The Tables for the Values a Machine has
private static void CreateTable(string name)
{
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = $"CREATE TABLE {name} (TemperatureCoolingFlow VARCHAR(20), TemperatureCoolingOut VARCHAR(20), TemperatureCoolingIn VARCHAR(20)," +
" TemperatureDTSAc VARCHAR(20), TemperatureDTSDc VARCHAR(20), TemperatureDTSAcBelow VARCHAR(20), TemperatureDTSDcBelow VARCHAR(20))";
command.ExecuteNonQuery();
}
//connection.Close();
}
}
//Inserts the Machine Values in the Table of the Machine
private static void InsertData(string name, List<BeckhoffVariable> variables)
{
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = $"INSERT INTO {name} (TemperatureCoolingFlow, TemperatureCoolingOut, TemperatureCoolingIn, TemperatureDTSAc," +
$" TemperatureDTSDc, TemperatureDTSAcBelow, TemperatureDTSDcBelow) VALUES(@machineVar0, @machineVar1, @machineVar2, " +
$" @machineVar3, @machineVar4, @machineVar5, @machineVar6); ";
command.CommandType = CommandType.Text;
command.Parameters.Add(new SQLiteParameter("@machineVar0", Convert.ToString(variables[0].Value)));
command.Parameters.Add(new SQLiteParameter("@machineVar1", Convert.ToString(variables[1].Value)));
command.Parameters.Add(new SQLiteParameter("@machineVar2", Convert.ToString(variables[2].Value)));
command.Parameters.Add(new SQLiteParameter("@machineVar3", Convert.ToString(variables[3].Value)));
command.Parameters.Add(new SQLiteParameter("@machineVar4", Convert.ToString(variables[4].Value)));
command.Parameters.Add(new SQLiteParameter("@machineVar5", Convert.ToString(variables[5].Value)));
command.Parameters.Add(new SQLiteParameter("@machineVar6", Convert.ToString(variables[6].Value)));
command.ExecuteNonQuery();
}
//connection.Close();
}
}
//Returns a List of all Machines in the Database
public List<BeckhoffMachineDto> getAllMachines()
{
List<BeckhoffMachineDto> machines = new List<BeckhoffMachineDto>();
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
machineTableIsCreated = CheckIfMachineTableExists();
if (!machineTableIsCreated)
{
CreateMachineTable();
machineTableIsCreated = true;
}
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM machines";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
BeckhoffMachineDto currMachine = new BeckhoffMachineDto();
currMachine.Name = (string)reader[0];
currMachine.NetId = reader[1].ToString().Replace('-', '.');
currMachine.Port = Convert.ToInt32(reader[2].ToString());
currMachine.Variables = new List<BeckhoffVariable>();
for (int i = 3; i <= 9; i++)
{
currMachine.Variables.Add(new BeckhoffVariable(reader[i].ToString().Replace('-', '.')));
}
machines.Add(currMachine);
}
//connection.Close();
}
}
}
return machines;
}
//Deletes the Machine from machines Table and deletes the whole Table from the given machine
public void DeleteMachine(BeckhoffMachineDto selectedMachine)
{
using (var connection = new SQLiteConnection("Data Source=C:/SVN/trunk/database.db;PRAGMA journal_mode=WAL;Pooling=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = $"DROP TABLE {selectedMachine.Name}";
command.ExecuteNonQuery();
command.CommandText = $"DELETE FROM machines WHERE Name = '{selectedMachine.Name}' AND NetId = '{selectedMachine.NetId.Replace('.', '-')}' AND Port = '{selectedMachine.Port}'";
command.ExecuteNonQuery();
}
//connection.Close();
}
}
}
The problem is that it gets locked quite a few times while running the program.
I already have WAL Mode on and Pooling on True.
I don't really get how it still gets locked.
SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"
SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"
SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"
SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"
SQLite error (5): database is locked in "SELECT * FROM machines WHERE Name = 'Machine1' AND NetId = '10.10.10.1.1.1' AND Port = '111';"
The Problem is that while I write the values I get into the DB, it selects something from the DB and throws an Database locked error but WAL and Pooling should prevent these.
Has anyone an idea of what I could do to solve my problem?
Apparently, the solution to this problem is to change the synchronous pragma of the sqlite database to NORMAL! When using WAL mode the best setting for the synchronous pragma is NORMAL but the default of the db is FULL. But FULL doesnt work good with WAL. When I changed it, there were no longer database locked errors.