Ejemplo n.º 1
0
        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);
                }
            }
        }
Ejemplo n.º 2
0
        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;
        }
Ejemplo n.º 3
0
        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);
                }
            }
        }
Ejemplo n.º 4
0
        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();
                            }
                        }
                    }
                }
            }
        }