public static bool AddPupil(string name, string surname, string email, string group, string entranceYear) { using (DBCommunication dbcom = new DBCommunication()) { var result = dbcom.InvokeEvent(true, "INSERT INTO `pupils` (`name`, `surname`, `email`, `groupID`, `entrance_year`) VALUES" + "(@name, @surname, @email, @group, @entrance_year);", new Dictionary <string, string> { { "@name", name }, { "@surname", surname }, { "@email", email }, { "@group", group }, { "@entrance_year", entranceYear } }, false); var id = dbcom.InvokeEvent(true, "SELECT `id` FROM `pupils` WHERE `name` = @name AND `surname` = @surname;", new Dictionary <string, string> { { "@name", name }, { "@surname", surname } }, true); dbcom.InvokeEvent(true, "INSERT INTO `progress` (`pupilId`) VALUES (@pupilId);", new Dictionary <string, string> { { "@pupilId", id[0][0] } }, false); var amount = dbcom.InvokeEvent(false, $"SELECT `amount_of_pupils` FROM `groups` WHERE `id` = {group};", null, true); dbcom.InvokeEvent(false, $"UPDATE `groups` SET `amount_of_pupils`={amount[0][0]}+1 WHERE `id`={group};", null, false); if (!result.Any()) { return(true); } } return(false); }
public static bool AddSubject(string subject) { bool returnResult = false; using (DBCommunication dbcom = new DBCommunication()) { var result = dbcom.InvokeEvent(true, "INSERT INTO `subjects` (`subject_name`) VALUES (@subject)", new Dictionary <string, string> { { "@subject", subject } }, false); if (!result.Any()) { result = dbcom.InvokeEvent(false, $"ALTER TABLE `progress` ADD COLUMN {subject} INT DEFAULT 0;", null, false); if (!result.Any()) { returnResult = true; } } } return(returnResult); }
public static Dictionary <string, string> SelectSubjectList() { Dictionary <string, string> returnDictionary = new Dictionary <string, string>(); using (DBCommunication db = new DBCommunication()) { var result = db.InvokeEvent(false, "SELECT * FROM `subjects`;", null, true); foreach (var list in result) { returnDictionary.Add(list[0], list[1]); } } return(returnDictionary); }
public static DataTable SelectAllFromTable(string table) { List <List <string> > result; List <string> columnNames; using (DBCommunication db = new DBCommunication()) { result = db.InvokeEvent(false, $"SELECT * FROM `{table}`;", null, true); columnNames = db.GetTableColumnsNames(table); } if (result != null && result.Any()) { DataTable dTable = new DataTable(); foreach (var t in columnNames) { DataColumn column = new DataColumn { DataType = typeof(string), ColumnName = t }; dTable.Columns.Add(column); } foreach (var rowList in result) { DataRow row = dTable.NewRow(); for (int i = 0; i < rowList.Count; i++) { row[columnNames[i]] = rowList[i]; } dTable.Rows.Add(row); } return(dTable); } return(null); }
public static bool AddCabinet(int cabinetNumber) { List <List <string> > result; using (DBCommunication dbcom = new DBCommunication()) { result = dbcom.InvokeEvent(true, "INSERT INTO `cabinets` (`cabinet_number`) VALUES (@cabinet)", new Dictionary <string, string> { { "@cabinet", cabinetNumber.ToString() } }, false); } if (result.Count == 0) { return(true); } return(false); }
public static bool AddTeacher(string name, string surname, string subject, string cabinet) { using (DBCommunication dbcom = new DBCommunication()) { var result = dbcom.InvokeEvent(true, "INSERT INTO `teachers` (`name`, `surname`, `subjectID`, `cabinetID`) VALUES" + "(@name, @surname, @subject, @cabinet);", new Dictionary <string, string> { { "@name", name }, { "@surname", surname }, { "@subject", subject }, { "@cabinet", cabinet } }, false); if (!result.Any()) { return(true); } } return(false); }
public static bool DeletePupilFromDb(string pupilId) { bool isDeleted = false; using (DBCommunication dbcom = new DBCommunication()) { var group = dbcom.InvokeEvent(false, $"SELECT `groupID` FROM `pupils` WHERE `id` = {pupilId};", null, true); dbcom.InvokeEvent(false, $"DELETE FROM `progress` WHERE `pupilID` = {pupilId};", null, false); dbcom.InvokeEvent(false, $"DELETE FROM `pupils` WHERE `id` = {pupilId};", null, false); var amount = dbcom.InvokeEvent(false, $"SELECT `amount_of_pupils` FROM `groups` WHERE `id` = {group[0][0]};", null, true); dbcom.InvokeEvent(false, $"UPDATE `groups` SET `amount_of_pupils`={amount[0][0]}-1 WHERE `id`={group[0][0]};", null, false); if (dbcom.InvokeEvent(false, $"SELECT * FROM `pupils` WHERE `id` = {pupilId};", null, true).Count == 0 && dbcom.InvokeEvent(false, $"SELECT * FROM `progress` WHERE `pupilID` = {pupilId};", null, true) .Count == 0) { isDeleted = true; } } return(isDeleted); }