private List<Entity> executeSelectQuery(Query query, Type typeEntity)
{
List<Entity> res = null;
SQLiteCommand cmd = new SQLiteCommand(connection);
cmd.CommandText = query.StatementForDatabase();
try
{
SQLiteDataReader r = cmd.ExecuteReader();
string line = String.Empty;
res = new List<Entity>();
Entity entity;
while (r.Read())
{
entity = (Entity)Activator.CreateInstance(typeEntity);
Dictionary<string, string> mappingTable = entity.mappingTable();
Dictionary<string, Type> serializationParameters = entity.serializationParameters();
PropertyInfo[] properties = entity.GetType().GetProperties();
foreach (var item in mappingTable)
{
List<PropertyInfo>list = properties.Where(o => o.Name == item.Key).ToList();
if (list.Count > 0)
{
PropertyInfo property = list[0];
if (serializationParameters.ContainsKey(item.Value))
{
if (!(r[item.Value] is DBNull))
{
BinaryFormatter deserializer = new BinaryFormatter();
byte[] byteArray = (byte[])r[item.Value];
using (var ms = new MemoryStream(byteArray, 0, byteArray.Length))
{
ms.Write(byteArray, 0, byteArray.Length);
ms.Position = 0;
object obj = deserializer.Deserialize(ms);
property.SetValue(entity, obj);
}
}
}
else
{
property.SetValue(entity, Convert.ChangeType(r[item.Value], property.PropertyType));
}
}
}
res.Add(entity);
}
r.Close();
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
return null;
}
return res;
}
private int executeUpdateInsertQuery(Query query, List<object>binaryObjects)
{
int insertId = 0;
SQLiteCommand cmd = new SQLiteCommand(connection);
cmd.CommandText = query.StatementForDatabase();
foreach (object obj in binaryObjects)
{
SQLiteParameter parameter = new SQLiteParameter("@data" + binaryObjects.IndexOf(obj).ToString(), System.Data.DbType.Binary);
parameter.Value = obj;
cmd.Parameters.Add(parameter);
}
try
{
cmd.ExecuteNonQuery();
string sql = @"select last_insert_rowid()";
cmd.CommandText = sql;
insertId = (int)(long)cmd.ExecuteScalar();
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
return 0;
}
return insertId;
}
private void executeDeleteQuery(Query query)
{
SQLiteCommand cmd = new SQLiteCommand(connection);
cmd.CommandText = query.StatementForDatabase();
try
{
cmd.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
}
}