private void btnDelete_Click(object sender, EventArgs e)
{
DialogResult result = MetroFramework.MetroMessageBox.Show(this, "Are you sure you want to Delete this Customer?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
try
{
//Get CustomerID
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string Select = "SELECT CustomerID FROM Customers WHERE CustomerFullName ='" + listBox1.Text.ToString() + "'";
SqlCommand sqlcom = new SqlCommand(Select, sqlcon);
SqlDataReader reader;
reader = sqlcom.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CustomerID = Convert.ToInt32((reader["CustomerID"]));
}
}
reader.Close();
sqlcon.Close();
SqlConnection sqlcon2 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon2.Open();
string cmd = "DELETE FROM Customers WHERE CustomerID ='" + CustomerID.ToString() + "'";
SqlCommand sqlcom2 = new SqlCommand(cmd, sqlcon2);
sqlcom2.ExecuteNonQuery();
sqlcon2.Close();
MetroFramework.MetroMessageBox.Show(this, "Customer Successfully Deleted!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
btnDelete.Enabled = false;
listBox1.Items.Clear();
SqlConnection sqlcon3 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon3.Open();
string CMD3 = "SELECT CustomerFullName FROM Customers";
SqlCommand sqlcom3 = new SqlCommand(CMD3, sqlcon3);
SqlDataReader Reader3;
Reader3 = sqlcom3.ExecuteReader();
if (Reader3.HasRows)
{
while (Reader3.Read())
{
listBox1.Items.Add(Reader3["CustomerFullName"].ToString());
}
}
Reader3.Close();
sqlcon3.Close();
}
catch
{
MetroFramework.MetroMessageBox.Show(this, "Could not Connect to the Database!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
private void loadDis()
{
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT *,file.name As file,client.name As client FROM disbursements LEFT JOIN client ON client.clientID = disbursements.clientID LEFT JOIN file ON file.fileID = disbursements.fileID WHERE disbursements.fileID='" + id + "' ;";
connection.Open();
Reader = command.ExecuteReader();
// create and execute query
t = new DataTable();
t.Columns.Add("DATE", typeof(string));
t.Columns.Add("No.", typeof(string));
t.Columns.Add("AMOUNT", typeof(string));
t.Columns.Add("BAL.", typeof(string));
t.Columns.Add("METHOD", typeof(string));
t.Columns.Add("DETAILS", typeof(string));
t.Rows.Add(new object[] { "", " ", "", "FILE SUMMARY", "", "" });
t.Rows.Add(new object[] { " ", " ", "", "", "", "" });
t.Rows.Add(new object[] { "DISBURSEMENTS", " ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Amount", "Balance", "Method", "Details" });
DisDictionary.Clear();
while (Reader.Read())
{
DisDictionary.Add((Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(9) ? "0" : Reader.GetString(9)));
t.Rows.Add(new object[] { (Reader.IsDBNull(14) ? "none" : Reader.GetString(14)), (Reader.IsDBNull(7) ? "none" : Reader.GetString(7)), Convert.ToDouble(Reader.IsDBNull(9) ? "0" : Reader.GetString(9)).ToString("n0"), Convert.ToDouble(Reader.IsDBNull(11) ? "none" : Reader.GetString(11)).ToString("n0"), (Reader.IsDBNull(8) ? "none" : Reader.GetString(8)), (Reader.IsDBNull(35) ? "none" : Reader.GetString(35)) });
}
totalDis = DisDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", " Total", totalDis.ToString("n0"), "", "", "" });
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM expenses LEFT JOIN client ON client.clientID = expenses.clientID LEFT JOIN file ON file.fileID = expenses.fileID WHERE expenses.fileID='" + id + "';";
connection2.Open();
Reader2 = command2.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "EXPENSES", " ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Amount", "Balance", "Method", "Details" });
ExpDictionary.Clear();
while (Reader2.Read())
{
ExpDictionary.Add((Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(7) ? "0" : Reader2.GetString(7)));
t.Rows.Add(new object[] { (Reader2.IsDBNull(11) ? "none" : Reader2.GetString(11)), " ", Convert.ToDouble(Reader2.IsDBNull(7) ? "0" : Reader2.GetString(7)).ToString("n0"), (Reader2.IsDBNull(8) ? "none" : Reader2.GetString(8)), (Reader2.IsDBNull(36) ? "none" : Reader2.GetString(36)), (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6)) });
}
totalExp = ExpDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", "Total ", totalExp.ToString("n0"), "", "", "" });
connection2.Close();
MySqlConnection connection3 = new MySqlConnection(DBConnect.conn);
MySqlCommand command3 = connection3.CreateCommand();
MySqlDataReader Reader3;
command3.CommandText = "SELECT * FROM events WHERE file ='" + nameTxt.Text + "';";
connection3.Open();
Reader3 = command3.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "EVENTS", "SCHEDULES ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Event", "Start", "End", "Progress", "Cost" });
while (Reader3.Read())
{
EventDictionary.Add((Reader3.IsDBNull(0) ? "none" : Reader3.GetString(0)), (Reader3.IsDBNull(18) ? "0" : Reader3.GetString(18)));
t.Rows.Add(new object[] { (Reader3.IsDBNull(10) ? "none" : Reader3.GetString(10)), (Reader3.IsDBNull(1) ? "none" : Reader3.GetString(1)), Convert.ToDateTime(Reader3.IsDBNull(2) ? "none" : Reader3.GetString(2)).ToString("H:mm:s"), Convert.ToDateTime(Reader3.IsDBNull(3) ? "none" : Reader3.GetString(3)).ToString("H:mm:s"), (Reader3.IsDBNull(16) ? "none" : Reader3.GetString(16)) + " " + (Reader3.IsDBNull(8) ? "none" : Reader3.GetString(8)), Convert.ToDouble(Reader3.IsDBNull(18) ? "0" : Reader3.GetString(18)).ToString("n0") });
}
totalEvent = EventDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", " ", "", "", "Total", totalEvent.ToString("n0") });
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "", " ", "", "", "Total", (totalDis - (totalEvent + totalExp)).ToString("n0") });
connection3.Close();
dtGrid.DataSource = t;
dtGrid.Rows[1].DefaultCellStyle.BackColor = Color.Beige;
}
public Form3()
{
InitializeComponent();
string myConnnection = "datasource=localhost; port=3306;username=root;password=Sercan.123; ";
MySqlConnection myConn = new MySqlConnection(myConnnection);
myConn.Open();
MySqlCommand command = new MySqlCommand();
command = myConn.CreateCommand();
command.CommandText = "SELECT schema_name FROM information_schema.schemata where schema_name not like '%_schema' and schema_name not like 'mysql';";
MySqlDataReader Reader1;
Reader1 = command.ExecuteReader();
while (Reader1.Read())
{
string row = "";
for (int i = 0; i < Reader1.FieldCount; i++)
{
row += Reader1.GetValue(i).ToString();
}
trV1.Nodes.Add(row);
}
for (int i = 0; i < trV1.Nodes.Count; i++)
{
trV1.Nodes[i].Nodes.Add("Tables");
trV1.Nodes[i].Nodes.Add("Views");
trV1.Nodes[i].Nodes.Add("Stored Procedure");
trV1.Nodes[i].Nodes.Add("Functions ");
}
Reader1.Close();
Reader1.Dispose();
myConn.Close();
myConn.Open();
MySqlCommand command_table = new MySqlCommand();
command_table = myConn.CreateCommand();
for (int i = 0; i < trV1.Nodes.Count; i++)
{
command_table.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_schema ='" + trV1.Nodes[i].Text + "' ";
MySqlDataReader Reader2;
Reader2 = command_table.ExecuteReader();
while (Reader2.Read())
{
string table = "";
for (int j = 0; j < Reader2.FieldCount; j++)
{
table += Reader2.GetValue(j).ToString();
}
trV1.Nodes[i].Nodes[0].Nodes.Add(table);
}
Reader2.Close();
Reader2.Dispose();
}
myConn.Close();
myConn.Open();
MySqlCommand command_view = new MySqlCommand();
command_view = myConn.CreateCommand();
for (int i = 0; i < trV1.Nodes.Count; i++)
{
command_view.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_type='VIEW' AND table_schema ='" + trV1.Nodes[i].Text + "' ";
MySqlDataReader Reader3;
Reader3 = command_view.ExecuteReader();
while (Reader3.Read())
{
string view = "";
for (int j = 0; j < Reader3.FieldCount; j++)
{
view += Reader3.GetValue(j).ToString();
}
trV1.Nodes[i].Nodes[1].Nodes.Add(view);
}
Reader3.Close();
Reader3.Dispose();
}
myConn.Close();
myConn.Open();
MySqlCommand command_procedure = new MySqlCommand();
command_procedure = myConn.CreateCommand();
for (int i = 0; i < trV1.Nodes.Count; i++)
{
command_procedure.CommandText = "SHOW PROCEDURE STATUS WHERE Db ='" + trV1.Nodes[i].Text + "' ";
MySqlDataReader Reader4;
Reader4 = command_procedure.ExecuteReader();
while (Reader4.Read())
{
string procedure = "";
for (int j = 0; j < Reader4.FieldCount; j++)
{
procedure += Reader4.GetValue(j).ToString();
}
trV1.Nodes[i].Nodes[2].Nodes.Add(procedure);
}
Reader4.Close();
Reader4.Dispose();
}
myConn.Close();
myConn.Open();
MySqlCommand command_function = new MySqlCommand();
command_function = myConn.CreateCommand();
for (int i = 0; i < trV1.Nodes.Count; i++)
{
command_function.CommandText = "SHOW FUNCTION STATUS WHERE Db ='" + trV1.Nodes[i].Text + "' ";
MySqlDataReader Reader5;
Reader5 = command_function.ExecuteReader();
while (Reader5.Read())
{
string function = "";
for (int j = 0; j < Reader5.FieldCount; j++)
{
function += Reader5.GetValue(j).ToString();
}
trV1.Nodes[i].Nodes[3].Nodes.Add(function);
}
Reader5.Close();
Reader5.Dispose();
}
ContextMenuStrip docMenu = new ContextMenuStrip();
ToolStripMenuItem SelectRows = new ToolStripMenuItem();
SelectRows.Text = "Select Rows";
ToolStripMenuItem AlterTable = new ToolStripMenuItem();
AlterTable.Text = "Alter Table";
ToolStripMenuItem DropTable = new ToolStripMenuItem();
DropTable.Text = "Drop Table";
docMenu.Items.AddRange(new ToolStripMenuItem[] { SelectRows, AlterTable, DropTable });
for (int i = 0; i < trV1.Nodes.Count; i++)
{
for (int j = 0; j < trV1.Nodes[i].Nodes[0].Nodes.Count; j++)
{
trV1.Nodes[i].Nodes[0].Nodes[j].ContextMenuStrip = docMenu;
}
}
SelectRows.Click += SelectRows_Click;
void SelectRows_Click(object sender, System.EventArgs e)
{
MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=Sercan.123");
connection.Open();
try
{
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM " + trV1.SelectedNode.Parent.Parent.Text + "." + trV1.SelectedNode.Text + " ", connection);
DataSet ds = new DataSet();
adapter.Fill(ds, trV1.SelectedNode.Text);
dataGridView1.DataSource = ds.Tables[trV1.SelectedNode.Text];
string a = ds.Tables[0].Columns.ToString();
//MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM blm437.ogrenci ", connection);
//DataSet ds = new DataSet();
//adapter.Fill(ds, "ogrenci");
//dataGridView1.DataSource = ds.Tables["ogrenci"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void listBox1_Click(object sender, EventArgs e)
{
SqlConnection sqlcon1 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon1.Open();
string Select = "SELECT UserID From Users WHERE UserName ='******'";
SqlCommand sqlcom1 = new SqlCommand(Select, sqlcon1);
SqlDataReader Reader;
Reader = sqlcom1.ExecuteReader();
if (Reader.HasRows)
{
while (Reader.Read())
{
isAdmin = Convert.ToInt32((Reader["UserID"]));
if (isAdmin == 1)
{
MetroFramework.MetroMessageBox.Show(this, "The Admin Account Cannot be Deleted!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
DialogResult dialogResult = MetroFramework.MetroMessageBox.Show(this, "Are you sure you want to delete this user?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialogResult == DialogResult.Yes)
{
try
{
SqlConnection sqlcon = new SqlConnection(Globals_Class.ConnectionString);
sqlcon.Open();
string Command = "DELETE FROM Users WHERE UserName ='******'";
SqlCommand sqlcom = new SqlCommand(Command, sqlcon);
sqlcom.ExecuteNonQuery();
MetroFramework.MetroMessageBox.Show(this, "The Selected User was Deleted Successfully!", "Message", MessageBoxButtons.OK, MessageBoxIcon.None);
listBox1.Items.Clear();
SqlConnection sqlcon2 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon2.Open();
string CMD2 = "SELECT UserName FROM Users";
SqlCommand sqlcom2 = new SqlCommand(CMD2, sqlcon2);
SqlDataReader Reader2;
Reader2 = sqlcom2.ExecuteReader();
if (Reader2.HasRows)
{
while (Reader2.Read())
{
listBox1.Items.Add(Reader2["UserName"].ToString());
}
}
Reader2.Close();
sqlcon2.Close();
}
catch
{
MetroFramework.MetroMessageBox.Show(this, "An Error Occurred whislt Deleting the User!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
listBox1.Items.Clear();
SqlConnection sqlcon3 = new SqlConnection(Globals_Class.ConnectionString);
sqlcon3.Open();
string CMD3 = "SELECT UserName FROM Users";
SqlCommand sqlcom3 = new SqlCommand(CMD3, sqlcon3);
SqlDataReader Reader3;
Reader3 = sqlcom3.ExecuteReader();
if (Reader3.HasRows)
{
while (Reader3.Read())
{
listBox1.Items.Add(Reader3["UserName"].ToString());
}
}
Reader3.Close();
sqlcon3.Close();
}
}
}
}
}
}