private SQLiteDataReader GetZodiacsDataReader()
{
using var command = _connection.CreateCommand();
command.Connection = _connection;
command.CommandText =
$"select {Zodiac.GetIdColumnName()}, {Zodiac.GetNameColumnName()}, {Zodiac.GetTypeColumnName()} " +
$"from {Zodiac.GetTableName()};";
Console.WriteLine("Execute SQL: " + command.CommandText);
return(command.ExecuteReader());
}
private void Delete(Zodiac zodiac)
{
using var command = _connection.CreateCommand();
command.Connection = _connection;
command.CommandText =
$"delete from {Zodiac.GetTableName()} " +
$"where {Zodiac.GetTypeColumnName()} = :enum_number;";
command.Parameters.AddWithValue("zodiac_id", (int)zodiac.Type);
Console.WriteLine("Execute SQL: " + command.CommandText);
command.ExecuteNonQuery();
}
public Zodiac GetZodiac(int id)
{
using var command = _connection.CreateCommand();
command.Connection = _connection;
command.CommandText =
$"select {Zodiac.GetIdColumnName()}, {Zodiac.GetNameColumnName()}, {Zodiac.GetTypeColumnName()} " +
$"from {Zodiac.GetTableName()} " +
$"where {Zodiac.GetIdColumnName()} = :id;";
command.Parameters.AddWithValue("id", id);
Console.WriteLine("Execute SQL: " + command.CommandText);
return(Factory.CreateZodiac(command.ExecuteReader()));
}
private SQLiteDataReader GetZodiacDataReader(ZodiacType zodiacType)
{
using var command = _connection.CreateCommand();
command.Connection = _connection;
command.CommandText =
$"select {Zodiac.GetIdColumnName()}, {Zodiac.GetNameColumnName()}, {Zodiac.GetTypeColumnName()} " +
$"from {Zodiac.GetTableName()} " +
$"where {Zodiac.GetTypeColumnName()} = :enum_number;";
command.Parameters.AddWithValue("enum_number", (int)zodiacType);
Console.WriteLine("Execute SQL: " + command.CommandText);
return(command.ExecuteReader());
}
private void Update(Zodiac zodiac)
{
var id = zodiac.Id;
if (id == 0)
{
id = GetZodiac(zodiac.Type).Id;
}
using var command = _connection.CreateCommand();
command.Connection = _connection;
command.CommandText =
$"update {Zodiac.GetTableName()} " +
$"set {Zodiac.GetNameColumnName()} = :new_name, {Zodiac.GetTypeColumnName()} = :new_enum_number " +
"where id = :id;";
command.Parameters.AddWithValue("new_name", zodiac.Name);
command.Parameters.AddWithValue("new_enum_number", (int)zodiac.Type);
command.Parameters.AddWithValue("id", id);
Console.WriteLine("Execute SQL: " + command.CommandText);
command.ExecuteNonQuery();
}
public void SaveOrUpdate(Zodiac zodiac)
{
if (IsZodiacAlreadySaved(zodiac))
{
Update(zodiac);
}
else
{
using var command = _connection.CreateCommand();
command.Connection = _connection;
command.CommandText =
$"insert into {Zodiac.GetTableName()}({Zodiac.GetNameColumnName()}, {Zodiac.GetTypeColumnName()}) " +
"values (:name, :type);";
command.Parameters.AddWithValue("name", zodiac.Name);
command.Parameters.AddWithValue("type", (int)zodiac.Type);
Console.WriteLine("Execute SQL: " + command.CommandText);
command.ExecuteNonQuery();
}
}
private bool IsZodiacAlreadySaved(Zodiac zodiac)
{
return(IsTableEmpty(Zodiac.GetTableName(), Zodiac.GetTypeColumnName(), (int)zodiac.Type));
}
