private void CheckTable(string tableName, string[] columnNames, TextBoxInfoDialog infoDlg, MySqlConnection conn) { // Check for existence of the table bool tableExists = this.TableExists(tableName, conn); if (tableExists) { m_logger.DebugFormat("Table \"{0}\" found.", tableName); infoDlg.AppendLine(string.Format("Table \"{0}\" found.", tableName)); } else { m_logger.DebugFormat("Table {0} not found.", tableName); infoDlg.AppendLine(string.Format("ERROR: Table \"{0}\" not found.", tableName)); return; } if (tableExists) { // Get column names from DB List <string> columnNamesFromDB = new List <string>(); string query = string.Format("DESCRIBE {0}", tableName); MySqlCommand cmd = new MySqlCommand(query, conn); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { string colName = rdr[0].ToString(); columnNamesFromDB.Add(colName); } rdr.Close(); // Check for appropriate columns. bool ok = true; foreach (string c in columnNames) { if (columnNamesFromDB.Contains(c, StringComparer.CurrentCultureIgnoreCase)) { infoDlg.AppendLine(string.Format("Found column \"{0}\"", c)); } else { ok = false; infoDlg.AppendLine(string.Format("ERROR: Column \"{0}\" not found!", c)); } } if (!ok) { infoDlg.AppendLine(string.Format("ERROR: Table \"{0}\" schema looks incorrect.", tableName)); } } }
private void createTableBtn_Click(object sender, EventArgs e) { string connStr = this.BuildConnectionString(); if (connStr == null) return; TextBoxInfoDialog infoDlg = new TextBoxInfoDialog(); infoDlg.ClearText(); infoDlg.Show(); try { using (MySqlConnection conn = new MySqlConnection(connStr)) { infoDlg.AppendLine("Connecting..."); conn.Open(); // User table string tableName = this.userTableTB.Text.Trim(); infoDlg.AppendLine(Environment.NewLine + string.Format("Creating table \"{0}\"", tableName)); if (!this.TableExists(tableName, conn)) { // Column names string pk = this.userPrimaryKeyColTB.Text.Trim(); string unameCol = this.unameColTB.Text.Trim(); string hashMethodCol = this.hashMethodColTB.Text.Trim(); string passwdCol = this.passwdColTB.Text.Trim(); // Is the primary key the same as the username? bool pkIsUserName = unameCol.Equals(pk, StringComparison.CurrentCultureIgnoreCase); StringBuilder sql = new StringBuilder(); sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName); if (!pkIsUserName) sql.AppendFormat(" {0} BIGINT auto_increment PRIMARY KEY, \r\n", pk); sql.AppendFormat(" {0} VARCHAR(128) {1}, \r\n", unameCol, pkIsUserName ? "PRIMARY KEY" : "NOT NULL UNIQUE"); sql.AppendFormat(" {0} TEXT NOT NULL, \r\n", hashMethodCol); sql.AppendFormat(" {0} TEXT \r\n", passwdCol); sql.Append(")"); // End create table. infoDlg.AppendLine("Executing SQL:"); infoDlg.AppendLine(sql.ToString()); using (MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn)) { cmd.ExecuteNonQuery(); infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName)); } } else { infoDlg.AppendLine( string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName)); } // Group table tableName = this.groupTableNameTB.Text.Trim(); infoDlg.AppendLine(Environment.NewLine + string.Format("Creating table \"{0}\"", tableName)); if (!this.TableExists(tableName, conn)) { // Column names string pk = this.groupTablePrimaryKeyColTB.Text.Trim(); string groupNameCol = this.groupNameColTB.Text.Trim(); // Is the primary key the same as the group name? bool pkIsGroupName = groupNameCol.Equals(pk, StringComparison.CurrentCultureIgnoreCase); StringBuilder sql = new StringBuilder(); sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName); if (!pkIsGroupName) sql.AppendFormat(" {0} BIGINT AUTO_INCREMENT PRIMARY KEY, \r\n", pk); sql.AppendFormat(" {0} VARCHAR(128) {1} \r\n", groupNameCol, pkIsGroupName ? "PRIMARY KEY" : "NOT NULL UNIQUE"); sql.Append(")"); // End create table. infoDlg.AppendLine("Executing SQL:"); infoDlg.AppendLine(sql.ToString()); using (MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn)) { cmd.ExecuteNonQuery(); infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName)); } } else { infoDlg.AppendLine( string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName)); } // user-Group table tableName = this.userGroupTableNameTB.Text.Trim(); infoDlg.AppendLine(Environment.NewLine + string.Format("Creating table \"{0}\"", tableName)); if (!this.TableExists(tableName, conn)) { // Column names string userFK = this.userGroupUserFKColTB.Text.Trim(); string userPK = this.userPrimaryKeyColTB.Text.Trim(); string groupFK = this.userGroupGroupFKColTB.Text.Trim(); string groupPK = this.groupTablePrimaryKeyColTB.Text.Trim(); string groupNameCol = this.groupNameColTB.Text.Trim(); string unameCol = this.unameColTB.Text.Trim(); // Is the primary key the same as the group name? bool pkIsGroupName = groupNameCol.Equals(groupPK, StringComparison.CurrentCultureIgnoreCase); bool pkIsUserName = unameCol.Equals(userPK, StringComparison.CurrentCultureIgnoreCase); StringBuilder sql = new StringBuilder(); sql.AppendFormat("CREATE TABLE {0} ( \r\n", tableName); sql.AppendFormat(" {0} {1}, \r\n", groupFK, pkIsGroupName ? "VARCHAR(128)" : "BIGINT"); sql.AppendFormat(" {0} {1}, \r\n", userFK, pkIsUserName ? "VARCHAR(128)" : "BIGINT"); sql.AppendFormat(" PRIMARY KEY ({0}, {1}) \r\n", userFK, groupFK); sql.Append(")"); // End create table. infoDlg.AppendLine("Executing SQL:"); infoDlg.AppendLine(sql.ToString()); MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn); cmd.ExecuteNonQuery(); infoDlg.AppendLine(string.Format("Table \"{0}\" created.", tableName)); } else { infoDlg.AppendLine( string.Format("WARNING: Table \"{0}\"already exists, skipping.", tableName)); } } } catch (MySqlException ex) { infoDlg.AppendLine(String.Format("ERROR: {0}", ex.Message)); } finally { infoDlg.AppendLine(Environment.NewLine + "Finished."); } }
private void testBtn_Click(object sender, EventArgs e) { TextBoxInfoDialog infoDlg = new TextBoxInfoDialog(); infoDlg.Show(); infoDlg.AppendLine("Beginning test of MySQL database..." + Environment.NewLine); MySqlConnection conn = null; string tableName = this.userTableTB.Text.Trim(); try { string connStr = this.BuildConnectionString(); if (connStr == null) return; infoDlg.AppendLine("Connection Status"); infoDlg.AppendLine("-------------------------------------"); conn = new MySqlConnection(connStr); conn.Open(); infoDlg.AppendLine(string.Format("Connection to {0} successful.", this.hostTB.Text.Trim())); // Variables to be used repeatedly below MySqlCommand cmd = null; MySqlDataReader rdr = null; string query = ""; // Check SSL status if (useSslCB.Checked) { string cipher = ""; query = "SHOW STATUS LIKE 'Ssl_cipher'"; cmd = new MySqlCommand(query, conn); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); cipher = rdr[1].ToString(); } rdr.Close(); if (string.IsNullOrEmpty(cipher)) { infoDlg.AppendLine( "Not using SSL." ); } else { infoDlg.AppendLine("SSL enabled, using cipher: " + cipher); } } else { infoDlg.AppendLine( "Not using SSL." ); } infoDlg.AppendLine( Environment.NewLine + "User Table" ); infoDlg.AppendLine( "-------------------------------"); CheckTable(tableName, new string[] { this.unameColTB.Text.Trim(), this.passwdColTB.Text.Trim(), this.hashMethodColTB.Text.Trim(), this.userPrimaryKeyColTB.Text.Trim() }, infoDlg, conn); infoDlg.AppendLine(Environment.NewLine + "Group Table"); infoDlg.AppendLine("-------------------------------"); CheckTable(this.groupTableNameTB.Text.Trim(), new string[] { this.groupNameColTB.Text.Trim(), this.groupTablePrimaryKeyColTB.Text.Trim() }, infoDlg, conn); infoDlg.AppendLine(Environment.NewLine + "User-Group Table"); infoDlg.AppendLine("-------------------------------"); CheckTable(this.userGroupTableNameTB.Text.Trim(), new string[] { this.userGroupUserFKColTB.Text.Trim(), this.userGroupGroupFKColTB.Text.Trim() }, infoDlg, conn); } catch (Exception ex) { if (ex is MySqlException) { MySqlException mysqlEx = ex as MySqlException; infoDlg.AppendLine("MySQL ERROR: " + mysqlEx.Message); } else { infoDlg.AppendLine(string.Format("ERROR: A fatal error occured: {0}", ex)); } } finally { infoDlg.AppendLine(Environment.NewLine + "Closing connection."); if (conn != null) conn.Close(); infoDlg.AppendLine("Test complete."); } }
private void CheckTable(string tableName, string[] columnNames, TextBoxInfoDialog infoDlg, MySqlConnection conn) { // Check for existence of the table bool tableExists = this.TableExists(tableName, conn); if (tableExists) { m_logger.DebugFormat("Table \"{0}\" found.", tableName); infoDlg.AppendLine(string.Format("Table \"{0}\" found.", tableName)); } else { m_logger.DebugFormat("Table {0} not found.", tableName); infoDlg.AppendLine(string.Format("ERROR: Table \"{0}\" not found.", tableName)); return; } if (tableExists) { // Get column names from DB List<string> columnNamesFromDB = new List<string>(); string query = string.Format("DESCRIBE {0}", tableName); MySqlCommand cmd = new MySqlCommand(query, conn); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { string colName = rdr[0].ToString(); columnNamesFromDB.Add(colName); } rdr.Close(); // Check for appropriate columns. bool ok = true; foreach (string c in columnNames) { if (columnNamesFromDB.Contains(c, StringComparer.CurrentCultureIgnoreCase)) infoDlg.AppendLine(string.Format("Found column \"{0}\"", c)); else { ok = false; infoDlg.AppendLine(string.Format("ERROR: Column \"{0}\" not found!", c)); } } if (!ok) infoDlg.AppendLine(string.Format("ERROR: Table \"{0}\" schema looks incorrect.", tableName)); } }