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