/// <summary>
/// Inserts the row.
/// </summary>
/// <param name="insertObj">The insert object.</param>
/// <returns></returns>
public int InsertRow(object obj)
{
int result = 0;
InsertObject insertObject = null;
string sqlInsert = null;
try
{
insertObject = InsertObject.CreateInstance(obj);
sqlInsert = string.Format(StringFormat.SqlInsert, insertObject.Table, insertObject.Columns, insertObject.Values);
result = SQLiteQuery.ExecuteNonQuery(sqlInsert, this.Connection);
if (result > 0)
{
this.HandleByteArrayList(insertObject.Table, insertObject.ByteArrayColumns, insertObject.ByteArrayValues, insertObject.KeyString);
}
}
catch (Exception exception)
{
APILog.Error(this, "InsertRow", exception);
}
finally
{
insertObject = null;
sqlInsert = null;
}
return(result);
}
/// <summary>
/// Drops the table.
/// </summary>
/// <typeparam name="T"></typeparam>
public void DropTable <T>()
{
var objectType = typeof(T);
string sqlQuery = string.Format("DROP TABLE IF EXISTS {0}", objectType.Name);
SQLiteQuery.ExecuteNonQuery(sqlQuery, this.Connection);
}
/// <summary>
/// Deletes all.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public bool DeleteAll <T>()
{
var objectType = typeof(T);
string sqlQuery = string.Format("DELETE FROM {0}", objectType.Name);
return(SQLiteQuery.ExecuteNonQuery(sqlQuery, this.Connection) > 0);
}
internal void Update()
{
var sb = new System.Text.StringBuilder();
sb.AppendLine("UPDATE users SET");
sb.AppendLine(" name = @name");
sb.AppendLine(",real_name = @real_name");
sb.AppendLine(",age = @age");
sb.AppendLine(",height = @height");
sb.AppendLine(",weight = @weight");
sb.AppendLine(",blood_type = @blood_type");
sb.AppendLine(",race = @race");
sb.AppendLine(",guild_name = @guild_name");
sb.AppendLine(",hobby = @hobby");
sb.AppendLine(",character_voice = @character_voice");
sb.AppendLine(",birth_month = @birth_month");
sb.AppendLine(",birth_day = @birth_day");
sb.AppendLine("WHERE");
sb.AppendLine("cd = @cd");
var param = new Dictionary <string, object> {
{ "name", this.Name },
{ "real_name", this.RealName },
{ "age", this.Age },
{ "height", this.Height },
{ "weight", this.Weight },
{ "blood_type", this.BloodType },
{ "race", this.Race },
{ "guild_name", this.GuildName },
{ "hobby", this.Hobby },
{ "character_voice", this.CV },
{ "birth_month", this.BirthMonth },
{ "birth_day", this.BirthDay }
};
using (var q = new SQLiteQuery())
using (var trans = q.BeginTransaction()) {
try {
q.ExecuteNonQuery(sb.ToString(), param);
trans.Commit();
} catch {
trans.Rollback();
throw;
}
}
}
/// <summary>
/// Deletes the row.
/// </summary>
/// <param name="deleteObj">The delete object.</param>
/// <returns></returns>
public int DeleteRow(object deleteObj)
{
int aux = 1;
var keyColumnList = new List <string>();
var objectType = deleteObj.GetType();
var propertyList = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
StringBuilder deleteQuery = new StringBuilder();
deleteQuery.Append("DELETE FROM ");
deleteQuery.Append(objectType.Name);
deleteQuery.Append(" WHERE ");
aux = 1;
foreach (var property in propertyList.Where(x => x.IsDataMember() && x.IsKey()))
{
keyColumnList.Add(property.Name);
}
foreach (string key in keyColumnList)
{
PropertyInfo property = propertyList.FirstOrDefault(x => x.Name == key);
if (property != null)
{
deleteQuery.Append(key);
deleteQuery.Append("=");
deleteQuery.Append("'");
deleteQuery.Append(property.GetValue(deleteObj, null));
deleteQuery.Append("'");
}
if (aux < keyColumnList.Count)
{
deleteQuery.Append(" AND ");
}
aux++;
}
return(SQLiteQuery.ExecuteNonQuery(deleteQuery.ToString(), this.Connection));
}
internal void Delete()
{
var sb = new System.Text.StringBuilder();
sb.AppendLine("DELETE FROM users");
sb.AppendLine("WHERE");
sb.AppendLine("cd = @CD");
using (var q = new SQLiteQuery())
using (var trans = q.BeginTransaction()) {
try {
q.ExecuteNonQuery(sb.ToString(), new Dictionary <string, object> {
{ "cd", this.Cd }
});
trans.Commit();
} catch {
trans.Rollback();
throw;
}
}
}
/// <summary>
/// Creates the table.
/// </summary>
/// <typeparam name="T"></typeparam>
public void CreateTable <T>()
{
try
{
int aux = 1;
List <string> keyColumnList = new List <string>();
var objectType = typeof(T);
var propertyList = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
StringBuilder sbCreateTable = new StringBuilder();
sbCreateTable.Append("CREATE TABLE IF NOT EXISTS ");
sbCreateTable.Append(objectType.Name);
sbCreateTable.Append("(");
foreach (var property in propertyList)
{
if (property.IsDataMember())
{
if (property.IsKey())
{
keyColumnList.Add(property.Name);
}
sbCreateTable.Append(property.Name);
sbCreateTable.Append(" ");
sbCreateTable.Append(property.PropertyType.ToSQLiteDataType());
if (property.PropertyType.IsNull())
{
sbCreateTable.Append(" NULL");
}
else
{
sbCreateTable.Append(" NOT NULL");
}
sbCreateTable.Append(",");
}
}
if (keyColumnList.Count > 0)
{
sbCreateTable.Append("PRIMARY KEY (");
foreach (string key in keyColumnList)
{
sbCreateTable.Append(key);
if (aux < keyColumnList.Count)
{
sbCreateTable.Append(",");
}
aux++;
}
sbCreateTable.Append("));");
}
else
{
sbCreateTable.Remove((sbCreateTable.Length - 1), 1);
sbCreateTable.Append(");");
}
SQLiteQuery.ExecuteNonQuery(sbCreateTable.ToString(), this.Connection);
}
catch (Exception exception)
{
APILog.Error(this, "CreateTable", exception);
}
}
/// <summary>
/// Updates the row.
/// </summary>
/// <param name="updateObj">The update object.</param>
/// <param name="whereClause">The where clause.</param>
/// <returns></returns>
public int UpdateRow(object updateObj, string whereClause = "")
{
int aux = 1;
var keyColumnList = new List <string>();
var byteArrayColumnList = new List <string>();
var byteArrayList = new List <byte[]>();
var objectType = updateObj.GetType();
var propertyList = objectType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
int propertiesCount = propertyList.Length;
StringBuilder updateQuery = new StringBuilder();
updateQuery.Append("UPDATE ");
updateQuery.Append(objectType.Name);
updateQuery.Append(" SET ");
foreach (var property in propertyList)
{
if (property.IsDataMember())
{
if (property.IsKey())
{
keyColumnList.Add(property.Name);
}
updateQuery.Append(property.Name);
updateQuery.Append("=");
object value = property.GetValue(updateObj, null);
value = Utility.GetValue(byteArrayColumnList, byteArrayList, property, value);
if (value == null)
{
updateQuery.Append("NULL");
}
else
{
updateQuery.Append("'");
updateQuery.Append(value.ToString());
updateQuery.Append("'");
}
if (aux < propertiesCount)
{
updateQuery.Append(",");
}
}
aux++;
}
if (updateQuery.ToString().EndsWith(","))
{
updateQuery.Remove((updateQuery.Length - 1), 1);
}
updateQuery.Append(" WHERE ");
aux = 1;
if (string.IsNullOrWhiteSpace(whereClause))
{
StringBuilder sbWhere = new StringBuilder();
foreach (string key in keyColumnList)
{
PropertyInfo property = propertyList.FirstOrDefault(x => x.Name == key);
if (property != null)
{
sbWhere.Append(key);
sbWhere.Append("=");
sbWhere.Append("'");
sbWhere.Append(property.GetValue(updateObj, null));
sbWhere.Append("'");
}
if (aux < keyColumnList.Count)
{
sbWhere.Append(" AND ");
}
aux++;
}
whereClause = sbWhere.ToString();
}
updateQuery.Append(whereClause);
int result = SQLiteQuery.ExecuteNonQuery(updateQuery.ToString(), this.Connection);
if (result > 0)
{
this.HandleByteArrayList(objectType.Name, byteArrayColumnList, byteArrayList, whereClause);
}
return(result);
}