} // end of GenerateSqlStaticDict method // Generates UPDATE statement for closing records and INSERT statement for openning records private static string GenerateSqlSlowDict(int targetId, MetricGroup metricGroup, ProbeResultingData data, List <Tuple <int, int> > rowsChanged, List <int> rowsNotInDict) { CacheTable dict = Configuration.inMemoryCache[InMemoryCache.GetCacheKey(targetId, metricGroup, CacheType.Dictionary)]; string sqlStmt = string.Empty; // old rows where endDate need to be updated if (rowsChanged.Count > 0) { sqlStmt = "UPDATE " + SqlServerProbe.DictTableName(targetId, metricGroup) + Environment.NewLine; sqlStmt += "SET endDate = '" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "'" + Environment.NewLine; sqlStmt += "WHERE"; foreach (Tuple <int, int> ids in rowsChanged) { sqlStmt += Environment.NewLine + "(id = " + ids.Item2.ToString() + " AND "; sqlStmt += "startDate = '" + SqlServerProbe.DateTimeToString((DateTime)dict[ids.Item2][metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes]) + "')"; sqlStmt += " OR "; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 4); // remove last ' OR ' sqlStmt += ";" + Environment.NewLine; // new records for changed rows sqlStmt += "INSERT INTO " + SqlServerProbe.DictTableName(targetId, metricGroup) + " (id,"; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ","; } for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ","; } sqlStmt += "startDate,endDate)" + Environment.NewLine; sqlStmt += "VALUES " + Environment.NewLine; foreach (Tuple <int, int> ids in rowsChanged) { sqlStmt += "(" + ids.Item2.ToString() + ","; for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeys[j].type, data.values[ids.Item1, j]) + ","; } for (int j = 0; j < metricGroup.NumberOfMultiRowKeyAttributes; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeyAttributes[j].type, data.values[ids.Item1, metricGroup.NumberOfMultiRowKeys + j]) + ","; } // add startDate and endDate sqlStmt += "'" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',NULL),"; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma } // new rows if (rowsNotInDict.Count > 0) { sqlStmt += "INSERT INTO " + SqlServerProbe.DictTableName(targetId, metricGroup) + " (id,"; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ","; } for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { sqlStmt += Environment.NewLine + metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ","; } sqlStmt += "startDate,endDate)" + Environment.NewLine; sqlStmt += "VALUES " + Environment.NewLine; foreach (int i in rowsNotInDict) { sqlStmt += "(NEXT VALUE FOR " + SqlServerProbe.SchemaName(targetId) + ".seq_" + metricGroup.dictTableName + ","; for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeys[j].type, data.values[i, j]) + ","; } for (int j = 0; j < metricGroup.NumberOfMultiRowKeyAttributes; j++) { sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeyAttributes[j].type, data.values[i, metricGroup.NumberOfMultiRowKeys + j]) + ","; } // add startDate and endDate sqlStmt += "'" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',NULL),"; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma } return(sqlStmt); } // end of GenerateSqlSlowDict method