DeleteCommon <T>
(
string SQLSuffix
)
{
// Create a success return object for this operation
DBSuccess dbSuccess = new DBSuccess();
try
{
// Get the table name
string tableName = typeof(T).Name;
// Create a SQLite DELETE command
SqliteCommand deleteCommand = new SqliteCommand();
// Implement delete command through using, so it's disposed of
using (deleteCommand)
{
// Add the SQL deletion statement to the command
deleteCommand.CommandText = string.Format(
@"DELETE FROM {0} WHERE {1}", tableName, SQLSuffix);
// Get a database connection
using (SqliteConnection db =
new SqliteConnection(DataAccess.SQLITE_CONN_STRING))
{
// Open the database
db.Open();
// Give the delete command access the database
deleteCommand.Connection = db;
// Execute the delete commond
deleteCommand.ExecuteNonQuery();
// Close the database connection
db.Close();
}
}
}
catch (Exception e)
{
// Error encountered alter the success return object
dbSuccess.Success = false;
dbSuccess.ErrorMsg = e.Message;
}
// Return the error object
return(dbSuccess);
}
Update
(
object record
)
{
// Create a success return object for this operation
DBSuccess dbSuccess = new DBSuccess();
// Get the record type
Type recType = record.GetType();
try
{
// Get the record id
int Id = Convert.ToInt32(record.GetType().GetProperty("Id").GetValue(record));
// Create the SET pairs
string setText = DBUtils.GetUpdateValues(record);
// Get the table name
string tableName = recType.Name;
// Create an UPDATE SQLite command
SqliteCommand updateCommand = new SqliteCommand();
// Give the UPDATE command SQL text
updateCommand.CommandText = string.Format("UPDATE {0} SET {1} WHERE ID = {2}", tableName, setText, Id);
// Get a database connection
using (SqliteConnection db =
new SqliteConnection(SQLITE_CONN_STRING))
{
using (updateCommand)
{
// Open the database
db.Open();
// Give the delete command access the database
updateCommand.Connection = db;
// Execute the delete commond
updateCommand.ExecuteNonQuery();
// Close the database connection
db.Close();
}
}
}
catch (Exception e)
{
// Error encountered alter the success return object
dbSuccess.Success = false;
dbSuccess.ErrorMsg = e.Message;
// Log the error
LogHelper.Log(LogLevel.Error, e.Message);
}
// Return the error object
return(dbSuccess);
}
CreateTable <T>
(
bool overwrite = false
)
{
// Method members
string entry = string.Empty;
string fieldType = string.Empty;
string fieldAttributes = string.Empty;
string tableName = typeof(T).Name;
List <string> elemList = new List <string>();
string defValue;
// Create a success return object for this operation
DBSuccess dbSuccess = new DBSuccess();
// If the table exists, nothing to do. If not, then
// force an overwrite
if (TableExists(tableName))
{
return(dbSuccess);
}
// Force a table overwrite
overwrite = true;
try
{
// YES: Create a record of type T
object record = Activator.CreateInstance <T>();
// Iterate through the properties of this record
foreach (var p in record.GetType().GetProperties())
{
// Initialize the database column entry
entry = "{0} {1} {2}";
// Initialize the default value
defValue = string.Empty;
// Get the field type based on the property type
switch (p.PropertyType.Name)
{
case "Int64":
case "Int32":
fieldType = "INTEGER";
defValue = "DEFAULT 0";
break;
case "Double":
fieldType = "DOUBLE";
defValue = "DEFAULT 0.0";
break;
case "DateTime":
fieldType = "DATETIME";
defValue = "DEFAULT CURRENT_TIMESTAMP";
break;
case "Boolean":
fieldType = "BOOLEAN";
defValue = "DEFAULT true";
break;
default:
fieldType = "NVARCHAR (8000)";
defValue = "DEFAULT ' '";
break;
}
// If the property type is an Enum, then use an INTEGER for it
if (Convert.ToBoolean(p.PropertyType.GetType().GetRuntimeProperty("IsEnum").GetValue(p.PropertyType)))
{
fieldType = "INTEGER";
}
// Get the field attributes base on the record property
// attributes
IEnumerable <Attribute> attributesCustom = p.GetCustomAttributes();
// Initialize the field attributes
fieldAttributes = "NULL";
if (attributesCustom.Contains(new PrimaryKeyAttribute()))
{
fieldAttributes += " PRIMARY KEY";
}
if (attributesCustom.Contains(new AutoIncrementAttribute()))
{
fieldAttributes += " AUTOINCREMENT";
}
if (attributesCustom.Contains(new NotNullAttribute()))
{
fieldAttributes = "NOT " + fieldAttributes;
if (!attributesCustom.Contains(new AutoIncrementAttribute()))
{
fieldAttributes += " " + defValue;
}
}
// Create and store the element
elemList.Add(string.Format(entry, p.Name, fieldType, fieldAttributes));
}
// Get the elements of the elemList joined by a comma
string elements = string.Join(", ", elemList);
// Form the SQL creation data
string sql = string.Format("CREATE TABLE IF NOT EXISTS {0} ({1});", tableName, elements);
// If overwriting the present table, drop it first
if (overwrite)
{
sql = string.Format("DROP TABLE IF EXISTS {0}; {1}", tableName, sql);
}
// Get a connection to the SQLite database
using (SqliteConnection db = new SqliteConnection(SQLITE_CONN_STRING))
{
// Open the database
db.Open();
// Create the database table given to this initialization
SqliteCommand createTable = new SqliteCommand(sql, db);
using (createTable)
// Execute the database creation command
createTable.ExecuteReader();
// Close the database
db.Close();
}
}
catch (Exception e)
{
// An error occurred, re-populate the Success object
dbSuccess.Success = false;
dbSuccess.ErrorMsg = e.Message;
// Log the error
LogHelper.Log(LogLevel.Error, e.Message);
}
// Return the error object
return(dbSuccess);
}
