private void btnLogin_Click(object sender, EventArgs e) { Program.databaseUser = textBoxUser.Text; Program.databasePassword = textBoxPassword.Text; Classes.Connection connection = new Classes.Connection(Program.databaseUser, Program.databasePassword); MySqlConnection c = connection.GetConnection(); try { c.Open(); //Tentativa de realizar uma conexão com os dados fornecidos Forms.FormUserLogin nextScreen = new Forms.FormUserLogin(); this.Visible = false; nextScreen.ShowDialog(); this.Close(); } catch (MySqlException ex) { if (ex.Code == 0x0) //Se o try retornar uma exeção tratamos ela { MessageBox.Show("Ocorreu um erro \n Usuário ou Senha incoretos", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { MessageBox.Show("Ocorreu um erro", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error); } Console.WriteLine(ex); // Provavélmente o Usuario e/ou a senha estão incorretos } }
// SQL Methods private void _load_schedules_to_datagridview(DataGridView dgv, string selectedDate) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "select `tbl_schedules`.`sched_id`, `tbl_schedules`.`sched_auto_departure`, `tbl_bus`.`bus_number` as `BUS NUMBER`, DATE_FORMAT(`tbl_schedules`.`sched_departure_time`, '%h:%i:%s %p') as `DEPARTURE TIME` from `tbl_schedules` left join `tbl_bus` on `tbl_schedules`.`sched_bus_id`=`tbl_bus`.`bus_id` where DATE_FORMAT(`sched_date`, '%c/%d/%Y')=@sched_date and `sched_is_active`=1 order by `sched_departure_time` ASC"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { MySqlDataAdapter da = new MySqlDataAdapter(); DataTable dt = new DataTable(); cmd.Parameters.Add("@sched_date", MySqlDbType.VarChar).Value = selectedDate; conn.Open(); da.SelectCommand = cmd; conn.Close(); da.Fill(dt); dgv.DataSource = dt; // hide datagridview columns Classes.Forms._hide_datagridview_column(this.dgv_schedules, new string[] { "sched_id", "sched_auto_departure" }); // call the format dgv method this._format_datagridview(this.dgv_schedules); } } }
// get datas private object[] _get_datas(int sched_id) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "select `sched_bus_id`, DATE_FORMAT(`sched_date`, '%c/%d/%Y') as `sched_date`, DATE_FORMAT(`sched_departure_time`, '%h:%i:%s %p') as `sched_departure_time`, `sched_name`, `sched_description`, `sched_auto_departure` from `tbl_schedules` where `sched_id`=@sched_id and `sched_is_active`=1"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { cmd.Parameters.Add("@sched_id", MySqlDbType.Int32).Value = sched_id; conn.Open(); MySqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { object[] returnDatas = new object[6]; while (dr.Read()) { returnDatas[0] = dr["sched_bus_id"]; returnDatas[1] = dr["sched_date"]; returnDatas[2] = dr["sched_departure_time"]; returnDatas[3] = dr["sched_name"]; returnDatas[4] = dr["sched_description"]; returnDatas[5] = dr["sched_auto_departure"]; } return(returnDatas); } dr.Close(); conn.Close(); } } return(null); }
private void _load_bus_numbers_to_combobox(ComboBox cmb) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "select `tbl_bus`.`bus_id`, `tbl_bus`.`bus_number` from `tbl_bus` where `tbl_bus`.`bus_is_active`=1 and `tbl_bus`.`bus_id` not in (select `tbl_schedules`.`sched_bus_id` from `tbl_schedules` where `tbl_schedules`.`sched_is_active`=1 and TIMESTAMP(`tbl_schedules`.`sched_date`, `tbl_schedules`.`sched_departure_time`)>=CURRENT_TIMESTAMP()) order by CAST(`bus_number` as SIGNED INTEGER) ASC"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { Dictionary <int, string> listItems = new Dictionary <int, string>(); listItems.Add(0, "— Select Bus Number —"); conn.Open(); MySqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { listItems.Add(Convert.ToInt32(dr["bus_id"]), dr["bus_number"].ToString()); } } else { this.cb_bus_number.Enabled = false; this.btn_save.Enabled = false; } dr.Close(); conn.Close(); // finalize the dictionary cmb.DisplayMember = "Value"; cmb.ValueMember = "Key"; cmb.DataSource = new BindingSource(listItems, null); } } }
// --------------------------------------------------------------------------------------------------- // Load search/autocomplete textbox private void _load_autocomplete(ToolStripTextBox txtbox) { Classes.Connection Connection = new Classes.Connection(); // init textbox autocomplete properties this.toolStripTxt_search.AutoCompleteSource = AutoCompleteSource.CustomSource; AutoCompleteStringCollection collection = new AutoCompleteStringCollection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "select `bus_number` from `tbl_bus` where `bus_is_active`=1 order by CAST(`bus_number` as SIGNED INTEGER) ASC"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { conn.Open(); MySqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { collection.Add(dr["bus_number"].ToString()); } } dr.Close(); conn.Close(); } } // finally add to the textbox collection txtbox.AutoCompleteCustomSource = collection; }
// ----------------------------------------------------------------------------------------------- // get the data via class name private object[] _get_class_info(string class_name) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "select `class_id`, `class_name`, `class_seat_count`, `class_has_aircon`, `class_remarks`, `class_edited_date` from `tbl_bus_class` where `class_name`=@class_name and `class_is_active`=1"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { cmd.Parameters.Add("@class_name", MySqlDbType.VarChar).Value = class_name; MySqlDataAdapter da = new MySqlDataAdapter(); DataSet ds = new DataSet(); conn.Open(); da.SelectCommand = cmd; conn.Close(); da.Fill(ds, "tbl_bus_class"); if (ds.Tables[0].Rows.Count > 0) { DataRow row = ds.Tables[0].Rows[0]; object[] returnDatas = new object[6]; returnDatas[0] = row.ItemArray[0]; returnDatas[1] = row.ItemArray[1]; returnDatas[2] = row.ItemArray[2]; returnDatas[3] = row.ItemArray[3]; returnDatas[4] = row.ItemArray[4]; returnDatas[5] = row.ItemArray[5]; return(returnDatas); } return(null); } } }
// get the data via class name private object[] _get_bus_info(string bus_number) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "select `bus_id`, `bus_class_id`, `bus_number`, `bus_plate_number` from `tbl_bus` where `bus_number`=@bus_number"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { cmd.Parameters.Add("@bus_number", MySqlDbType.VarChar).Value = bus_number; MySqlDataAdapter da = new MySqlDataAdapter(); DataSet ds = new DataSet(); conn.Open(); da.SelectCommand = cmd; conn.Close(); da.Fill(ds, "tbl_bus"); if (ds.Tables[0].Rows.Count > 0) { DataRow row = ds.Tables[0].Rows[0]; object[] returnDatas = new object[6]; returnDatas[0] = row.ItemArray[0]; returnDatas[1] = row.ItemArray[1]; returnDatas[2] = row.ItemArray[2]; returnDatas[3] = row.ItemArray[3]; return(returnDatas); } } } return(null); }
// Load/Fill the combobox private void _load_bus_class_to_combobox(ComboBox cmb) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "select `class_id`, `class_name` from `tbl_bus_class` where `class_is_active`=1 order by `class_name` ASC"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { Dictionary <int, string> listItems = new Dictionary <int, string>(); listItems.Add(0, "— Select Bus Class —"); conn.Open(); MySqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { listItems.Add(Convert.ToInt32(dr["class_id"]), dr["class_name"].ToString()); } } dr.Close(); conn.Close(); cmb.DisplayMember = "Value"; cmb.ValueMember = "Key"; cmb.DataSource = new BindingSource(listItems, null); } } }
// ------------------------------------------------------------------------ private void _load_datas(DataGridView dgv, string[] arrSearchKeys = null, string[] arrSearchValues = null) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmtNormal = "select `class_id`, `class_name`, `class_seat_count`, `class_has_aircon`, `class_remarks` from `tbl_bus_class` where `class_is_active`=1"; string stmtSearch = "select `class_id`, `class_name`, `class_seat_count`, `class_has_aircon`, `class_remarks` from `tbl_bus_class` where `class_is_active`=1 and ("; if (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues)) { for (int i = 0; i < arrSearchKeys.Length; i++) { string commaStr = i >= arrSearchKeys.Length - 1 ? "" : " or "; stmtSearch += string.Format("`{0}` like @{1}{2}", arrSearchKeys[i], arrSearchKeys[i], commaStr); } } stmtSearch += ")"; string stmt = (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues)) ? stmtSearch : stmtNormal; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { MySqlDataAdapter da = new MySqlDataAdapter(); DataSet ds = new DataSet(); if (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues)) { for (int i = 0; i < arrSearchKeys.Length; i++) { cmd.Parameters.AddWithValue("@" + arrSearchKeys[i], "%" + arrSearchValues[i] + "%"); } } conn.Open(); da.SelectCommand = cmd; conn.Close(); da.Fill(ds, "tbl_bus_class"); string[] arrColumns = new string[] { "ID", "CLASS NAME", "NUMBER OF SEATS", "AIR-CONDITIONED", "REMARKS" }; Classes.Forms._create_datagrid_columns(this.dgv_datas, arrColumns); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow row = ds.Tables[0].Rows[i]; string[] arrDatas = new string[5]; arrDatas[0] = row["class_id"].ToString(); arrDatas[1] = row["class_name"].ToString(); arrDatas[2] = row["class_seat_count"].ToString(); arrDatas[3] = row["class_has_aircon"].ToString() == "1" ? "YES" : "NO"; arrDatas[4] = row["class_remarks"].ToString(); Classes.Forms._add_rows(this.dgv_datas, arrDatas); } // hide columns string[] arrHiddenColumns = new string[1] { "ID" }; Classes.Forms._hide_datagridview_column(this.dgv_datas, arrHiddenColumns); } } }
private Classes.CurrentPlayer LoadPlayer(int _id) { Classes.Connection connection = new Classes.Connection(); if (connection.LoadPlayerFromDataBase(_id, out _thePlayer)) { return(_thePlayer); } else { MessageBox.Show("Failed to load player"); return(null); } }
private void _load_datas_to_datagrid(DataGridView dgv, string[] arrSearchKeys = null, string[] arrSearchValues = null) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string selected_fields = "`tbl_bus`.`bus_id`, `tbl_bus_class`.`class_name` as `BUS CLASS`, `tbl_bus`.`bus_number` as `BUS NUMBER`, `tbl_bus`.`bus_plate_number` as `PLATE NUMBER`, `tbl_bus`.`bus_created_date` AS `BUS CREATED DATE`"; string joinStmt = "left join `tbl_bus_class` on `tbl_bus`.`bus_class_id`=`tbl_bus_class`.`class_id`"; string stmtNormal = "select " + selected_fields + " from `tbl_bus` " + joinStmt + " where `bus_is_active`=1 order by CAST(`bus_number` as SIGNED INTEGER) ASC"; string stmtSearch = "select " + selected_fields + " from `tbl_bus` " + joinStmt + " where `bus_is_active`=1 and ("; if (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues)) { for (int i = 0; i < arrSearchKeys.Length; i++) { string commaStr = i >= arrSearchKeys.Length - 1 ? "" : " or "; stmtSearch += string.Format("`{0}` like @{1}{2}", arrSearchKeys[i], arrSearchKeys[i], commaStr); } } stmtSearch += ")order by CAST(`bus_number` as SIGNED INTEGER) ASC"; string stmt = (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues)) ? stmtSearch : stmtNormal; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { MySqlDataAdapter da = new MySqlDataAdapter(); DataTable dt = new DataTable(); if (!Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchKeys) && !Classes.Datatypes.ArrayIsNullOrEmpty(arrSearchValues)) { for (int i = 0; i < arrSearchKeys.Length; i++) { cmd.Parameters.AddWithValue("@" + arrSearchKeys[i], "%" + arrSearchValues[i] + "%"); } } conn.Open(); da.SelectCommand = cmd; conn.Close(); da.Fill(dt); dgv.DataSource = dt; // hide some datagridview columns Classes.Forms._hide_datagridview_column(this.dgv_datas, new string[1] { "bus_id" }); } } }
// remove data private Boolean _remove_data(int sched_id) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "update `tbl_schedules` set `sched_is_active`=0 where `sched_id`=@sched_id"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { cmd.Parameters.Add("@sched_id", MySqlDbType.Int32).Value = sched_id; conn.Open(); int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery()); conn.Close(); if (affectedRows > 0) { return(true); } return(false); } } }
// saving the bus schedule private Boolean _save_bus_schedule(int schedule_id = 0) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmtAdd = "insert into `tbl_schedules` (`sched_bus_id`, `sched_date`, `sched_departure_time`, `sched_name`, `sched_description`, `sched_created_by`, `sched_auto_departure`) values (@sched_bus_id, @sched_date, @sched_departure_time, @sched_name, @sched_description, @sched_created_by, @sched_auto_departure)"; string stmtUpdate = "update `tbl_schedules` set `sched_bus_id`=@sched_bus_id, `sched_date`=@sched_date, `sched_departure_time`=@sched_departure_time, `sched_name`=@sched_name, `sched_description`=@sched_description, `sched_auto_departure`=@sched_auto_departure, `sched_edited_by`=@sched_edited_by where `sched_id`=@sched_id and `sched_is_active`=1"; string stmt = schedule_id != 0 ? stmtUpdate : stmtAdd; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { cmd.Parameters.Add("@sched_bus_id", MySqlDbType.Int32).Value = this.cb_bus_number.SelectedValue; cmd.Parameters.Add("@sched_date", MySqlDbType.DateTime).Value = this.dtp_sched.Value; cmd.Parameters.Add("@sched_departure_time", MySqlDbType.DateTime).Value = this.dtp_departure_time.Value; cmd.Parameters.Add("@sched_name", MySqlDbType.Text).Value = this.txt_sched_name.Text; cmd.Parameters.Add("@sched_description", MySqlDbType.Text).Value = this.txt_description.Text; cmd.Parameters.Add("@sched_auto_departure", MySqlDbType.Bit).Value = this.chk_auto_departure.Checked; if (0 != schedule_id) { cmd.Parameters.Add("@sched_edited_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id; cmd.Parameters.Add("@sched_id", MySqlDbType.Int32).Value = schedule_id; } else { cmd.Parameters.Add("@sched_created_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id; } conn.Open(); int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery()); conn.Close(); if (affectedRows > 0) { return(true); } } } return(false); }
// remove data private Boolean _remove_data(int class_id) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmt = "update `tbl_bus_class` set `class_is_active`=0, `class_edited_by`=@class_edited_by where `class_id`=@class_id"; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { cmd.Parameters.Add("@class_edited_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id; cmd.Parameters.Add("@class_id", MySqlDbType.Int32).Value = class_id; conn.Open(); int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery()); conn.Close(); if (affectedRows > 0) { return(true); } return(false); } } }
private Boolean _save_data(int class_id = 0) { if (this._check_field_requirements() == true) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmtInsert = "insert into `tbl_bus_class` (`class_name`, `class_seat_count`, `class_has_aircon`, `class_seat_price`, `class_remarks`, `class_created_by`) values (@class_name, @class_seat_count, @class_has_aircon, @class_seat_price, @class_remarks, @class_created_by)"; string stmtUpdate = "update `tbl_bus_class` set `class_name`=@class_name, `class_seat_count`=@class_seat_count, `class_has_aircon`=@class_has_aircon, `class_seat_price`=@class_seat_price, `class_remarks`=@class_remarks, `class_edited_by`=@class_edited_by where `class_id`=@class_id"; string stmt = class_id != 0 ? stmtUpdate : stmtInsert; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { cmd.Parameters.Add("@class_name", MySqlDbType.VarChar).Value = this.txt_class_name.Text; cmd.Parameters.Add("@class_seat_count", MySqlDbType.Int32).Value = this.txt_number_of_seats.Text; cmd.Parameters.Add("@class_has_aircon", MySqlDbType.Bit).Value = this.has_aircon; cmd.Parameters.Add("@class_seat_price", MySqlDbType.Double).Value = Convert.ToDouble(this.txt_seat_price.Text); cmd.Parameters.Add("@class_remarks", MySqlDbType.Text).Value = this.txt_remarks.Text; if (0 != class_id) { cmd.Parameters.Add("@class_edited_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id; cmd.Parameters.Add("@class_id", MySqlDbType.Int32).Value = class_id; } else { cmd.Parameters.Add("@class_created_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id; } conn.Open(); int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery()); conn.Close(); if (affectedRows > 0) { return(true); } } } } return(false); }
// -------------------------------------- SQL STATEMENTS ----------------------------------------------- private Boolean _save_data(int bus_id = 0) { if (this._check_field_requirements() == true) { Classes.Connection Connection = new Classes.Connection(); using (MySqlConnection conn = new MySqlConnection(Connection.connStr)) { string stmtInsert = "insert into `tbl_bus` (`bus_class_id`, `bus_number`, `bus_plate_number`, `bus_created_by`) values (@bus_class_id, @bus_number, @bus_plate_number, @bus_created_by)"; string stmtUpdate = "update `tbl_bus` set `bus_class_id`=@bus_class_id, `bus_number`=@bus_number, `bus_plate_number`=@bus_plate_number, `bus_edited_by`=@bus_edited_by where `bus_id`=@bus_id"; string stmt = bus_id != 0 ? stmtUpdate : stmtInsert; using (MySqlCommand cmd = new MySqlCommand(stmt, conn)) { cmd.Parameters.Add("@bus_class_id", MySqlDbType.VarChar).Value = this.cb_buss_class.SelectedValue; cmd.Parameters.Add("@bus_number", MySqlDbType.VarChar).Value = this.txt_bus_number.Text; cmd.Parameters.Add("@bus_plate_number", MySqlDbType.VarChar).Value = this.txt_plate_number.Text; if (0 != bus_id) { cmd.Parameters.Add("@bus_edited_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id; cmd.Parameters.Add("@bus_id", MySqlDbType.Int32).Value = bus_id; } else { cmd.Parameters.Add("@bus_created_by", MySqlDbType.Int32).Value = Classes.Globals.user_control_id; } conn.Open(); int affectedRows = Convert.ToInt32(cmd.ExecuteNonQuery()); conn.Close(); if (affectedRows > 0) { return(true); } } } } return(false); }