public override bool Delete(string table, QueryFilter queryFilter)
{
Dictionary<string, object> ps = new Dictionary<string, object>();
string query = "DELETE FROM " + table + (queryFilter != null ? (" WHERE " + queryFilter.ToSQL(':', out ps)) : "");
SQLiteCommand cmd = new SQLiteCommand(query);
AddParams(ref cmd, ps);
try
{
ExecuteNonQuery(cmd);
}
catch (Exception e)
{
MainConsole.Instance.Error("[SQLiteDataManager] Delete(" + query + "), " + e);
return false;
}
CloseReaderCommand(cmd);
return true;
}
public override bool Update(string table, Dictionary<string, object> values, Dictionary<string, int> incrementValues, QueryFilter queryFilter, uint? start, uint? count)
{
if ((values == null || values.Count < 1) && (incrementValues == null || incrementValues.Count < 1))
{
MainConsole.Instance.Warn("Update attempted with no values");
return false;
}
string query = string.Format("UPDATE {0}", table); ;
Dictionary<string, object> ps = new Dictionary<string, object>();
string filter = "";
if (queryFilter != null && queryFilter.Count > 0)
{
filter = " WHERE " + queryFilter.ToSQL(':', out ps);
}
List<string> parts = new List<string>();
if (values != null)
{
foreach (KeyValuePair<string, object> value in values)
{
string key = ":updateSet_" + value.Key.Replace("`", "");
ps[key] = value.Value;
parts.Add(string.Format("{0} = {1}", value.Key, key));
}
}
if (incrementValues != null)
{
foreach (KeyValuePair<string, int> value in incrementValues)
{
string key = ":updateSet_increment_" + value.Key.Replace("`", "");
ps[key] = value.Value;
parts.Add(string.Format("{0} = {0} + {1}", value.Key, key));
}
}
query += " SET " + string.Join(", ", parts.ToArray()) + filter;
if (start.HasValue)
{
query += " LIMIT " + start.Value.ToString();
if (count.HasValue)
{
query += ", " + count.Value.ToString();
}
}
SQLiteCommand cmd = new SQLiteCommand(query);
AddParams(ref cmd, ps);
try
{
ExecuteNonQuery(cmd);
}
catch (SQLiteException e)
{
MainConsole.Instance.Error("[SQLiteLoader] Update(" + query + "), " + e);
}
CloseReaderCommand(cmd);
return true;
}
public override List<string> Query(string[] wantedValue, string table, QueryFilter queryFilter, Dictionary<string, bool> sort, uint? start, uint? count)
{
string query = string.Format("SELECT {0} FROM {1}", string.Join(", ", wantedValue), table); ;
Dictionary<string, object> ps = new Dictionary<string, object>();
List<string> retVal = new List<string>();
List<string> parts = new List<string>();
if (queryFilter != null && queryFilter.Count > 0)
{
query += " WHERE " + queryFilter.ToSQL(':', out ps);
}
if (sort != null && sort.Count > 0)
{
parts = new List<string>();
foreach (KeyValuePair<string, bool> sortOrder in sort)
{
parts.Add(string.Format("`{0}` {1}", sortOrder.Key, sortOrder.Value ? "ASC" : "DESC"));
}
query += " ORDER BY " + string.Join(", ", parts.ToArray());
}
if (start.HasValue)
{
query += " LIMIT " + start.Value.ToString();
if (count.HasValue)
{
query += ", " + count.Value.ToString();
}
}
int i = 0;
var cmd = PrepReader(query);
AddParams(ref cmd, ps);
using (IDataReader reader = cmd.ExecuteReader())
{
var RetVal = new List<string>();
while (reader.Read())
{
for (i = 0; i < reader.FieldCount; i++)
{
Type r = reader[i].GetType();
RetVal.Add(r == typeof(DBNull) ? null : reader[i].ToString());
}
}
//reader.Close();
CloseReaderCommand(cmd);
return RetVal;
}
}
private List<string> Query2(string query, QueryFilter queryFilter, Dictionary<string, bool> sort, uint? start, uint? count)
{
Dictionary<string, object> ps = new Dictionary<string, object>();
List<string> retVal = new List<string>();
List<string> parts = new List<string>();
if (queryFilter != null && queryFilter.Count > 0)
{
query += " WHERE " + queryFilter.ToSQL(':', out ps);
}
if (sort != null && sort.Count > 0)
{
parts = new List<string>();
foreach (KeyValuePair<string, bool> sortOrder in sort)
{
parts.Add(string.Format("`{0}` {1}", sortOrder.Key, sortOrder.Value ? "ASC" : "DESC"));
}
query += " ORDER BY " + string.Join(", ", parts.ToArray());
}
if (start.HasValue)
{
query += " LIMIT " + start.Value.ToString();
if (count.HasValue)
{
query += ", " + count.Value.ToString();
}
}
int i = 0;
var cmd = PrepReader(query);
AddParams(ref cmd, ps);
lock (GetLock())
{
using (SqliteDataReader reader = cmd.ExecuteReader())
{
var RetVal = new List<string>();
while (reader.Read())
{
if (reader.HasRows)
{
for (i = 0; i < reader.FieldCount; i++)
RetVal.Add(reader[i] == null ? null : reader[i].ToString());
}
}
//reader.Close();
CloseReaderCommand(cmd);
return RetVal;
}
}
}
public override bool Delete(string table, QueryFilter queryFilter)
{
Dictionary<string, object> ps = new Dictionary<string,object>();
uint j=0;
string query = "DELETE FROM " + table + (queryFilter != null ? (" WHERE " + queryFilter.ToSQL('?', out ps, ref j)) : "");
try
{
ExecuteNonQuery(query, ps);
}
catch (Exception e)
{
MainConsole.Instance.Error("[MySQLDataLoader] Delete(" + query + "), " + e);
return false;
}
return true;
}
public override List<string> Query(string[] wantedValue, string table, QueryFilter queryFilter, Dictionary<string, bool> sort, uint? start, uint? count)
{
string query = string.Format("SELECT {0} FROM {1}", string.Join(", ", wantedValue), table); ;
Dictionary<string, object> ps = new Dictionary<string,object>();
List<string> retVal = new List<string>();
List<string> parts = new List<string>();
if (queryFilter != null && queryFilter.Count > 0)
{
uint j = 0;
query += " WHERE " + queryFilter.ToSQL('?', out ps, ref j);
}
if (sort != null && sort.Count > 0)
{
parts = new List<string>();
foreach (KeyValuePair<string, bool> sortOrder in sort)
{
parts.Add(string.Format("`{0}` {1}", sortOrder.Key, sortOrder.Value ? "ASC" : "DESC"));
}
query += " ORDER BY " + string.Join(", ", parts.ToArray());
}
if(start.HasValue){
query += " LIMIT " + start.Value.ToString();
if (count.HasValue)
{
query += ", " + count.Value.ToString();
}
}
IDataReader reader = null;
int i = 0;
try
{
using (reader = Query(query, ps))
{
while (reader.Read())
{
for (i = 0; i < reader.FieldCount; i++)
{
Type r = reader[i].GetType();
retVal.Add(r == typeof(DBNull) ? null : reader.GetString(i));
}
}
return retVal;
}
}
catch (Exception e)
{
MainConsole.Instance.Error("[MySQLDataLoader] Query(" + query + "), " + e);
return null;
}
finally
{
try
{
if (reader != null)
{
reader.Close();
//reader.Dispose ();
}
}
catch (Exception e)
{
MainConsole.Instance.Error("[MySQLDataLoader] Query(" + query + "), " + e);
}
}
}