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(); } }