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); } }