public void Init()
{
try
{
string path = Path.Combine(Program.Config.LauncherDir, "cards.cdb");
m_cards = new Dictionary<int, CardInfos>();
if (!File.Exists(path)) return;
SQLiteConnection connection = new SQLiteConnection("Data Source=" + path);
connection.Open();
SQLiteDataReader reader = new SQLiteCommand("SELECT id, alias, type, level, race, attribute, atk, def FROM datas", connection).ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
CardInfos infos = new CardInfos(id)
{
AliasId = reader.GetInt32(1),
Type = reader.GetInt32(2),
Level = reader.GetInt32(3),
Race = reader.GetInt32(4),
Attribute = reader.GetInt32(5),
Atk = reader.GetInt32(6),
Def = reader.GetInt32(7)
};
m_cards.Add(id, infos);
}
reader.Close();
reader = new SQLiteCommand("SELECT id, name, desc FROM texts", connection).ExecuteReader();
while (reader.Read())
{
int key = reader.GetInt32(0);
if (m_cards.ContainsKey(key))
{
m_cards[key].Name = reader.GetString(1);
m_cards[key].CleanedName = m_cards[key].Name.Trim().ToLower().Replace("-", " ").Replace("'", " ").Replace(" ", " ").Replace(" ", " ");
m_cards[key].Description = reader.GetString(2);
}
}
connection.Close();
}
catch (Exception)
{
}
}
public void Init()
{
try
{
m_cards = new Dictionary<int, CardInfos>();
string str = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) ?? "";
string str2 = Path.Combine(str, @"cards.cdb");
if (!File.Exists(str2)) return;
SQLiteConnection connection = new SQLiteConnection("Data Source=" + str2);
connection.Open();
SQLiteDataReader reader = new SQLiteCommand("SELECT id, alias, type, level, race, attribute, atk, def FROM datas", connection).ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
CardInfos infos = new CardInfos(id)
{
AliasId = reader.GetInt32(1),
Type = reader.GetInt32(2),
Level = reader.GetInt32(3),
Race = reader.GetInt32(4),
Attribute = reader.GetInt32(5),
Atk = reader.GetInt32(6),
Def = reader.GetInt32(7)
};
m_cards.Add(id, infos);
}
reader.Close();
reader = new SQLiteCommand("SELECT id, name, desc FROM texts", connection).ExecuteReader();
while (reader.Read())
{
int key = reader.GetInt32(0);
if (m_cards.ContainsKey(key))
{
m_cards[key].Name = reader.GetString(1);
m_cards[key].CleanedName = m_cards[key].Name.Trim().ToLower().Replace("-", " ").Replace("'", " ").Replace(" ", " ").Replace(" ", " ");
m_cards[key].Description = reader.GetString(2);
}
}
connection.Close();
Loaded = true;
}
catch (Exception)
{
MessageBox.Show("Error loading cards.cdb");
}
}
public int this[string Key]
{
set
{
Context.Create();
if (Context.Connection.SQLiteCountByColumnName(Context.Name, "Key", Key) == 0)
{
var sql = "insert into ";
sql += Context.Name;
sql += " (Key, ValueInt32) values (";
sql += "'";
sql += Key;
sql += "'";
sql += ", ";
sql += ((object)value).ToString();
sql += ")";
new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery();
return;
}
#region update
{
var sql = "update ";
sql += Context.Name;
sql += " set ValueInt32 = ";
sql += ((object)value).ToString();
sql += " where Key = ";
sql += "'";
sql += Key;
sql += "'";
new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery();
}
#endregion
}
get
{
Context.Create();
var sql = "select ValueInt32 from ";
sql += Context.Name;
sql += " where Key = ";
sql += "'";
sql += Key;
sql += "'";
//new SQLiteCommand(sql, Connection).ExecuteScalar();
var value = 0;
var reader = new SQLiteCommand(sql, Context.Connection).ExecuteReader();
if (reader.Read())
{
value = reader.GetInt32(0);
}
reader.Close();
return value;
}
}
public static bool SQLiteTableExists(this SQLiteConnection c, string name)
{
var w = "select name from sqlite_master where type='table' and name=";
w += "'";
w += name;
w += "'";
var reader = new SQLiteCommand(w, c).ExecuteReader();
var value = reader.Read();
reader.Close();
return value;
}
public static int SQLiteCountByColumnName(this SQLiteConnection Connection, string Name, string ByColumnName, string ValueString)
{
var sql = "select count(*) from ";
sql += Name;
sql += " where ";
sql += ByColumnName;
sql += " = ";
sql += "'";
sql += ValueString;
sql += "'";
var value = 0;
var reader = new SQLiteCommand(sql, Connection).ExecuteReader();
if (reader.Read())
{
value = reader.GetInt32(0);
}
reader.Close();
return value;
}
public Point this[string id]
{
set
{
Context.Create();
if (Context.Connection.SQLiteCountByColumnName(Context.Name, "id", id) == 0)
{
var sql = "insert into ";
sql += Context.Name;
sql += " (id, x, y) values (";
sql += "'";
sql += id;
sql += "'";
sql += ", ";
sql += "'";
sql += value.x;
sql += "'";
sql += ", ";
sql += "'";
sql += value.y;
sql += "'";
sql += ")";
new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery();
return;
}
#region update
{
var sql = "update ";
sql += Context.Name;
sql += " set x = ";
sql += "'";
sql += value.x;
sql += "'";
sql += " set y = ";
sql += "'";
sql += value.y;
sql += "'";
sql += " where id = ";
sql += "'";
sql += id;
sql += "'";
new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery();
}
#endregion
}
get
{
Context.Create();
var sql = "select x, y from ";
sql += Context.Name;
sql += " where id = ";
sql += "'";
sql += id;
sql += "'";
//new SQLiteCommand(sql, Connection).ExecuteScalar();
var value = default(Point);
var reader = new SQLiteCommand(sql, Context.Connection).ExecuteReader();
if (reader.Read())
{
value = new Point { x = reader.GetString(0), y = reader.GetString(1) };
}
reader.Close();
return value;
}
}
public static bool SQLiteTableExists(this SQLiteConnection c, string name)
{
// http://www.electrictoolbox.com/check-if-mysql-table-exists/
//var w = "select name from sqlite_master where type='table' and name=";
var w = "select table_name from information_schema.tables where table_name=";
w += "'";
w += name;
w += "'";
var reader = new SQLiteCommand(w, c).ExecuteReader();
var value = reader.Read();
reader.Close();
return value;
}
public List<string> GetKeys()
{
var sql = "select id from ";
sql += Name;
var value = new List<string>();
var reader = new SQLiteCommand(sql, Connection).ExecuteReader();
while (reader.Read())
{
value.Add(reader.GetString(0));
}
reader.Close();
return value;
}
public DateTime GetUserWorkStart(DateTimeOffset date)
{
var firstEntryDateTime = DateTime.Now; // default value
try
{
var firstEntryReader = new SQLiteCommand("SELECT time FROM " + Settings.WindowsActivityTable +
" WHERE STRFTIME('%s', DATE(time))==STRFTIME('%s', DATE('" + date.Date.ToString("u") + "'))" +
" AND STRFTIME('%H', TIME(time)) >= STRFTIME('%H', TIME('04:00:00'))" + // day start should be after 04 am
" AND process != '" + Dict.Idle +
"' ORDER BY time ASC LIMIT 1;", _connection).ExecuteReader();
if (firstEntryReader.HasRows)
{
firstEntryReader.Read(); // read only once
firstEntryDateTime = DateTime.Parse((string)firstEntryReader["time"]);
}
firstEntryReader.Close();
}
catch (Exception e)
{
LogError(e.Message);
}
return firstEntryDateTime;
}
public DateTime GetUserWorkEnd(DateTimeOffset date)
{
var lastEntryDateTime = DateTime.Now;
try
{
var lastEntryReader = new SQLiteCommand("SELECT time FROM " + Settings.WindowsActivityTable +
" WHERE STRFTIME('%s', DATE(time))==STRFTIME('%s', DATE('" +
date.Date.ToString("u") + "'))" +
" AND process != '" + Dict.Idle + "' ORDER BY time DESC LIMIT 1;",
_connection).ExecuteReader();
if (lastEntryReader.HasRows)
{
lastEntryReader.Read(); // read only once
lastEntryDateTime = DateTime.Parse((string)lastEntryReader["time"]);
}
lastEntryReader.Close();
}
catch (Exception e)
{
LogError(e.Message);
}
return lastEntryDateTime;
}
public TimeZoneInfo GetLastTimeZoneEntry()
{
try
{
var reader = new SQLiteCommand("SELECT timezone FROM " + Settings.TimeZoneTable + " ORDER BY time DESC LIMIT 1;", _connection).ExecuteReader();
if (reader.HasRows)
{
reader.Read(); // read only once
var timeZone = TimeZoneInfo.FindSystemTimeZoneById((string)reader["timezone"]);
return timeZone;
}
reader.Close();
return null; // no entry or failed to parse
}
catch (Exception e)
{
LogError(e.Message);
return null; // other error
}
}
/// <summary>
/// Run a query against the Database.
/// </summary>
/// <param name="sql">The SQL to run</param>
/// <returns>A DataTable containing the result set.</returns>
public DataTable ExecuteReader(string sql)
{
SQLiteConnection connection = new SQLiteConnection(connectionString);
try
{
connection.Open();
var reader = new SQLiteCommand(connection)
{
CommandText = sql
}.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
reader.Close();
return (dataTable);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
connection.Close();
}
}
