} // end of LoadDictionaryIntoCache function // Loads data from repository into in-memory cache. Creates a new record in dictionaryCache public static void LoadDataIntoCache(int targetId, MetricGroup metricGroup, bool allowReload, SqlConnection connection = null, SqlTransaction transaction = null) { string cacheKey; SqlConnection conn; if (metricGroup.changeSpeed != ChangeSpeed.Fast) { throw new Exception("Only fast changing metric is allowed"); } SqlCommand cmd = null; SqlDataReader dataReader = null; // create new in-memory cache for dictionary if (!ContainsKey(GetCacheKey(targetId, metricGroup, CacheType.Data))) { Dictionary <int, Column> valueColumns = new Dictionary <int, Column>(); // value/metric columns for (int i = 0; i < metricGroup.NumberOfMetrics; i++) { valueColumns.Add(i, metricGroup.metrics[i]); } TryAdd(GetCacheKey(targetId, metricGroup, CacheType.Data), new CacheTable(new Dictionary <int, Column>(), valueColumns, metricGroup.isCumulative)); } cacheKey = GetCacheKey(targetId, metricGroup, CacheType.Data); CacheTable tmpCache = _cache[cacheKey].CloneAndClear(); // don't reload cache unless allowReload is specified if (allowReload == false && tmpCache.loadedFromDatabase) { return; } try { string sqlStmt = "SELECT dictId, "; for (int i = 0; i < metricGroup.NumberOfMetrics; i++) { sqlStmt += metricGroup.metrics[i].name.Replace(' ', '_') + ", "; } sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2); // remove last comma sqlStmt += Environment.NewLine + "FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + Environment.NewLine + "WHERE dt = (SELECT MAX(dt) FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + ")"; _logger.Trace(sqlStmt); if (connection == null) { conn = new SqlConnection(Configuration.GetReposConnectionString("Cache")); conn.Open(); } else { conn = connection; } int attempt = 1; bool canTry = true; while (attempt < 3 && canTry) { cmd = conn.CreateCommand(); cmd.CommandText = sqlStmt; cmd.CommandType = System.Data.CommandType.Text; if (transaction != null) { cmd.Transaction = transaction; } try { dataReader = cmd.ExecuteReader(); int id; object[] values = new object[metricGroup.NumberOfMetrics]; while (dataReader.Read()) { id = (int)dataReader["dictId"]; for (int i = 0; i < metricGroup.NumberOfMetrics; i++) { values[i] = dataReader[metricGroup.metrics[i].name.Replace(' ', '_')]; } tmpCache.Add(id, new object[0], values); } dataReader.Close(); tmpCache.loadedFromDatabase = true; Replace(cacheKey, tmpCache); } catch (SqlException e) { if (transaction != null) { transaction.Rollback(); } switch (e.Number) { case 208: // Invalid object // Do not create tables if target has been deleted if (!Configuration.targets.ContainsKey(targetId)) { return; } SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup); break; default: _logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString()); canTry = false; break; } } finally { if (dataReader != null) { ((IDisposable)dataReader).Dispose(); } if (cmd != null) { ((IDisposable)cmd).Dispose(); } } attempt++; } if (connection == null) { conn.Close(); } } catch (Exception e) { _logger.Error("SqlException: " + e.Message); } } // end of LoadDataIntoCache function
private static void LoadCacheTableFromDatabaseSingleRowRealtime(MetricGroup metricGroup) { SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader dataReader = null; // check whether dictionary is loaded if (!ContainsKey(GetCacheKey(-1, metricGroup))) { CreateCacheTableSingleRow(metricGroup, CacheType.Data); } CacheTable cache = _cache[GetCacheKey(-1, metricGroup)]; if (cache.loadedFromDatabase) { return; } try { conn = new SqlConnection(Configuration.GetReposConnectionString("Cache")); conn.Open(); int targetId; string query; object[] values = new object[metricGroup.NumberOfMetrics]; // load data for each target with the specified metric group active foreach (InstanceSchedule schedule in Configuration.timeTable.Values) { targetId = schedule._targetId; if (schedule._metricGroupId == metricGroup.id) { query = QueryToLoadSingleRowRealtime(targetId, metricGroup); cmd = null; dataReader = null; int attempt = 1; bool canTry = true; while (attempt < 3 && canTry) { try { cmd = new SqlCommand(query); cmd.Connection = conn; dataReader = cmd.ExecuteReader(); // should return only one row if (dataReader.Read()) { for (int i = 0; i < metricGroup.NumberOfMetrics; i++) { values[i] = dataReader[metricGroup.metrics[i].name.Replace(' ', '_')]; } cache.Add(-1, new object[] { targetId }, values); } dataReader.Close(); } catch (SqlException e) { switch (e.Number) { case 208: // Invalid object // Do not create tables if target has been deleted if (!Configuration.targets.ContainsKey(targetId)) { return; } SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup); break; default: _logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString()); _logger.Error(e.StackTrace); canTry = false; break; } } finally { if (dataReader != null) { ((IDisposable)dataReader).Dispose(); } if (cmd != null) { ((IDisposable)cmd).Dispose(); } } attempt++; } } } conn.Close(); } catch (SqlException e) { _logger.Error(e.Message); _logger.Error(e.StackTrace); } finally { if (conn != null) { ((IDisposable)conn).Dispose(); } } cache.loadedFromDatabase = true; }
// Loads dictionary from repository into in-memory cache. Creates a new record in dictionaryCache public static void LoadDictionaryIntoCache(int targetId, MetricGroup metricGroup, bool allowReload, SqlConnection connection = null, SqlTransaction transaction = null) { string cacheKey; SqlConnection conn; int tryCount = 0; bool canExit = false; // create new in-memory cache for dictionary if (!ContainsKey(GetCacheKey(targetId, metricGroup, CacheType.Dictionary))) { Dictionary <int, Column> keyColumns = new Dictionary <int, Column>(); Dictionary <int, Column> attrColumns = new Dictionary <int, Column>(); // key columns for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { keyColumns.Add(i, metricGroup.multiRowKeys[i]); } // attribute columns for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { attrColumns.Add(i, metricGroup.multiRowKeyAttributes[i]); } TryAdd(GetCacheKey(targetId, metricGroup, CacheType.Dictionary), new CacheTable(keyColumns, attrColumns, false)); } cacheKey = GetCacheKey(targetId, metricGroup, CacheType.Dictionary); CacheTable tmpCache = _cache[cacheKey].CloneAndClear(); // don't reload cache unless allowReload is specified if (allowReload == false && tmpCache.loadedFromDatabase) { return; } string sqlStmt = "SELECT id, "; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { sqlStmt += metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ", "; } for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { sqlStmt += metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ", "; } if (metricGroup.multiRowKeyAttributesChangeSpeed == ChangeSpeed.Static) { sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2); // remove last comma } else { sqlStmt += "startDate "; } sqlStmt += Environment.NewLine + "FROM " + SqlServerProbe.DictTableName(targetId, metricGroup); if (metricGroup.multiRowKeyAttributesChangeSpeed == ChangeSpeed.Slow) { sqlStmt += Environment.NewLine + "WHERE endDate IS NULL"; } _logger.Trace(sqlStmt); while (!canExit && tryCount < 2) { try { if (connection == null) { conn = new SqlConnection(Configuration.GetReposConnectionString("Cache")); conn.Open(); } else { conn = connection; } using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sqlStmt; cmd.CommandType = System.Data.CommandType.Text; if (transaction != null) { cmd.Transaction = transaction; } using (SqlDataReader dataReader = cmd.ExecuteReader()) { int id; object[] keys = new object[metricGroup.NumberOfMultiRowKeys]; object[] values = new object[metricGroup.NumberOfMultiRowKeyAttributes]; while (dataReader.Read()) { id = (int)dataReader["id"]; for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++) { keys[i] = dataReader[1 + i]; } for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++) { values[i] = dataReader[1 + metricGroup.NumberOfMultiRowKeys + i]; } // add record to dictionary switch (metricGroup.multiRowKeyAttributesChangeSpeed) { case ChangeSpeed.Static: tmpCache.Add(id, keys, values); break; case ChangeSpeed.Slow: tmpCache.Add(id, keys, values, (DateTime)dataReader[1 + metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes]); break; default: throw new Exception("Only Static and Slow changing dictionaries are supported"); } } tmpCache.loadedFromDatabase = true; Replace(cacheKey, tmpCache); dataReader.Close(); } } if (connection == null) { conn.Close(); } } catch (SqlException e) { if (e.Number == 208) // Invalid object { // Do not create tables if target has been deleted if (!Configuration.targets.ContainsKey(targetId)) { return; } SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup); } else { _logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString()); } } // end of catch tryCount++; } // end of while } // end of LoadDictionaryIntoCache function