private void CreateIndexes(bool full)
{
IList <ForeignKeyMissingIndexes> missingIndexes = GetSelectedForeignKeys();
string createIndexes = Optimizer.CreateIndexesForeignKey(missingIndexes, full);
MessageBoxWithTextBox form = new MessageBoxWithTextBox(this, createIndexes, "Creating indexes", "Create indexes");
if (form.ShowDialog(this) == DialogResult.Yes)
{
Cursor.Current = Cursors.WaitCursor;
try
{
Database.Instance.ExecuteNonResultQuery(form.richTextBoxText.Text);
MessageBox.Show(this, "Creating indexes was successful.", "Creating indexes", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception exc)
{
Debug.WriteLine(exc);
MessageBox.Show(this, "Creating indexes failed with this error: " + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
ForeignKeyWithoutIndexes_Load(this, null);
Cursor.Current = Cursors.Default;
}
}
private void buttonArchive_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
ArchiveEntryTabPage tabPage = (ArchiveEntryTabPage)tabControlTables.SelectedTab.Controls[0];
IDictionary <string, string> convertorTableNames = new Dictionary <string, string>(tabPage.tableLayoutPanelTablesnames.RowCount * 2);
bool tableNamesChanged = false;
for (int i = 0; i <= tabPage.tableLayoutPanelTablesnames.RowCount; i++)
{
for (int j = 0; j < tabPage.tableLayoutPanelTablesnames.ColumnCount; j += 2)
{
if (tabPage.tableLayoutPanelTablesnames.GetControlFromPosition(j, i) != null)
{
string text = tabPage.tableLayoutPanelTablesnames.GetControlFromPosition(j + 1, i).Text;
if (text.Trim().Length == 0)
{
MessageBox.Show(this, $"New tablename of tablename {tabPage.tableLayoutPanelTablesnames.GetControlFromPosition(j, i).Text} cannot be empty", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!tableNamesChanged && tabPage.tableLayoutPanelTablesnames.GetControlFromPosition(j, i).Text != text)
{
tableNamesChanged = true;
}
convertorTableNames.Add(tabPage.tableLayoutPanelTablesnames.GetControlFromPosition(j, i).Text.Replace("[", "").Replace("]", "").ToLower(), text);
}
}
}
if (!tableNamesChanged && checkBoxInsertWithSelect.Checked)
{
MessageBox.Show(this, $"You have checked \"{checkBoxInsertWithSelect.Text}\", but you do not change any tablename. You cannot Select for Insert from same table. Please rename table or uncheck option {checkBoxInsertWithSelect.Checked}.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
DatabaseObject tableToArchive = _tablesLimit.First(t => t.NameWithSchema == tabControlTables.SelectedTab.Text);
Cursor.Current = Cursors.Default;
Stream fileStream = null;
StreamWriter streamWriter = null;
string filename = "";
bool deleteFile = false;
try
{
if (checkBoxOnlySave.Checked && saveSQLFileDialog.ShowDialog(this) == DialogResult.OK)
{
fileStream = saveSQLFileDialog.OpenFile();
filename = saveSQLFileDialog.FileName;
}
else
{
fileStream = Optimizer.GetTempFileStreamWriter(out filename);
deleteFile = true;
}
streamWriter = new StreamWriter(fileStream, Encoding.UTF8);
Cursor.Current = Cursors.WaitCursor;
if (!Optimizer.Instance.ArchiveTable(tableToArchive, convertorTableNames, checkBoxCreateTable.Checked, tabPage.Conditions, checkBoxInsertWithSelect.Checked, checkBoxSaveAllEntries.Checked, streamWriter))
{
Cursor.Current = Cursors.Default;
MessageBox.Show(this, "No data to archiving", "Archive status", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (checkBoxOnlySave.Checked)
{
Cursor.Current = Cursors.Default;
MessageBox.Show(this, "Archived entries were saved.", "Archiving completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBoxWithTextBox messageBox = new MessageBoxWithTextBox(this, "Archive:", "Archiving is ready", "Execute");
using (StreamReader streamReader = new StreamReader(fileStream, Encoding.UTF8))
{
fileStream.Seek(0, SeekOrigin.Begin);
messageBox.richTextBoxText.Text = streamReader.ReadToEnd();
streamWriter = null;
fileStream = null;
}
Cursor.Current = Cursors.Default;
//To option repair query in case of error and execute it again
while (true)
{
if (messageBox.ShowDialog(this) == DialogResult.Yes)
{
Cursor.Current = Cursors.WaitCursor;
try
{
Database.Instance.ExecuteNonResultQuery(messageBox.richTextBoxText.Text);
Cursor.Current = Cursors.Default;
MessageBox.Show(this, "Archiving was executed.", "Archiving completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
break;
}
catch (SqlException exc)
{
Debug.WriteLine(exc);
Cursor.Current = Cursors.Default;
MessageBox.Show(this, "Error occured during executing query: " + exc.Message, "Query failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
break;
}
}
messageBox.Dispose();
}
}
catch (DatabaseException exc)
{
Debug.WriteLine(exc);
MessageBox.Show(this, $"Error while getting data - {exc.InnerException.Message}", "Cannot get data", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
catch (IOException exc)
{
Debug.WriteLine(exc);
MessageBox.Show(this, $"Error with data file - {exc.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
catch (OutOfMemoryException exc)
{
Debug.WriteLine(exc);
MessageBox.Show(this, $"Program is out of memory - processed data are saved at file {filename}", "Fatal error", MessageBoxButtons.OK, MessageBoxIcon.Error);
deleteFile = false;
Process.Start("explorer.exe", $"/select, {filename}");
return;
}
finally
{
Cursor.Current = Cursors.Default;
if (streamWriter != null)
{
streamWriter.Dispose();
fileStream = null;
}
fileStream?.Dispose();
if (deleteFile)
{
File.Delete(filename);
}
}
}
private void buttonDelete_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
DeleteEntryTabPage tabPage = (DeleteEntryTabPage)tabControlTables.SelectedTab.Controls[0];
IDictionary <ForeignKey, ForeignKey.DeleteActions> foreignKeyActions = new Dictionary <ForeignKey, ForeignKey.DeleteActions>();
for (int i = 0; i < tabPage.tableLayoutPanelForeignKeys.RowCount; i++)
{
ComboBox comboBox = (ComboBox)tabPage.tableLayoutPanelForeignKeys.GetControlFromPosition(1, i);
if (comboBox == null)
{
break;
}
ForeignKey.DeleteActions deleteAction = ((DeleteActionObject)comboBox.SelectedItem).DeleteAction.Value;
foreignKeyActions.Add((ForeignKey)tabPage.tableLayoutPanelForeignKeys.GetControlFromPosition(0, i).Tag, deleteAction);
}
DatabaseObject tableWhereDelete = _tablesLimit.First(t => t.NameWithSchema == tabControlTables.SelectedTab.Text);
MessageBoxWithTextBox messageBox = new MessageBoxWithTextBox(this, "Delete query:", "Delete procedure is ready", "Execute");
try
{
messageBox.richTextBoxText.Text = Optimizer.Instance.DeleteEntriesFromTable(tableWhereDelete, tabPage.Conditions, foreignKeyActions);
}
catch (DatabaseException exc)
{
Debug.WriteLine(exc);
Cursor.Current = Cursors.Default;
MessageBox.Show(this, $"Error while getting data - {exc.InnerException.Message}", "Cannot get data", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
catch (DeleteDependencyException exc)
{
Debug.WriteLine(exc);
Cursor.Current = Cursors.Default;
MessageBox.Show(this, exc.Message, "Cannot get data", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Cursor.Current = Cursors.Default;
//To option repair query in case of error and execute it again
while (true)
{
if (messageBox.ShowDialog(this) == DialogResult.Yes)
{
Cursor.Current = Cursors.WaitCursor;
try
{
Database.Instance.ExecuteNonResultQuery(messageBox.richTextBoxText.Text);
Cursor.Current = Cursors.Default;
MessageBox.Show(this, "Delete procedure was executed.", "Delete procedure completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
break;
}
catch (SqlException exc)
{
Debug.WriteLine(exc);
Cursor.Current = Cursors.Default;
MessageBox.Show(this, "Error occured during executing query: " + exc.Message, "Query failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
break;
}
}
messageBox.Dispose();
}