CRUD на SQLite

namespace DataStorage
{
public abstract class AbstractAdapter : IDisposable
{
static readonly string ConnectionString = ConfigurationManager.AppSettings["DBConnection"];
protected SQLiteConnection conn = new SQLiteConnection(ConnectionString);

public AbstractAdapter()
{
conn.Open();
}
public void Dispose()
{
if (conn.State != (ConnectionState.Closed | ConnectionState.Broken))
{
conn.Close();
}

namespace DataStorage
{
public class CrudAdapter : AbstractAdapter where T : class, new()
{
string tableName;
int primaryKeyCount;
Dictionary fieldsHash;

public CrudAdapter()
{
var ttype = typeof(T);
if (ttype.GetCustomAttributes(typeof(CrudEntityAttribute), false).Count() == 0) throw new InvalidCastException("CrudEntityAttribute is not pressent in " + ttype + " entity");
var classAttr = (CrudEntityAttribute)ttype.GetCustomAttributes(typeof(CrudEntityAttribute), false).First();
if (String.IsNullOrEmpty(classAttr.TableName)) throw new InvalidCastException("TableName is not set for " + ttype);
tableName = classAttr.TableName;

var fields = ttype.GetFields(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public)
.Where((x) => { return x.GetCustomAttributes(typeof(CrudIndexAttribute), false).Count() > 0; });
if (fields.Count() == 0) throw new InvalidCastException("No CrudIndexAttribute in " + ttype + " entity");

fieldsHash = new Dictionary();
foreach (FieldInfo field in fields)
{
var attr = (CrudIndexAttribute)field.GetCustomAttributes(typeof(CrudIndexAttribute), false).First();
if (attr.FieldIndex<0) throw new InvalidCastException("No index is set for "+field+" in " + ttype + " entity"); if(!String.IsNullOrEmpty(attr.KeyName))primaryKeyCount++; fieldsHash.Add(field, attr); } if (primaryKeyCount == 0) throw new InvalidCastException("No primary keys in " + ttype + " entity"); fieldsHash = fieldsHash.OrderBy((x) => x.Value.FieldIndex).ToDictionary((x) => x.Key, (x)=>x.Value);

int indexer = 0;
foreach (var fld in fieldsHash)
{
if (fld.Value.FieldIndex != indexer++) throw new Exception("Invalid indexing in " + fld.Value);
}
}

public List GetAllEntries()
{
var command = conn.CreateCommand();
command.CommandText = "Select * from ["+tableName+"]";
var reader = command.ExecuteReader();

List output = new List();
var ttype = typeof(T);

while (reader.Read())
{
output.Add(CreateEntity(reader));
}
reader.Close();
return output;
}
public T GetEntity(params object[] key)
{
if (primaryKeyCount != key.Count()) throw new InvalidOperationException("Invalid amount of primary keys should be " + primaryKeyCount);
var command = conn.CreateCommand();

command.CommandText = "Select * from [" + tableName + "] where" + BuildKeyClosure(key);
var reader = command.ExecuteReader();

T ret = null;
if (reader.Read()) ret = CreateEntity(reader);
reader.Close();
return ret;
}
public void Delete(T t)
{
var command = conn.CreateCommand();
command.CommandText = "delete from [" + tableName + "] where" + BuildKeyClosure(t);
int cnt = command.ExecuteNonQuery();
if (cnt > 2) throw new Exception("Ops more than one record deleted");
}

public T SaveOrUpdate(T t)
{
Delete(t);

StringBuilder sb = new StringBuilder();
foreach (var fld in fieldsHash)
{
if (sb.Length > 0) sb.Append(",");
sb.Append("'" + fld.Key.GetValue(t) + "'");
}
var command = conn.CreateCommand();
command.CommandText = String.Format("insert into [{0}] values ({1})",
tableName, sb.ToString());
command.ExecuteNonQuery();

return t;
}

private T CreateEntity(SQLiteDataReader reader)
{
T t = new T();
foreach (var fld in fieldsHash)
{
fld.Key.SetValue(t, Convert.ChangeType(reader.GetValue(fld.Value.FieldIndex), fld.Key.FieldType));
}
return t;
}

private string BuildKeyClosure(T t)
{
var key = new object[primaryKeyCount];
StringBuilder sb = new StringBuilder();
int indexer = 0;
foreach (var fld in fieldsHash)
{
if (!String.IsNullOrEmpty(fld.Value.KeyName))
{
sb.Append(" " + fld.Value.KeyName + "=='" + fld.Key.GetValue(t) + "'");
indexer++;
}
}
return sb.ToString();
}
private string BuildKeyClosure(object[] key)
{
StringBuilder sb = new StringBuilder();
int indexer = 0;

[CrudIndex(FieldIndex = 1)]
private int isCompleted;

[CrudIndex(FieldIndex = 2)]
public string FileName;

var ret = crud.GetAllEntries();
var rett = crud.GetEntity("test");

crud.SaveOrUpdate(rett);

public string this[string index]
{
get
{
return this.GetSetting(index);
}
set
{
SetSetting(index, value);
}
}

public Dictionary Settings
{
get
{
if (CacheNeedsUpdate)
{
var command = conn.CreateCommand();
command.CommandText = "Select * from [settings]";

var reader = command.ExecuteReader();
settings.Clear();
while (reader.Read())
{
settings.Add(reader.GetString(0), reader.GetString(1));
}
reader.Close();
CacheNeedsUpdate = false;
}
return settings;
}
set
{
if (CacheNeedsUpdate) { var z = Settings; }
foreach (KeyValuePair setting in value)
{
if (GetSetting(setting.Key, true) != setting.Value)
{
SetSetting(setting.Key, setting.Value);
}
}
settings = value;
}
}

public void SetSetting(string key, string value)
{
var command = conn.CreateCommand();
if (GetSetting(key) != null)
{
settings[key] = value;
command.CommandText = "update [settings] set [value]=\"" + value + "\" where [name]=\"" + key + "\"";
}
else
{
settings.Add(key, value);
command.CommandText = "insert into settings ([name],[value]) values (\"" + key + "\",\"" + value + "\")";
}
command.ExecuteNonQuery();
}