public static string GetFormatedMemCachedKey(this DataRow myRow, List<string> PKColumnsList, CachedQuery QuerySpecs) { string FormattedKey = string.Empty; if (myRow != null && myRow.Table.Columns != null && myRow.Table.Columns.Count > 0) { if (PKColumnsList != null && PKColumnsList.Count > 1) { FormattedKey = string.Format("{0}.key=", QuerySpecs.KeyPrefix); foreach(string ColumnName in PKColumnsList) { FormattedKey += myRow[ColumnName].ToString().ToUpper().Trim().Replace(" ", string.Empty) + "+"; } int LastSeparatorPos = FormattedKey.LastIndexOf("+"); if (LastSeparatorPos > 0) { FormattedKey = FormattedKey.Remove(LastSeparatorPos); } } else if (PKColumnsList != null && PKColumnsList.Count == 1) { FormattedKey = string.Format("{0}.key={1}", QuerySpecs.KeyPrefix, myRow[PKColumnsList.ElementAt(0)].ToString()); } else { FormattedKey = string.Format("{0}.key={1}", QuerySpecs.KeyPrefix, myRow[0].ToString()); } } return FormattedKey; }
/// <summary> /// Loads the DataRows retrieved from MYSQL in a Generic Dictionary Ready to be Cached in MemCached. Uses Generic Collections but will have to be converted to a HashTable for the Memcached Client /// </summary> /// <param name="MySQLConfig"></param> /// <param name="QuerySpecs"></param> /// <param name="MySQLTableRowsToCache"></param> /// <param name="DictionaryToCache"></param> /// <param name="ErrorMessage"></param> /// <returns></returns> public static bool GetQueryCacheDictionaryFromDataTable(DatabaseSettings DBConfig, CachedQuery QuerySpecs, DataTable DataTableRowsToCache, out Dictionary<string,Dictionary<string, string>> DictionaryToCache, out string ErrorMessage) { bool CreatedDictionary = false; ErrorMessage = string.Empty; DictionaryToCache = new Dictionary<string, Dictionary<string, string>>(); List<string> PKColumnNames = null; /* * First Get Database Table Schema for the table to cache */ DataTable TableSchemaDefinition = Utils.GetSchemaTypeDatabaseSQLTable(DBConfig, QuerySpecs.DatabaseTableName, QuerySpecs.DBConnString); /* * Get Primary Key ColumnNames */ if (TableSchemaDefinition.Columns != null && TableSchemaDefinition.Columns.Count > 0) { PKColumnNames = Utils.GetPrimaryKeyColumnNamesCollection(TableSchemaDefinition); } /* * Build in Memory Dictionary to Load in Memcached service */ if (DataTableRowsToCache != null && DataTableRowsToCache.Rows.Count > 0) { foreach (DataRow dr in DataTableRowsToCache.Rows) { string MainDictKey = dr.GetFormatedMemCachedKey(PKColumnNames, QuerySpecs); Dictionary<string, string> NestedDictValue = dr.Table.Columns .Cast<DataColumn>() .ToDictionary(col => col.ColumnName, col => dr[col.ColumnName].ToString()); DictionaryToCache.Add(MainDictKey, NestedDictValue); } } /* * Determine whether dictionary was populated */ CreatedDictionary = (DictionaryToCache != null && DictionaryToCache.Count > 0); return CreatedDictionary; }
private static List<CachedQuery> LoadQueriesSettings(XmlNodeList queriesNodes) { List<CachedQuery> ReturnCollection = new List<CachedQuery>(); if (queriesNodes != null && queriesNodes.Count > 0) { foreach (XmlNode queryNode in queriesNodes) { if (queryNode.HasChildNodes) { CachedQuery cachedQuery = new CachedQuery(); foreach (XmlNode XmlItem in queryNode.ChildNodes) { if (XmlItem.Name.Equals("keyprefix")) { cachedQuery.KeyPrefix = XmlItem.InnerText; continue; } if (XmlItem.Name.Equals("sql")) { cachedQuery.Sql = XmlItem.InnerText; continue; } if (XmlItem.Name.Equals("database_tablename")) { cachedQuery.DatabaseTableName = XmlItem.InnerText; continue; } if (XmlItem.Name.Equals("db_connection")) { cachedQuery.DBConnString = XmlItem.InnerText; continue; } } /* * Add new query to return collection */ ReturnCollection.Add(cachedQuery); } } } /* * Results */ return ReturnCollection; }
public static bool LoadQueryInRedis(MemcachedLoaderConfig Config, CachedQuery QueryToLoad, out string ErrMsg) { bool LoadedQuery = false; ErrMsg = string.Empty; Dictionary<string, Dictionary<string, string>> MemoryDict; IRedisClientsManager RedisClientManager; string RedisConnectionString = Config.RedisConnectionSettings.GetConnectionString(); try { using (RedisClientManager = new PooledRedisClientManager(RedisConnectionString)) { /* * Get Redis Client */ var client = RedisClientManager.GetClient(); /* * Retrieve Query Data from MySql */ DataTable QueryDataTable = Utils.GetDataTable(Config.DBConnectionSettings, QueryToLoad); /* * Determine whether to permanently persist kvp cached object in Redis */ bool PersistCachedObject = (Config.RedisConnectionSettings.CacheObjectSeconds <= 0); /* * Cache each row from the data table as a JSON serialized dictionary into the Redis Cache Server */ if (QueryDataTable != null && QueryDataTable.Rows.Count > 0) { //Define a dictionary to store the data table to be serialized into a JSON object MemoryDict = null; ErrMsg = string.Empty; /* * Convert DataTable / MySQL Query ResultSet in Dictionary<string,Dictionary<string,string>> object */ bool Success = Utils.GetQueryCacheDictionaryFromDataTable(Config.DBConnectionSettings, QueryToLoad, QueryDataTable, out MemoryDict, out ErrMsg); /* * Table Data Dictionary was successfully created - Cached each row in Memcached as a JSON dictionary */ if (Success) { foreach (KeyValuePair<string, Dictionary<string, string>> TableDictionaryKvp in MemoryDict) { string Key = TableDictionaryKvp.Key; string JsonStoreValue = JsonConvert.SerializeObject(TableDictionaryKvp.Value, new KeyValuePairConverter()); /* * Store value permanently or set an Expires Datetime */ if (PersistCachedObject) { LoadedQuery = client.Set<string>(Key, JsonStoreValue); } else { LoadedQuery = client.Set<string>(Key, JsonStoreValue, DateTime.Now.AddSeconds(Config.RedisConnectionSettings.CacheObjectSeconds)); } } } } /* * Successfully loaded data table in Redis Cache */ LoadedQuery = true; Utils.GetEventLog().WriteEntry(string.Format("[MemcachedLoaderService.Redis] Successfully loaded table [{0}] in the memory cache.", QueryToLoad.KeyPrefix)); } } catch (Exception ex) { LoadedQuery = false; ErrMsg = string.Format("[MemcachedLoaderService.Redis] Can't load query into Cache. Memcached Error Message [{0}].", ex.Message); Utils.GetEventLog().WriteEntry(ErrMsg); } return LoadedQuery; }
/// <summary> /// Retrieves a query from a Microsoft SQL Server /// </summary> /// <param name="SqlServerConfig"></param> /// <param name="MemCQuery"></param> /// <param name="UseQueryDBOverride"></param> /// <returns></returns> public static DataTable GetMSSQLServerTable(DatabaseSettings SqlServerConfig, CachedQuery MemCQuery, bool UseQueryDBOverride = false) { DataTable MyQueryTable = new DataTable(); try { string ConnString = Utils.GetMSSQLServerConnectionString(SqlServerConfig); /* * Use overriden query connection string if query specs has one */ if (UseQueryDBOverride) { ConnString = DBTypesUtils.GetDBTypeInfo(MemCQuery.DBConnString).ConnectionString; } /* * Get Data Table logic using MySQL ADO.NET provider */ using (SqlConnection dbConn = new SqlConnection(ConnString)) { dbConn.Open(); SqlCommand MySqlServerCmd = new SqlCommand(MemCQuery.Sql, dbConn); MySqlServerCmd.CommandType = CommandType.Text; MySqlServerCmd.CommandTimeout = int.MaxValue; SqlDataAdapter SqlServerAdapter = new SqlDataAdapter(MySqlServerCmd); DataSet MyCachedQueryDataSet = new DataSet(); SqlServerAdapter.Fill(MyCachedQueryDataSet); if (MyCachedQueryDataSet != null && MyCachedQueryDataSet.Tables != null && MyCachedQueryDataSet.Tables.Count > 0) { MyQueryTable = MyCachedQueryDataSet.Tables[0]; } dbConn.Close(); } } catch (Exception ex) { string ErrorMessage = string.Format("MemcachedLoaderService. Error Retrieving data from Microsoft Sql Server. Select Query is [{0}]. Error Message is [{1}].", MemCQuery.Sql, ex.Message); Utils.GetEventLog().WriteEntry(ErrorMessage); } /* * Return database table */ return MyQueryTable; }
/// <summary> /// Generic Get ADO.NET DataTable method /// </summary> /// <param name="DatabaseConfig"></param> /// <param name="MemCQuery"></param> /// <returns></returns> public static DataTable GetDataTable(DatabaseSettings DatabaseConfig, CachedQuery MemCQuery) { DataTable MyQueryTable = null; DBType DatabaseType; /* * First Determine whether to use the Main Database Connection Settings or use the Override Connection string of the Query. Override takes precedence over main */ bool UseQueryDBConnectionString = (!string.IsNullOrWhiteSpace(MemCQuery.DBConnString) && MemCQuery.DBConnString.Contains("|")); /* * Determine Database Type */ if (UseQueryDBConnectionString) { string[] DBConnArray = MemCQuery.DBConnString.Split('|'); DatabaseType = DBTypesUtils.GetDBType(DBConnArray[0]); } else { DatabaseType = DBTypesUtils.GetDBType(DatabaseConfig.DBType); } /* * Use appropriate database retrieval logic based on DBType */ switch (DatabaseType) { case DBType.MYSQL: MyQueryTable = GetMySQLTable(DatabaseConfig, MemCQuery, UseQueryDBConnectionString); break; case DBType.ORACLE: break; case DBType.POSTGRESQL: break; case DBType.SQLSERVER: MyQueryTable = GetMSSQLServerTable(DatabaseConfig, MemCQuery, UseQueryDBConnectionString); break; case DBType.UNSUPPORTED: break; default: break; } return MyQueryTable; }
public static bool LoadQueryInMemCached(MemcachedLoaderConfig Config, CachedQuery QueryToLoad, out string ErrorMessage) { bool LoadedQuery = false; ErrorMessage = string.Empty; ResponseCode response = ResponseCode.UnknownCommand; Dictionary<string, Dictionary<string, string>> MemoryDict; try { /* * Connect to memcached server */ ServerConnectionCollection MemCachedServers = new ServerConnectionCollection(); /* * Add Server from Config Settings */ MemCachedServers.Add(Config.MemcachedConnectionSettings.Server, port: Config.MemcachedConnectionSettings.Port); /* * Create the client */ IConnectionProvider provider = new ConnectionProvider(MemCachedServers); MemcachedClient client = new MemcachedClient(provider); /* * Retrieve Query Data from MySql */ DataTable QueryDataTable = GetDataTable(Config.DBConnectionSettings, QueryToLoad); /* * Determine whether to permanently persist kvp cached object in Redis */ bool PersistCachedObject = (Config.MemcachedConnectionSettings.CacheObjectSeconds <= 0); /* * Cache each row from the data table as a JSON serialized dictionary */ if (QueryDataTable != null && QueryDataTable.Rows.Count > 0) { //Define a dictionary to store the data table to be serialized into a JSON object MemoryDict = null; string ErrMsg = string.Empty; /* * Convert DataTable / MySQL Query ResultSet in Dictionary<string,Dictionary<string,string>> object */ bool Success = Utils.GetQueryCacheDictionaryFromDataTable(Config.DBConnectionSettings, QueryToLoad, QueryDataTable, out MemoryDict, out ErrMsg); /* * Table Data Dictionary was successfully created - Cached each row in Memcached as a JSON dictionary */ if (Success) { foreach (KeyValuePair<string, Dictionary<string, string>> TableDictionaryKvp in MemoryDict) { string Key = TableDictionaryKvp.Key; string JsonStoreValue = JsonConvert.SerializeObject(TableDictionaryKvp.Value, new KeyValuePairConverter()); /* * Determine right expiration Datetime value */ DateTime ExpireDate = (PersistCachedObject) ? DateTime.MaxValue : DateTime.Now.AddSeconds(Config.MemcachedConnectionSettings.CacheObjectSeconds); /* * Load Kvp in Memcached */ response = client.Set(Key, JsonStoreValue, ExpireDate); /* * If key already exists replace it */ if (response == ResponseCode.KeyExists) { response = client.Replace(Key, JsonStoreValue, ExpireDate); } } } } /* * Success */ LoadedQuery = (response == ResponseCode.NoError); Utils.GetEventLog().WriteEntry(string.Format("[MemcachedLoaderService.Memcached] Successfully loaded table [{0}] in the memory cache.", QueryToLoad.KeyPrefix)); } catch (Exception ex) { ErrorMessage = string.Format("[MemcachedLoaderService.Memcached] Can't load query into Cache. Memcached Error Message [{0}].", ex.Message); Utils.GetEventLog().WriteEntry(ErrorMessage); } /* * Results */ return LoadedQuery; }