/// <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;
}
public static MemcachedLoaderConfig LoadConfiguration(string XmlDocPath)
{
MemcachedLoaderConfig config = new MemcachedLoaderConfig();
try
{
XmlDocument XmlDoc = new XmlDocument();
XmlDoc.Load(XmlDocPath);
/*
* Load cache service refresh/reload entire cache seconds internal. Service will reload all queries based on this interval
*/
XmlNode RefreshSeconds = XmlDoc.SelectSingleNode("/configuration/reload_entire_cache_seconds");
config.ReloadEntireCacheSeconds = int.Parse(RefreshSeconds.InnerText);
/*
* Enable Redis Caching Flag
*/
XmlNode EnableRedisCaching = XmlDoc.SelectSingleNode("/configuration/enable_redis_caching");
config.EnableRedisCaching = bool.Parse(EnableRedisCaching.InnerText);
/*
* Enable Memcached Caching
*/
XmlNode EnableMemcachedCaching = XmlDoc.SelectSingleNode("/configuration/enable_memcached_caching");
config.EnableMemcachedCaching = bool.Parse(EnableMemcachedCaching.InnerText);
/*
* Load memcached server connections settings
*/
MemcachedSettings MemcachedServerSettings = new MemcachedSettings();
//server
string mcServer = XmlDoc.SelectSingleNode("/configuration/memcached/server").InnerText;
MemcachedServerSettings.Server = mcServer;
//port
int mcPort = int.Parse(XmlDoc.SelectSingleNode("/configuration/memcached/port").InnerText);
MemcachedServerSettings.Port = mcPort;
//cache items pin seconds
int mcCacheItemExpireSeconds = int.Parse(XmlDoc.SelectSingleNode("/configuration/memcached/cache_object_seconds").InnerText);
MemcachedServerSettings.CacheObjectSeconds = mcCacheItemExpireSeconds;
/*
* Load Redis server connection settings
*/
RedisSettings RedisServerSettings = new RedisSettings();
//redis server host
string redisServer = XmlDoc.SelectSingleNode("/configuration/redis/server").InnerText;
RedisServerSettings.Server = redisServer;
//redis server port
int redisPort = int.Parse(XmlDoc.SelectSingleNode("/configuration/redis/port").InnerText);
RedisServerSettings.Port = redisPort;
//redis server password
string redisPassword = XmlDoc.SelectSingleNode("/configuration/redis/password").InnerText;
RedisServerSettings.Password = redisPassword;
//redis global cache object seconds setting
int redisCacheItemExpireSeconds = int.Parse(XmlDoc.SelectSingleNode("/configuration/redis/cache_object_seconds").InnerText);
RedisServerSettings.CacheObjectSeconds = redisCacheItemExpireSeconds;
/*
* Load MySQL database connection settings - for now a single server support
*/
DatabaseSettings MySqlConfig = new DatabaseSettings();
//server
string dbType = XmlDoc.SelectSingleNode("/configuration/database_settings/db_type").InnerText;
MySqlConfig.DBType = dbType;
//server
string dbServer = XmlDoc.SelectSingleNode("/configuration/database_settings/server").InnerText;
MySqlConfig.Server = dbServer;
//port
string dbPort = XmlDoc.SelectSingleNode("/configuration/database_settings/port").InnerText;
MySqlConfig.Port = dbPort;
//username
string dbUsername = XmlDoc.SelectSingleNode("/configuration/database_settings/username").InnerText;
MySqlConfig.Username = dbUsername;
//password
string dbPassword = XmlDoc.SelectSingleNode("/configuration/database_settings/password").InnerText;
MySqlConfig.Password = dbPassword;
//password
string dbName = XmlDoc.SelectSingleNode("/configuration/database_settings/database").InnerText;
MySqlConfig.Database = dbName;
/*
* Load all objects in main configuration object
*/
config.MemcachedConnectionSettings = MemcachedServerSettings;
config.RedisConnectionSettings = RedisServerSettings;
config.DBConnectionSettings = MySqlConfig;
config.CachedQueriesCollection = LoadQueriesSettings(XmlDoc.SelectNodes("/configuration/cache_queries/query"));
}
catch (Exception ex)
{
EventLog eventLog = Utils.GetEventLog();
string ErrorMessage = string.Format("MemcachedLoaderService. Error loading Service Configuration XML File. Error message was [{0}].", ex.Message);
eventLog.WriteEntry(ErrorMessage);
eventLog.Dispose();
throw new ApplicationException(ErrorMessage);
}
/*
* Returns fresh instance of configuration settings
*/
return config;
}
/// <summary>
/// Get Table Schema. Needed to build PK simple or compounded key values
/// </summary>
/// <param name="DatabaseConfig"></param>
/// <param name="TableName"></param>
/// <returns></returns>
private static DataTable GetSchemaTypeDatabaseSQLTable(DatabaseSettings DatabaseConfig, string TableName, string OverrideDBConnectionStr = "")
{
DataTable ReturnTable = null;
DBType DatabaseType;
string ConnString = Utils.GetMySQLConnectionString(DatabaseConfig);
bool UseOverrideDBConnection = (!string.IsNullOrWhiteSpace(OverrideDBConnectionStr) && OverrideDBConnectionStr.Contains('|'));
if (UseOverrideDBConnection)
{
DatabaseType = DBTypesUtils.GetDBTypeInfo(OverrideDBConnectionStr).DatabaseType;
ConnString = DBTypesUtils.GetDBTypeInfo(OverrideDBConnectionStr).ConnectionString;
}
else
{
DatabaseType = DBTypesUtils.GetDBType(DatabaseConfig.DBType);
}
switch (DatabaseType)
{
case DBType.MYSQL:
ReturnTable = GetMySQLSchemaTypeTable(ConnString, TableName);
break;
case DBType.ORACLE:
break;
case DBType.POSTGRESQL:
break;
case DBType.SQLSERVER:
ReturnTable = GetMSSQLServerSchemaTypeTable(ConnString, TableName);
break;
case DBType.UNSUPPORTED:
break;
default:
break;
}
return ReturnTable;
}
/// <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;
}
/// <summary>
/// Formats a SQL Server Connection String
/// </summary>
/// <param name="SqlServerConfig"></param>
/// <returns></returns>
public static string GetMSSQLServerConnectionString(DatabaseSettings SqlServerConfig)
{
if (SqlServerConfig == null)
throw new ApplicationException("Invalid Microsoft Sql Server Configuration Settings Object Instance. Cannot build a connection string.");
string FormattedPort = (!string.IsNullOrWhiteSpace(SqlServerConfig.Port)) ? ("," + SqlServerConfig.Port) : string.Empty;
return string.Format("Address={0}{1};Database={2};User ID={3};Pwd={4};", SqlServerConfig.Server, FormattedPort, SqlServerConfig.Database, SqlServerConfig.Username, SqlServerConfig.Password);
}
/// <summary>
/// Formats a MySQL Connection String
/// </summary>
/// <param name="MySQLConfig"></param>
/// <returns></returns>
public static string GetMySQLConnectionString(DatabaseSettings MySQLConfig)
{
if (MySQLConfig == null)
throw new ApplicationException("Invalid MySQL Configuration Settings Object Instance. Cannot build a connection string.");
return string.Format("Server={0};Port={1};Database={2};Uid={3};Pwd={4};", MySQLConfig.Server, MySQLConfig.Port, MySQLConfig.Database, MySQLConfig.Username, MySQLConfig.Password);
}
