public int Delete(SQLiteConnection dbConnection)
{
Type current = GetType();
string table = current.GetCustomAttribute <SqliteTableAttribute>().TableName;
var propertyInfos = current.GetProperties(BindingFlags.Public | BindingFlags.Instance);
Tuple <string, object, DbType> key = null;
foreach (var prop in propertyInfos)
{
SqliteColumnAttribute sqliteColumn = prop.GetCustomAttribute <SqliteColumnAttribute>();
if (sqliteColumn == null || !sqliteColumn.IsKey)
{
continue;
}
key = Tuple.Create(sqliteColumn.ColumnName, prop.GetValue(this), sqliteColumn.Type);
break;
}
using (SQLiteCommand command = new SQLiteCommand(dbConnection))
{
command.CommandText = string.Format("DELETE FROM {0} WHERE {1}=:{1}", table, key.Item1);
command.Parameters.Add(key.Item1, key.Item3).Value = key.Item2;
return(command.ExecuteNonQuery());
}
}
public virtual List <InputValuesControl.InputItem> GetAsInputItem(bool isNew)
{
List <InputValuesControl.InputItem> items = new List <InputValuesControl.InputItem>();
var propertyInfos = GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in propertyInfos)
{
SqliteColumnAttribute sqliteColumn = prop.GetCustomAttribute <SqliteColumnAttribute>();
if (sqliteColumn == null)
{
continue;
}
var item = new InputValuesControl.InputItem(prop.Name, prop.PropertyType);
item.Label = string.Format("{0}: ", sqliteColumn.Description);
item.Value = prop.GetValue(this);
item.Required = !sqliteColumn.AllowNull;
item.ReadOnly = sqliteColumn.ReadOnly;
if (isNew && sqliteColumn.DefaultValue != null)
{
item.Value = sqliteColumn.DefaultValue;
}
if (isNew && prop.PropertyType == typeof(DateTime))
{
item.Value = DateTime.Now;
}
if (prop.PropertyType == typeof(DateTime))
{
item.Format = "dd/MM/yyyy";
}
items.Add(item);
}
return(items);
}
public void FillInstanceFromReader(SQLiteDataReader reader)
{
var propertyInfos = GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in propertyInfos)
{
SqliteColumnAttribute sqliteColumn = prop.GetCustomAttribute <SqliteColumnAttribute>();
if (sqliteColumn == null)
{
continue;
}
try
{
object value = reader[sqliteColumn.ColumnName];
prop.SetValue(this, value);
}
catch (Exception e)
{
Console.WriteLine("{0} was not recognized. Error: {1}", sqliteColumn.ColumnName, e.InnerException);
}
}
}
public int Save(SQLiteConnection dbConnection, bool isNew = false)
{
Type current = GetType();
string table = current.GetCustomAttribute <SqliteTableAttribute>().TableName;
var propertyInfos = current.GetProperties(BindingFlags.Public | BindingFlags.Instance);
Tuple <string, object, DbType> key = null;
List <Tuple <string, object, DbType> > columns = new List <Tuple <string, object, DbType> >();
foreach (var prop in propertyInfos)
{
SqliteColumnAttribute sqliteColumn = prop.GetCustomAttribute <SqliteColumnAttribute>();
if (sqliteColumn == null)
{
continue;
}
if (sqliteColumn.IsKey)
{
if (sqliteColumn.ReadOnly)
{
key = Tuple.Create(sqliteColumn.ColumnName, prop.GetValue(this), sqliteColumn.Type);
}
else
{
columns.Add(Tuple.Create(sqliteColumn.ColumnName, prop.GetValue(this), sqliteColumn.Type));
key = columns[columns.Count - 1];
}
}
else
{
columns.Add(Tuple.Create(sqliteColumn.ColumnName, prop.GetValue(this), sqliteColumn.Type));
}
}
StringBuilder sql = new StringBuilder();
if (isNew)
{
string keys = string.Join(",", columns.Select(x => string.Format("[{0}]", x.Item1)));
string values = string.Join(",", columns.Select(x => ":" + x.Item1));
sql.AppendFormat("INSERT INTO [{0}] ({1}) VALUES ({2})", table, keys, values);
}
else
{
string values = string.Join(",", columns.Select(x => String.Format("[{0}]=:{1}", x.Item1, x.Item1)));
sql.AppendFormat("UPDATE [{0}] SET {1} WHERE {2}=:{2}", table, values, key.Item1);
}
using (SQLiteCommand command = new SQLiteCommand(dbConnection))
{
command.CommandText = sql.ToString();
foreach (var value in columns)
{
command.Parameters.Add(value.Item1, value.Item3).Value = value.Item2;
}
if (!isNew)
{
command.Parameters.Add(key.Item1, key.Item3).Value = key.Item2;
}
try
{
return(command.ExecuteNonQuery());
}
catch (SQLiteException e)
{
Console.WriteLine(e);
Console.WriteLine(e.StackTrace);
}
}
return(-1);
}
