protected virtual string InsertClause(IDbCommand cmd)
{
List <string> sets = new List <string>();
//Columnnames
DataColumnCollection dcc = GetSchemaTable().Columns;
foreach (DataColumn dc in dcc)
{
sets.Add(string.Format(" [{0}]", dc.ColumnName));
}
String columNames = string.Format("({0}, [{1}])", String.Join(",", sets.ToArray()).Trim(), GeometryColumn);
sets.Clear();
//Parameter
foreach (DataColumn dc in dcc)
{
IDataParameter param = null;
sets.Add(string.Format(" {0}", ParamForColumn(dc, out param)));
cmd.Parameters.Add(param);
}
//Geometry
sets.Add(string.Format("{0}", string.Format(GeomFromWkbFormatString, "@PGeo")));
cmd.Parameters.Add(DbUtility.CreateParameter <byte[]>("@PGeo", ParameterDirection.Input));
return(String.Format("{0} VALUES({1})", columNames, string.Join(",", sets.ToArray()).Trim()));
}
protected virtual String ParamForColumn(DataColumn dc, out IDataParameter param)
{
String paramName = ParamForColumn(dc);
param = DbUtility.CreateParameter(paramName, dc.DataType, ParameterDirection.Input);
return(paramName);
}
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;
}
}
}
protected override string InsertClause(IDbCommand cmd)
{
var sets = new List <string>();
//Columnnames
DataColumnCollection dcc = GetSchemaTable().Columns;
foreach (DataColumn dc in dcc)
{
sets.Add(string.Format(" \"{0}\"", dc.ColumnName));
}
String columNames = string.Format("({0}, \"{1}\")", String.Join(",", sets.ToArray()).Trim(), GeometryColumn);
sets.Clear();
//Parameter
foreach (DataColumn dc in dcc)
{
IDataParameter param = null;
sets.Add(string.Format(" :{0}", ParamForColumn(dc, out param)));
cmd.Parameters.Add(param);
}
//Geometry
sets.Add(string.Format("{0}", string.Format(GeomFromWkbFormatString, ":PGeo")));
//cmd.Parameters.Add(DbUtility.CreateParameterByType("PGeo", toDbType(typeof(byte[])), ParameterDirection.Input));
cmd.Parameters.Add(DbUtility.CreateParameter <byte[]>("PGeo", ParameterDirection.Input));
return(String.Format("{0} VALUES({1})", columNames, string.Join(",", sets.ToArray()).Trim()));
}
protected virtual string UpdateClause(IDbCommand cmd)
{
List <string> sets = new List <string>();
//Attribute
foreach (DataColumn dc in GetSchemaTable().Columns)
{
IDataParameter param = null;
sets.Add(string.Format(" [{0}]={1}", dc.ColumnName, ParamForColumn(dc, out param)));
cmd.Parameters.Add(param);
}
//Geometry
sets.Add(
string.Format("[{0}]={1}",
GeometryColumn,
string.Format(GeomFromWkbFormatString, "@PGeo")));
cmd.Parameters.Add(DbUtility.CreateParameter <byte[]>("@PGeo", ParameterDirection.Input));
return(String.Join(",", sets.ToArray()).Trim());
}
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();
}
}
}
protected virtual string WhereClause(IDbCommand cmd)
{
cmd.Parameters.Add(DbUtility.CreateParameter <TOid>("@POldOid", ParameterDirection.Input));
return(string.Format("{0}=@POldOid", OidColumn));
}
//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;
}
protected override string WhereClause(IDbCommand cmd)
{
cmd.Parameters.Add(DbUtility.CreateParameter <UInt32>(":POldOid", ParameterDirection.Input));
return(string.Format("{0}=:POldOid", OidColumn));
}
