private void updateButton_Click(object sender, EventArgs e)
{
Database db = new Database();
DynamicInputParamBox inputFields;
db.OpenConnection();
try
{
OleDbCommand command = new OleDbCommand();
DataRow defaultRow = ((DataRowView)dataGridView1.SelectedRows[0].DataBoundItem).Row;
inputFields = new DynamicInputParamBox("UPDATE", currentTableName.Text, (DataTable)dataGridView1.DataSource,
primaryKeys[currentTableName.Text], defaultRow);
if (inputFields.ShowDialog() != DialogResult.OK)
{
return;
}
String setPart = "", wherePart = "";
String updateString = $"UPDATE [{currentTableName.Text}]";
List <String> inputs = inputFields.getFieldsData();
int i = 0; // для присваивания параметров внутри команды.
int j = 0; // для хождения по коллекции возвращённых результатов.
bool setBeginFlag = true, whereBeginFlag = true; // флаги, обозначающие начала конструкций частей SET и WHERE.
foreach (DataColumn column in ((DataTable)(dataGridView1.DataSource)).Columns)
{
if (primaryKeys[currentTableName.Text].Contains(column.ColumnName))
{
continue;
}
String argument = inputs[j++];
if (!setBeginFlag)
{
setPart += ", ";
}
else
{
setBeginFlag = false;
}
setPart += $"[{column.ColumnName}]=@{i}";
if (column.DataType == typeof(int))
{
int result = int.Parse(argument);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(double))
{
double result = double.Parse(argument);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(DateTime))
{
DateTime result = DateTime.Parse(argument);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(short))
{
short result = short.Parse(argument);
command.Parameters.AddWithValue($"@{i}", result);
}
else
{
command.Parameters.AddWithValue($"@{i}", argument);
}
i++;
}
j = 0;
foreach (DataColumn column in ((DataTable)(dataGridView1.DataSource)).Columns)
{
if (!primaryKeys[currentTableName.Text].Contains(column.ColumnName))
{
continue;
}
String argument = inputs[j++];
if (!whereBeginFlag)
{
wherePart += "AND ";
}
else
{
whereBeginFlag = false;
}
wherePart += $"[{column.ColumnName}]=@{i} ";
if (column.DataType == typeof(int))
{
int result = int.Parse(argument);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(double))
{
double result = double.Parse(argument);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(DateTime))
{
DateTime result = DateTime.Parse(argument);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(short))
{
short result = short.Parse(argument);
command.Parameters.AddWithValue($"@{i}", result);
}
else
{
command.Parameters.AddWithValue($"@{i}", argument);
}
i++;
}
if (setPart.Length == 0 || wherePart.Length == 0)
{
MessageBox.Show("You can't update this element. Delete this and create a new element.",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Warning,
MessageBoxDefaultButton.Button1,
MessageBoxOptions.DefaultDesktopOnly);
return;
}
updateString += " SET " + setPart + " WHERE " + wherePart;
command.Connection = db.GetConnection();
command.CommandText = updateString;
if (command.ExecuteNonQuery() == 1)
{
UpdateTable(db, currentTableName.Text);
}
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message, "Warning",
MessageBoxButtons.OK,
MessageBoxIcon.Warning,
MessageBoxDefaultButton.Button1,
MessageBoxOptions.DefaultDesktopOnly);
}
finally
{
db.CloseConnection();
}
}
private void insertButton_Click(object sender, EventArgs e)
{
Database db = new Database();
DynamicInputParamBox inputFields;
int i;
db.OpenConnection();
try
{
inputFields = new DynamicInputParamBox("ADD", currentTableName.Text, (DataTable)dataGridView1.DataSource);
if (inputFields.ShowDialog() != DialogResult.OK)
{
return;
}
String insertString = $"INSERT INTO [{currentTableName.Text}] VALUES(";
bool isFirstParam = true;
i = 0;
foreach (DataColumn column in ((DataTable)dataGridView1.DataSource).Columns)
{
if (!isFirstParam)
{
insertString += ", ";
}
else
{
isFirstParam = false;
}
insertString += $"@{i}";
i++;
}
insertString += ")";
OleDbCommand command = new OleDbCommand(insertString, db.GetConnection());
List <String> inputs = inputFields.getFieldsData();
i = 0;
foreach (DataColumn column in ((DataTable)dataGridView1.DataSource).Columns)
{
if (column.DataType == typeof(int))
{
int result = int.Parse(inputs[i]);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(double))
{
double result = double.Parse(inputs[i]);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(DateTime))
{
DateTime result = DateTime.Parse(inputs[i]);
command.Parameters.AddWithValue($"@{i}", result);
}
else if (column.DataType == typeof(short))
{
short result = short.Parse(inputs[i]);
command.Parameters.AddWithValue($"@{i}", result);
}
else
{
command.Parameters.AddWithValue($"@{i}", inputs[i]);
}
i++;
}
if (command.ExecuteNonQuery() == 1)
{
UpdateTable(db, currentTableName.Text);
}
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message, "Warning",
MessageBoxButtons.OK,
MessageBoxIcon.Warning,
MessageBoxDefaultButton.Button1,
MessageBoxOptions.DefaultDesktopOnly);
}
finally
{
db.CloseConnection();
}
}