public Boolean Update()
{
if (!this.validate())
{
return(false);
}
this.dbConnection = AppContainer.GetDatabaseConnection();
this.dbConnection.Open();
try
{
string sql = "UPDATE student SET Names='" + this.names + "', " +
"Street='" + this.street + "', " +
"City='" + this.city + "', " +
"ZipCode='" + this.zipCode + "', " +
"BirthDate='" + this.birthDate + "', " +
"StudentClass='" + this.studentClass + "' WHERE Id=" + this.id.ToString();
SQLiteCommand command = new SQLiteCommand(sql, this.dbConnection);
Int32 affectedRows = command.ExecuteNonQuery();
SimpleMessage result = new SimpleMessage("Uczen zaktualizowany. Zmienionych wierszy: " + affectedRows, "Sukces!");
return(true);
}
catch (SQLiteException exception)
{
SimpleMessage result = new SimpleMessage(exception.ToString(), "Blad aktualizacji ucznia");
return(false);
}
finally
{
this.dbConnection.Close();
}
}
public Boolean Save()
{
if (!this.validate())
{
return(false);
}
this.dbConnection = AppContainer.GetDatabaseConnection();
this.dbConnection.Open();
try
{
string sql = "INSERT INTO student (Names, Street, City, ZipCode, BirthDate, StudentClass) VALUES(" +
"'" + this.names + "'," +
"'" + this.street + "'," +
"'" + this.city + "'," +
"'" + this.zipCode + "'," +
"'" + this.birthDate + "'," +
"'" + this.studentClass + "')";
SQLiteCommand command = new SQLiteCommand(sql, this.dbConnection);
Int32 affectedRows = command.ExecuteNonQuery();
SimpleMessage result = new SimpleMessage("Uczen zapisany. Zmienionych wierszy: " + affectedRows, "Sukces!");
return(true);
}catch (SQLiteException exception)
{
SimpleMessage result = new SimpleMessage(exception.ToString(), "Blad zapisu ucznia");
return(false);
}
finally
{
this.dbConnection.Close();
}
}
public AddStudentForm(MainWindow MainWindow)
{
InitializeComponent();
this.mainWindow = MainWindow;
this.mainWindow.Hide();
this.studentClassList.DataSource = StudentClassProvider.GetClasess();
this.dbConnection = AppContainer.GetDatabaseConnection();
}
private static SQLiteDataReader GetSubjects()
{
SQLiteConnection db = AppContainer.GetDatabaseConnection();
db.Open();
string sql = "SELECT * FROM subject";
SQLiteCommand command = new SQLiteCommand(sql, db);
return(command.ExecuteReader());
}
public Marks(string studentName, string studentId, string subjectId, RunningLesson runningLessonWindow)
{
InitializeComponent();
this.studentName.Text = studentName;
this.studentId = studentId;
this.subjectId = subjectId;
this.parentWindow = runningLessonWindow;
this.db = AppContainer.GetDatabaseConnection();
this.getAllMarks();
}
public static Boolean Remove(string studentId)
{
SQLiteConnection db = AppContainer.GetDatabaseConnection();
db.Open();
try
{
string sql = "DELETE FROM student WHERE Id=" + studentId;
SQLiteCommand command = new SQLiteCommand(sql, db);
command.ExecuteNonQuery();
return(true);
}catch (SQLiteException error)
{
SimpleMessage result = new SimpleMessage(error.ToString(), "Blad kasowania ucznia");
return(false);
}
finally {
db.Close();
}
}
public Boolean Update()
{
this.dbConnection = AppContainer.GetDatabaseConnection();
this.dbConnection.Open();
try
{
string sql = "UPDATE subject SET SubjectName='" + this.SubjectName + "' WHERE Id=" + this.Id.ToString();
SQLiteCommand command = new SQLiteCommand(sql, this.dbConnection);
Int32 affectedRows = command.ExecuteNonQuery();
SimpleMessage result = new SimpleMessage("Przedmiot zaktualizowany. Zmienionych wierszy: " + affectedRows, "Sukces!");
return(true);
}
catch (SQLiteException exception)
{
SimpleMessage result = new SimpleMessage(exception.ToString(), "Blad aktualizacji przedmiotu");
return(false);
}
finally
{
this.dbConnection.Close();
}
}
public Boolean Save()
{
this.dbConnection = AppContainer.GetDatabaseConnection();
this.dbConnection.Open();
try
{
string sql = "INSERT INTO subject (SubjectName) VALUES(" +
"'" + this.SubjectName + "')";
SQLiteCommand command = new SQLiteCommand(sql, this.dbConnection);
Int32 affectedRows = command.ExecuteNonQuery();
SimpleMessage result = new SimpleMessage("Przedmiot zapisany. Zmienionych wierszy: " + affectedRows, "Sukces!");
return(true);
}
catch (SQLiteException exception)
{
SimpleMessage result = new SimpleMessage(exception.ToString(), "Blad zapisu przedmiotu");
return(false);
}
finally
{
this.dbConnection.Close();
}
}
public void getAllStudentsWithMarks()
{
SQLiteConnection db = AppContainer.GetDatabaseConnection();
db.Open();
// Clear the air
this.studentsList.Items.Clear();
try
{
// Get SubjectId
string subjectIdSql = "SELECT Id FROM subject WHERE SubjectName=\"" + this.subjectName + "\" LIMIT 1";
SQLiteCommand subjectCommand = new SQLiteCommand(subjectIdSql, db);
SQLiteDataReader subjectReader = subjectCommand.ExecuteReader();
this.subjectId = subjectReader["Id"].ToString();
string sql = "SELECT s.Id,s.Names,sm.MarkType,sm.Mark FROM student s" +
" LEFT JOIN student_mark sm ON s.Id=sm.StudentId WHERE (sm.SubjectId=" + this.subjectId + " OR sm.SubjectId IS NULL)" +
" AND s.StudentClass=\"" + this.className + "\"";
SQLiteCommand command = new SQLiteCommand(sql, db);
SQLiteDataReader reader = command.ExecuteReader();
Dictionary <string, List <string> > marks = new Dictionary <string, List <string> >();
Dictionary <string, string> IdToNamesMap = new Dictionary <string, string>();
while (reader.Read())
{
string Id = reader["Id"].ToString();
if (!IdToNamesMap.ContainsKey(Id))
{
IdToNamesMap.Add(Id, reader["Names"].ToString());
}
if (!marks.ContainsKey(Id))
{
marks.Add(Id, new List <string>());
}
string markType = reader["MarkType"].ToString();
string mark = reader["mark"].ToString();
string markInfo = "";
if (markType.Length > 0 && mark.Length > 0)
{
markInfo = markType + ":" + mark;
}
marks[Id].Add(
markInfo
);
}
foreach (KeyValuePair <string, List <string> > mark in marks)
{
string[] row = { mark.Key, IdToNamesMap[mark.Key], string.Join(",", mark.Value.ToArray()) };
ListViewItem item = new ListViewItem(row);
this.studentsList.Items.Add(item);
}
this.studentsList.FullRowSelect = true;
this.studentsList.View = View.Details;
}
catch (SQLiteException exception)
{
SimpleMessage sm = new SimpleMessage(exception.ToString());
}finally
{
db.Close();
}
}
public MainWindow()
{
InitializeComponent();
this.dbConnection = AppContainer.GetDatabaseConnection();
}
