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; }