protected virtual IDbCommand PrepareSelectCommand(Expression query) { Expression exp = ExpressionMerger.MergeExpressions(query, DefinitionQuery); //if (DefaultProviderProperties != null) //{ // var attributes = // GetProviderPropertyValue<AttributesCollectionExpression, CollectionExpression<PropertyNameExpression>>( // DefaultProviderProperties.ProviderProperties, // null); // if (attributes != null) // exp = ExpressionMerger.MergeExpressions(exp, new AttributesProjectionExpression(attributes)); //} ExpressionTreeToSqlCompilerBase <TOid> compiler = CreateSqlCompiler(exp); List <ProviderPropertyExpression> props = new List <ProviderPropertyExpression>( MergeProviderProperties( compiler.ProviderProperties, DefaultProviderProperties == null ? null : DefaultProviderProperties.ProviderProperties.Collection)); IDbCommand cmd = DbUtility.CreateCommand(); cmd.CommandText = GenerateSelectSql(props, compiler); cmd.CommandType = CommandType.Text; foreach (IDataParameter p in compiler.ParameterCache.Values) { cmd.Parameters.Add(p); } return(cmd); }
//protected override DataTable BuildSchemaTable() //{ // return base.BuildSchemaTable(true); //} public override IExtents GetExtents() { using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) using (IDbCommand cmd = DbUtility.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = string.Format( "SELECT MIN({0}_Envelope_MinX), MIN({0}_Envelope_MinY), MAX({0}_Envelope_MaxX), MAX({0}_Envelope_MaxY) FROM {1}.{2} {3}", GeometryColumn, TableSchema, Table, GetWithClause(DefaultProviderProperties == null ? null : DefaultProviderProperties.ProviderProperties.Collection)); cmd.CommandType = CommandType.Text; conn.Open(); using (IDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (r.Read()) { if (r.IsDBNull(0) || r.IsDBNull(1) || r.IsDBNull(2) || r.IsDBNull(3)) { return(GeometryFactory.CreateExtents()); } double xmin = r.GetDouble(0); double ymin = r.GetDouble(1); double xmax = r.GetDouble(2); double ymax = r.GetDouble(3); return(GeometryFactory.CreateExtents2D(xmin, ymin, xmax, ymax)); } } return(GeometryFactory.CreateExtents()); } }
protected override void ReadSpatialReference(out ICoordinateSystem cs, out string srid) { using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) { using (IDbCommand cmd = DbUtility.CreateCommand()) { cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT TOP 1 SRID from ST.GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = @pschema and F_TABLE_NAME = @ptablename"; cmd.Parameters.Add(DbUtility.CreateParameter("pschema", TableSchema, ParameterDirection.Input)); cmd.Parameters.Add(DbUtility.CreateParameter("ptablename", Table, ParameterDirection.Input)); conn.Open(); object result = cmd.ExecuteScalar(); if (result is int) { int isrid = (int)result; cs = SridMap.DefaultInstance.Process(isrid, (ICoordinateSystem)null); srid = !Equals(cs, default(ICoordinateSystem)) ? SridMap.DefaultInstance.Process(cs, "") : ""; return; } } } cs = default(ICoordinateSystem); srid = ""; }
public PostGisProvider(IGeometryFactory geometryFactory, string connectionString, string tableSchema, string tableName, string oidColumn, string geometryColumn, ICoordinateTransformationFactory coordinateTransformationFactory) : base( new PostGisDbUtility(), geometryFactory, connectionString, tableSchema, tableName, oidColumn, geometryColumn, coordinateTransformationFactory) { using (NpgsqlConnection cn = (NpgsqlConnection)DbUtility.CreateConnection(connectionString)) { try { cn.Open(); if (!PostGisProviderStatic.Has_X_Privilege(cn, "table", "\"public\".\"geometry_columns\"", "SELECT")) { throw new PostGisException( "Insufficient rights to access table \"public\".\"geometry_columns\"!"); } if ( !PostGisProviderStatic.Has_X_Privilege(cn, "table", string.Format("\"{0}\".\"{1}\"", tableSchema, tableName), "SELECT")) { throw new PostGisException(string.Format( "Insufficient rights to access table \"{0}\".\"{1}\"!", tableSchema, tableName)); } NpgsqlCommand cmd = (NpgsqlCommand)DbUtility.CreateCommand(); cmd.Connection = cn; cmd.CommandText = @"SELECT x.""type"" FROM ""public"".""geometry_columns"" AS x WHERE (x.""f_table_schema""=:p0 AND x.""f_table_name""=:p1 AND x.""f_geometry_column""=:p2);"; cmd.Parameters.Add(DbUtility.CreateParameter("p0", tableSchema, ParameterDirection.Input)); cmd.Parameters.Add(DbUtility.CreateParameter("p1", tableName, ParameterDirection.Input)); cmd.Parameters.Add(DbUtility.CreateParameter("p2", geometryColumn, ParameterDirection.Input)); NpgsqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (dr.HasRows) { dr.Read(); //valid geometry type _validGeometryType = parseGeometryType(dr.GetString(0)); } else { _validGeometryType = OgcGeometryType.Geometry; } } catch (Exception) { _validGeometryType = OgcGeometryType.Unknown; } } }
public virtual void Update(IEnumerable <FeatureDataRow <TOid> > features) { using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) { if (conn.State == ConnectionState.Closed) { conn.Open(); } { Boolean success = true; using (IDbTransaction tran = conn.BeginTransaction()) { IDbCommand cmd = DbUtility.CreateCommand(); cmd.Connection = conn; cmd.Transaction = tran; cmd.CommandText = string.Format( "UPDATE {0} SET {1} WHERE ({2});", QualifiedTableName, UpdateClause(cmd), WhereClause(cmd)); foreach (FeatureDataRow row in features) { for (int i = 0; i < cmd.Parameters.Count - 2; i++) { ((IDataParameter)cmd.Parameters[i]).Value = row[i]; } ((IDataParameter)cmd.Parameters["@PGeo"]).Value = row.Geometry.AsBinary(); ((IDataParameter)cmd.Parameters["@POldOid"]).Value = row[OidColumn, DataRowVersion.Original]; try { cmd.ExecuteNonQuery(); } catch (Exception ex) { Debug.WriteLine(ex.Message); success = false; break; } } if (success) { tran.Commit(); } } } } }
public void FixGeometries() { using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) { string sql = string.Format("UPDATE {0} Set {1} = {1}.MakeValid()", QualifiedTableName, GeometryColumn); conn.Open(); using (IDbCommand cmd = DbUtility.CreateCommand()) { cmd.CommandText = sql; cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } }
public override IExtents GetExtents() { using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) using (IDbCommand cmd = DbUtility.CreateCommand()) { conn.Open(); cmd.Connection = conn; /*cmd.CommandText = String.Format( * "SELECT " + * "st_xmin(x.ext) AS xmin, st_ymin(x.ext) AS ymin, " + * "st_xmax(x.ext) AS xmax, st_ymax(x.ext) AS ymax " + * "FROM "+ * "(SELECT st_box3d( st_estimated_extent('{0}', '{1}', '{2}') ) AS ext ) as x;", * TableSchema, Table, GeometryColumn); * */ cmd.CommandText = String.Format( "SELECT " + "st_xmin(x.ext) AS xmin, st_ymin(x.ext) AS ymin, " + "st_xmax(x.ext) AS xmax, st_ymax(x.ext) AS ymax " + "FROM " + "(SELECT st_extent( \"{2}\" ) as ext FROM {0}.\"{1}\") as x;", TableSchema, Table, GeometryColumn); cmd.CommandType = CommandType.Text; using (NpgsqlDataReader r = (NpgsqlDataReader)cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (r.Read()) { if (r.IsDBNull(0) || r.IsDBNull(1) || r.IsDBNull(2) || r.IsDBNull(3)) { return(GeometryFactory.CreateExtents()); } return(GeometryFactory.CreateExtents2D( r.GetDouble(0), r.GetDouble(1), r.GetDouble(2), r.GetDouble(3))); } } return(GeometryFactory.CreateExtents()); } }
public virtual void Delete(IEnumerable <FeatureDataRow <TOid> > features) { List <TOid> featureIds = new List <TOid>(); foreach (FeatureDataRow <TOid> fdr in features) { featureIds.Add(fdr.Id); } ExpressionTreeToSqlCompilerBase <TOid> compiler = CreateSqlCompiler(null); using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) { using (IDbCommand cmd = DbUtility.CreateCommand()) { cmd.Connection = conn; cmd.CommandText = String.Format( "DELETE FROM {0} WHERE {1} in ({2})", QualifiedTableName, OidColumn, String.Join(",", Enumerable.ToArray( Processor.Select(featureIds, delegate(TOid o) { return (compiler.CreateParameter(o).ParameterName); }))) ); conn.Open(); foreach (IDataParameter p in compiler.ParameterCache.Values) { cmd.Parameters.Add(p); } cmd.ExecuteNonQuery(); } } }
protected virtual DataTable BuildSchemaTable(Boolean withGeometryColumn) { using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) using (IDbCommand cmd = DbUtility.CreateCommand()) { cmd.CommandText = string.Format("SELECT * FROM {0} ", QualifiedTableName); cmd.CommandType = CommandType.Text; cmd.Connection = conn; IDbDataAdapter da = DbUtility.CreateAdapter(cmd); DataSet ds = new DataSet(); da.FillSchema(ds, SchemaType.Source); DataTable dt = ds.Tables[0]; //remove geometry column from schema if (!withGeometryColumn) { dt.Columns.Remove(GeometryColumn); } return(dt); } }
public virtual void Insert(IEnumerable <FeatureDataRow <TOid> > features) { OgcGeometryType geometryType = OgcGeometryType.Unknown; using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) { if (conn.State == ConnectionState.Closed) { conn.Open(); } using (IDbTransaction tran = conn.BeginTransaction()) { IDbCommand cmd = DbUtility.CreateCommand(); cmd.Connection = conn; cmd.Transaction = tran; cmd.CommandText = string.Format( "INSERT INTO {0} {1};", QualifiedTableName, InsertClause(cmd)); int geometryParameterIndex = cmd.Parameters.Count - 1; foreach (FeatureDataRow <TOid> row in features) { if (cmd.Parameters.Count > 0) { for (int i = 0; i < row.FieldCount; i++) { ((IDataParameter)cmd.Parameters[i]).Value = row.GetValue(i); } ((IDataParameter)cmd.Parameters[geometryParameterIndex]).Value = row.Geometry.AsBinary(); } else { for (int i = 0; i < row.FieldCount; i++) { cmd.Parameters.Add(DbUtility.CreateParameter(string.Format("P{0}", i), row[i], ParameterDirection.Input)); } geometryParameterIndex = cmd.Parameters.Add( DbUtility.CreateParameter("PGeo", row.Geometry.AsBinary(), ParameterDirection.Input)); } //for (int i = 0; i < cmd.Parameters.Count - 1; i++) // ((IDataParameter)cmd.Parameters[i]).Value = row[i]; //byte[] geomBinary = row.Geometry.AsBinary(); //((IDataParameter)cmd.Parameters["@PGeo"]).Value = geomBinary; if (geometryType == OgcGeometryType.Unknown) { geometryType = row.Geometry.GeometryType; } if (row.Geometry.GeometryType == geometryType) { cmd.ExecuteNonQuery(); } } tran.Commit(); } } }
//TODO: add a set of strategies for reading srid protected override void ReadSpatialReference(out ICoordinateSystem cs, out string srid) { using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) { using (IDbCommand cmd = DbUtility.CreateCommand()) { cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = string.Format( @" DECLARE @found bit set @found = 0; IF EXISTS(select * from sys.objects where object_id = object_id(@p1 + '.Geometry_Columns')) BEGIN IF EXISTS(SELECT * FROM [{1}].[Geometry_Columns] WHERE F_Table_Catalog = @p0 AND F_Table_Schema = @p1 AND F_Table_Name = @p2 AND F_Geometry_Column = @p3) BEGIN SELECT DISTINCT SRID FROM [{1}].[Geometry_Columns] WHERE F_Table_Catalog = @p0 AND F_Table_Schema = @p1 AND F_Table_Name = @p2 AND F_Geometry_Column = @p3 SET @found = 1 END END IF @found = 0 BEGIN SELECT DISTINCT [{0}].STSrid FROM [{1}].[{2}] END", GeometryColumn, TableSchema, Table); cmd.Parameters.Add(DbUtility.CreateParameter("p0", conn.Database, ParameterDirection.Input)); cmd.Parameters.Add(DbUtility.CreateParameter("p1", TableSchema, ParameterDirection.Input)); cmd.Parameters.Add(DbUtility.CreateParameter("p2", Table, ParameterDirection.Input)); cmd.Parameters.Add(DbUtility.CreateParameter("p3", GeometryColumn, ParameterDirection.Input)); conn.Open(); using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { if (reader.RecordsAffected > 1) { throw new Exception( string.Format( "The Sql Server Table [{0}].[{1}] contains geometries in multiple Srids.", TableSchema, Table)); } object v = reader[0]; if (v is int) { int isrid = (int)v; cs = SridMap.DefaultInstance.Process(isrid, default(ICoordinateSystem)); srid = !Equals(cs, default(ICoordinateSystem)) ? SridMap.DefaultInstance.Process(cs, String.Empty) : String.Empty; return; } } } } } cs = default(ICoordinateSystem); srid = String.Empty; }
public override IExtents GetExtents() { bool withNoLock = GetProviderPropertyValue <WithNoLockExpression, bool>( DefaultProviderProperties == null ? null : DefaultProviderProperties.ProviderProperties.Collection, false); SqlServer2008ExtentsMode server2008ExtentsCalculationMode = GetProviderPropertyValue <MsSqlServer2008ExtentsModeExpression, SqlServer2008ExtentsMode>( DefaultProviderProperties == null ? null : DefaultProviderProperties.ProviderProperties.Collection, SqlServer2008ExtentsMode.QueryIndividualFeatures); using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) using (IDbCommand cmd = DbUtility.CreateCommand()) { cmd.Connection = conn; switch (server2008ExtentsCalculationMode) { case SqlServer2008ExtentsMode.UseSqlSpatialTools: { cmd.CommandText = string.Format( @" declare @envelope Geometry select @envelope = dbo.GeometryEnvelopeAggregate({0}) from {1}.{2} {3} select @envelope.STPointN(2).STX as MinX, @envelope.STPointN(2).STY as MinY, @envelope.STPointN(4).STX as MaxX, @envelope.STPointN(4).STY as MaxY", GeometryColumn, TableSchema, Table, withNoLock ? "WITH(NOLOCK)" : String.Empty); break; } case SqlServer2008ExtentsMode.UseEnvelopeColumns: { cmd.CommandText = string.Format( "SELECT MIN({0}_Envelope_MinX), MIN({0}_Envelope_MinY), MAX({0}_Envelope_MaxX), MAX({0}_Envelope_MaxY) FROM {1}.{2} {3}", GeometryColumn, TableSchema, Table, withNoLock ? "WITH(NOLOCK)" : String.Empty); break; } default: { cmd.CommandText = string.Format( @" select Min({0}.STEnvelope().STPointN(1).STX)as MinX, Min({0}.STEnvelope().STPointN(1).STY) as MinY, Max({0}.STEnvelope().STPointN(3).STX) as MaxX, Max({0}.STEnvelope().STPointN(3).STY) as MaxY FROM {1}.{2} {3}" , this.GeometryColumn, TableSchema, Table, withNoLock ? "WITH(NOLOCK)" : String.Empty); break; } } cmd.CommandType = CommandType.Text; conn.Open(); using (IDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (r.Read()) { if (r.IsDBNull(0) || r.IsDBNull(1) || r.IsDBNull(2) || r.IsDBNull(3)) { return(GeometryFactory.CreateExtents()); } double xmin = r.GetDouble(0); double ymin = r.GetDouble(1); double xmax = r.GetDouble(2); double ymax = r.GetDouble(3); return(GeometryFactory.CreateExtents2D(xmin, ymin, xmax, ymax)); } } } return(GeometryFactory.CreateExtents()); }