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 List <SchoolPeriod> GetSchoolPeriodsOfDate(DateTime Date)
{
List <SchoolPeriod> l = new List <SchoolPeriod>();
using (DbConnection conn = dl.Connect())
{
DbDataReader dRead;
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT *" +
" FROM SchoolPeriods" +
" WHERE " + SqlVal.SqlDate(Date) +
" BETWEEN dateStart and dateFinish" +
";";
dRead = cmd.ExecuteReader();
while (dRead.Read())
{
SchoolPeriod p = GetOneSchoolPeriodFromRow(dRead);
l.Add(p);
}
}
return(l);
}
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();
}
}
