public bool UpdateForeignKeys(EPMData DAO) { DataTable AllForeignKeysByList = GetAllForeignKeys(DAO); //Loop thru list tables and ADD FOREIGN KEYS foreach (DataRow AllListForeignKeys in AllForeignKeysByList.Rows) { //DELETE EXISTING FK's from list table DAO.Command = "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"; DAO.AddParam("@tableName", AllListForeignKeys["TABLE_NAME"].ToString()); // - CAT.NET false-positive: All single quotes are escaped/removed. DAO.ExecuteNonQuery(DAO.GetClientReportingConnection); //DELETE EXISTING FK's from list snapshot table DAO.Command = "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"; DAO.AddParam("@tableName", AllListForeignKeys["SNAPSHOT_TABLE_NAME"].ToString()); // - CAT.NET false-positive: All single quotes are escaped/removed. DAO.ExecuteNonQuery(DAO.GetClientReportingConnection); //ADD FK's to list table DAO.Command = AllListForeignKeys["FK_TABLE_SCRIPT"].ToString(); // - CAT.NET false-positive: All single quotes are escaped/removed. DAO.ExecuteNonQuery(DAO.GetClientReportingConnection); //ADD FK's to list snapshot table DAO.Command = AllListForeignKeys["FK_SNAPSHOT_TABLE_SCRIPT"].ToString(); // - CAT.NET false-positive: All single quotes are escaped/removed. DAO.ExecuteNonQuery(DAO.GetClientReportingConnection); } return(true); }
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"]; }
/// <summary> /// Refreshes all. /// </summary> /// <returns></returns> public string RefreshAll() { try { using (var epmData = new EPMData(Web.Site.ID)) { epmData.DeleteWork(Guid.Empty, -1); epmData.Command = "select timerjobuid from timerjobs where siteguid=@siteguid and listguid is null and jobtype=5"; epmData.AddParam("@siteguid", Web.Site.ID); object result = epmData.ExecuteScalar(epmData.GetEPMLiveConnection); Guid timerJobId = Guid.Empty; if (result != null) { timerJobId = (Guid)result; } else { timerJobId = Guid.NewGuid(); epmData.Command = "INSERT INTO TIMERJOBS (siteguid, jobtype, jobname, scheduletype, webguid, timerjobuid) VALUES (@siteguid, 5, 'Reporting Refresh All', 2, @webguid, @timerjobuid)"; epmData.AddParam("@siteguid", Web.Site.ID); epmData.AddParam("@webguid", Web.ID); epmData.AddParam("@timerjobuid", timerJobId); epmData.ExecuteNonQuery(epmData.GetEPMLiveConnection); } if (timerJobId != Guid.Empty) { CoreFunctions.enqueue(timerJobId, 0, Web.Site); } } return("<RefreshAll><Data/></RefreshAll>"); } catch (APIException) { throw; } catch (Exception exception) { throw new APIException((int)Errors.RefreshAll, exception.GetBaseException().Message); } }
private void CleanupListAfterFieldUpdate(EPMData DAO, string sList) { using (SPSite site = new SPSite(DAO.SiteId)) { using (SPWeb web = site.RootWeb) { Guid listID = DAO.GetListId(sList, web.ID); //DELETE WORK DAO.DeleteWork(listID, -1); //END DAO.Command = "select timerjobuid from timerjobs where siteguid=@siteguid and listguid = @listguid and jobtype=6"; DAO.AddParam("@siteguid", site.ID.ToString()); DAO.AddParam("@listguid", listID.ToString()); object oResult = DAO.ExecuteScalar(DAO.GetEPMLiveConnection); Guid timerjobuid = Guid.Empty; if (oResult != null) { timerjobuid = (Guid)oResult; } else { timerjobuid = Guid.NewGuid(); DAO.Command = "INSERT INTO TIMERJOBS (timerjobuid, siteguid, jobtype, jobname, scheduletype, webguid, listguid, jobdata) VALUES (@timerjobuid, @siteguid, 6, 'List Data Cleanup', 0, @webguid, @listguid, @jobdata)"; DAO.AddParam("@siteguid", site.ID.ToString()); DAO.AddParam("@webguid", web.ID.ToString()); DAO.AddParam("@listguid", listID.ToString()); DAO.AddParam("@jobdata", sList); DAO.AddParam("@timerjobuid", timerjobuid); DAO.ExecuteNonQuery(DAO.GetEPMLiveConnection); } if (timerjobuid != Guid.Empty) { EPMLiveCore.CoreFunctions.enqueue(timerjobuid, 0, site); } } } //--End }
private void DeleteCheck() { var reportBiz = new ReportBiz(SPContext.Current.Site.ID); EPMData _DAO = new EPMData(SPContext.Current.Site.ID); _DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID"; _DAO.AddParam("@RPTListID", _listId); string sTableName = _DAO.ExecuteScalar(_DAO.GetClientReportingConnection).ToString(); DataTable refTables = reportBiz.GetReferencingTables(_DAO, sTableName); if (refTables.Rows.Count == 0) { output = "true"; //reportBiz.GetListBiz(new Guid(Request["List"])).Delete(); } else { string sLists = GetRefLists(refTables, _DAO); //SPUtility.Redirect("epmlive/ListMappings.aspx?delete=true&id=" + param + "&name=" + sTableName, SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current); output = "false," + sLists; } }
private bool DeleteList(SPListEventProperties properties) { bool isSuccessful = true; var reportBiz = new ReportBiz(properties.SiteId); Guid listId = properties.ListId; var DAO = new EPMData(properties.SiteId); DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID"; DAO.AddParam("@RPTListID", listId); string sTableName = string.Empty; try { sTableName = DAO.ExecuteScalar(DAO.GetClientReportingConnection).ToString(); DataTable refTables = reportBiz.GetReferencingTables(DAO, sTableName); if (refTables.Rows.Count == 0) { reportBiz.GetListBiz(listId).Delete(); } else { isSuccessful = false; DAO.LogStatus(listId.ToString(), sTableName, "Database table delete attempt: Unable to delete " + sTableName + ".", sTableName + " is referenced by other tables.", 2, 5, Guid.NewGuid().ToString()); //Logged in the RefreshAll event log. } } catch (Exception ex) { DAO.LogStatus(listId.ToString(), sTableName, "Database table delete attempt: Unable to delete " + sTableName + ". " + ex.Message, ex.StackTrace, 2, 5, Guid.NewGuid().ToString()); //Logged in the RefreshAll event log. } reportBiz = null; DAO.Dispose(); return(isSuccessful); }
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); }
public bool AddTimerJob(SPWeb web, int iTime, int iScheduleType, string days, bool runNow) { SqlConnection con = null; bool passed = true; try { SPSecurity.RunWithElevatedPrivileges(delegate() { Guid timerjobuid = Guid.Empty; if (_DAO == null) { _DAO = new EPMData(web.Site.ID); } _DAO.Command = "select timerjobuid from timerjobs where siteguid=@siteguid and jobtype=8"; _DAO.AddParam("@siteguid", web.Site.ID.ToString()); object oResult = _DAO.ExecuteScalar(_DAO.GetEPMLiveConnection); if (oResult != null) { timerjobuid = (Guid)oResult; _DAO.Command = "UPDATE TIMERJOBS SET runtime = @runtime, scheduleType = @scheduletype, days = @days WHERE jobtype=8"; _DAO.AddParam("@runtime", iTime); _DAO.AddParam("@scheduletype", iScheduleType); _DAO.AddParam("@days", days); _DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection); } else { timerjobuid = Guid.NewGuid(); _DAO.Command = "INSERT INTO TIMERJOBS (timerjobuid,jobname,siteguid,webguid,listguid,jobtype,enabled,runtime,scheduletype,days,jobdata,lastqueuecheck,parentjobuid) VALUES (@timerjobuid,@jobname,@siteguid,@webguid,NULL,@jobtype,@enabled,@runtime,@scheduletype,@days,@jobdata,@lastqueuecheck,@parentjobuid)"; _DAO.AddParam("@timerjobuid", timerjobuid); _DAO.AddParam("@jobname", "EPMLiveADSync"); _DAO.AddParam("@siteguid", web.Site.ID.ToString()); _DAO.AddParam("@webguid", web.Site.RootWeb.ID.ToString()); _DAO.AddParam("@jobtype", 8); _DAO.AddParam("@enabled", true); _DAO.AddParam("@runtime", iTime); _DAO.AddParam("@scheduletype", iScheduleType); _DAO.AddParam("@days", days); _DAO.AddParam("@jobdata", web.Site.RootWeb.Url); _DAO.AddParam("@lastqueuecheck", DBNull.Value); _DAO.AddParam("@parentjobuid", DBNull.Value); _DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection); } if (runNow && timerjobuid != Guid.Empty) { EPMLiveCore.CoreFunctions.enqueue(timerjobuid, 0); } }); } catch (Exception ex) { passed = false; _ExecutionLogs.Add(" ERROR -- Location: AddTimerJob() -- Message: " + ex.Message); _hasErrors = true; } finally { _DAO.Dispose(); } return(passed); }
//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); } } }
protected void Page_Load(object sender, EventArgs e) { _DAO = new EPMData(SPContext.Current.Site.ID); string action = Request["Action"]; Guid timerjobguid = Guid.NewGuid(); //DataTable dtResults; if (action != null && action == "SnapshotAll") { try { //Local Testing -- Start //_DAO.SnapshotLists(timerjobguid, _DAO.GetListNames()); //dtResults = _DAO.GetSnapshotResults(timerjobguid); // -- END //PROD -- Start string sListIDs = _DAO.GetAllListIDs(); using (SPSite site = SPContext.Current.Site) { using (SPWeb web = SPContext.Current.Web) { _DAO.Command = "INSERT INTO TIMERJOBS (timerjobuid, siteguid, jobtype, jobname, scheduletype, webguid, jobdata) VALUES (@timerjobuid,@siteguid, 7, 'Reporting Snapshot All', 0, @webguid, @jobdata)"; _DAO.AddParam("@timerjobuid", timerjobguid); _DAO.AddParam("@siteguid", site.ID.ToString()); _DAO.AddParam("@webguid", web.ID.ToString()); _DAO.AddParam("@jobdata", sListIDs); _DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection); } CoreFunctions.enqueue(timerjobguid, 0); } //END } catch (Exception ex) { _DAO.LogStatus(string.Empty, string.Empty, "Process: SnapshotAll - " + ex.Message, ex.StackTrace, 2, 9, string.Empty); Response.Write("Error: " + ex.Message); return; } SPUtility.Redirect("epmlive/ListMappings.aspx", SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current); } else if (action != null && action == "CleanupAll") { //Local Testing -- Start //string sListNames = _DAO.GetListNames(); //DataTable dtWebResults = null; //timerjobguid = new Guid(); //DataTable dtListResults = new RefreshLists().InitializeResultsDT(sListNames); //if (sListNames != string.Empty) //{ // RefreshLists oListRefresh = null; // foreach (SPWeb web in SPContext.Current.Site.AllWebs) // { // oListRefresh = new RefreshLists(web, sListNames); // oListRefresh.StartRefresh(timerjobguid, out dtWebResults); // dtWebResults.TableName = web.ServerRelativeUrl; // oListRefresh.AppendStatus(web.Name, web.ServerRelativeUrl, dtListResults, dtWebResults); // } // oListRefresh.SaveResults(dtListResults, timerjobguid); //} // -- End //PROD -- Start using (SPSite site = SPContext.Current.Site) { using (SPWeb web = SPContext.Current.Web) { //DELETE WORK _DAO.DeleteWork(Guid.Empty, -1); //END _DAO.Command = "select timerjobuid from timerjobs where siteguid=@siteguid and listguid is null and jobtype=6"; _DAO.AddParam("@siteguid", site.ID.ToString()); object oResult = _DAO.ExecuteScalar(_DAO.GetEPMLiveConnection); Guid timerjobuid = Guid.Empty; if (oResult != null) { timerjobuid = (Guid)oResult; } else { timerjobuid = Guid.NewGuid(); _DAO.Command = "INSERT INTO TIMERJOBS (siteguid, jobtype, jobname, scheduletype, webguid, timerjobuid) VALUES (@siteguid, 6, 'List Data Cleanup', 2, @webguid, @timerjobuid)"; _DAO.AddParam("@siteguid", site.ID.ToString()); _DAO.AddParam("@webguid", web.ID.ToString()); _DAO.AddParam("@timerjobuid", timerjobuid); _DAO.ExecuteNonQuery(_DAO.GetEPMLiveConnection); } if (timerjobuid != Guid.Empty) { CoreFunctions.enqueue(timerjobuid, 0); } } } // -- END SPUtility.Redirect("epmlive/ListMappings.aspx", SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current); } }
public void RaisePostBackEvent(string sAction) { string param = sAction.Remove(sAction.LastIndexOf("_")); sAction = sAction.Substring(sAction.LastIndexOf("_") + 1); switch (sAction) { case "cleanup": CleanupLists(param); break; case "snapshot": SnapshotLists(param); break; case "delete": var reportBiz = new ReportBiz(SPContext.Current.Site.ID, SPContext.Current.Web.ID, reportingV2Enabled); var listId = new Guid(param); _DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID"; _DAO.AddParam("@RPTListID", param); string sTableName = _DAO.ExecuteScalar(_DAO.GetClientReportingConnection).ToString(); DataTable refTables = reportBiz.GetReferencingTables(_DAO, sTableName); if (refTables.Rows.Count == 0) { reportBiz.GetListBiz(new Guid(param)).Delete(); } else { SPUtility.Redirect("epmlive/ListMappings.aspx?delete=true&id=" + param + "&name=" + sTableName, SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current); } break; } SPUtility.Redirect("epmlive/ListMappings.aspx?", SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current); }
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); } //} }