/// <summary>
/// Initializes a new connection to SQL Server
/// </summary>
/// <param name="connectionStr">Connectionstring</param>
/// <param name="tablename">Name of data table</param>
/// <param name="spatialColumnName">Name of spatial column</param>
/// <param name="oidColumnName">Name of column with unique identifier</param>
/// <param name="spatialObjectType">spatial type (Geometry or Geography)</param>
/// <param name="srid">The spatial reference id</param>
/// <param name="extentsMode">Mode for calculating full extents of the data</param>
public SqlServer2008(string connectionStr, string tablename, string spatialColumnName, string oidColumnName,
SqlServerSpatialObjectType spatialObjectType, int srid, SqlServer2008ExtentsMode extentsMode)
{
ConnectionString = connectionStr;
ParseTablename(tablename);
GeometryColumn = spatialColumnName;
ObjectIdColumn = oidColumnName;
SpatialObjectType = spatialObjectType;
switch (spatialObjectType)
{
case SqlServerSpatialObjectType.Geometry:
_spatialTypeString = "geometry";
_reorientObject = string.Empty;
break;
//case SqlServerSpatialObjectType.Geography:
default:
_spatialTypeString = "geography";
_reorientObject = ".ReorientObject()";
break;
}
SRID = srid;
ExtentsMode = extentsMode;
if (!string.IsNullOrEmpty(TableSchema))
{
QualifiedTable = $"[{TableSchema}].[{Table}]";
}
else
{
QualifiedTable = $"[{Table}]";
}
}
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());
}
/// <summary>
/// Initializes a new connection to SQL Server
/// </summary>
/// <param name="connectionStr">Connectionstring</param>
/// <param name="tablename">Name of data table</param>
/// <param name="geometryColumnName">Name of geometry column</param>
/// <param name="oidColumnName">Name of column with unique identifier</param>
/// <param name="spatialObjectType">The type of the spatial object to use for spatial queries</param>
/// <param name="useSpatialIndexExtentAsExtent">If true, the bounds of the spatial index is used for the GetExtents() method which heavily increases performance instead of reading through all features in the table</param>
/// <param name="SRID">The spatial reference id</param>
public SqlServer2008(string connectionStr, string tablename, string geometryColumnName, string oidColumnName, SqlServerSpatialObjectType spatialObjectType, bool useSpatialIndexExtentAsExtent, int SRID)
{
ConnectionString = connectionStr;
Table = tablename;
if (Table.IndexOf(".") > 0)
{
string[] parts = Table.Split('.');
Table = parts[1];
TableSchema = parts[0];
}
GeometryColumn = geometryColumnName;
ObjectIdColumn = oidColumnName;
_spatialObjectType = spatialObjectType;
switch (spatialObjectType)
{
case SqlServerSpatialObjectType.Geometry:
_spatialObject = "geometry";
break;
//case SqlServerSpatialObjectType.Geography:
default:
_spatialObject = "geography";
break;
}
_extentsMode = (useSpatialIndexExtentAsExtent ? SqlServer2008ExtentsMode.SpatialIndex : SqlServer2008ExtentsMode.QueryIndividualFeatures);
this.SRID = SRID;
}
