public string CheckUserPassword(string user) { var database = new SqlDatabaseConnector(); database.Connector.Open(); string checkPasswordQuery = $"USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); CREATE TABLE IF NOT EXISTS Dogs(DogID int NOT NULL PRIMARY KEY AUTO_INCREMENT, OwnerID int, Name varchar(255), Race varchar(255), Notes varchar(255)); ALTER TABLE Dogs ADD FOREIGN KEY(OwnerID) REFERENCES Persons(PersonID); SELECT * FROM Dogs WHERE OwnerID IN(SELECT PersonID FROM Persons WHERE Nick = '{user}')"; var cmd = new MySqlCommand(checkPasswordQuery, database.Connector); MySqlDataReader rdr = cmd.ExecuteReader(); var buff = new List <object>(); while (rdr.Read()) { buff.Add(new { DogId = rdr["DogID"], OwnerId = rdr["OwnerID"], Name = rdr["Name"], Race = rdr["Race"], Notes = rdr["Notes"] }); } var returnString = JsonConvert.SerializeObject(buff); rdr.Close(); database.Connector.Close(); return(returnString); }
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); }
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(); 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(); }
public SqlConnectionsUpdateChecker() { _sqlConnector = new SqlDatabaseConnector(); _sqlQuery = new SqlCommand("SELECT * FROM tblUpdate", _sqlConnector.SqlConnection); _lastUpdateTime = default(DateTime); _lastDatabaseUpdateTime = default(DateTime); }
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(); }
public SqlDatabaseVersionVerifier(SqlDatabaseConnector sqlDatabaseConnector) { if (sqlDatabaseConnector == null) { throw new ArgumentNullException(nameof(sqlDatabaseConnector)); } _sqlDatabaseConnector = sqlDatabaseConnector; }
public SqlVersion23To24Upgrader(SqlDatabaseConnector sqlDatabaseConnector) { if (sqlDatabaseConnector == null) { throw new ArgumentNullException(nameof(sqlDatabaseConnector)); } _sqlDatabaseConnector = sqlDatabaseConnector; }
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 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); }
public bool AddUser(string user, string email, string password) { var database = new SqlDatabaseConnector(); database.Connector.Open(); string addUserQuery = $"USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); INSERT INTO Persons VALUES(null, '{user}', '{email}', '{password}')"; var data = new DataSet(); var recreate = new MySqlCommand(addUserQuery, database.Connector).ExecuteNonQuery(); database.Connector.Close(); return(true); }
public bool AddDog(int ownerId, string dogName, string dogRace, string notes) { var database = new SqlDatabaseConnector(); database.Connector.Open(); string addUserQuery = $"USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); CREATE TABLE IF NOT EXISTS Dogs(DogID int NOT NULL PRIMARY KEY AUTO_INCREMENT, OwnerID int, Name varchar(255), Race varchar(255), Notes varchar(255)); ALTER TABLE Dogs ADD FOREIGN KEY(OwnerID) REFERENCES Persons(PersonID); INSERT INTO Dogs VALUES(null, '{ownerId}','{dogName}', '{dogRace}', '{notes}')"; var data = new DataSet(); var recreate = new MySqlCommand(addUserQuery, database.Connector).ExecuteNonQuery(); database.Connector.Close(); return(true); }
public string GetResetDataBase() { var database = new SqlDatabaseConnector(); database.Connector.Open(); string resetDatabaseQuery = "CREATE DATABASE IF NOT EXISTS Swierzaki; USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); DROP TABLE IF EXISTS Dogs; CREATE TABLE Dogs(DogID int NOT NULL PRIMARY KEY AUTO_INCREMENT, OwnerID int, Name varchar(255), Race varchar(255), Notes varchar(255)); ALTER TABLE Dogs ADD FOREIGN KEY(OwnerID) REFERENCES Persons(PersonID); INSERT INTO Dogs VALUES(null, 1, 'Burek', 'Mieszaniec', 'AAA'); INSERT INTO Dogs VALUES(null, 2, 'Mialek', 'Quint', 'BBB'); "; var data = new DataSet(); var recreate = new MySqlCommand(resetDatabaseQuery, database.Connector).ExecuteNonQuery(); database.Connector.Close(); return("Zresetowano bazę danych"); }
public SqlConnectionListMetaData GetDatabaseMetaData(SqlDatabaseConnector sqlDatabaseConnector) { SqlConnectionListMetaData metaData; SqlDataReader sqlDataReader = null; try { var sqlCommand = new SqlCommand("SELECT * FROM tblRoot", sqlDatabaseConnector.SqlConnection); if (!sqlDatabaseConnector.IsConnected) { sqlDatabaseConnector.Connect(); } sqlDataReader = sqlCommand.ExecuteReader(); if (!sqlDataReader.HasRows) { return(null); // assume new empty database } else { sqlDataReader.Read(); } metaData = new SqlConnectionListMetaData { Name = sqlDataReader["Name"] as string ?? "", Protected = sqlDataReader["Protected"] as string ?? "", Export = (bool)sqlDataReader["Export"], ConfVersion = 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(metaData); }
public string GetResetDataBase() { try { var database = new SqlDatabaseConnector(); database.Connector.Open(); string resetDatabaseQuery = "CREATE DATABASE IF NOT EXISTS Swierzaki; USE Swierzaki; DROP TABLE IF EXISTS Persons; CREATE TABLE Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); INSERT INTO Persons VALUES(null, 'Muffy', '*****@*****.**', 'AAA'); INSERT INTO Persons VALUES(null, 'Admin', '*****@*****.**', 'AAA');"; var data = new DataSet(); var recreate = new MySqlCommand(resetDatabaseQuery, database.Connector).ExecuteNonQuery(); database.Connector.Close(); } catch (Exception e) { return(e.Message); } return("Zresetowano bazę danych"); }
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) { AuthenticationRequestor = 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 void WriteDatabaseMetaData(RootNodeInfo rootTreeNode, SqlDatabaseConnector sqlDatabaseConnector) { var cryptographyProvider = new LegacyRijndaelCryptographyProvider(); string strProtected; if (rootTreeNode != null) { if (rootTreeNode.Password) { var password = rootTreeNode.PasswordString.ConvertToSecureString(); strProtected = cryptographyProvider.Encrypt("ThisIsProtected", password); } else { strProtected = cryptographyProvider.Encrypt("ThisIsNotProtected", Runtime.EncryptionKey); } } else { strProtected = cryptographyProvider.Encrypt("ThisIsNotProtected", Runtime.EncryptionKey); } 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 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(); }
public bool CheckUserPassword(string user, string password) { var database = new SqlDatabaseConnector(); database.Connector.Open(); string checkPasswordQuery = $"USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); select PersonID from Persons where Nick = '{user}' and Password = '******'"; var cmd = new MySqlCommand(checkPasswordQuery, database.Connector); MySqlDataReader rdr = cmd.ExecuteReader(); string buff = null; while (rdr.Read()) { buff = rdr[rdr.GetName(0)].ToString(); } rdr.Close(); database.Connector.Close(); return(!String.IsNullOrEmpty(buff)); }
private SqlConnectionListMetaData HandleFirstRun(SqlDatabaseMetaDataRetriever metaDataRetriever, SqlDatabaseConnector connector) { metaDataRetriever.WriteDatabaseMetaData(new RootNodeInfo(RootNodeType.Connection), connector); return(metaDataRetriever.GetDatabaseMetaData(connector)); }
public SqlDataProvider(SqlDatabaseConnector sqlDatabaseConnector) { SqlDatabaseConnector = sqlDatabaseConnector; }