/// <summary>
/// Gets the spatial create string.
/// </summary>
/// <param name="schema">The schema.</param>
/// <param name="table">The table.</param>
/// <param name="column">The column.</param>
/// <param name="srid">The srid.</param>
/// <param name="subtype">The subtype.</param>
/// <param name="dimension">The dimension.</param>
/// <param name="isNullable">Whether or not the column is nullable</param>
/// <returns></returns>
public virtual string GetSpatialCreateString(string schema, string table, string column, int srid, string subtype, int dimension, bool isNullable)
{
StringBuilder builder = new StringBuilder();
string quotedSchema = !string.IsNullOrEmpty(schema) ? adaptor.QuoteForSchemaName(schema) : string.Empty;
string quoteForTableName = adaptor.QuoteForTableName(table);
string quoteForColumnName = adaptor.QuoteForColumnName(column);
builder.AppendFormat("ALTER TABLE {0}{1} DROP COLUMN {2}"
, quotedSchema
, quoteForTableName
, quoteForColumnName
);
builder.Append(this.MultipleQueriesSeparator);
builder.AppendFormat("ALTER TABLE {0}{1} ADD {2} {3} {4}"
, quotedSchema
, quoteForTableName
, quoteForColumnName
, this.sqlTypeName
, isNullable ? "NULL" : "NOT NULL"
);
builder.Append(this.MultipleQueriesSeparator);
if (srid > 0)
{
// EXECUTE is needed to avoid the error "The multi-part identifier could not be bound."
builder.AppendFormat("EXECUTE('ALTER TABLE {0}{1} WITH CHECK ADD CONSTRAINT {2} CHECK ({3}.{4} = {5})')"
, quotedSchema
, quoteForTableName
, adaptor.Quote("CK_NHSP_" + table + "_" + column + "_SRID")
, quoteForColumnName
, adaptor.Quote("STSrid")
, srid
);
builder.Append(this.MultipleQueriesSeparator);
}
if (!string.IsNullOrEmpty(subtype) && string.Compare(subtype, "GEOMETRY") != 0)
{
builder.AppendFormat("ALTER TABLE {0}{1} WITH CHECK ADD CONSTRAINT {2} CHECK ({3}.{4}() = '{5}')"
, quotedSchema
, quoteForTableName
, adaptor.Quote("CK_NHSP_" + table + "_" + column + "_TYPE")
, quoteForColumnName
, adaptor.Quote("STGeometryType")
, subtype
);
builder.Append(this.MultipleQueriesSeparator);
}
return(builder.ToString());
}
/// <summary>
/// Gets the spatial create string.
/// </summary>
/// <param name="schema">The schema.</param>
/// <param name="table">The table.</param>
/// <param name="column">The column.</param>
/// <param name="srid">The srid.</param>
/// <param name="subtype">The subtype.</param>
/// <param name="dimension">The dimension.</param>
/// <param name="isNullable">Whether or not the column is nullable</param>
/// <returns></returns>
public string GetSpatialCreateString(string schema, string table, string column, int srid, string subtype, int dimension, bool isNullable)
{
StringBuilder builder = new StringBuilder();
string quotedSchema = null;
if (!string.IsNullOrEmpty(schema))
{
quotedSchema = adaptor.QuoteForSchemaName(schema) + StringHelper.Dot;
}
builder.AppendFormat("ALTER TABLE {0}{1} DROP COLUMN {2}"
, quotedSchema
, adaptor.QuoteForTableName(table)
, adaptor.QuoteForColumnName(column)
);
builder.Append(this.MultipleQueriesSeparator);
builder.AppendFormat("EXECUTE ST.AddGeometryColumn '{0}','{1}','{2}',{3},'{4}'",
schema, table, column, srid, subtype);
builder.Append(this.MultipleQueriesSeparator);
return(builder.ToString());
}
/// <summary>
/// Gets the spatial drop string.
/// </summary>
/// <param name="schema">The schema.</param>
/// <param name="table">The table.</param>
/// <param name="column">The column.</param>
/// <returns></returns>
public virtual string GetSpatialDropString(string schema, string table, string column)
{
StringBuilder builder = new StringBuilder();
string quotedSchema = null;
if (!string.IsNullOrEmpty(schema))
{
quotedSchema = adaptor.QuoteForSchemaName(schema) + StringHelper.Dot;
}
builder.AppendFormat("ALTER TABLE {0}{1} DROP COLUMN {2}"
, quotedSchema
, adaptor.QuoteForTableName(table)
, adaptor.QuoteForColumnName(column)
);
builder.Append(MultipleQueriesSeparator);
return(builder.ToString());
}