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 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 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 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);
}
