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 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.");
}
}
