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;
}
