public void AddORRemoveForeignKey_AddOperationFalse_ExecutesCorrectly() { // Arrange var expectedCommands = new List <string> { "BEGIN TRY " + "WHILE EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME " + "like 'FK_EPMLIVE_%' AND TABLE_NAME=@tableName) BEGIN DECLARE @cName nvarchar(Max) " + "SELECT @cName = (SELECT TOP 1 [CONSTRAINT_NAME] FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE " + "WHERE CONSTRAINT_NAME LIKE 'FK_EPMLIVE_%' AND TABLE_NAME=@tableName) " + "DECLARE @sql nvarchar(Max) SELECT @sql = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @cName " + "EXEC sp_executesql @sql END END TRY BEGIN CATCH PRINT 'Error Detected' END CATCH", "BEGIN TRY " + "WHILE EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME like" + " 'FK_EPMLIVE_%' AND TABLE_NAME=@tableName) BEGIN DECLARE @cName nvarchar(Max) " + "SELECT @cName = (SELECT TOP 1 [CONSTRAINT_NAME] FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE " + "WHERE CONSTRAINT_NAME LIKE 'FK_EPMLIVE_%' AND TABLE_NAME=@tableName) " + "DECLARE @sql nvarchar(Max) SELECT @sql = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @cName " + "EXEC sp_executesql @sql END END TRY BEGIN CATCH PRINT 'Error Detected' END CATCH" }; var executedCommands = new List <string>(); var dao = new ShimEPMData { ExecuteNonQuerySqlConnection = _ => true, GetClientReportingConnectionGet = () => new SqlConnection(), Dispose = () => { }, CommandSetString = command => executedCommands.Add(command) }; ShimReportBiz.AllInstances.GetAllForeignKeysEPMData = (_, epmData) => new ShimDataTable { RowsGet = () => new ShimDataRowCollection { GetEnumerator = () => new List <DataRow> { new ShimDataRow { ItemGetString = name => DummyGuid } }.GetEnumerator() } }; var foreignKey = new ShimDataRow { ItemGetString = name => DummyString }; // Act var result = reportBiz.AddORRemoveForeignKey(dao, foreignKey, false); // Assert result.ShouldSatisfyAllConditions( () => result.ShouldBeTrue(), () => expectedCommands.All(p => executedCommands.Contains(p)).ShouldBeTrue()); }
private bool UpdateField(SPListEventProperties properties) { bool isSuccessful = true; string tableName = string.Empty; string ssTableName = string.Empty; var rb = new ReportBiz(properties.SiteId); bool isUpdateRequire = false; try { var rd = new ReportData(properties.SiteId); var cols = new ColumnDefCollection(); // Actual table name tableName = rd.GetTableName(properties.ListId); // Snapshot table name ssTableName = rd.GetTableNameSnapshot(properties.ListId); // Create column definition collection to process with cols.AddColumn(properties.Field); var DAO = new EPMData(properties.SiteId); // Update only when field type is changed from single to multi value and vice-verse if (IsUpdateRequire(DAO, properties, tableName, cols[0].SqlColumnName)) { isUpdateRequire = true; // Get Foreign Key DataTable foreignKeyTable = rb.GetSpecificForeignKey(DAO, properties.ListId.ToString(), tableName, cols[0].SqlColumnName); // Delete Foreign Key if (foreignKeyTable.Rows.Count > 0) { rb.AddORRemoveForeignKey(DAO, foreignKeyTable.Rows[0], false); } // Delete & Add Field rd.UpdateColumns(tableName, cols); rd.UpdateColumns(ssTableName, cols); rd.UpdateListColumns(properties.ListId, cols); // Add Foreign Key only if lookup type is NOT allowed multi select if (foreignKeyTable.Rows.Count > 0 && !properties.Field.TypeAsString.Equals("LookupMulti", StringComparison.InvariantCultureIgnoreCase)) { rb.AddORRemoveForeignKey(DAO, foreignKeyTable.Rows[0], true); } } DAO.Dispose(); rd.Dispose(); } catch (Exception ex) { var DAO = new EPMData(properties.SiteId); DAO.LogStatus(properties.ListId.ToString(), properties.FieldName, "Database column update attempt: Unable to update column " + properties.FieldName + ". " + ex.Message, ex.StackTrace, 2, 5, Guid.NewGuid().ToString()); //Logged in the RefreshAll event log. DAO.Dispose(); } if (properties.Field is SPFieldLookup && isUpdateRequire) { #region Update all foreign keys try { //FOREIGN IMPLEMENTATION -- START var DAO = new EPMData(properties.SiteId); rb.UpdateForeignKeys(DAO); DAO.Dispose(); // -- END } catch (Exception ex) { isSuccessful = false; SPSecurity.RunWithElevatedPrivileges(delegate { if (!EventLog.SourceExists("EPMLive Reporting - UpdateForeignKeys")) { EventLog.CreateEventSource("EPMLive Reporting - UpdateForeignKeys", "EPM Live"); } LogEntry(ex, "EPMLive Reporting - UpdateForeignKeys"); }); } #endregion #region Cleanup list after field data type update to retain value try { // List clean up -- START var _DAO = new EPMData(properties.SiteId); CleanupListAfterFieldUpdate(_DAO, properties.ListTitle); // -- END } catch (Exception ex) { isSuccessful = false; SPSecurity.RunWithElevatedPrivileges(delegate { if (!EventLog.SourceExists("EPMLive Reporting - CleanupListAfterFieldUpdate")) { EventLog.CreateEventSource("EPMLive Reporting - CleanupListAfterFieldUpdate", "EPM Live"); } LogEntry(ex, "EPMLive Reporting - CleanupListAfterFieldUpdate"); }); } #endregion } return(isSuccessful); }