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> /// Returns int value of last ID in default database /// </summary> /// <returns> /// <see cref="int" /> /// </returns> public int GetLastDatabaseId() { int id = -1; using (SQLiteConnection db = new SQLiteConnection($"Data Source={DatabaseLocation};Version=3;")) { db.Open(); using (SQLiteDataReader r = new SQLiteCommand("SELECT MAX(id) FROM users", db).ExecuteReader()) { while (r.Read()) { id = Convert.ToInt32(r.GetValue(0)); } } } return id; }