Пример #1
0
        /// <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;
        }
Пример #3
0
        /// <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;
        }
Пример #4
0
        /// <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;
        }
Пример #5
0
        /// <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;
        }
Пример #6
0
        /// <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);
        }
Пример #7
0
        /// <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);
        }