internal Class GetClassOfStudent(string IdSchool, string SchoolYearCode, Student Student) { Class c = new Class(); using (DbConnection conn = dl.Connect()) { DbDataReader dRead; DbCommand cmd = conn.CreateCommand(); cmd = conn.CreateCommand(); cmd.CommandText = "SELECT Classes.*" + " FROM Classes" + " JOIN Classes_Students ON Classes.idClass = Classes_Students.idClass" + " JOIN Students ON Students.idStudent = Classes_Students.idStudent" + " WHERE" + " Classes.idSchool = '" + SqlVal.SqlString(IdSchool) + "'" + " AND Classes.idSchoolYear = '" + SqlVal.SqlString(SchoolYearCode) + "'" + " AND Students.IdStudent = " + Student.IdStudent + ";"; dRead = cmd.ExecuteReader(); while (dRead.Read()) { GetClassFromRow(c, dRead); break; // just the first! } } return(c); }
internal void SaveClass(Class Class) { //bool leaveConnectionOpen = true; //if (conn == null) //{ // conn = dl.Connect(); // leaveConnectionOpen = false; //} using (DbConnection conn = dl.Connect()) { DbCommand cmd = conn.CreateCommand(); string query = "UPDATE Classes" + " SET" + " idClass=" + Class.IdClass + "" + ",idSchoolYear='" + SqlVal.SqlString(Class.SchoolYear) + "'" + ",idSchool='" + SqlVal.SqlString(Class.IdSchool) + "'" + ",abbreviation='" + SqlVal.SqlString(Class.Abbreviation) + "'" + ",desc='" + SqlVal.SqlString(Class.Description) + "'" + ",uriWebApp='" + Class.UriWebApp + "'" + ",pathRestrictedApplication='" + SqlVal.SqlString(Class.PathRestrictedApplication) + "'" + " WHERE idClass=" + Class.IdClass + ";"; cmd.CommandText = query; cmd.ExecuteNonQuery(); cmd.Dispose(); } }
internal DataTable GetClassDataTable(string IdSchool, string IdSchoolYear, string ClassAbbreviation) { DataTable t; using (DbConnection conn = dl.Connect()) { DataAdapter dAdapter; DataSet dSet = new DataSet(); string query = "SELECT DISTINCT registerNumber, Classes.idSchool, Classes.idSchoolYear, " + "Classes.abbreviation, Students.*" + " FROM Students, Classes_Students, Classes" + " WHERE Students.idStudent = Classes_Students.idStudent AND Classes.idClass = Classes_Students.idClass" + " AND Classes.idSchool = '" + SqlVal.SqlString(IdSchool) + "' AND Classes.idSchoolYear = '" + SqlVal.SqlString(IdSchoolYear) + "' AND Classes.abbreviation = '" + SqlVal.SqlString(ClassAbbreviation) + "' ORDER BY Students.lastName, Students.firstName;"; dAdapter = new SQLiteDataAdapter(query, (System.Data.SQLite.SQLiteConnection)conn); dAdapter.Fill(dSet); t = dSet.Tables[0]; dAdapter.Dispose(); dSet.Dispose(); } return(t); }
internal Class GetClass(string IdSchool, string IdSchoolYear, string ClassAbbreviation) { Class c = new Class(); using (DbConnection conn = dl.Connect()) { DbDataReader dRead; DbCommand cmd = conn.CreateCommand(); cmd = conn.CreateCommand(); string query = "SELECT Classes.*" + " FROM Classes" + " WHERE" + " Classes.idSchoolYear = '" + SqlVal.SqlString(IdSchoolYear) + "'" + " AND Classes.abbreviation = '" + SqlVal.SqlString(ClassAbbreviation) + "'"; if (IdSchool != null && IdSchool != "") { query += " AND Classes.idSchool = '" + SqlVal.SqlString(IdSchool) + "'"; } query += ";"; cmd.CommandText = query; dRead = cmd.ExecuteReader(); while (dRead.Read()) { GetClassFromRow(c, dRead); break; // just the first! } } return(c); }
internal List <Tag> GetTagsContaining(string Pattern) { DbDataReader dRead; DbCommand cmd; List <Tag> TagList = new List <Tag>(); using (DbConnection conn = dl.Connect()) { string query = "SELECT *" + " FROM Tags" + " WHERE Tag LIKE '%" + SqlVal.SqlString(Pattern) + "%'" + ";"; cmd = new SQLiteCommand(query); cmd.Connection = conn; dRead = cmd.ExecuteReader(); while (dRead.Read()) { Tag t = new Tag(); t.IdTag = (int)dRead["IdTag"]; t.TagName = (string)dRead["tag"]; t.Desc = (string)dRead["Desc"]; TagList.Add(t); } dRead.Dispose(); cmd.Dispose(); } return(TagList); }
internal void CreateUser(User User) { using (DbConnection conn = Connect()) { // check if username is existing DbCommand cmd = conn.CreateCommand(); // !!!! TODO !!!! // create row in table string?now = SqlVal.SqlDate(DateTime.Now); cmd.CommandText = "INSERT INTO Users " + "(username, lastName, firstName, email," + "password,creationTime,lastChange,lastPasswordChange,salt,idUserCategory,isEnabled)" + "Values " + "('" + SqlVal.SqlString(User.Username) + "','" + SqlVal.SqlString(User.LastName) + "','" + SqlVal.SqlString(User.FirstName) + "','" + SqlVal.SqlString(User.Email) + "','" + SqlVal.SqlString(User.Password) + "'," + now + "," + now + "," + now + ",'" + SqlVal.SqlString(User.Salt) + "','" + SqlVal.SqlString(User.IdUserCategory.ToString()) + "', TRUE" + ");"; cmd.ExecuteNonQuery(); cmd.Dispose(); } }
internal void ChangePassword(User User) { using (DbConnection conn = Connect()) { DbCommand cmd = conn.CreateCommand(); cmd.CommandText = "UPDATE Users" + " Set" + " password='******'," + " lastPasswordChange=" + SqlVal.SqlDate(DateTime.Now) + "," + " salt='" + SqlVal.SqlString(User.Salt) + "'" + " WHERE username='******'" + ";"; cmd.ExecuteNonQuery(); cmd.Dispose(); } }
internal int?SaveStartLink(int?IdStartLink, int?IdClass, string SchoolYear, string StartLink, string Desc) { DbCommand cmd = null; try { using (DbConnection conn = dl.Connect()) { cmd = conn.CreateCommand(); if (IdStartLink != null && IdStartLink != 0) { cmd.CommandText = "UPDATE Classes_StartLinks" + " SET" + " idStartLink=" + IdStartLink + ",idClass=" + IdClass + "" + ",startLink='" + SqlVal.SqlString(StartLink) + "'" + ",desc='" + SqlVal.SqlString(Desc) + "'" + " WHERE idStartLink=" + IdStartLink + ";"; } else { IdStartLink = NextKey("Classes_StartLinks", "IdStartLink"); cmd.CommandText = "INSERT INTO Classes_StartLinks" + " (idStartLink,idClass,startLink,desc)" + " VALUES " + "(" + IdStartLink + "," + IdClass + ",'" + SqlVal.SqlString(StartLink) + "'" + ",'" + SqlVal.SqlString(Desc) + "'" + ");"; } cmd.ExecuteNonQuery(); cmd.Dispose(); } } catch (Exception ex) { Commons.ErrorLog("DbLayer.SaveStartLink: " + ex.Message, true); IdStartLink = null; cmd.Dispose(); } return(IdStartLink); }
internal List <Student> GetStudentsOfClassList(string Scuola, string Anno, string SiglaClasse, bool IncludeNonActiveStudents) { DbDataReader dRead; DbCommand cmd; List <Student> ls = new List <Student>(); using (DbConnection conn = dl.Connect()) { string query = "SELECT registerNumber, Classes.idSchoolYear, " + "Classes.abbreviation, Classes.idClass, Classes.idSchool, " + "Students.*" + " FROM Students" + " JOIN Classes_Students ON Students.idStudent=Classes_Students.idStudent" + " JOIN Classes ON Classes.idClass=Classes_Students.idClass" + " WHERE Classes.idSchoolYear = '" + SqlVal.SqlString(Anno) + "'" + " AND Classes.abbreviation = '" + SqlVal.SqlString(SiglaClasse) + "'"; if (!IncludeNonActiveStudents) { query += " AND (Students.disabled = 0 OR Students.disabled IS NULL)"; } if (Scuola != null && Scuola != "") { query += " AND Classes.idSchool='" + Scuola + "'"; } query += " ORDER BY Students.LastName, Students.FirstName"; query += ";"; cmd = conn.CreateCommand(); cmd.CommandText = query; dRead = cmd.ExecuteReader(); while (dRead.Read()) { Student s = GetStudentFromRow(dRead); s.Class = (string)dRead["abbreviation"]; s.IdClass = (int)dRead["idClass"]; ls.Add(s); } dRead.Dispose(); cmd.Dispose(); } return(ls); }
internal void UpdateUser(User User) { using (DbConnection conn = Connect()) { DbCommand cmd = conn.CreateCommand(); cmd.CommandText = "UPDATE Users" + " Set" + " description='" + SqlVal.SqlString(User.Description) + "'," + " lastName='" + SqlVal.SqlString(User.LastName) + "'," + " firstName='" + SqlVal.SqlString(User.FirstName) + "'," + " email='" + SqlVal.SqlString(User.Email) + "'," + //" password="******"'," + " lastChange=" + SqlVal.SqlDate(DateTime.Now) + "," + //" lastPasswordChange=" + SqlVal.SqlDate(DateTime.Now) + "," + //" creationTime=" + SqlVal.SqlDate(User.CreationTime) + "," + " salt='" + SqlVal.SqlString(User.Salt) + "'," + " isEnabled=" + SqlVal.SqlBool(User.IsEnabled) + " idUserCategory=" + SqlVal.SqlInt(User.IdUserCategory) + " WHERE username='******'" + ";"; cmd.ExecuteNonQuery(); cmd.Dispose(); } }
internal void UpdateUserOverride(string username, string lastname, string firstname, string password, string email, string description, DateTime last, DateTime lastpassw, DateTime creation, string salt, bool isenabled, int idusercateogry) { using (DbConnection conn = Connect()) { DbCommand cmd = conn.CreateCommand(); cmd.CommandText = "UPDATE Users" + " Set" + " description='" + SqlVal.SqlString(description) + "'," + " lastName='" + SqlVal.SqlString(lastname) + "'," + " firstName='" + SqlVal.SqlString(firstname) + "'," + " email='" + SqlVal.SqlString(email) + "'," + " password="******"'," + " lastChange=" + SqlVal.SqlDate(last) + "," + " lastPasswordChange=" + SqlVal.SqlDate(lastpassw) + "," + " creationTime=" + SqlVal.SqlDate(creation) + "," + " salt='" + SqlVal.SqlString(salt) + "'," + " isEnabled=" + SqlVal.SqlBool(isenabled) + " idUserCategory=" + SqlVal.SqlInt(idusercateogry) + " WHERE username='******'" + ";"; cmd.ExecuteNonQuery(); cmd.Dispose(); } }
internal int CreateClassAndStudents(string[,] StudentsData, string ClassAbbreviation, string ClassDescription, string SchoolYear, string OfficialSchoolAbbreviation, bool LinkPhoto) { // creation of a new class in the Classes table // finds a key for the new class int idClass = NextKey("Classes", "idClass"); using (DbConnection conn = dl.Connect()) { DbCommand cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO Classes " + "(idClass, Desc, idSchoolYear, idSchool, abbreviation) " + "Values (" + idClass + ",'" + SqlVal.SqlString(ClassDescription) + "','" + SqlVal.SqlString(SchoolYear) + "','" + SqlVal.SqlString(OfficialSchoolAbbreviation) + "','" + SqlVal.SqlString(ClassAbbreviation) + "'" + ");"; cmd.ExecuteNonQuery(); // find the key for next student int idNextStudent = NextKey("Students", "idStudent"); // find the key for next picture int idNextPhoto = NextKey("StudentsPhotos", "idStudentsPhoto"); // add the student to the students' table // start from the second row of the file, first row is descriptions for (int riga = 1; riga < StudentsData.GetLength(0); riga++) { int rigap1 = riga + 1; // create new student cmd.CommandText = "INSERT INTO Students " + "(idStudent, lastName, firstName, residence, origin, email, birthDate, birthPlace) " + "Values (" + "'" + idNextStudent + "','" + SqlVal.SqlString(StudentsData[riga, 1]) + "','" + SqlVal.SqlString(StudentsData[riga, 2]) + "','" + SqlVal.SqlString(StudentsData[riga, 3]) + "','" + SqlVal.SqlString(StudentsData[riga, 4]) + "','" + SqlVal.SqlString(StudentsData[riga, 5]) + "'," + SqlVal.SqlDate(StudentsData[riga, 6]) + ",'" + SqlVal.SqlString(StudentsData[riga, 7]) + "'" + ");"; cmd.ExecuteNonQuery(); // aggiunge lo studente alla classe cmd.CommandText = "INSERT INTO Classes_Students " + "(idClass, idStudent, registerNumber) " + "Values ('" + idClass + "','" + idNextStudent + "','" + rigap1.ToString() + "'" + ");"; cmd.ExecuteNonQuery(); if (LinkPhoto) { // aggiunge la foto alle foto cmd.CommandText = "INSERT INTO StudentsPhotos " + "(idStudentsPhoto, photoPath)" + "Values " + "('" + idNextPhoto + "','" + SqlVal.SqlString(SchoolYear) + SqlVal.SqlString(ClassAbbreviation) + "\\" + SqlVal.SqlString(StudentsData[riga, 1]) + "_" + SqlVal.SqlString(StudentsData[riga, 2]) + "_" + SqlVal.SqlString(ClassAbbreviation) + SqlVal.SqlString(SchoolYear) + ".jpg" + // TODO mettere l'estensione del file che c'รจ effettivamente "');"; // relative path. Home path will be added at visualization time cmd.ExecuteNonQuery(); // add the picture to the link table cmd.CommandText = "INSERT INTO StudentsPhotos_Students " + "(idStudentsPhoto, idStudent, idSchoolYear) " + "Values (" + idNextPhoto + "," + idNextStudent + ",'" + SqlVal.SqlString(SchoolYear) + "');"; cmd.ExecuteNonQuery(); idNextPhoto++; } idNextStudent++; } cmd.Dispose(); } return(idClass); }
internal void RestoreTableXml(string TableName, bool EraseBefore) { DataSet dSet = new DataSet(); DataTable t = null; dSet.ReadXml(Commons.PathDatabase + "\\" + TableName + ".xml", XmlReadMode.ReadSchema); t = dSet.Tables[0]; if (t.Rows.Count == 0) { return; } using (DbConnection conn = dl.Connect()) { DbCommand cmd; cmd = conn.CreateCommand(); if (EraseBefore) { cmd.CommandText = "DELETE FROM " + TableName + ";"; cmd.ExecuteNonQuery(); } cmd.CommandText = "INSERT INTO " + TableName + "("; // column names DataRow r = t.Rows[0]; foreach (DataColumn c in t.Columns) { cmd.CommandText += c.ColumnName + ","; } cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); cmd.CommandText += ")VALUES"; // row values foreach (DataRow row in t.Rows) { cmd.CommandText += "("; foreach (DataColumn c in t.Columns) { switch (Type.GetTypeCode(c.DataType)) { case TypeCode.String: case TypeCode.Char: { cmd.CommandText += "'" + SqlVal.SqlString(row[c.ColumnName].ToString()) + "',"; break; }; case TypeCode.DateTime: { DateTime?d = SafeDb.SafeDateTime(row[c.ColumnName]); cmd.CommandText += "'" + ((DateTime)(d)).ToString("yyyy-MM-dd_HH.mm.ss") + "',"; break; } default: { if (!(row[c.ColumnName] is DBNull)) { cmd.CommandText += row[c.ColumnName] + ","; } else { cmd.CommandText += "0,"; } break; } } } cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); cmd.CommandText += "),"; } cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); cmd.CommandText += ";"; cmd.ExecuteNonQuery(); dSet.Dispose(); t.Dispose(); cmd.Dispose(); } }