//----------------------------------------------------------------------------------------
#region Select Function
private void SelectButton_Click(object sender, EventArgs e)
{
if (table != null)
{
try
{
StringBuilder command = new StringBuilder(String.Format("WHERE {0} {1} ",
filterBox.SelectedItem,
compererBox.SelectedItem));
if (compererBox.Text == "LIKE" || compererBox.Text == "NOT LIKE")
{
command.AppendFormat("'%{0}%' ", filterTextBox.Text);
}
else
{
command.AppendFormat("'{0}' ", filterTextBox.Text);
}
for (int i = 0; i < 3; i++)
{
ComboBox andOrBox = null;
if (this.groupBox2.Controls.Find("AddFilterBox" + i, false).Length != 0)
{
andOrBox = this.groupBox2.Controls.Find("AddFilterBox" + i, false)[0] as ComboBox;
command.AppendFormat(" {0} ", andOrBox.Text);
if (andOrBox != null && andOrBox.SelectedIndex != -1)
{
command.AppendFormat("{0} {1} ", this.groupBox2.Controls.Find("FilterBox" + i, false)[0].Text,
this.groupBox2.Controls.Find("CompererBox" + i, false)[0].Text);
if (this.groupBox2.Controls.Find("CompererBox" + i, false)[0].Text == "LIKE" ||
this.groupBox2.Controls.Find("CompererBox" + i, false)[0].Text == "NOT LIKE")
{
command.AppendFormat("'%{0}%' ", this.groupBox2.Controls.Find("FilterTextBox" + i, false)[0].Text);
}
else
{
command.AppendFormat("'{0}' ", this.groupBox2.Controls.Find("FilterTextBox" + i, false)[0].Text);
}
}
}
}
dataGridView1.DataSource = TableExplorer.SelectTable(command.ToString(), "SELECT * FROM " + table.TableName);
}
catch (Exception ex)
{
MessageBox.Show("Some SELECT fields are empty or irregular! \n" + ex.Message);
}
}
else
{
MessageBox.Show("There is no table selected!");
}
}
private void ComplexQueryForm_Load(object sender, EventArgs e)
{
DataTable table = TableExplorer.SelectTable("", "SELECT DISTINCT m.MovieID, m.Title FROM Movies m RIGHT JOIN MovieActors ma ON m.MovieID = ma.MovieID");
MoviesComBox.DataSource = table;
MoviesComBox.DisplayMember = "Title";
MoviesComBox.ValueMember = "MovieID";
}
private void ModifyTablesForm_Load(object sender, EventArgs e)
{
TableExplorer.GetWholeDB(videoLib);
List <string> tabArr = TableExplorer.GetVideoLibTableNames();
foreach (string table in tabArr)
{
TabPage tab = new TabPage()
{
Name = table,
Text = table,
};
tableTabControl.TabPages.Add(tab);
}
foreach (TabPage tab in tableTabControl.TabPages)
{
DataGridView dataGr = new DataGridView()
{
Name = tab.Name + "DataGridView",
Location = new Point(3, 3),
Size = new Size(730, 250),
DataSource = videoLib.Tables[tab.Name],
};
Button btn = new Button()
{
Name = tab.Name + "UpdateBtn",
Text = "Update Table",
Location = new Point(20, 270),
Size = new Size(110, 23),
};
btn.Click += updateButton_Click;
Button btn2 = new Button()
{
Name = tab.Name + "UpdateAllhBtn",
Text = "Update All Tables",
Location = new Point(140, 270),
Size = new Size(110, 23),
};
btn2.Click += updateAllButton_Click;
Button btn3 = new Button()
{
Name = tab.Name + "RefreshBtn",
Text = "Refresh Tables",
Location = new Point(260, 270),
Size = new Size(110, 23),
};
btn3.Click += refreshButton_Click;
tab.Controls.AddRange(new Control[] { dataGr, btn, btn2, btn3 });
}
}
private void RefreshTables()
{
showTableBox.Items.Clear();
var arr = TableExplorer.GetVideoLibTableNames();
foreach (string tab in arr)
{
showTableBox.Items.Add(tab);
}
}
private void MoviesComBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (MoviesComBox.SelectedValue is int)
{
string command = String.Format(
"SELECT X.ActorID, FName, LName, DateOfBirth, CountryOfBirth, CityOfBirth " +
"FROM MovieActors X " +
"JOIN Actors a ON a.ActorID = X.ActorID WHERE MovieID = \'{0}\'", MoviesComBox.SelectedValue);
dataGridView1.DataSource = TableExplorer.SelectTable("", command);
}
}
private void deleteButton_Click(object sender, EventArgs e)
{
DialogResult res = MessageBox.Show("Are you sure you want to delete this table?", "DeleteDialog", MessageBoxButtons.OKCancel);
if (res == DialogResult.OK)
{
String msg = TableExplorer.DeleteTable(showTableBox.Text);
MessageBox.Show(msg);
RefreshTables();
}
}
//----------------------------------------------------------------------------------------
#region Fill Tables and controls
private void DBexplorerForm_Load(object sender, EventArgs e)
{
TableExplorer.GetWholeDB(this.videoLib);
var Arr = TableExplorer.GetVideoLibTableNames();
foreach (string tab in Arr)
{
showTableBox.Items.Add(tab);
}
}
void refreshButton_Click(object sender, EventArgs e)
{
videoLib.Reset();
TableExplorer.GetWholeDB(videoLib);
foreach (TabPage tab in tableTabControl.TabPages)
{
var grid = tab.Controls.Find(tab.Name + "DataGridView", false)[0] as DataGridView;
grid.DataSource = null;
grid.DataSource = videoLib.Tables[tab.Name];
grid.Refresh();
}
}
void updateAllButton_Click(object sender, EventArgs e)
{
TableExplorer.UpdateDataSource(videoLib);
}
void updateButton_Click(object sender, EventArgs e)
{
TableExplorer.UpdateTableDataSource(videoLib.Tables[tableTabControl.SelectedTab.Name]);
}