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"); } }
internal Action GetAction(string actionName) { var cn = new SQLiteConnection("Data Source=" + DbName + ";Version=3;"); cn.Open(); var reader = new SQLiteCommand("select id, command, arg1, arg2 from actions where id = '" + actionName + "'", cn).ExecuteReader(); Action action = null; try { if (reader.Read()) { action = new Action(); action.Id = reader.GetString(reader.GetOrdinal("id")); action.Arg1 = reader.GetString(reader.GetOrdinal("arg1")); var arg2 = reader["arg2"]; action.Arg2 = arg2.ToString(); //action.Arg3 = reader.GetString(reader.GetOrdinal("arg3")); if (reader.GetString(reader.GetOrdinal("command")) == "pgdb-backup") { action.Command = new PostgreSQLBackupCommand(action.Arg1, action.Arg2, action.Arg3, action.Arg4); } if (reader.GetString(reader.GetOrdinal("command")) == "execute-bat") { action.Command = new ExecuteBatCommand(action.Arg1); } if (reader.GetString(reader.GetOrdinal("command")) == "sync-actions") { action.Command = new SyncActionsCommand(action.Arg1); } } } finally { cn.Close(); } return action; }
public string this[string Key] { set { Context.Create(); if (Context.Connection.SQLiteCountByColumnName(Context.Name, "Key", Key) == 0) { var sql = "insert into "; sql += Context.Name; sql += " (Key, ValueString) values ("; sql += "'"; sql += Key; sql += "'"; sql += ", "; sql += "'"; sql += value; sql += "'"; sql += ")"; new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery(); return; } #region update { var sql = "update "; sql += Context.Name; sql += " set ValueString = "; sql += "'"; sql += value; sql += "'"; sql += " where Key = "; sql += "'"; sql += Key; sql += "'"; new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery(); } #endregion } get { Context.Create(); var sql = "select ValueString from "; sql += Context.Name; sql += " where Key = "; sql += "'"; sql += Key; sql += "'"; //new SQLiteCommand(sql, Connection).ExecuteScalar(); var value = default(string); var reader = new SQLiteCommand(sql, Context.Connection).ExecuteReader(); if (reader.Read()) { value = reader.GetString(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 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; }
internal FeatureDataTable GetBaseTable() { const string sqlPragmaTableInfo ="PRAGMA table_info('{0}');"; const string sqlSelectHasColumnData = "SELECT COUNT(*) FROM \"sqlite_master\" WHERE \"type\"='table' AND \"name\"='gpkg_column_data';"; const string sqlSelectColumnData = "SELECT * FROM \"gpkg_column_data\" WHERE \"table_name\"=? AND \"column_name\"=?;"; const string sqlSelectColumnConstraint = "SELECT * FROM \"gpkg_column_constraint\" WHERE \"constraint_name\"=?;"; var fdt = new FeatureDataTable(); // Get the geometry column definition if not previously done if (string.IsNullOrEmpty(GeometryColumn)) GetGeometryColumnDefinition(); using (var cnCI = new SQLiteConnection(_connectionString).OpenAndReturn()) using (var cnCD = new SQLiteConnection(_connectionString).OpenAndReturn()) using (var cnCC = new SQLiteConnection(_connectionString).OpenAndReturn()) { var rdrCI = new SQLiteCommand(string.Format(sqlPragmaTableInfo, TableName), cnCI).ExecuteReader(); if (!rdrCI.HasRows) throw new GeoPackageException("The table '{0}' does not exist in database!"); // has additional column data? var cmdCD = new SQLiteCommand(sqlSelectHasColumnData, cnCD); var hasCD = Convert.ToInt32(cmdCD.ExecuteScalar()) == 1; // additional column data cmdCD = new SQLiteCommand(sqlSelectColumnData, cnCD); var parCD0 = cmdCD.Parameters.Add("table_name", DbType.String); parCD0.Value = TableName; var parCD1 = cmdCD.Parameters.Add("column_name", DbType.String); // additional column constaint(s) var cmdCC = new SQLiteCommand(sqlSelectColumnConstraint, cnCC); var parCC0 = cmdCC.Parameters.Add("pcc", DbType.String); while (rdrCI.Read()) { // Get the column name var columnName = rdrCI.GetString(1); // We don't want the geometry to appear as an attribute in the feature data table; if (columnName == GeometryColumn) continue; // Set up the column // column name and type var dc = new DataColumn(rdrCI.GetString(1), GpkgUtility.GetTypeForDataTypeString(rdrCI.GetString(2))); // Allow DBNull? if (rdrCI.GetInt32(3) == 0) dc.AllowDBNull = true; // Assign default value if (!rdrCI.IsDBNull(4)) dc.DefaultValue = rdrCI.GetValue(4); // Add the column fdt.Columns.Add(dc); // Get additional information if (hasCD) { parCD1.Value = columnName; var rdrCD = cmdCD.ExecuteReader(CommandBehavior.SingleRow); if (rdrCD.HasRows) { rdrCD.Read(); if (!rdrCD.IsDBNull(2)) dc.Caption = rdrCD.GetString(2); if (!rdrCD.IsDBNull(3)) dc.ExtendedProperties.Add("Title", rdrCD.GetString(3)); if (!rdrCD.IsDBNull(4)) dc.ExtendedProperties.Add("Description", rdrCD.GetString(4)); if (!rdrCD.IsDBNull(5)) dc.ExtendedProperties.Add("MimeType", rdrCD.GetString(5)); if (!rdrCD.IsDBNull(rdrCD.GetOrdinal("constraint_name"))) { parCC0.Value = rdrCD.GetString(rdrCD.GetOrdinal("constraint_name")); var rdrCC = cmdCC.ExecuteReader(); while (rdrCC.Read()) { } } } } if (rdrCI.GetInt32(5) == 1) { fdt.PrimaryKey = new[] {dc}; OidColumn = dc.ColumnName; } } } return fdt; }
/// <summary> /// Get the non-spatial columns /// </summary> private void GetNonSpatialColumns() { if (!string.IsNullOrEmpty(_columns)) return; if (string.IsNullOrEmpty(ConnectionID)) return; using (var cn = GetConnection(ConnectionString)) { using (var dr = new SQLiteCommand(string.Format("PRAGMA table_info('{0}');", Table), cn).ExecuteReader()) { if (!dr.HasRows) throw new InvalidOperationException("Provider configuration incomplete or wrong!"); var columns = new List<string> { string.Equals(ObjectIdColumn, "rowid", StringComparison.OrdinalIgnoreCase) ? "\"ROWID\" AS \"ROWID\"" : string.Format("\"{0}\"", ObjectIdColumn) }; while (dr.Read()) { var column = dr.GetString(1); if (string.Equals(column, ObjectIdColumn)) continue; if (string.Equals(column, GeometryColumn)) continue; columns.Add(string.Format("\"{0}\"", column)); } _columns = string.Join(", ", columns); } } }
private void ApplicationControl_Load(object sender, EventArgs e) { #region read var csb = new SQLiteConnectionStringBuilder { DataSource = DataSource, Version = 3 }; using (var c = new SQLiteConnection(csb.ConnectionString)) { c.Open(); { var sql = "create table if not exists Table1 (ContentKey INTEGER PRIMARY KEY AUTOINCREMENT, ContentValue text not null, ContentBytes blob)"; using (var reader = new SQLiteCommand(sql, c).ExecuteReader()) { } } { var sql = "select ContentValue, ContentBytes from Table1"; var cmd = new SQLiteCommand(sql, c); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var ContentValue = reader.GetString(reader.GetOrdinal("ContentValue")); var chunkSize = 4096; // Get size of image data–pass null as the byte array parameter long bytesize = reader.GetBytes(reader.GetOrdinal("ContentBytes"), 0, null, 0, 0); // Allocate byte array to hold image data byte[] imageData = new byte[bytesize]; long bytesread = 0; int curpos = 0; while (bytesread < bytesize) { // chunkSize is an arbitrary application defined value bytesread += reader.GetBytes(reader.GetOrdinal("ContentBytes"), curpos, imageData, curpos, chunkSize); curpos += chunkSize; } dataGridView1.Rows.Add( "", ContentValue, "" + bytesize ); } } } } #endregion }