private void LoadDataBase()
{
MySqlConnection connection = MySQLConnector.Connect();
MySqlDataAdapter tableSource = new MySqlDataAdapter("SELECT * FROM " + currentTableName, connection);
bindingDataTable = new DataTable();
tableSource.Fill(bindingDataTable);
MySqlDataAdapter tableInformation = new MySqlDataAdapter("DESCRIBE " + currentTableName, connection);
//コンボボックスを挿入する際に以前のものを消さなければ挿入される場所がおかしくなる
WordDataGrid.Columns.Clear();
WordDataGrid.DataSource = bindingDataTable;
DataTable tableInformationTable = new DataTable();
tableInformation.Fill(tableInformationTable);
foreach (var dataRow in tableInformationTable.Rows.Cast <DataRow>())
{
CreateEnumComboBox(dataRow);
}
WordDataGrid.Columns[0].ReadOnly = true;
FetchTableAutoIncrement();
}
public static void Main(string[] args)
{
Database = new MySQLConnector();
Database.Connect();
CreateHostBuilder(args).Build().Run();
Database.Connection.Close();
}
private void Write_Click(object sender, EventArgs e)
{
for (var rowIndex = 0; rowIndex < bindingDataTable.Rows.Count; ++rowIndex)
{
for (var columnIndex = 1; columnIndex < bindingDataTable.Columns.Count; ++columnIndex)
{
bindingDataTable.Rows[rowIndex][columnIndex] = WordDataGrid.Rows[rowIndex].Cells[columnIndex].Value;
}
}
MySqlConnection connection = MySQLConnector.Connect();
foreach (var dataRow in bindingDataTable.Rows.Cast <DataRow>())
{
MySqlCommand cmd = null;
switch (dataRow.RowState)
{
case DataRowState.Unchanged:
break;
case DataRowState.Deleted:
break;
case DataRowState.Modified:
cmd = new MySqlCommand(ModifyRow(dataRow), connection);
break;
case DataRowState.Added:
cmd = new MySqlCommand(AddRow(dataRow), connection);
break;
default:
break;
}
if (cmd == null)
{
continue;
}
cmd.ExecuteNonQuery();
}
LoadDataBase();
}
/// <summary>
/// コマンドの実行
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Execution_btn_Click(object sender, EventArgs e)
{
SyncBindingSourceWithDataGridView();
MySqlConnection connection = MySQLConnector.Connect();
for (var i = 0; i < bindingDataTable.Rows.Count; ++i)
{
MySqlCommand cmd = null;
var dataRow = bindingDataTable.Rows[i];
var deleteColumnIndex = DataTable_dgv.ColumnCount - 1;
//削除CheckBoxはbindingSource
if (Convert.ToBoolean(DataTable_dgv.Rows[i].Cells[deleteColumnIndex].Value))
{
cmd = new MySqlCommand(CreateDeleteRowCmd(dataRow), connection);
cmd?.ExecuteNonQuery();
continue;
}
switch (dataRow.RowState)
{
case DataRowState.Modified:
cmd = new MySqlCommand(CreateModifyRowCmd(dataRow), connection);
break;
case DataRowState.Added:
cmd = new MySqlCommand(CreateAddRowCmd(dataRow), connection);
break;
default:
break;
}
cmd?.ExecuteNonQuery();
}
SetAdvanceDataTable();
}
/// <summary>
/// データグリッドの列挙子をコンボボックスに変更する
/// </summary>
private void OperateEnumColumn()
{
MySqlConnection connection = MySQLConnector.Connect();
MySqlDataAdapter tableInformation = new MySqlDataAdapter("DESCRIBE " + tableName, connection);
DataTable tableInformationTable = new DataTable();
tableInformation.Fill(tableInformationTable);
foreach (var dataRow in tableInformationTable.Rows.Cast <DataRow>())
{
if (!MeansEnumuration(GetTypeString(dataRow)))
{
continue;
}
ReplaceColumnWithComboBox(dataRow);
}
}
/// <summary>
/// オートインクリメントの取得
/// </summary>
private void FetchAutoIncrement()
{
MySqlConnection connection = MySQLConnector.Connect();
MySqlDataAdapter autoIncrementData = new MySqlDataAdapter(
"SELECT auto_increment FROM information_schema.TABLES WHERE table_name = " + "\'" + tableName + "\';",
connection);
DataTable autoIncrementTable = new DataTable();
autoIncrementData.Fill(autoIncrementTable);
// テーブルの要素がまだ追加されたことがなかった場合
if (autoIncrementTable.Rows[0][0].GetType() == typeof(DBNull))
{
autoIncrement = 1;
return;
}
autoIncrement = int.Parse(autoIncrementTable.Rows[0][0].ToString());
}
/// <summary>
/// データベースから取得してきたそのままのテーブルをdgvにセットする
/// </summary>
private void FetchPrimitiveDataTable()
{
MySqlDataAdapter tableSource = new MySqlDataAdapter("SELECT * FROM " + tableName, MySQLConnector.Connect());
bindingDataTable = new DataTable();
tableSource.Fill(bindingDataTable);
//コンボボックスを挿入する際に以前のものを消さなければ挿入される場所がおかしくなる
DataTable_dgv.Columns.Clear();
DataTable_dgv.DataSource = bindingDataTable;
}
