Esempio n. 1
0
        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;
        }
Esempio n. 2
0
        /// <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;
        }