public static bool erase(Model model, string where, params OdbcParameter[] parameters)
{
string identifier = (string)getFieldValue(model, model.key);
string table = model.GetType().Name;
// Parse Query Parameters
OdbcParameter[] defaultParameters = { new OdbcParameter(model.key, identifier) };
parameters = (parameters == null)? defaultParameters : parameters;
// Execute SQL query
try {
OdbcCommand cmd = db.CreateCommand();
cmd.CommandText = String.Format("DELETE FROM {0} WHERE {1}=?", table, model.key);
cmd.Parameters.Add(new OdbcParameter(model.key, identifier));
// Execute the SQL query
int rows = cmd.ExecuteNonQuery();
// Rows affected will be non-zero if successful
return (rows > 0);
} catch (Exception e) {
__fail("Erase failed", e);
return false;
}
}
public static bool exists(Model model)
{
string identifier = (string)getFieldValue(model, model.key);
string table = model.GetType().Name;
// Execute SQL query
try {
OdbcCommand cmd = db.CreateCommand();
cmd.CommandText = String.Format("SELECT {1} FROM {0} WHERE {1}=?", table, model.key);
cmd.Parameters.Add(new OdbcParameter(model.key, identifier));
// Execute the SQL query
int rows = cmd.ExecuteNonQuery();
// Non-existence will return 0 rows
return !(rows == 0);
} catch (Exception e) {
__fail("Existence check failed", e);
return false;
}
}
private static Object getFieldValue(Model model, string fieldName)
{
Type ReflectionObject = model.GetType();
return ReflectionObject.GetField(fieldName).GetValue(model);
}
private static List<string> getFieldNames(Model model)
{
Type ReflectionObject = model.GetType();
List<string> fields = new List<string>();
// Use Reflection to grab the field names
foreach (FieldInfo field in ReflectionObject.GetFields())
fields.Add(field.Name);
return fields;
}
public static bool put(Model model)
{
List<string> fieldNames = getFieldNames(model);
string fields = String.Join(", ", fieldNames);
// Determine the table by the name of the Model
string table = model.GetType().Name;
// Use Reflection to grab the field names and parameterize the values
string values = null;
Type ReflectionObject = model.GetType();
List<OdbcParameter> parameters = new List<OdbcParameter>();
foreach (FieldInfo field in ReflectionObject.GetFields()) {
parameters.Add(new OdbcParameter(field.Name, String.Format("{0}", field.GetValue(model))));
values += (string.IsNullOrEmpty(values))? "?" : ", ?";
}
// Build SQL query
string sql = String.Format("REPLACE INTO {0}({1}) VALUES ({2})", table, fields, values);
// Execute SQL query
OdbcCommand cmd;
try {
cmd = db.CreateCommand();
cmd.CommandText = sql;
// Inject the Parameters
foreach (OdbcParameter parameter in parameters)
cmd.Parameters.Add(parameter);
// Operation was successful if rows affected > 0
return (cmd.ExecuteNonQuery() > 0);
} catch (OdbcException e) {
__fail("Write failed", e);
return false;
}
}
public static List<Model> get(Model model, string orderBy = null, int nLimit = 0, string where = null, params OdbcParameter[] parameters)
{
string[] fieldNames = getFieldNames(model).ToArray();
string fields = String.Join(", ", fieldNames);
string limit;
// Determine the table by the name of the Model
string table = model.GetType().Name;
// Parse SQL query clauses
orderBy = (null == orderBy) ? null : String.Format(" ORDER BY {0}", orderBy);
limit = ( 0 == nLimit) ? null : String.Format(" LIMIT {0}", nLimit);
where = (null == where) ? null : String.Format(" WHERE {0}", where);
// build SQL query
string sql = String.Format("SELECT {0} FROM {1}{2}{3}{4}", fields, table, where, orderBy, limit);
// Execute SQL query
List<Model> records = new List<Model>();
try {
OdbcCommand cmd = db.CreateCommand();
cmd.CommandText = sql;
// Inject the Parameters
foreach (OdbcParameter parameter in parameters)
cmd.Parameters.Add(parameter);
// Read the results
OdbcDataReader result = cmd.ExecuteReader();
while (result.Read()) {
if (model is Author)
records.Add( __constructAuthor(result) );
else
records.Add( __constructStory(result) );
}
} catch (OdbcException e) {
__fail("Retrieve failed", e);
}
return records;
}