public IFeatureProvider ConstructSourceProvider(IGeometryServices geometryServices) { header("Construct PostGis source provider\n" + "* Author Felix Obermaier 2009\n" + "* Ingenieurgruppe IVV GmbH & Co. KG\n" + "* http://www.ivv-aachen.de"); string connectionString = GetValue( "Please enter the connection string for the source database file.", Settings.Default.SourceConnectionString); string schema = GetValue("Please enter the schema name", Settings.Default.SourceSchema); string tableName = GetValue("Please enter the table name.", null); _oidColumn = GetValue("Please enter the id column name.", null); string geometryColumn = GetValue("Please enter the geometry column name.", null); Type type; PostGisDbUtility dbUtility = new PostGisDbUtility(); using (IDbConnection conn = dbUtility.CreateConnection(connectionString)) { using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format("SELECT \"{0}\" FROM \"{1}\".\"{2}\" LIMIT 1;", _oidColumn, schema, tableName); cmd.CommandType = CommandType.Text; conn.Open(); type = cmd.ExecuteScalar().GetType(); } } _sourceProviderOidType = type; Type t = typeof(PostGisProvider <>); Type specialized = t.MakeGenericType(type); _sourceProvider = (IFeatureProvider) Activator.CreateInstance(specialized, geometryServices.DefaultGeometryFactory, connectionString, schema, tableName, _oidColumn, geometryColumn, geometryServices.CoordinateTransformationFactory); _sourceProvider.Open(); _sourceProvider.CoordinateTransformation = SetCoordinateTransfromation(_sourceProvider.OriginalSpatialReference); _sourceProvider.Open(); return(_sourceProvider); }
public IFeatureProvider ConstructSourceProvider(IGeometryServices geometryServices) { header("Construct PostGis source provider\n" + "* Author Felix Obermaier 2009\n" + "* Ingenieurgruppe IVV GmbH & Co. KG\n" + "* http://www.ivv-aachen.de"); string connectionString = GetValue( "Please enter the connection string for the source database file.", Settings.Default.SourceConnectionString); string schema = GetValue("Please enter the schema name", Settings.Default.SourceSchema); string tableName = GetValue("Please enter the table name.", null); _oidColumn = GetValue("Please enter the id column name.", null); string geometryColumn = GetValue("Please enter the geometry column name.", null); Type type; PostGisDbUtility dbUtility = new PostGisDbUtility(); using (IDbConnection conn = dbUtility.CreateConnection(connectionString)) { using (IDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format("SELECT \"{0}\" FROM \"{1}\".\"{2}\" LIMIT 1;", _oidColumn, schema, tableName); cmd.CommandType = CommandType.Text; conn.Open(); type = cmd.ExecuteScalar().GetType(); } } _sourceProviderOidType = type; Type t = typeof (PostGisProvider<>); Type specialized = t.MakeGenericType(type); _sourceProvider = (IFeatureProvider) Activator.CreateInstance(specialized, geometryServices.DefaultGeometryFactory, connectionString, schema, tableName, _oidColumn, geometryColumn, geometryServices.CoordinateTransformationFactory); _sourceProvider.Open(); _sourceProvider.CoordinateTransformation = SetCoordinateTransfromation(_sourceProvider.OriginalSpatialReference); _sourceProvider.Open(); return _sourceProvider; }
private static String ColumnsClause(string tableName, DataColumnCollection dcc, ConstraintCollection ccc) { string[] columns = new String[dcc.Count]; Int32 index = 0; foreach (DataColumn dc in dcc) { String columnName = dc.ColumnName; if (columnName == "oid") { columnName = "poid"; } columns[index++] = string.Format(" \"{0}\" {1}{2}", columnName, PostGisDbUtility.GetTypeString(dc.DataType), dc.DefaultValue == DBNull.Value ? "" : String.Format(" DEFAULT {0}", String.Format(CultureInfo.InvariantCulture, "{0}", dc.DefaultValue))); ; } index = 0; string[] constraints = new String[ccc.Count]; foreach (Constraint c in ccc) { UniqueConstraint uc = c as UniqueConstraint; if (uc != null) { if (uc.IsPrimaryKey) { constraints[index++] = String.Format( ", CONSTRAINT \"{0}\" PRIMARY KEY ({1})", String.Format("PK_{0}_{1}", tableName, uc.ConstraintName), ColumnNamesToCommaSeparatedString(uc.Columns)); } else { constraints[index++] = String.Format( ", CONSTRAINT \"{0}\" UNIQUE ({1})", String.Format("UNI_{0}_{1}", tableName, uc.ConstraintName), ColumnNamesToCommaSeparatedString(uc.Columns)); } } ForeignKeyConstraint fc = c as ForeignKeyConstraint; if (fc != null) { constraints[index++] = String.Format( " CONSTRAINT \"{0}\" FOREIGN KEY ({1}) REFERENCES {2} ({3}) MATCH FULL ON UPDATE {4} ON DELETE {5}", String.Format("FK_{0}_{1}_{3}", tableName, fc.RelatedTable.TableName, uc.ConstraintName), ColumnNamesToCommaSeparatedString(fc.Columns), fc.RelatedTable.TableName, ColumnNamesToCommaSeparatedString(fc.RelatedColumns), ruleToAction(fc.UpdateRule), ruleToAction(fc.DeleteRule) ); } } String constraintsClause = ""; if (index > 0) { Array.Resize(ref constraints, index); constraintsClause = String.Join(String.Empty, constraints); } return(String.Join(",", columns) + constraintsClause); }
public static void CreateDataTable <TOid>( FeatureDataTable featureDataTable, String schemaName, String tableName, String connectionString, String geometryColumnName, OgcGeometryType geometryType) { string srid = featureDataTable.GeometryFactory.SpatialReference != null ? featureDataTable.GeometryFactory.SpatialReference.AuthorityCode : DefaultSridInt.ToString(); NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder(connectionString); PostGisDbUtility util = new PostGisDbUtility(); NpgsqlConnection conn = (NpgsqlConnection)util.CreateConnection(connectionString); if (conn != null) { try { if (conn.State == ConnectionState.Closed) { conn.Open(); } if (!Has_X_Privilege(conn, "database", csb.Database, "CREATE")) { throw new PostGisException("Insufficient rights to create table!"); } if (!Has_X_Privilege(conn, "function", "addgeometrycolumn(character varying, character varying, int, character varying, int)", "EXECUTE")) { throw new PostGisException("Insufficient rights to access addgeometrycolumn function!"); } string createTableClause = string.Format( "CREATE TABLE {0}.\"{1}\" ({2}) WITH(OIDS=FALSE);", schemaName, tableName, ColumnsClause(tableName, featureDataTable.Columns, featureDataTable.Constraints)); new NpgsqlCommand(createTableClause, conn).ExecuteNonQuery(); String addGeometryColumnClause = String.Format("('{0}', '{1}', {2}, '{3}', {4})", tableName, geometryColumnName, srid, geometryType.ToString().ToUpper(), 2); //adding spatial column new NpgsqlCommand(String.Format("SELECT AddGeometryColumn {0};", addGeometryColumnClause), conn).ExecuteNonQuery(); //adding GIST index new NpgsqlCommand(String.Format("CREATE INDEX index_{0}_{1} ON {2}.\"{3}\" USING gist(\"{4}\");", tableName, geometryColumnName, schemaName, tableName, geometryColumnName), conn).ExecuteNonQuery(); } catch (NpgsqlException ex) { Trace.Write(ex.Message); throw new PostGisException(string.Format("Cannot create geometry column with type of '{0}'", geometryType)); } catch { } } conn.Close(); conn = null; PostGisProvider <TOid> prov = new PostGisProvider <TOid>( featureDataTable.GeometryFactory, connectionString, schemaName, tableName, featureDataTable.Columns[0].ColumnName, geometryColumnName); prov.Insert(featureDataTable); return; }