Skip to content

Instantly share code, notes, and snippets.

@dgwaldo
Last active August 29, 2015 14:08
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dgwaldo/4caded5cc4da690be1dd to your computer and use it in GitHub Desktop.
Save dgwaldo/4caded5cc4da690be1dd to your computer and use it in GitHub Desktop.
Changing SQL Connection String
public class ConfigurationManagerWrapper : IConfigurationManager
{
public NameValueCollection AppSettings
{
get
{
return ConfigurationManager.AppSettings;
}
}
public string ConnectionStrings(string name)
{
return ConfigurationManager.ConnectionStrings[name].ConnectionString;
}
public T GetSection<T>(string sectionName)
{
return (T)ConfigurationManager.GetSection(sectionName);
}
public void ModifyConnectionString(string connectionName, string newConnectionString)
{
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.ConnectionStrings.ConnectionStrings[connectionName].ConnectionString = newConnectionString;
config.Save();
ConfigurationManager.RefreshSection("connectionStrings");
}
}
/// <summary>
/// Allows the connection string to be set in the app.config by the user.
/// It will be overwritten by the click once installer when updating, and will need to be set again.
/// </summary>
public class DbSwitcherWindowViewModel : BindableBase
{
private readonly IMessenger _messenger;
private readonly ISqlConnectionsWrapper _sqlConnectionsWrapper;
private readonly IConfigurationManager _configurationManager;
private const string ConfigConnectionName = "DefaultConnection";
private string _existingConnectionString;
private string _newConnectionString;
private string _message;
public DbSwitcherWindowViewModel(IMessenger messenger, ISqlConnectionsWrapper sqlConnectionsWrapper, IConfigurationManager configurationManager)
{
_messenger = messenger;
_sqlConnectionsWrapper = sqlConnectionsWrapper;
_configurationManager = configurationManager;
ExistingConnectionString = _configurationManager.ConnectionStrings(ConfigConnectionName);
NewConnectionString = ExistingConnectionString;
SetConnectionStringCmd = new RelayCommand<string>(UpdateConnectionString);
TestConnectionCmd = new RelayCommand(TestSqlConnection);
}
public string ExistingConnectionString
{
get { return _existingConnectionString; }
set { SetProperty(ref _existingConnectionString, value); }
}
public string NewConnectionString
{
get { return _newConnectionString; }
set
{
SetProperty(ref _newConnectionString, value);
ValidateSqlConnectionString();
}
}
public string Message
{
get { return _message; }
set { SetProperty(ref _message, value); }
}
public RelayCommand<string> SetConnectionStringCmd { get; set; }
public RelayCommand TestConnectionCmd { get; set; }
/// <summary>
/// Checks to see if connection string can open a Db connection.
/// </summary>
private void TestSqlConnection()
{
try
{
Message = _sqlConnectionsWrapper.TestConnection(NewConnectionString);
}
catch (Exception ex)
{
Message = ex.Message;
}
}
/// <summary>
/// Provides a little error checking to see if the format of the connection string is OK...
/// </summary>
private void ValidateSqlConnectionString()
{
try
{
Message = String.Empty;
_sqlConnectionsWrapper.ValidateConnectionString(NewConnectionString);
}
catch (Exception ex)
{
Message = ex.Message;
}
}
private void UpdateConnectionString(string newConnectionString)
{
if (ExistingConnectionString == NewConnectionString) return;
_configurationManager.ModifyConnectionString(ConfigConnectionName, newConnectionString);
_messenger.Send("Database connection updated, please restart application.");
}
}
public class SqlConnectionsWrapper : ISqlConnectionsWrapper
{
/// <summary>
/// Wraps SqlConnection class to open a SQL Connection.
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
public string TestConnection(string connectionString)
{
using (var testConn = new SqlConnection(connectionString))
{
testConn.Open();
return "Connection succeeded.";
}
}
/// <summary>
/// Wraps SqlConnectionStringBuilderClass class to check a connection string for errors.
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
public void ValidateConnectionString(string connectionString)
{
// ReSharper disable once ObjectCreationAsStatement
new SqlConnectionStringBuilder { ConnectionString = connectionString };
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment