/// <summary> /// Get show connection string in specific environment. /// </summary> /// <param name="showCode"></param> /// <param name="environment"></param> /// <param name="appTypeCode"></param> /// <returns></returns> public virtual string GetConnectionString(string showCode, ServerTypeCode environment, AppTypeCode appTypeCode) { // flush environment keys CMSecurity.Flush(); // find environment master DataRow[] server = ConnectionManagerBase.ServerTable.Select("servertypecode = '" + environment.ToString() + "' and IsEnvironmentMaster = 1"); if (server.Length == 0) { throw new Exception("Failed to retrieve environment server information."); } string entServerName = server[0]["ServerName"].ToString(); // for caching purpose string cachedConnectionStringKey = string.Concat(entServerName, showCode, appTypeCode.ToString()).ToLower(); if (cachedConnectionStrings.ContainsKey(cachedConnectionStringKey)) { return(cachedConnectionStrings[cachedConnectionStringKey].ToString()); } return(this.GetConnectionString( cachedConnectionStringKey, ConnectionManagerBase.GetGenericConnectionString(entServerName, this.EntDatabaseName), showCode, appTypeCode.ToString())); }
/// <summary> /// Pass in temporary enterprise connection string instead of retrieving from config file. /// Might be used to retrieve show connection string from other server. /// </summary> /// <param name="entConnectionString"></param> public ConnectionManagerBase(string entConnectionString) { CMSecurity.Flush(); // need to validate if this is an enterprise connection string string enterpriseDatabaseName = "Enterprise"; // VS2005 if (System.Configuration.ConfigurationManager.AppSettings[DefaultEntDatabaseNameAppSettingsKey] != null) { // VS2005 enterpriseDatabaseName = System.Configuration.ConfigurationManager.AppSettings[DefaultEntDatabaseNameAppSettingsKey]; } System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@"database\s*=\s*" + enterpriseDatabaseName, System.Text.RegularExpressions.RegexOptions.IgnoreCase); if (reg.IsMatch(entConnectionString)) { this.passedEntConnectionString = entConnectionString; } else { string newEntConnectionString = ""; string[] part = entConnectionString.Split(';'); for (int i = 0; i < part.Length; i++) { if (part[i].Length > 0) { if (part[i].ToLower().Trim().IndexOf("database") == 0) { part[i] = "database=" + enterpriseDatabaseName; } newEntConnectionString += part[i]; if (i != part.Length - 1) { newEntConnectionString += ";"; } } } this.passedEntConnectionString = newEntConnectionString; } }
private string GetConnectionString(string cachedConnectionStringKey, string enterpriseConnectionString, string showCode, string appTypeCode) { // trip to enterprise database SqlConnection connection = new SqlConnection(enterpriseConnectionString); SqlCommand command = new SqlCommand("dbo.spShowConnectionSelectByShowCode", connection); command.CommandType = CommandType.StoredProcedure; // VS2005 command.Parameters.AddWithValue("@ShowCode", showCode); SqlDataReader reader = null; try { connection.Open(); reader = command.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { if (connection.State == ConnectionState.Open) { connection.Close(); } throw new Exception("Failed open connection. " + ex.Message); } string defaultConnectionString = ""; string connectionString = ""; try { while (reader.Read()) { // JDM: check if encrypted password column exists in results bool hasEPass = false; for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetName(i).ToLower() == "epassword") { hasEPass = true; break; } } if (reader.GetString(reader.GetOrdinal("AppTypeCode")).ToLower() == appTypeCode.ToLower()) { if (reader.GetString(reader.GetOrdinal("ConnectionType")).ToLower() == "sqlauth") { if (hasEPass && !reader.IsDBNull(reader.GetOrdinal("EPassword"))) { string toDecrypt = reader.GetString(reader.GetOrdinal("EPassword")); toDecrypt = CMSecurity.DeRectifier(toDecrypt, enterpriseConnectionString); connectionString = GetGenericConnectionString( reader.GetString(reader.GetOrdinal("ServerName")), reader.GetString(reader.GetOrdinal("DatabaseName")), reader.GetString(reader.GetOrdinal("UserName")), toDecrypt); } else { connectionString = GetGenericConnectionString( reader.GetString(reader.GetOrdinal("ServerName")), reader.GetString(reader.GetOrdinal("DatabaseName")), reader.GetString(reader.GetOrdinal("UserName")), reader.GetString(reader.GetOrdinal("UserPassword"))); } break; } else { connectionString = GetWinAuthConnectionString( reader.GetString(reader.GetOrdinal("ServerName")), reader.GetString(reader.GetOrdinal("DatabaseName"))); break; } } else if (reader.GetString(reader.GetOrdinal("AppTypeCode")).ToLower() == this.DefaultAppTypeCode.ToLower()) { if (reader.GetString(reader.GetOrdinal("ConnectionType")).ToLower() == "sqlauth") { if (hasEPass && !reader.IsDBNull(reader.GetOrdinal("EPassword"))) { string toDecrypt = reader.GetString(reader.GetOrdinal("EPassword")); toDecrypt = CMSecurity.DeRectifier(toDecrypt, enterpriseConnectionString); defaultConnectionString = GetGenericConnectionString( reader.GetString(reader.GetOrdinal("ServerName")), reader.GetString(reader.GetOrdinal("DatabaseName")), reader.GetString(reader.GetOrdinal("UserName")), toDecrypt); } else { defaultConnectionString = GetGenericConnectionString( reader.GetString(reader.GetOrdinal("ServerName")), reader.GetString(reader.GetOrdinal("DatabaseName")), reader.GetString(reader.GetOrdinal("UserName")), reader.GetString(reader.GetOrdinal("UserPassword"))); } } else { defaultConnectionString = GetWinAuthConnectionString( reader.GetString(reader.GetOrdinal("ServerName")), reader.GetString(reader.GetOrdinal("DatabaseName"))); } } } } catch (Exception ex) { throw new Exception("Failed read information from database. " + ex.Message); } finally { reader.Close(); connection.Close(); } if (connectionString.Length == 0 && defaultConnectionString.Length == 0) { throw new Exception(string.Format("Unable to get default or '{0}' connection information for {1}. There is no entry in ShowConnection table.", appTypeCode, showCode)); } // return AppTypeCode connection string if available if (connectionString.Length > 0) { // add/update cached connection strings collection lock (cachedConnectionStrings.SyncRoot) { if (cachedConnectionStrings.ContainsKey(cachedConnectionStringKey)) { cachedConnectionStrings[cachedConnectionStringKey] = connectionString; } else { cachedConnectionStrings.Add(cachedConnectionStringKey, connectionString); } } return(connectionString); } else { // add/update cached connection strings collection lock (cachedConnectionStrings.SyncRoot) { if (cachedConnectionStrings.ContainsKey(cachedConnectionStringKey)) { cachedConnectionStrings[cachedConnectionStringKey] = defaultConnectionString; } else { cachedConnectionStrings.Add(cachedConnectionStringKey, defaultConnectionString); } } return(defaultConnectionString); } }