public SqlConnectionsUpdateChecker()
{
_sqlConnector = new SqlDatabaseConnector();
_sqlQuery = new SqlCommand("SELECT * FROM tblUpdate", _sqlConnector.SqlConnection);
_lastUpdateTime = default(DateTime);
_lastDatabaseUpdateTime = default(DateTime);
}
public ConnectionTreeModel LoadConnections(bool import)
{
IDeserializer deserializer;
if (UseDatabase)
{
var connector = new SqlDatabaseConnector();
var dataProvider = new SqlDataProvider(connector);
var dataTable = dataProvider.Load();
deserializer = new DataTableDeserializer(dataTable);
}
else
{
var dataProvider = new FileDataProvider(ConnectionFileName);
var xmlString = dataProvider.Load();
deserializer = new XmlConnectionsDeserializer(xmlString, PromptForPassword);
}
var connectionTreeModel = deserializer.Deserialize();
if (connectionTreeModel != null)
frmMain.Default.ConnectionsFileName = ConnectionFileName;
else
connectionTreeModel = new ConnectionTreeModel();
if (import) return connectionTreeModel;
PuttySessionsManager.Instance.AddSessions();
connectionTreeModel.RootNodes.AddRange(PuttySessionsManager.Instance.RootPuttySessionsNodes);
return connectionTreeModel;
}
public async Task <ConnectionTestResult> TestConnectivity(string server, string database, string username, string password)
{
using (var sqlConnector = new SqlDatabaseConnector(server, database, username, password))
{
try
{
await sqlConnector.ConnectAsync();
return(ConnectionTestResult.ConnectionSucceded);
}
catch (SqlException sqlException)
{
if (sqlException.Message.Contains("The server was not found"))
{
return(ConnectionTestResult.ServerNotAccessible);
}
if (sqlException.Message.Contains("Cannot open database"))
{
return(ConnectionTestResult.UnknownDatabase);
}
if (sqlException.Message.Contains("Login failed for user"))
{
return(ConnectionTestResult.CredentialsRejected);
}
return(ConnectionTestResult.UnknownError);
}
catch (Exception)
{
return(ConnectionTestResult.UnknownError);
}
}
}
private bool VerifyDatabaseVersion(SqlDatabaseConnector sqlDatabaseConnector)
{
var isVerified = false;
try
{
var databaseVersion = GetDatabaseVersion(sqlDatabaseConnector);
SqlCommand sqlCommand;
if (databaseVersion.Equals(new Version()))
{
return true;
}
if (databaseVersion.CompareTo(new Version(2, 2)) == 0) // 2.2
{
Runtime.MessageCollector.AddMessage(MessageClass.InformationMsg, $"Upgrading database from version {databaseVersion} to version 2.3.");
sqlCommand = new SqlCommand("ALTER TABLE tblCons ADD EnableFontSmoothing bit NOT NULL DEFAULT 0, EnableDesktopComposition bit NOT NULL DEFAULT 0, InheritEnableFontSmoothing bit NOT NULL DEFAULT 0, InheritEnableDesktopComposition bit NOT NULL DEFAULT 0;", sqlDatabaseConnector.SqlConnection);
sqlCommand.ExecuteNonQuery();
databaseVersion = new Version(2, 3);
}
if (databaseVersion.CompareTo(new Version(2, 3)) == 0) // 2.3
{
Runtime.MessageCollector.AddMessage(MessageClass.InformationMsg, $"Upgrading database from version {databaseVersion} to version 2.4.");
sqlCommand = new SqlCommand("ALTER TABLE tblCons ADD UseCredSsp bit NOT NULL DEFAULT 1, InheritUseCredSsp bit NOT NULL DEFAULT 0;", sqlDatabaseConnector.SqlConnection);
sqlCommand.ExecuteNonQuery();
databaseVersion = new Version(2, 4);
}
if (databaseVersion.CompareTo(new Version(2, 4)) == 0) // 2.4
{
Runtime.MessageCollector.AddMessage(MessageClass.InformationMsg, $"Upgrading database from version {databaseVersion} to version 2.5.");
sqlCommand = new SqlCommand("ALTER TABLE tblCons ADD LoadBalanceInfo varchar (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, AutomaticResize bit NOT NULL DEFAULT 1, InheritLoadBalanceInfo bit NOT NULL DEFAULT 0, InheritAutomaticResize bit NOT NULL DEFAULT 0;", sqlDatabaseConnector.SqlConnection);
sqlCommand.ExecuteNonQuery();
databaseVersion = new Version(2, 5);
}
if (databaseVersion.CompareTo(new Version(2, 5)) == 0) // 2.5
isVerified = true;
if (isVerified == false)
Runtime.MessageCollector.AddMessage(MessageClass.WarningMsg, string.Format(Language.strErrorBadDatabaseVersion, databaseVersion, GeneralAppInfo.ProductName));
}
catch (Exception ex)
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, string.Format(Language.strErrorVerifyDatabaseVersionFailed, ex.Message));
}
return isVerified;
}
private void UpdateUpdatesTable(SqlDatabaseConnector sqlDatabaseConnector)
{
var sqlQuery = new SqlCommand("DELETE FROM tblUpdate", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
sqlQuery = new SqlCommand("INSERT INTO tblUpdate (LastUpdate) VALUES(\'" + MiscTools.DBDate(DateTime.Now) + "\')", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
}
private void UpdateRootNodeTable(RootNodeInfo rootTreeNode, SqlDatabaseConnector sqlDatabaseConnector)
{
var cryptographyProvider = new LegacyRijndaelCryptographyProvider();
string strProtected;
if (rootTreeNode != null)
{
if (rootTreeNode.Password)
{
_password = Convert.ToString(rootTreeNode.PasswordString).ConvertToSecureString();
strProtected = cryptographyProvider.Encrypt("ThisIsProtected", _password);
}
else
{
strProtected = cryptographyProvider.Encrypt("ThisIsNotProtected", _password);
}
}
else
{
strProtected = cryptographyProvider.Encrypt("ThisIsNotProtected", _password);
}
var sqlQuery = new SqlCommand("DELETE FROM tblRoot", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
if (rootTreeNode != null)
{
sqlQuery =
new SqlCommand(
"INSERT INTO tblRoot (Name, Export, Protected, ConfVersion) VALUES(\'" +
MiscTools.PrepareValueForDB(rootTreeNode.Name) + "\', 0, \'" + strProtected + "\'," +
ConnectionsFileInfo.ConnectionFileVersion.ToString(CultureInfo.InvariantCulture) + ")",
sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
}
else
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, $"UpdateRootNodeTable: rootTreeNode was null. Could not insert!");
}
}
private void UpdateConnectionsTable(ContainerInfo rootTreeNode, SqlDatabaseConnector sqlDatabaseConnector)
{
var sqlQuery = new SqlCommand("DELETE FROM tblCons", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
var serializer = new DataTableSerializer(SaveFilter);
var dataTable = serializer.Serialize(rootTreeNode);
var dataProvider = new SqlDataProvider(sqlDatabaseConnector);
dataProvider.Save(dataTable);
}
private void SaveToSql()
{
var sqlConnector = new SqlDatabaseConnector();
sqlConnector.Connect();
if (!VerifyDatabaseVersion(sqlConnector))
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, Language.strErrorConnectionListSaveFailed);
return ;
}
var rootTreeNode = Runtime.ConnectionTreeModel.RootNodes.OfType<RootNodeInfo>().First();
UpdateRootNodeTable(rootTreeNode, sqlConnector);
UpdateConnectionsTable(rootTreeNode, sqlConnector);
UpdateUpdatesTable(sqlConnector);
sqlConnector.Disconnect();
sqlConnector.Dispose();
}
private Version GetDatabaseVersion(SqlDatabaseConnector sqlDatabaseConnector)
{
Version databaseVersion;
SqlDataReader sqlDataReader = null;
try
{
var sqlCommand = new SqlCommand("SELECT * FROM tblRoot", sqlDatabaseConnector.SqlConnection);
sqlDataReader = sqlCommand.ExecuteReader();
if (!sqlDataReader.HasRows)
return new Version(); // assume new empty database
else
sqlDataReader.Read();
databaseVersion = new Version(Convert.ToString(sqlDataReader["confVersion"], CultureInfo.InvariantCulture));
}
catch (Exception ex)
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, $"Retrieving database version failed. {ex}");
throw;
}
finally
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
sqlDataReader.Close();
}
return databaseVersion;
}