private bool AddField(SPListEventProperties properties)
{
bool isSuccessful = true;
string tableName = string.Empty;
string ssTableName = string.Empty;
var rb = new ReportBiz(properties.SiteId);
try
{
var rd = new ReportData(properties.SiteId);
var cols = new ColumnDefCollection();
tableName = rd.GetTableName(properties.ListId);
ssTableName = rd.GetTableNameSnapshot(properties.ListId);
if (!rd.ColumnExists(tableName, properties.Field.InternalName))
{
cols.AddColumn(properties.Field);
rd.AddColumns(tableName, cols);
rd.AddColumns(ssTableName, cols);
rd.InsertListColumns(properties.ListId, cols);
}
rd.Dispose();
}
catch (Exception ex)
{
var DAO = new EPMData(properties.SiteId);
DAO.LogStatus(properties.ListId.ToString(), properties.FieldName,
"Database column add attempt: Unable to add 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)
{
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");
});
}
}
return(isSuccessful);
}
public void UpdateForeignKeys_Should_ExecuteCorrectly()
{
// 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()
}
};
// Act
var result = reportBiz.UpdateForeignKeys(dao);
// 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);
}