private bool Archive(int targetId, ArchiveOffset archiveOffset, MetricGroup metricGroup, DateTime archiveTo, DateTime archiveFrom) { // Do not archive static and slow changing metrics if (metricGroup.changeSpeed != ChangeSpeed.Fast) return false; // Compose SQL statement // Save aggregated data in a temp table string sqlStmt = "SELECT " + RoundDate("dt", archiveOffset.IntervalInSeconds) + " as dt, " + Environment.NewLine; // add dictId if the metric group has multiple rows if (metricGroup.isMultiRow) sqlStmt += "dictId, "; // Add AVG(column names) foreach (var item in metricGroup.metrics) sqlStmt += "AVG(" + item.Value.name.Replace(' ', '_') + ") as " + item.Value.name.Replace(' ', '_') + ", "; sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + Environment.NewLine; // remove last comma sqlStmt += "INTO #AVG_TMP_" + metricGroup.dataTableName + Environment.NewLine; sqlStmt += "FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + Environment.NewLine; sqlStmt += "WHERE dt BETWEEN @dateFrom AND @dateTo" + Environment.NewLine; sqlStmt += "GROUP BY " + RoundDate("dt", archiveOffset.IntervalInSeconds) + ", "; // add dictId if the metric group has multiple rows if (metricGroup.isMultiRow) sqlStmt += "dictId, "; sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + ";" + Environment.NewLine + Environment.NewLine; // remove last comma // Delete aggregated records sqlStmt += "DELETE FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + " WHERE dt BETWEEN @dateFrom AND @dateTo;" + Environment.NewLine + Environment.NewLine; // Copy records from the temp table sqlStmt += "INSERT INTO " + SqlServerProbe.DataTableName(targetId, metricGroup) + " (dt, "; // add dictId if the metric group has multiple rows if (metricGroup.isMultiRow) sqlStmt += "dictId, "; // Add column names foreach (var item in metricGroup.metrics) sqlStmt += item.Value.name.Replace(' ', '_') + ", "; sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2); // remove last comma sqlStmt += ")" + Environment.NewLine; sqlStmt += "SELECT dt, "; // add dictId if the metric group has multiple rows if (metricGroup.isMultiRow) sqlStmt += "dictId, "; // Add column names foreach (var item in metricGroup.metrics) sqlStmt += item.Value.name.Replace(' ', '_') + ", "; sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + Environment.NewLine; // remove last comma sqlStmt += "FROM #AVG_TMP_" + metricGroup.dataTableName + Environment.NewLine + Environment.NewLine; // Update ArchivedToDate value sqlStmt += "UPDATE dbo.ArchiveWatermarks SET ArchivedToDate = @dateTo WHERE ArchiveOffsetId = @archiveOffsetId and TargetId = @targetId;"; _logger.Trace(sqlStmt); // Execute SQL statement SqlTransaction reposTran = null; SqlCommand reposCmd = null; try { if (_reposConn.State != ConnectionState.Open) _reposConn.Open(); reposTran = _reposConn.BeginTransaction(); reposCmd = _reposConn.CreateCommand(); reposCmd.Transaction = reposTran; reposCmd.CommandType = CommandType.Text; reposCmd.CommandText = sqlStmt; reposCmd.CommandTimeout = 300; reposCmd.Parameters.Add("@targetId", SqlDbType.Int); reposCmd.Parameters["@targetId"].Value = targetId; reposCmd.Parameters.Add("@archiveOffsetId", SqlDbType.Int); reposCmd.Parameters["@archiveOffsetId"].Value = archiveOffset.Id; reposCmd.Parameters.Add("@dateFrom", SqlDbType.DateTime2, 6); reposCmd.Parameters["@dateFrom"].Value = RoundDate(archiveFrom, archiveOffset.IntervalInSeconds); reposCmd.Parameters.Add("@dateTo", SqlDbType.DateTime2, 6); reposCmd.Parameters["@dateTo"].Value = archiveTo; reposCmd.Prepare(); reposCmd.ExecuteNonQuery(); reposTran.Commit(); } catch (SqlException e) { if (_reposConn.State != ConnectionState.Open) { Manager.SetRepositoryAccessibility(false); return false; } switch (e.Number) { case 208: // Ignore missing tables. Target might be recently initialized break; default: _logger.Error("SqlException: {0} ErrorCode: {1}", e.Message, e.Number); break; } if (reposTran != null) { // Transaction might be rolled back if commit fails. In this case second rollback will fail try { reposTran.Rollback(); } catch (Exception) { _logger.Debug("Transaction has been rolled back already"); } } return false; } catch (Exception e) { if (_reposConn.State == ConnectionState.Open) { _logger.Error(e.Message); _logger.Error(e.StackTrace); } else { Manager.SetRepositoryAccessibility(false); } return false; } finally { if (reposCmd != null) ((IDisposable)reposCmd).Dispose(); if (reposTran != null) ((IDisposable)reposTran).Dispose(); } return true; }
} // end of Work method private bool Archive(int targetId, ArchiveOffset archiveOffset, MetricGroup metricGroup, DateTime archiveTo, DateTime archiveFrom) { // Do not archive static and slow changing metrics if (metricGroup.changeSpeed != ChangeSpeed.Fast) { return(false); } // Compose SQL statement // Save aggregated data in a temp table string sqlStmt = "SELECT " + RoundDate("dt", archiveOffset.IntervalInSeconds) + " as dt, " + Environment.NewLine; // add dictId if the metric group has multiple rows if (metricGroup.isMultiRow) { sqlStmt += "dictId, "; } // Add AVG(column names) foreach (var item in metricGroup.metrics) { sqlStmt += "AVG(" + item.Value.name.Replace(' ', '_') + ") as " + item.Value.name.Replace(' ', '_') + ", "; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + Environment.NewLine; // remove last comma sqlStmt += "INTO #AVG_TMP_" + metricGroup.dataTableName + Environment.NewLine; sqlStmt += "FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + Environment.NewLine; sqlStmt += "WHERE dt BETWEEN @dateFrom AND @dateTo" + Environment.NewLine; sqlStmt += "GROUP BY " + RoundDate("dt", archiveOffset.IntervalInSeconds) + ", "; // add dictId if the metric group has multiple rows if (metricGroup.isMultiRow) { sqlStmt += "dictId, "; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + ";" + Environment.NewLine + Environment.NewLine; // remove last comma // Delete aggregated records sqlStmt += "DELETE FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + " WHERE dt BETWEEN @dateFrom AND @dateTo;" + Environment.NewLine + Environment.NewLine; // Copy records from the temp table sqlStmt += "INSERT INTO " + SqlServerProbe.DataTableName(targetId, metricGroup) + " (dt, "; // add dictId if the metric group has multiple rows if (metricGroup.isMultiRow) { sqlStmt += "dictId, "; } // Add column names foreach (var item in metricGroup.metrics) { sqlStmt += item.Value.name.Replace(' ', '_') + ", "; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2); // remove last comma sqlStmt += ")" + Environment.NewLine; sqlStmt += "SELECT dt, "; // add dictId if the metric group has multiple rows if (metricGroup.isMultiRow) { sqlStmt += "dictId, "; } // Add column names foreach (var item in metricGroup.metrics) { sqlStmt += item.Value.name.Replace(' ', '_') + ", "; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + Environment.NewLine; // remove last comma sqlStmt += "FROM #AVG_TMP_" + metricGroup.dataTableName + Environment.NewLine + Environment.NewLine; // Update ArchivedToDate value sqlStmt += "UPDATE dbo.ArchiveWatermarks SET ArchivedToDate = @dateTo WHERE ArchiveOffsetId = @archiveOffsetId and TargetId = @targetId;"; _logger.Trace(sqlStmt); // Execute SQL statement SqlTransaction reposTran = null; SqlCommand reposCmd = null; try { if (_reposConn.State != ConnectionState.Open) { _reposConn.Open(); } reposTran = _reposConn.BeginTransaction(); reposCmd = _reposConn.CreateCommand(); reposCmd.Transaction = reposTran; reposCmd.CommandType = CommandType.Text; reposCmd.CommandText = sqlStmt; reposCmd.CommandTimeout = 300; reposCmd.Parameters.Add("@targetId", SqlDbType.Int); reposCmd.Parameters["@targetId"].Value = targetId; reposCmd.Parameters.Add("@archiveOffsetId", SqlDbType.Int); reposCmd.Parameters["@archiveOffsetId"].Value = archiveOffset.Id; reposCmd.Parameters.Add("@dateFrom", SqlDbType.DateTime2, 6); reposCmd.Parameters["@dateFrom"].Value = RoundDate(archiveFrom, archiveOffset.IntervalInSeconds); reposCmd.Parameters.Add("@dateTo", SqlDbType.DateTime2, 6); reposCmd.Parameters["@dateTo"].Value = archiveTo; reposCmd.Prepare(); reposCmd.ExecuteNonQuery(); reposTran.Commit(); } catch (SqlException e) { if (_reposConn.State != ConnectionState.Open) { Manager.SetRepositoryAccessibility(false); return(false); } switch (e.Number) { case 208: // Ignore missing tables. Target might be recently initialized break; default: _logger.Error("SqlException: {0} ErrorCode: {1}", e.Message, e.Number); break; } if (reposTran != null) { // Transaction might be rolled back if commit fails. In this case second rollback will fail try { reposTran.Rollback(); } catch (Exception) { _logger.Debug("Transaction has been rolled back already"); } } return(false); } catch (Exception e) { if (_reposConn.State == ConnectionState.Open) { _logger.Error(e.Message); _logger.Error(e.StackTrace); } else { Manager.SetRepositoryAccessibility(false); } return(false); } finally { if (reposCmd != null) { ((IDisposable)reposCmd).Dispose(); } if (reposTran != null) { ((IDisposable)reposTran).Dispose(); } } return(true); } // end of Archive method