private static void FixPrimaryKeysForTable(DbSyncTableDescription description)
{
try
{
IEnumerator <DbSyncColumnDescription> enumerator = description.PkColumns.GetEnumerator();
while (enumerator.MoveNext())
{
DbSyncColumnDescription colDesc = enumerator.Current;
if (colDesc.IsPrimaryKey && colDesc.QuotedName != "SYNC_ID")
{
colDesc.IsPrimaryKey = false;
}
}
if (enumerator != null)
{
enumerator.Dispose();
}
enumerator = description.NonPkColumns.GetEnumerator();
while (enumerator.MoveNext())
{
DbSyncColumnDescription colDesc = enumerator.Current;
if (colDesc.UnquotedName == "SYNC_ID")
{
colDesc.IsPrimaryKey = true;
}
//if (colDesc.UnquotedName == "ID")
//{
// colDesc.IsPrimaryKey = true;
//}
}
if (enumerator != null)
{
enumerator.Dispose();
}
}
finally
{
//((IDisposable) description).Dispose();
}
}
public static void ProvisionTable(SqlConnection server, SqlConnection client,
string tableName, bool deprovisonScopeFirst)
{
bool clientMode = !client.ConnectionString.Equals(server.ConnectionString);
if (client.State == System.Data.ConnectionState.Closed)
{
client.Open();
}
SqlCommand command = client.CreateCommand();
DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(tableName);
SqlSyncScopeProvisioning destinationConfig = new SqlSyncScopeProvisioning(client);
if (deprovisonScopeFirst && destinationConfig.ScopeExists(tableName))
{
Deprovisioning.DropTable(client, tableName);
Deprovisioning.DeprovisonScope(client, tableName);
}
// Get table info from server
DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, server);
DbSyncColumnDescription uniqueColumn = table.Columns.Where(f => f.Type == "uniqueidentifier").FirstOrDefault();
DbSyncColumnDescription geometryColumn = table.Columns.Where(f => f.Type.EndsWith("geometry")).FirstOrDefault();
DbSyncColumnDescription identityColumn = table.Columns.Where(f => f.AutoIncrementStepSpecified).FirstOrDefault();
DbSyncColumnDescription joinColumn = table.PkColumns.FirstOrDefault();
if (table.PkColumns.Count() != 1)
{
throw new SyncException(@"Table must have a single primary key column to be used with synchronisation.");
}
// Force uniqueidentifier as primary key to enable two way, if needed.
if (uniqueColumn != null &&
!uniqueColumn.IsPrimaryKey)
{
table.PkColumns.FirstOrDefault().IsPrimaryKey = false;
uniqueColumn.IsPrimaryKey = true;
joinColumn = uniqueColumn;
}
if (geometryColumn != null)
{
geometryColumn.ParameterName += "_was_geometry";
geometryColumn.Type = "nvarchar";
geometryColumn.Size = "max";
geometryColumn.SizeSpecified = true;
}
// Remove identity columns from scope so that we don't get key conflicts.
if (identityColumn != null && identityColumn != joinColumn)
{
table.Columns.Remove(identityColumn);
}
destinationConfig.SetCreateTableDefault(clientMode ? DbSyncCreationOption.Create
: DbSyncCreationOption.Skip);
// Add the table that we found on the server to the description.
scopeDescription.Tables.Add(table);
//It is important to call this after the tables have been added to the scope
destinationConfig.PopulateFromScopeDescription(scopeDescription);
// Drop the table from the client if we are in client mode
// TODO We should sync the table first, but only if it is upload.
if (clientMode)
{
if (client.State == System.Data.ConnectionState.Closed)
{
client.Open();
}
Deprovisioning.DropTable(client, tableName);
}
try
{
//provision the client
destinationConfig.Apply();
}
catch (DbProvisioningException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.Error.WriteLine(ex.Message);
Console.ResetColor();
}
// Readd indentity column back onto client as primary key.
if (clientMode && identityColumn != null && identityColumn != joinColumn)
{
string sql = @"ALTER TABLE [{1}] ADD {0} int IDENTITY(1,1) NOT NULL;
ALTER TABLE [{1}] DROP CONSTRAINT PK_{1};
ALTER TABLE [{1}] ADD CONSTRAINT PK_{1} PRIMARY KEY CLUSTERED ({0});";
command.CommandText = String.Format(sql, identityColumn.QuotedName, tableName);
command.ExecuteNonQuery();
}
// If we have a uniqueidentifier column and on client. Add index and default value.
if (uniqueColumn != null && clientMode && uniqueColumn == joinColumn)
{
string sql = @"ALTER TABLE [{1}] ADD UNIQUE ({0});
ALTER TABLE [{1}] ADD CONSTRAINT [DF_{1}_{2}]
DEFAULT (newid()) FOR {0};";
command.CommandText = String.Format(sql, uniqueColumn.QuotedName,
tableName,
uniqueColumn.UnquotedName.Replace(' ', '_'));
command.ExecuteNonQuery();
}
if (geometryColumn == null)
{
return;
}
if (server.State == System.Data.ConnectionState.Closed)
{
server.Open();
}
SqlCommand servercommand = server.CreateCommand();
servercommand.CommandText = string.Format(@"SELECT TOP 1 [srid]
FROM [geometry_columns]
WHERE [f_table_name] = '{0}'",
tableName);
int?srid = servercommand.ExecuteScalar() as int?;
if (!srid.HasValue)
{
srid = libsyncing.Properties.Settings.Default.defaultsrid;
ProgressUpdate("No SRID found in geometry_columns. Using default: " + srid);
}
// Everything after this point is for geometry based tables only.
if (clientMode)
{
try
{
string sql = String.Format(@"ALTER TABLE [{0}] ALTER COLUMN [{1}] geometry;
CREATE SPATIAL INDEX [ogr_{0}_sidx] ON [{0}]
(
[{1}]
)
USING GEOMETRY_GRID WITH
(
BOUNDING_BOX =(300000, 6700000, 500000, 7000000),
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];", tableName, geometryColumn.UnquotedName);
// Index
command.CommandText = sql;
//command.Parameters.AddWithValue("@table", tableName);
//command.Parameters.AddWithValue("!geomcolumn", geometryColumn.QuotedName);
command.ExecuteNonQuery();
}
catch (SqlException sqlException)
{
if (!sqlException.Message.Contains("already exists"))
{
throw;
}
}
}
// Server and client. Drop trigger and create WKT transfer trigger.
Deprovisioning.DropTableGeomTrigger(client, tableName);
command.CommandText = string.Format(@"CREATE TRIGGER [dbo].[{0}_GEOMSRID_trigger]
ON [dbo].[{0}]
AFTER INSERT, UPDATE
AS
BEGIN
IF CONTEXT_INFO() = 0xFF
RETURN
SET CONTEXT_INFO 0xFF
UPDATE [dbo].[{0}]
SET [{1}].STSrid = {2}
FROM [dbo].[{0}]
WHERE {3} IN (SELECT {3} FROM inserted
WHERE inserted.[{1}] IS NOT NULL
AND [{1}].STSrid <> {2}
)
SET CONTEXT_INFO 0x00
END", tableName, geometryColumn.UnquotedName, srid.Value, joinColumn.QuotedName);
command.ExecuteNonQuery();
// Alter selectedchanges stored procedure to convert geometry to WKT on fly.
Server clientserver = new Server(client.DataSource);
Database db = clientserver.Databases[client.Database];
foreach (StoredProcedure sp in db.StoredProcedures.Cast <StoredProcedure>()
.Where(sp => sp.Name.Equals(tableName + "_selectchanges", StringComparison.InvariantCultureIgnoreCase) ||
sp.Name.Equals(tableName + "_selectrow", StringComparison.InvariantCultureIgnoreCase)))
{
sp.TextBody = sp.TextBody.Replace(geometryColumn.QuotedName, string.Format("{0}.STAsText() as {0}", geometryColumn.QuotedName));
try
{
sp.Alter();
}
catch (FailedOperationException ex)
{
if (ex.Operation != "Alter")
{
throw;
}
}
}
}
public static void ProvisionDatabase(SqlConnection destination, SqlConnection master, string scopeName, string srid)
{
bool oneway = false;
if (scopeName == "OneWay")
{
oneway = true;
}
bool isSlave = false;
if (!destination.ConnectionString.Equals(master.ConnectionString))
{
isSlave = true;
}
DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(scopeName);
SqlSyncScopeProvisioning destinationConfig = new SqlSyncScopeProvisioning(destination);
if (destinationConfig.ScopeExists(scopeName))
{
return;
}
// TODO: Retrieve actual sync tables
IList <SpatialColumnInfo> twowaytableList = new List <SpatialColumnInfo>();
twowaytableList.Add(new SpatialColumnInfo {
TableName = "WaterJobs"
});
twowaytableList.Add(new SpatialColumnInfo {
TableName = "SewerJobs"
});
twowaytableList.Add(new SpatialColumnInfo {
TableName = "ParkAssets"
});
// TODO: Retrieve actual sync tables
IList <SpatialColumnInfo> onewaytableList = new List <SpatialColumnInfo>();
onewaytableList.Add(new SpatialColumnInfo {
TableName = "WaterFittings"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "WaterMains"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "WaterMeters"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "WaterServices"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "SewerNodes"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "SewerPipes"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "RoadLabels"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "Cadastre"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "AddressNumbers"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "Towns"
});
onewaytableList.Add(new SpatialColumnInfo {
TableName = "LocalityBoundaries"
});
if (isSlave)
{
destination.Open();
if (!oneway)
{
DropTables(destination, twowaytableList);
}
else
{
DropTables(destination, onewaytableList);
}
destination.Close();
}
DbSyncColumnDescription identityColumn = null;
DbSyncColumnDescription geometryColumn = null;
if (!oneway)
{
foreach (SpatialColumnInfo spatialTable in twowaytableList)
{
DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(spatialTable.TableName, master);
if (table.PkColumns.Count() != 1 || table.PkColumns.First().Type != "uniqueidentifier")
{
try
{
table.Columns["UniqueID"].IsPrimaryKey = true;
table.PkColumns.FirstOrDefault().IsPrimaryKey = false;
}
catch (Exception)
{
throw new DataSyncException("Tables require a column called 'UniqueID' of type 'uniqueidentifier' to be used with spatial syncing." +
"\nThe UniqueID column should also have a default value of newid() and a UNIQUE, NONCLUSTERED index.");
}
}
foreach (DbSyncColumnDescription item in table.NonPkColumns)
{
if (item.AutoIncrementStepSpecified)
{
identityColumn = item;
spatialTable.IdentityColumn = item.UnquotedName;
continue;
}
if (!item.Type.Contains("geometry"))
{
continue;
}
spatialTable.GeometryColumn = item.UnquotedName;
geometryColumn = item;
geometryColumn.ParameterName += "_was_geometry";
geometryColumn.Type = "nvarchar";
geometryColumn.Size = "max";
geometryColumn.SizeSpecified = true;
}
if (geometryColumn == null || identityColumn == null)
{
throw new DataSyncException("Spatial tables must contain a geometry column and an identity column.");
}
table.Columns.Remove(identityColumn);
if (destination.Equals(master))
{
destinationConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
}
else
{
identityColumn.IsPrimaryKey = true;
destinationConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
}
scopeDescription.Tables.Add(table);
}
}
if (oneway)
{
foreach (var spatialTable in onewaytableList)
{
DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(spatialTable.TableName, master);
spatialTable.IdentityColumn = table.PkColumns.FirstOrDefault().UnquotedName;
foreach (DbSyncColumnDescription item in table.NonPkColumns)
{
if (!item.Type.Contains("geometry"))
{
continue;
}
spatialTable.GeometryColumn = item.UnquotedName;
geometryColumn = item;
geometryColumn.ParameterName += "_was_geometry";
geometryColumn.Type = "nvarchar";
geometryColumn.Size = "max";
geometryColumn.SizeSpecified = true;
}
if (geometryColumn == null)
{
throw new DataSyncException("Spatial tables must contain a geometry column and an identity column.");
}
scopeDescription.Tables.Add(table);
}
}
//It is important to call this after the tables have been added to the scope
destinationConfig.PopulateFromScopeDescription(scopeDescription);
//provision the server
destinationConfig.Apply();
destination.Open();
if (!oneway)
{
foreach (SpatialColumnInfo spatialTable in twowaytableList)
{
string tableName = spatialTable.TableName;
SqlCommand command = destination.CreateCommand();
if (isSlave)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] int IDENTITY(1,1) NOT NULL", tableName, spatialTable.IdentityColumn);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT PK_{0}", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT PK_{0} PRIMARY KEY CLUSTERED ([{1}])", tableName, spatialTable.IdentityColumn);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD UNIQUE (UniqueID)", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [DF_{0}_UniqueID] DEFAULT (newid()) FOR [UniqueID]", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] geometry", tableName, spatialTable.GeometryColumn);
command.ExecuteNonQuery();
command.CommandText = string.Format("CREATE SPATIAL INDEX [SIndex_{0}_{1}] ON [{0}]([{1}]) USING GEOMETRY_GRID WITH (BOUNDING_BOX =(300000, 6700000, 500000, 7000000), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]", tableName, spatialTable.GeometryColumn);
command.ExecuteNonQuery();
}
try
{
command.CommandText = string.Format("CREATE TRIGGER [dbo].[{0}_GEOMSRID_trigger]\nON [dbo].[{0}]\nAFTER INSERT, UPDATE\nAS UPDATE [dbo].[{0}] SET [{1}].STSrid = {2}\nFROM [dbo].[{0}]\nJOIN inserted\nON [dbo].[{0}].[UniqueID] = inserted.[UniqueID] AND inserted.[{1}] IS NOT NULL", spatialTable.TableName, spatialTable.GeometryColumn, srid);
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (!ex.Message.StartsWith("There is already"))
{
throw;
}
}
Server server = new Server(destination.DataSource);
Database db = server.Databases[destination.Database];
foreach (StoredProcedure sp in db.StoredProcedures.Cast <StoredProcedure>().Where(sp => sp.Name.Equals(tableName + "_selectchanges", StringComparison.InvariantCultureIgnoreCase) || sp.Name.Equals(tableName + "_selectrow", StringComparison.InvariantCultureIgnoreCase)))
{
sp.TextBody = sp.TextBody.Replace(string.Format("[{0}]", spatialTable.GeometryColumn), string.Format("[{0}].STAsText() as [{0}]", spatialTable.GeometryColumn));
sp.Alter();
}
}
}
else
{
foreach (SpatialColumnInfo spatialTable in onewaytableList)
{
string tableName = spatialTable.TableName;
SqlCommand command = destination.CreateCommand();
if (isSlave)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] geometry", tableName, spatialTable.GeometryColumn);
command.ExecuteNonQuery();
command.CommandText = string.Format("CREATE SPATIAL INDEX [SIndex_{0}_{1}] ON [{0}]([{1}]) USING GEOMETRY_GRID WITH (BOUNDING_BOX =(300000, 6700000, 500000, 7000000), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]", tableName, spatialTable.GeometryColumn);
command.ExecuteNonQuery();
}
try
{
command.CommandText = string.Format("CREATE TRIGGER [dbo].[{0}_GEOMSRID_trigger]\nON [dbo].[{0}]\nAFTER INSERT, UPDATE\nAS UPDATE [dbo].[{0}] SET [{1}].STSrid = {2}\nFROM [dbo].[{0}]\nJOIN inserted\nON [dbo].[{0}].[{3}] = inserted.[{3}] AND inserted.[{1}] IS NOT NULL", spatialTable.TableName, spatialTable.GeometryColumn, srid, spatialTable.IdentityColumn);
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (!ex.Message.StartsWith("There is already"))
{
throw;
}
}
Server server = new Server(destination.DataSource);
Database db = server.Databases[destination.Database];
foreach (StoredProcedure sp in db.StoredProcedures.Cast <StoredProcedure>()
.Where(sp => sp.Name.Equals(tableName + "_selectchanges", StringComparison.InvariantCultureIgnoreCase) ||
sp.Name.Equals(tableName + "_selectrow", StringComparison.InvariantCultureIgnoreCase)))
{
sp.TextBody = sp.TextBody.Replace(string.Format("[{0}]", spatialTable.GeometryColumn),
string.Format("[{0}].STAsText() as [{0}]", spatialTable.GeometryColumn));
sp.Alter();
}
}
}
}
private static DbSyncScopeDescription ParseCSDLDocument(ArgsParser parser, string uriString, XDocument document)
{
DbSyncScopeDescription scopeDescription = null;
var uri = new Uri(uriString);
// Assumption is that for OData Sync metadata document, the URI is of format http://foo/snc.svc/scopename/$metadata.
// In this case we are looking for the last but one segment.
var scopeName = uri.Segments[uri.Segments.Length - 2];
if (scopeName.EndsWith("/"))
{
scopeName = scopeName.Substring(0, scopeName.Length - 1);
}
if (parser.UseVerbose)
{
SyncSvcUtil.Log("Parsed ScopeName as {0}", scopeName);
}
// Its an CSDL document
var dataServicesElem = document.Root.Element(Constants.SyncScopeDataServicesElement);
if (dataServicesElem == null)
{
throw new CsdlException("No <DataServices> element found in the <edmx> document.");
}
var schemaElement = dataServicesElem.Element(Constants.SyncScopeSchemaElement);
if (schemaElement == null)
{
throw new CsdlException("No <Schema> element found in the <DataServices> document.");
}
scopeDescription = new DbSyncScopeDescription(scopeName);
// Loop over each <EntityType> element and add it as a DbSyncTableDescription
foreach (var entity in schemaElement.Elements(Constants.SyncScopeEntityTypeElement))
{
var nameAttr = entity.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (nameAttr == null)
{
throw new CsdlException("<EntityType> has no Name attribute. \n" + entity);
}
// Parse each entity and create a DbSyncTableDescription
var table = new DbSyncTableDescription(nameAttr.Value);
// Look for <Key> element
var keyElem = entity.Element(Constants.SyncScopeEntityTypeKeyElement);
if (keyElem == null)
{
throw new CsdlException("<EntityType> has no <Key> elements defined. \n" + entity);
}
var keyNames = new List <string>();
// Loop over each <PropertyRef> element and add it to the list for lookup
foreach (var prop in keyElem.Elements(Constants.SyncScopeEntityTypeKeyRefElement))
{
var keyName = prop.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (keyName != null)
{
keyNames.Add(keyName.Value);
}
}
// Loop over each <Property> element and add it as a DbSyncColumnDescription
foreach (var field in entity.Elements(Constants.SyncScopeEntityTypePropertyElement))
{
// Read Property name
var fieldName = field.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (fieldName == null)
{
throw new CsdlException("<Property> has no Name attribute. \n" + field);
}
// Read Property Edm type
var fieldType = field.Attribute(Constants.SyncScopeEntityTypeTypeAttribute);
if (fieldType == null)
{
throw new CsdlException("<Property> has no Type attribute. \n" + field);
}
// Read Property Nullable attribute
var fieldNullable = field.Attribute(Constants.SyncScopeEntityTypeNullableAttribute);
var column = new DbSyncColumnDescription(fieldName.Value, GetSqlTypeForEdm(fieldType.Value));
if (fieldNullable != null && bool.Parse(fieldNullable.Value))
{
column.IsNullable = true;
}
column.IsPrimaryKey = keyNames.Contains(fieldName.Value);
table.Columns.Add(column);
}
scopeDescription.Tables.Add(table);
}
return(scopeDescription);
}
/// <summary>
/// Denotes the mapping between the SQLType and the actual .NET CLR type
/// Uses the mapping defined in the following MSDN link http://msdn.microsoft.com/en-us/library/ms131092.aspx
/// </summary>
/// <param name="tableDesc">DbSyncTableDescription object</param>
/// <param name="colDesc">DbSyncColumnDescription object</param>
/// <returns> A .NET CLRT type name</returns>
private static CodeTypeReference GetTypeFromSqlType(DbSyncTableDescription tableDesc, DbSyncColumnDescription colDesc)
{
string sqltype = colDesc.Type;
bool isNullable = colDesc.IsNullable;
if (sqltype.Equals("sql_variant", StringComparison.OrdinalIgnoreCase))
{
sqltype = "variant";
}
SqlDbType type = (SqlDbType)Enum.Parse(typeof(SqlDbType), sqltype, true);
Type retType;
switch (type)
{
case SqlDbType.Bit:
retType = typeof(bool);
break;
case SqlDbType.BigInt:
retType = (typeof(Int64));
break;
case SqlDbType.Binary:
case SqlDbType.Image:
case SqlDbType.VarBinary:
case SqlDbType.Timestamp:
retType = (typeof(byte[]));
break;
case SqlDbType.Char:
retType = (typeof(string));
break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
retType = (typeof(DateTime));
break;
case SqlDbType.DateTimeOffset:
retType = (typeof(DateTimeOffset));
break;
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
retType = (typeof(decimal));
break;
case SqlDbType.Float:
retType = (typeof(double));
break;
case SqlDbType.Int:
retType = (typeof(int));
break;
case SqlDbType.NChar:
case SqlDbType.Text:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Xml:
retType = (typeof(string));
break;
case SqlDbType.Real:
retType = (typeof(Single));
break;
case SqlDbType.SmallInt:
retType = (typeof(Int16));
break;
case SqlDbType.Time:
retType = (typeof(TimeSpan));
break;
case SqlDbType.TinyInt:
retType = (typeof(byte));
break;
case SqlDbType.UniqueIdentifier:
retType = (typeof(Guid));
break;
case SqlDbType.Variant:
default:
throw new NotSupportedException(string.Format("Column '{0}' in Table '{1}' has an unsupported SqlType - '{2}'",
colDesc.UnquotedName, tableDesc.UnquotedGlobalName, sqltype));
}
if (isNullable && retType.IsValueType)
{
CodeTypeReference ctr = new CodeTypeReference(typeof(Nullable <>));
ctr.TypeArguments.Add(retType);
return(ctr);
}
else
{
return(new CodeTypeReference(retType));
}
}
public static void ProvisionTable(SqlConnection master, SqlConnection destination, string tableName, int srid, bool oneWayOnly)
{
bool isSlave = false;
if (!destination.ConnectionString.Equals(master.ConnectionString))
{
isSlave = true;
}
DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(tableName);
SqlSyncScopeProvisioning destinationConfig = new SqlSyncScopeProvisioning(destination);
if (destinationConfig.ScopeExists(tableName))
{
throw new SyncConstraintConflictNotAllowedException(@"Scope already exists. Please deprovision scope first.");
}
DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, master);
DbSyncColumnDescription uniqueColumn = table.Columns.Where(f => f.Type == "uniqueidentifier").FirstOrDefault();
DbSyncColumnDescription geometryColumn = table.Columns.Where(f => f.Type.EndsWith("geometry")).FirstOrDefault();
DbSyncColumnDescription identityColumn = table.Columns.Where(f => f.AutoIncrementStepSpecified).FirstOrDefault();
DbSyncColumnDescription joinColumn = table.PkColumns.FirstOrDefault();
if (table.PkColumns.Count() != 1)
{
throw new SyncException(@"Table must have a single primary key column to be used with synchronisation.");
}
if (uniqueColumn != null && !uniqueColumn.IsPrimaryKey && !oneWayOnly)
{
table.PkColumns.FirstOrDefault().IsPrimaryKey = false;
uniqueColumn.IsPrimaryKey = true;
joinColumn = uniqueColumn;
}
if (geometryColumn != null)
{
geometryColumn.ParameterName += "_was_geometry";
geometryColumn.Type = "nvarchar";
geometryColumn.Size = "max";
geometryColumn.SizeSpecified = true;
}
if (identityColumn != null && identityColumn != joinColumn)
{
table.Columns.Remove(identityColumn);
}
destinationConfig.SetCreateTableDefault(isSlave ? DbSyncCreationOption.Create : DbSyncCreationOption.Skip);
scopeDescription.Tables.Add(table);
//It is important to call this after the tables have been added to the scope
destinationConfig.PopulateFromScopeDescription(scopeDescription);
//provision the server
destinationConfig.Apply();
destination.Open();
SqlCommand command = destination.CreateCommand();
if (isSlave && identityColumn != null && identityColumn != joinColumn)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ADD {1} int IDENTITY(1,1) NOT NULL", tableName, identityColumn.QuotedName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT PK_{0}", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT PK_{0} PRIMARY KEY CLUSTERED ({1})", tableName, identityColumn.QuotedName);
command.ExecuteNonQuery();
}
if (uniqueColumn != null && isSlave && uniqueColumn == joinColumn)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ADD UNIQUE ({1})", tableName, uniqueColumn.QuotedName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [DF_{0}_{2}] DEFAULT (newid()) FOR {1}", tableName, uniqueColumn.QuotedName, uniqueColumn.UnquotedName.Replace(' ', '_'));
command.ExecuteNonQuery();
}
if (geometryColumn == null)
{
return;
}
if (isSlave)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN {1} geometry", tableName, geometryColumn.QuotedName);
command.ExecuteNonQuery();
try
{
command.CommandText = string.Format("CREATE SPATIAL INDEX [ogr_{2}_sidx] ON [{0}]({1}) USING GEOMETRY_GRID WITH (BOUNDING_BOX =(300000, 6700000, 500000, 7000000), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]", tableName, geometryColumn.QuotedName, geometryColumn.UnquotedName);
command.ExecuteNonQuery();
}
catch (SqlException sqlException)
{
if (!sqlException.Message.Contains("already exists"))
{
throw;
}
}
}
command.CommandText = string.Format("IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_GEOMSRID_trigger]')) DROP TRIGGER [dbo].[{0}_GEOMSRID_trigger]", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("CREATE TRIGGER [dbo].[{0}_GEOMSRID_trigger]\nON [dbo].[{0}]\nAFTER INSERT, UPDATE\nAS UPDATE [dbo].[{0}] SET [{1}].STSrid = {2}\nFROM [dbo].[{0}]\nJOIN inserted\nON [dbo].[{0}].{3} = inserted.{3} AND inserted.[{1}] IS NOT NULL", tableName, geometryColumn.UnquotedName, srid, joinColumn.QuotedName);
command.ExecuteNonQuery();
Server server = new Server(destination.DataSource);
Database db = server.Databases[destination.Database];
foreach (StoredProcedure sp in db.StoredProcedures.Cast <StoredProcedure>().Where(sp => sp.Name.Equals(tableName + "_selectchanges", StringComparison.InvariantCultureIgnoreCase) || sp.Name.Equals(tableName + "_selectrow", StringComparison.InvariantCultureIgnoreCase)))
{
sp.TextBody = sp.TextBody.Replace(geometryColumn.QuotedName, string.Format("{0}.STAsText() as {0}", geometryColumn.QuotedName));
sp.Alter();
}
}
/// <summary>
/// Called whenever a checkbox from the list of columns (sync/filter) is checked or unchecked
/// If Column index is 1 then its a sync column
/// If column is removed then remove from sync columns collection. Also disable the column if it was a filter column.
/// Disable the filter col checkbox so it cannot be added as a filter without adding it as a sync column first
/// If column is enabled then add it to sync columns collection. Enable filter col checkbox so it can be clicked
/// If Column index is 2 then its a filter column.
/// If filter col is disabled then remove the column from filter params/clause/columns collection
/// If filter col is enabled then add the column to the filter params/clause/columns collection
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void colsView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
SyncTableConfigElement table = selectedScope.SyncTables.GetElement(this.tablesBox.SelectedItem.ToString());
if (e.ColumnIndex > 0)
{
// Check to see if its a column being added/removed from sync
DataGridViewCheckBoxCell cell = this.colsView.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell;
DbSyncColumnDescription col = tableDesc.Columns[e.RowIndex];
if (e.ColumnIndex == 1)
{
if (cell.Value == null || !(bool)cell.Value)
{
// Sync column unchecked
SyncColumnConfigElement colConfig = table.SyncColumns.GetElement(this.colsView.Rows[e.RowIndex].Cells[0].Value.ToString());
if (colConfig != null)
{
table.SyncColumns.Remove(colConfig.Name);
this.RemoveFilterColumnInfo(table, colConfig);
this.colsView.Rows[e.RowIndex].Cells[2].Value = false;
// Make filter col readonly
this.colsView.Rows[e.RowIndex].Cells[2].ReadOnly = true;
}
}
else if (table.SyncColumns.GetElement(col.UnquotedName) == null)
{
// Sync column is checked. Add it back to the SyncColumn list
SyncColumnConfigElement colConfig = new SyncColumnConfigElement()
{
Name = col.UnquotedName,
IsPrimaryKey = false,
IsNullable = col.IsNullable,
SqlType = col.Type,
};
table.SyncColumns.Add(colConfig);
// Set the filter col to enabled.
this.colsView.Rows[e.RowIndex].Cells[2].ReadOnly = false;
}
table.IncludeAllColumns = table.SyncColumns.Count == colsView.Rows.Count;
}
else
{
// Its a filter column
SyncColumnConfigElement colConfig = table.SyncColumns.GetElement(colsView.Rows[e.RowIndex].Cells[0].Value.ToString());
if (colConfig != null)
{
string filterParamName = "@" + WizardHelper.SanitizeName(colConfig.Name);
string andFilterClause = string.Format(AndFilterClauseFormat, colConfig.Name, filterParamName);
string FilterClause = string.Format(FilterClauseFormat, colConfig.Name, filterParamName);
if (cell.Value != null && !(bool)cell.Value)
{
// Filter column unchecked
this.RemoveFilterColumnInfo(table, colConfig);
}
else if (table.FilterColumns.GetElement(colConfig.Name) == null)
{
// Add Filter column
table.FilterColumns.Add(new FilterColumnConfigElement()
{
Name = colConfig.Name
});
// Add Filter parameter
table.FilterParameters.Add(new FilterParameterConfigElement()
{
Name = filterParamName,
SqlType = tableDesc.Columns[e.RowIndex].Type,
});
// Fix by xperiandi, Thks !
if ((tableDesc.Columns[e.RowIndex].SizeSpecified))
{
// Set size
DbSyncColumnDescription column = tableDesc.Columns[e.RowIndex];
string columnsSize = column.Size;
if (string.Compare(columnsSize, "max", StringComparison.OrdinalIgnoreCase) == 0 &&
(string.Compare(column.Type, "nvarchar", StringComparison.OrdinalIgnoreCase) *
string.Compare(column.Type, "varchar", StringComparison.OrdinalIgnoreCase)) == 0)
{
table.FilterParameters.GetElementAt(table.FilterParameters.Count - 1).DataSize = 4000;
}
else
{
table.FilterParameters.GetElementAt(table.FilterParameters.Count - 1).DataSize = int.Parse(columnsSize);
}
}
if (string.IsNullOrEmpty(table.FilterClause))
{
table.FilterClause = string.Format(FilterClauseFormat, colConfig.Name, filterParamName);
}
else
{
table.FilterClause += string.Format(AndFilterClauseFormat, colConfig.Name, filterParamName);
}
}
}
this.filterClauseTxtBox.Text = table.FilterClause;
}
}
}
/// <summary>
/// Denotes the mapping between the SQLType and the actual .NET CLR type
/// Uses the mapping defined in the following MSDN link http://msdn.microsoft.com/en-us/library/ms131092.aspx
/// </summary>
/// <param name="tableDesc">DbSyncTableDescription object</param>
/// <param name="colDesc">DbSyncColumnDescription object</param>
/// <returns> A .NET CLRT type name</returns>
private static CodeTypeReference GetTypeFromSqlType(DbSyncTableDescription tableDesc, DbSyncColumnDescription colDesc)
{
string sqltype = colDesc.Type;
bool isNullable = colDesc.IsNullable;
if(sqltype.Equals("sql_variant", StringComparison.OrdinalIgnoreCase))
{
sqltype = "variant";
}
SqlDbType type = (SqlDbType)Enum.Parse(typeof(SqlDbType), sqltype, true);
Type retType;
switch (type)
{
case SqlDbType.Bit:
retType = typeof(bool);
break;
case SqlDbType.BigInt:
retType = (typeof(Int64));
break;
case SqlDbType.Binary:
case SqlDbType.Image:
case SqlDbType.VarBinary:
case SqlDbType.Timestamp:
retType = (typeof(byte[]));
break;
case SqlDbType.Char:
retType = (typeof(string));
break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
retType = (typeof(DateTime));
break;
case SqlDbType.DateTimeOffset:
retType = (typeof(DateTimeOffset));
break;
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
retType = (typeof(decimal));
break;
case SqlDbType.Float:
retType = (typeof(double));
break;
case SqlDbType.Int:
retType = (typeof(int));
break;
case SqlDbType.NChar:
case SqlDbType.Text:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Xml:
retType = (typeof(string));
break;
case SqlDbType.Real:
retType = (typeof(Single));
break;
case SqlDbType.SmallInt:
retType = (typeof(Int16));
break;
case SqlDbType.Time:
retType = (typeof(TimeSpan));
break;
case SqlDbType.TinyInt:
retType = (typeof(byte));
break;
case SqlDbType.UniqueIdentifier:
retType = (typeof(Guid));
break;
case SqlDbType.Variant:
default:
throw new NotSupportedException(string.Format("Column '{0}' in Table '{1}' has an unsupported SqlType - '{2}'",
colDesc.UnquotedName, tableDesc.UnquotedGlobalName, sqltype));
}
if (isNullable && retType.IsValueType)
{
CodeTypeReference ctr = new CodeTypeReference(typeof(Nullable<>));
ctr.TypeArguments.Add(retType);
return ctr;
}
else
{
return new CodeTypeReference(retType);
}
}
private static DbSyncScopeDescription ParseCSDLDocument(ArgsParser parser, string uriString, XDocument document)
{
DbSyncScopeDescription scopeDescription = null;
Uri uri = new Uri(uriString);
// Assumption is that for OData Sync metadata document, the URI is of format http://foo/snc.svc/scopename/$metadata.
// In this case we are looking for the last but one segment.
string scopeName = uri.Segments[uri.Segments.Length - 2];
if (scopeName.EndsWith("/"))
{
scopeName = scopeName.Substring(0, scopeName.Length - 1);
}
if (parser.UseVerbose)
{
SyncSvcUtil.Log("Parsed ScopeName as {0}", scopeName);
}
// Its an CSDL document
XElement dataServicesElem = document.Root.Element(Constants.SyncScopeDataServicesElement);
if (dataServicesElem == null)
{
throw new CsdlException("No <DataServices> element found in the <edmx> document.");
}
XElement schemaElement = dataServicesElem.Element(Constants.SyncScopeSchemaElement);
if (schemaElement == null)
{
throw new CsdlException("No <Schema> element found in the <DataServices> document.");
}
scopeDescription = new DbSyncScopeDescription(scopeName);
// Loop over each <EntityType> element and add it as a DbSyncTableDescription
foreach (XElement entity in schemaElement.Elements(Constants.SyncScopeEntityTypeElement))
{
XAttribute nameAttr = entity.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (nameAttr == null)
{
throw new CsdlException("<EntityType> has no Name attribute. \n" + entity.ToString());
}
// Parse each entity and create a DbSyncTableDescription
DbSyncTableDescription table = new DbSyncTableDescription(nameAttr.Value);
// Look for <Key> element
XElement keyElem = entity.Element(Constants.SyncScopeEntityTypeKeyElement);
if (keyElem == null)
{
throw new CsdlException("<EntityType> has no <Key> elements defined. \n" + entity.ToString());
}
List<string> keyNames = new List<string>();
// Loop over each <PropertyRef> element and add it to the list for lookup
foreach (XElement prop in keyElem.Elements(Constants.SyncScopeEntityTypeKeyRefElement))
{
XAttribute keyName = prop.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (keyName != null)
{
keyNames.Add(keyName.Value);
}
}
// Loop over each <Property> element and add it as a DbSyncColumnDescription
foreach (XElement field in entity.Elements(Constants.SyncScopeEntityTypePropertyElement))
{
// Read Property name
XAttribute fieldName = field.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (fieldName == null)
{
throw new CsdlException("<Property> has no Name attribute. \n" + field.ToString());
}
// Read Property Edm type
XAttribute fieldType = field.Attribute(Constants.SyncScopeEntityTypeTypeAttribute);
if (fieldType == null)
{
throw new CsdlException("<Property> has no Type attribute. \n" + field.ToString());
}
// Read Property Nullable attribute
XAttribute fieldNullable = field.Attribute(Constants.SyncScopeEntityTypeNullableAttribute);
DbSyncColumnDescription column = new DbSyncColumnDescription(fieldName.Value, GetSqlTypeForEdm(fieldType.Value));
if (fieldNullable != null && bool.Parse(fieldNullable.Value))
{
column.IsNullable = true;
}
column.IsPrimaryKey = keyNames.Contains(fieldName.Value);
table.Columns.Add(column);
}
scopeDescription.Tables.Add(table);
}
return scopeDescription;
}
