protected void LoadSnapshots()
{
try
{
DataTable manipulateDt = new DataTable();
_DAO.Command = "spGetSnapshotManagementDetails";
_DAO.CommandType = CommandType.StoredProcedure;
DataTable dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
SPBoundField gridColumn;
if (!IsPostBack)
{
foreach (DataColumn column in dt.Columns)
{
gridColumn = new SPBoundField();
gridColumn.HeaderText = column.ColumnName;
gridColumn.DataField = column.ColumnName;
if (column.ColumnName.ToLower().EndsWith("id"))
{
gridColumn.Visible = false;
}
grdVwSnapshots.Columns.Add(gridColumn);
}
grdVwSnapshots.DataSource = dt;
grdVwSnapshots.DataBind();
}
}
catch { }
}
public DataTable GetReferencingTables(EPMData DAO, string sTableName)
{
string sql = string.Format("EXEC sp_MSdependencies N'{0}', null, 1315327", sTableName.Replace("'", ""));
// - CAT.NET false-positive: All single quotes are escaped/removed.
//string sql = "EXEC sp_MSdependencies N'{0}', null, 1315327";
DAO.Command = sql;
//DAO.AddParam("@tableName", sTableName);
return(DAO.GetTable(DAO.GetClientReportingConnection));
}
protected void PopulateLists()
{
DataTable dt;
//_DAO.Command = "SELECT ListName FROM RPTList WHERE SiteId='" + SPContext.Current.Web.Site.ID + "'"; - CAT.NET
_DAO.Command = "SELECT ListName FROM RPTList WHERE SiteId=@siteId";
_DAO.AddParam("@siteId", SPContext.Current.Web.Site.ID);
dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
//using (SPContext.Current.Web)
//{
foreach (DataRow row in dt.Rows)
{
var item = new ListItem();
item.Text = row["ListName"].ToString();
item.Value = row["ListName"].ToString();
ListBoxLists.Items.Add(item);
}
//}
}
protected void LoadSnapshot()
{
DataTable dt;
//_DAO.Command = "SELECT * FROM RPTPeriods WHERE PeriodID = '" + Request.QueryString["uid"] + "'"; - CAT.NET
_DAO.Command = "SELECT * FROM RPTPeriods WHERE PeriodID = @uid";
_DAO.AddParam("@uid", Request.QueryString["uid"].Split(',')[0]);
dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
snapShotDate.SelectedDate = DateTime.Parse(dt.Rows[0]["PeriodDate"].ToString());
title.Text = dt.Rows[0]["Title"].ToString();
activate.Checked = (bool)dt.Rows[0]["Enabled"];
}
public DataTable GetSpecificForeignKey(EPMData DAO, string listId, string tableName, string columnName)
{
// Checks if the specified column has foreign key associated with it
bool hasForeignKey = HasForeignKey(DAO, tableName, columnName);
//Initialize return value DataTable (will hold all FK update scripts for both live and snapshot tables)
var ForeignKeysByTable = new DataTable();
ForeignKeysByTable.Columns.Add("TABLE_NAME");
ForeignKeysByTable.Columns.Add("SNAPSHOT_TABLE_NAME");
ForeignKeysByTable.Columns.Add("FK_TABLE_SCRIPT");
ForeignKeysByTable.Columns.Add("FK_SNAPSHOT_TABLE_SCRIPT");
// Get script if foreign key found
if (hasForeignKey)
{
// Get specific lookup field from list
string sql =
"SELECT dbo.RPTColumn.RPTListId, dbo.RPTList.ListName, dbo.RPTList.TableName, dbo.RPTList.TableNameSnapshot, dbo.RPTColumn.InternalName , dbo.RPTColumn.ColumnName" +
" FROM dbo.RPTList INNER JOIN dbo.RPTColumn ON dbo.RPTList.RPTListId = dbo.RPTColumn.RPTListId " +
"WHERE dbo.RPTList.SiteId=@siteId AND dbo.RPTList.RPTListId=@listId AND (dbo.RPTColumn.SharePointType = N'lookup') " +
"AND (ColumnType = 'Int' OR ColumnType = 'NTEXT') AND dbo.RPTColumn.ColumnName = @columnName";
DAO.Command = sql;
DAO.AddParam("@siteId", DAO.SiteId);
DAO.AddParam("@listId", listId);
DAO.AddParam("@columnName", columnName);
DataTable ListLookupFields = DAO.GetTable(DAO.GetClientReportingConnection);
//Initialize and open topsite/web
SPWeb web = null;
SPSite site = null;
SPList childList = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
site = new SPSite(DAO.SiteId);
web = site.OpenWeb(); //IGNORE SPDispose 110, web is being disposed outside scope
childList = web.Lists[new Guid(listId)];
});
if (childList == null)
{
foreach (SPWeb w in site.AllWebs)
{
try
{
childList = w.Lists[new Guid(listId)];
}
catch { }
w.Close();
if (childList != null)
{
break;
}
}
}
try
{
if (ListLookupFields != null && ListLookupFields.Rows.Count > 0)
{
foreach (DataRow lookupField in ListLookupFields.Rows)
{
var field =
(SPFieldLookup)
childList.Fields.GetFieldByInternalName(lookupField["InternalName"].ToString());
if (field.TypeAsString.ToLower() != "filteredlookup" && field.LookupList != null &&
field.LookupList != string.Empty)
{
#region Get parent list info of lookup field
string listuid = field.LookupList;
SPList parentList = null;
try
{
parentList = web.Lists[new Guid(listuid)];
}
catch { }
DataRow parentListInfo = null;
if (parentList != null)
{
try
{
//Get All mapped lists
DAO.Command = "SELECT * FROM RPTList WHERE SiteId=@siteId AND RPTListId=@listId";
DAO.AddParam("@siteId", DAO.SiteId);
DAO.AddParam("@listId", parentList.ID);
DataTable parentListTable = DAO.GetTable(DAO.GetClientReportingConnection);
if (parentListTable != null && parentListTable.Rows.Count > 0)
{
parentListInfo = parentListTable.Rows[0];
}
}
catch { }
}
#endregion
#region Add foreign key script to table
if (parentListInfo != null)
{
string parentTableName = parentListInfo["TableName"].ToString();
//string childTableName = childListInfo[0]["TableName"].ToString();
string childTableName = lookupField["TableName"].ToString();
//string childTableSnapShotName = childListInfo[0]["TableNameSnapshot"].ToString();
string childTableSnapShotName = lookupField["TableNameSnapshot"].ToString();
string fieldName = lookupField["InternalName"].ToString();
string tableFKName = TrimLongFKID("FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableName.ToUpper() + "_" + parentTableName.ToUpper());
string snapshotFKName = TrimLongFKID("FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableSnapShotName.ToUpper() + "_" + parentTableName.ToUpper());
//Init. LST_XXXX_Table FK SCRIPT
string LST_TABLE_FK_SCRIPT =
"IF (EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'" +
childTableName + "\') AND OBJECT_ID('[" + tableFKName + "]','F') IS NULL) " +
"BEGIN " +
"ALTER TABLE [dbo].[" + childTableName + "] WITH NOCHECK " +
"ADD CONSTRAINT [" + tableFKName + "] FOREIGN KEY([webid], [" + lookupField["ColumnName"] + "]) REFERENCES [dbo].[" +
parentTableName + "] ([WebId], [ItemId]) NOT FOR REPLICATION " +
"ALTER TABLE [dbo].[" + childTableName + "] NOCHECK CONSTRAINT [" + tableFKName + "] " +
"END ";
//Init. LST_XXXX_Snapshot_Table FK SCRIPT
string LST_SNAPSHOT_TABLE_FK_SCRIPT =
"IF (EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'" +
childTableSnapShotName + "\') AND OBJECT_ID('[" + snapshotFKName + "]','F') IS NULL) " +
"BEGIN " +
"ALTER TABLE [dbo].[" + childTableSnapShotName + "] WITH NOCHECK " +
"ADD CONSTRAINT [" + snapshotFKName + "] FOREIGN KEY([webid], [" + lookupField["ColumnName"] + "]) REFERENCES [dbo].[" +
parentTableName + "] ([WebId], [ItemId]) NOT FOR REPLICATION " +
"ALTER TABLE [dbo].[" + childTableSnapShotName.ToUpper() +
"] NOCHECK CONSTRAINT [" + snapshotFKName + "] " +
"END ";
//Add FK SCRIPTS
DataRow row = ForeignKeysByTable.NewRow();
row["TABLE_NAME"] = childTableName.Replace("'", "''");
// - CAT.NET false-positive: All single quotes are escaped/removed.
row["FK_TABLE_SCRIPT"] = LST_TABLE_FK_SCRIPT;
//.Replace("'", "''"); // - CAT.NET false-positive: All single quotes are escaped/removed.
row["SNAPSHOT_TABLE_NAME"] = childTableSnapShotName.Replace("'", "''");
// - CAT.NET false-positive: All single quotes are escaped/removed.
row["FK_SNAPSHOT_TABLE_SCRIPT"] = LST_SNAPSHOT_TABLE_FK_SCRIPT;
//.Replace("'", "''"); // - CAT.NET false-positive: All single quotes are escaped/removed.
ForeignKeysByTable.Rows.Add(row);
}
#endregion
}
}
}
}
catch { }
finally
{
if (web != null)
{
web.Dispose();
}
if (site != null)
{
site.Dispose();
}
}
}
return(ForeignKeysByTable);
}
//Modules created by xjh -- START
public DataTable GetAllForeignKeys(EPMData DAO)
{
//Initialize return value DataTable (will hold all FK update scripts for both live and snapshot tables)
var AllForeignKeysByTable = new DataTable();
AllForeignKeysByTable.Columns.Add("TABLE_NAME");
AllForeignKeysByTable.Columns.Add("SNAPSHOT_TABLE_NAME");
AllForeignKeysByTable.Columns.Add("FK_TABLE_SCRIPT");
AllForeignKeysByTable.Columns.Add("FK_SNAPSHOT_TABLE_SCRIPT");
//Get All mapped lists
//DAO.Command = "SELECT * FROM RPTList WHERE SiteId='" + _siteId.ToString() + "'"; - CAT.NET
DAO.Command = "SELECT * FROM RPTList WHERE SiteId=@siteId";
DAO.AddParam("@siteId", _siteId);
DataTable AllMappedLists = DAO.GetTable(DAO.GetClientReportingConnection);
//Get All lookup fields for ALL mapped lists
string sql =
"SELECT dbo.RPTColumn.RPTListId, dbo.RPTList.ListName, dbo.RPTList.TableName, dbo.RPTList.TableNameSnapshot, dbo.RPTColumn.InternalName , dbo.RPTColumn.ColumnName" +
" FROM dbo.RPTList INNER JOIN dbo.RPTColumn ON dbo.RPTList.RPTListId = dbo.RPTColumn.RPTListId " +
"WHERE dbo.RPTList.SiteId=@siteId AND (dbo.RPTColumn.SharePointType = N'lookup') AND ColumnType = 'Int'";
DAO.Command = sql;
DAO.AddParam("@siteId", _siteId);
DataTable AllListsLookupFields = DAO.GetTable(DAO.GetClientReportingConnection);
//Initialize and open topsite/web
SPWeb web = null;
SPSite site = null;
SPSecurity.RunWithElevatedPrivileges(delegate
{
site = new SPSite(_siteId);
web = site.OpenWeb(); //IGNORE SPDispose 110, web is being disposed outside scope
});
try
{
//Loop thru all mapped lists
foreach (DataRow list in AllMappedLists.Rows)
{
//Init. Lookup fields for list
DataRow[] lookupFields = AllListsLookupFields.Select("RPTListId='" + list["RPTListId"] + "'");
//Check for lookup fields for this list
if (lookupFields != null && lookupFields.Length > 0)
{
//Init. list
SPList childList = null;
try
{
childList = web.Lists[list["ListName"].ToString()];
}
catch { }
if (childList == null)
{
foreach (SPWeb w in site.AllWebs)
{
try
{
childList = w.Lists[list["ListName"].ToString()];
}
catch { }
w.Close();
if (childList != null)
{
break;
}
}
}
//Loop thru all list lookup fields
foreach (DataRow lookupField in lookupFields)
{
var field =
(SPFieldLookup)
childList.Fields.GetFieldByInternalName(lookupField["InternalName"].ToString());
if (field.TypeAsString.ToLower() != "filteredlookup" && field.LookupList != null &&
field.LookupList != string.Empty)
{
string listuid = field.LookupList;
SPList parentList = null;
try
{
parentList = web.Lists[new Guid(listuid)];
}
catch { }
DataRow[] childListInfo =
AllMappedLists.Select("ListName='" + childList.Title.Replace("'", "") + "'");
// - CAT.NET false-positive: All single quotes are escaped/removed.
DataRow[] parentListInfo = null;
if (parentList != null)
{
try
{
parentListInfo =
AllMappedLists.Select("ListName='" + parentList.Title.Replace("'", "") + "'");
// - CAT.NET false-positive: All single quotes are escaped/removed.
}
catch { }
}
if (parentListInfo != null && parentListInfo.Length > 0)
{
//CAT.NET (issue) - Not being updated. Internal call, table name check and validation is done prior to reaching this point.
string parentTableName = parentListInfo[0]["TableName"].ToString();
string childTableName = childListInfo[0]["TableName"].ToString();
string childTableSnapShotName = childListInfo[0]["TableNameSnapshot"].ToString();
string fieldName = lookupField["InternalName"].ToString();
//Init. LST_XXXX_Table FK SCRIPT
string LST_TABLE_FK_SCRIPT =
"IF (EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'" +
childTableName + "\')) " +
"BEGIN " +
"ALTER TABLE [dbo].[" + childTableName + "] WITH NOCHECK " +
"ADD CONSTRAINT [FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableName.ToUpper() + "_" + parentTableName.ToUpper() +
"] FOREIGN KEY([webid], [" + lookupField["ColumnName"] + "]) REFERENCES [dbo].[" +
parentTableName + "] ([WebId], [ItemId]) NOT FOR REPLICATION " +
"ALTER TABLE [dbo].[" + childTableName + "] NOCHECK CONSTRAINT [FK_EPMLIVE_" +
lookupField["InternalName"] + "_" + childTableName.ToUpper() + "_" +
parentTableName.ToUpper() + "] " +
"END ";
//Init. LST_XXXX_Snapshot_Table FK SCRIPT
string LST_SNAPSHOT_TABLE_FK_SCRIPT =
"IF (EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'" +
childTableSnapShotName + "\')) " +
"BEGIN " +
"ALTER TABLE [dbo].[" + childTableSnapShotName + "] WITH NOCHECK " +
"ADD CONSTRAINT [FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableSnapShotName.ToUpper() + "_" + parentTableName.ToUpper() +
"] FOREIGN KEY([webid], [" + lookupField["ColumnName"] + "]) REFERENCES [dbo].[" +
parentTableName + "] ([WebId], [ItemId]) NOT FOR REPLICATION " +
"ALTER TABLE [dbo].[" + childTableSnapShotName.ToUpper() +
"] NOCHECK CONSTRAINT [FK_EPMLIVE_" + lookupField["InternalName"] + "_" +
childTableSnapShotName.ToUpper() + "_" + parentTableName.ToUpper() + "] " +
"END ";
//Add FK SCRIPTS
DataRow row = AllForeignKeysByTable.NewRow();
row["TABLE_NAME"] = childTableName.Replace("'", "''");
// - CAT.NET false-positive: All single quotes are escaped/removed.
row["FK_TABLE_SCRIPT"] = LST_TABLE_FK_SCRIPT;
//.Replace("'", "''"); // - CAT.NET false-positive: All single quotes are escaped/removed.
row["SNAPSHOT_TABLE_NAME"] = childTableSnapShotName.Replace("'", "''");
// - CAT.NET false-positive: All single quotes are escaped/removed.
row["FK_SNAPSHOT_TABLE_SCRIPT"] = LST_SNAPSHOT_TABLE_FK_SCRIPT;
//.Replace("'", "''"); // - CAT.NET false-positive: All single quotes are escaped/removed.
AllForeignKeysByTable.Rows.Add(row);
}
}
}
}
}
}
catch
{
if (web != null)
{
try
{
web.Dispose();
}
catch { }
}
if (site != null)
{
try
{
site.Dispose();
}
catch { }
}
}
if (web != null)
{
try
{
web.Dispose();
}
catch { }
}
if (site != null)
{
try
{
site.Dispose();
}
catch { }
}
return(AllForeignKeysByTable);
}
//protected Panel pnl_results;
protected void Page_Load(object sender, EventArgs e)
{
string sListName = string.Empty;
string sEntries = string.Empty;
Literal lit;
var site = new SPSite(SPContext.Current.Site.ID);
_DAO = new EPMData(site.ID);
if (Request.QueryString["uid"] == null)
{
//_DAO.Command = string.Format("SELECT ListName, LongMessage FROM RPTLog WHERE RPTListID=@rptListID AND (Timestamp BETWEEN DATEADD(s, - 1, @timestamp) AND DATEADD(s, 2,@timestamp)) AND Type=@type AND CAST(ShortMessage as NVarchar(MAX))='{0}'", Request.QueryString["sm"]); - CAT.NET
_DAO.Command =
"SELECT ListName, LongMessage FROM RPTLog WHERE RPTListID=@rptListID AND (Timestamp BETWEEN DATEADD(s, - 1, @timestamp) AND DATEADD(s, 2,@timestamp)) AND Type=@type AND CAST(ShortMessage as NVarchar(MAX))=@sm";
// - CAT.NET false-positive: All single quotes are escaped/removed.
_DAO.AddParam("@rptListID", Request.QueryString["id"]);
_DAO.AddParam("@timestamp",
DateTime.Parse(Request.QueryString["ts"]).ToString(CultureInfo.GetCultureInfo("en-US")));
_DAO.AddParam("@type", Request.QueryString["type"]);
_DAO.AddParam("@sm", Request.QueryString["sm"]);
DataTable dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
if (sListName != row["ListName"].ToString())
{
sListName = row["ListName"].ToString();
lit = new Literal();
lit.Text = "<b>" + sListName + "</b><br/><br/>";
pnl_results.Controls.Add(lit);
}
lit = new Literal();
lit.Text = row["LongMessage"].ToString();
pnl_results.Controls.Add(lit);
}
}
else
{
lit = new Literal();
lit.Text = "<b> No errors found.</b><br/><br/>";
pnl_results.Controls.Add(lit);
}
}
else
{
//_DAO.Command = string.Format("SELECT ListName, LongMessage, Timestamp FROM RPTLog WHERE timerjobguid='{0}'", Request.QueryString["uid"]);
_DAO.Command = "SELECT ListName, LongMessage, Timestamp FROM RPTLog WHERE timerjobguid=@uid";
_DAO.AddParam("@uid", Request.QueryString["uid"]);
DataTable dt = _DAO.GetTable(_DAO.GetClientReportingConnection);
var lists = new ArrayList();
//Looping thru all rows and adding distinct list names
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
if (!lists.Contains(row["ListName"].ToString()))
{
lists.Add(row["ListName"].ToString());
}
}
}
else
{
lit = new Literal();
lit.Text = "<b> No errors found.</b><br/><br/>";
pnl_results.Controls.Add(lit);
return;
}
//Looping thru all lists
foreach (string list in lists)
{
sListName = list;
sEntries = string.Empty;
//Looping thru all list log entries
DataRow[] lstEntries = dt.Select("ListName='" + list + "'");
foreach (DataRow lstEntry in lstEntries)
{
sEntries = sEntries + lstEntry["LongMessage"] + " Run on " + lstEntry["Timestamp"] + " <br/>";
}
lit = new Literal();
lit.Text = "<b>" + sListName + "</b><br/>";
pnl_results.Controls.Add(lit);
lit = new Literal();
lit.Text = " " + sEntries;
pnl_results.Controls.Add(lit);
}
}
}
