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