internal Class GetClassById(int?IdClass)
{
DbDataReader dRead;
DbCommand cmd;
Class c = null;
using (DbConnection conn = dl.Connect())
{
string query = "SELECT *" +
" FROM Classes" +
" WHERE Classes.idClass=" + IdClass +
";";
cmd = new SQLiteCommand(query);
dRead = cmd.ExecuteReader();
while (dRead.Read())
{
c = new Class(IdClass, SafeDb.SafeString(dRead["abbreviation"]), SafeDb.SafeString(dRead["idSchoolYear"]),
SafeDb.SafeString(dRead["idSchool"]));
c.PathRestrictedApplication = SafeDb.SafeString(dRead["pathRestrictedApplication"]);
c.UriWebApp = SafeDb.SafeString(dRead["uriWebApp"]);
}
dRead.Dispose();
cmd.Dispose();
}
return(c);
}
private void ReadUI()
{
if (currentAnnotation == null)
{
currentAnnotation = new StudentAnnotation();
}
currentAnnotation.IsActive = chkCurrentAnnotationActive.Checked;
currentAnnotation.IdSchoolYear = txtSchoolYear.Text;
if (txtAnnotation.Text != "")
{
currentAnnotation.IdAnnotation = SafeDb.SafeInt(txtIdAnnotation.Text);
}
else
{
currentAnnotation.IdAnnotation = null;
}
if (currentAnnotation.IdStudent != 0)
{
currentAnnotation.IdStudent = SafeDb.SafeInt(txtIdStudent.Text);
}
else
{
currentAnnotation.IdStudent = null;
}
currentAnnotation.Annotation = txtAnnotation.Text;
}
internal List <Class> GetClassesOfYear(string School, string Year)
{
DbDataReader dRead;
DbCommand cmd;
List <Class> lc = new List <Class>();
// Execute the query
using (DbConnection conn = Connect())
{
string query = "SELECT Classes.* " +
" FROM Classes" +
" WHERE idSchoolYear = '" + Year + "'" +
" ORDER BY abbreviation" +
";";
cmd = conn.CreateCommand();
cmd.CommandText = query;
dRead = cmd.ExecuteReader();
// fill the combo with this year's classes
while (dRead.Read())
{
Class c = new Class((int)dRead["idClass"],
(string)dRead["abbreviation"], Year, "dummy");
c.UriWebApp = SafeDb.SafeString(dRead["UriWebApp"]);
c.PathRestrictedApplication = SafeDb.SafeString(dRead["pathRestrictedApplication"]);
lc.Add(c);
}
dRead.Dispose();
cmd.Dispose();
}
return(lc);
}
private void FillClassData(Class Class)
{
dtClass = db.GetClassTable(Class.IdClass);
DgwClass.DataSource = dtClass;
//dgwAllievi.DataSource = db.GetClass(txtOfficialSchoolAbbreviation.Text, idSchoolYear, cmbClasses.Text);
DgwStudents.DataSource = db.GetStudentsOfClassList(TxtOfficialSchoolAbbreviation.Text,
idSchoolYear, CmbClasses.Text, true);
TxtClassDescription.Text = SafeDb.SafeString(DgwClass.Rows[DgwClass.CurrentRow.Index].Cells["desc"].Value);
currentClass = (Class)CmbClasses.SelectedItem;
TxtStartLinksFolder.Text = currentClass.PathRestrictedApplication;
}
private void DgwLinks_RowEnter(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex > -1)
{
DataRow row = ((DataTable)(DgwLinks.DataSource)).Rows[e.RowIndex];
CmbClasses.SelectedItem = (string)row["abbreviation"];
currentClass.Abbreviation = (string)row["abbreviation"];
TxtStartLink.Text = SafeDb.SafeString(row["startLink"]);
TxtLinkDescription.Text = SafeDb.SafeString(row["desc"]);
currentIdStartLink = (int)row["idStartLink"];
currentClass.IdClass = (int)row["idClass"];
}
}
private void GetClassFromRow(Class Class, DbDataReader Row)
{
if (Class == null)
{
Class = new Class();
}
Class.IdClass = (int)Row["idClass"];
Class.Abbreviation = SafeDb.SafeString(Row["abbreviation"]);
Class.IdSchool = SafeDb.SafeString(Row["idSchool"]);
Class.PathRestrictedApplication = SafeDb.SafeString(Row["pathRestrictedApplication"]);
Class.SchoolYear = SafeDb.SafeString(Row["idSchoolYear"]);
Class.UriWebApp = SafeDb.SafeString(Row["uriWebApp"]);
Class.Description = SafeDb.SafeString(Row["desc"]);
}
internal void BackupTableTsv(string TableName)
{
DbDataReader dRead;
DbCommand cmd;
string fileContent = "";
using (DbConnection conn = dl.Connect())
{
string query = "SELECT *" +
" FROM " + TableName + " ";
cmd = new SQLiteCommand(query);
cmd.Connection = conn;
dRead = cmd.ExecuteReader();
int y = 0;
while (dRead.Read())
{
// field names only in first row
if (y == 0)
{
string types = "";
for (int i = 0; i < dRead.FieldCount; i++)
{
fileContent += "\"" + dRead.GetName(i) + "\"\t";
types += "\"" + SafeDb.SafeString(dRead.GetDataTypeName(i)) + "\"\t";
}
fileContent = fileContent.Substring(0, fileContent.Length - 1) + "\r\n";
fileContent += types.Substring(0, types.Length - 1) + "\r\n";
}
// field values
string values = "";
if (dRead.GetValue(0) != null)
{
Console.Write(dRead.GetValue(0));
for (int i = 0; i < dRead.FieldCount; i++)
{
values += "\"" + SafeDb.SafeString(dRead.GetValue(i).ToString()) + "\"\t";
}
fileContent += values.Substring(0, values.Length - 1) + "\r\n";
}
else
{
}
y++;
}
TextFile.StringToFile(Commons.PathDatabase + "\\" + TableName + ".tsv", fileContent, false);
dRead.Dispose();
cmd.Dispose();
}
}
internal SchoolPeriod GetOneSchoolPeriodFromRow(DbDataReader Row)
{
SchoolPeriod p = new SchoolPeriod();
p.IdSchoolPeriodType = SafeDb.SafeString(Row["idSchoolPeriodType"]);
if (p.IdSchoolPeriodType != "N")
{
p.DateFinish = SafeDb.SafeDateTime(Row["dateFinish"]);
p.DateStart = SafeDb.SafeDateTime(Row["dateStart"]);
}
p.Name = SafeDb.SafeString(Row["name"]);
p.Desc = SafeDb.SafeString(Row["desc"]);
p.IdSchoolPeriod = SafeDb.SafeString(Row["idSchoolPeriod"]);
p.IdSchoolYear = SafeDb.SafeString(Row["idSchoolYear"]);
return(p);
}
private void BtnSaveClassData_Click(object sender, EventArgs e)
{
DataGridViewRow dgr = DgwClass.Rows[0];
int? idClass = ((Class)CmbClasses.SelectedItem).IdClass;
Class c = new Class(idClass, CmbClasses.Text, CmbSchoolYear.Text, TxtOfficialSchoolAbbreviation.Text);
//c.Description= SafeDb.SafeString(dgr.Cells["desc"].Value);
c.Description = TxtClassDescription.Text;
c.UriWebApp = SafeDb.SafeString(dgr.Cells["uriWebApp"].Value);
c.PathRestrictedApplication = SafeDb.SafeString(dgr.Cells["pathRestrictedApplication"].Value);
db.SaveClass(c);
db.SaveStudentsOfList((List <Student>)DgwStudents.DataSource, null);
FillClassData(c);
}
private Student GetStudentFromRow(DbDataReader Row)
{
Student s = new Student();
s.IdStudent = (int)Row["IdStudent"];
s.LastName = SafeDb.SafeString(Row["LastName"]);
s.FirstName = SafeDb.SafeString(Row["FirstName"]);
s.Residence = SafeDb.SafeString(Row["Residence"]);
s.Origin = SafeDb.SafeString(Row["Origin"]);
s.Email = SafeDb.SafeString(Row["Email"]);
if (!(Row["birthDate"] is DBNull))
{
s.BirthDate = SafeDb.SafeDateTime(Row["birthDate"]);
}
s.BirthPlace = SafeDb.SafeString(Row["birthPlace"]);
s.Eligible = SafeDb.SafeBool(Row["drawable"]);
s.Disabled = SafeDb.SafeBool(Row["disabled"]);
s.RevengeFactorCounter = SafeDb.SafeInt(Row["VFCounter"]);
return(s);
}
private User GetUserFromRow(DbDataReader dRead)
{
User u = null;
if (dRead.HasRows)
{
u = new User(SafeDb.SafeString(dRead["username"]),
SafeDb.SafeString(dRead["password"]));
u.Description = SafeDb.SafeString(dRead["description"]);
u.LastName = SafeDb.SafeString(dRead["lastName"]);
u.FirstName = SafeDb.SafeString(dRead["firstName"]);
u.Email = SafeDb.SafeString(dRead["email"]);
//u.Password = SafeDb.SafeString(dRead["password"]);
u.LastChange = SafeDb.SafeDateTime(dRead["lastChange"]);
u.LastPasswordChange = SafeDb.SafeDateTime(dRead["lastPasswordChange"]);
u.CreationTime = SafeDb.SafeDateTime(dRead["creationTime"]);
u.Salt = SafeDb.SafeString(dRead["salt"]);
u.IdUserCategory = SafeDb.SafeInt(dRead["idUserCategory"]);
u.IsEnabled = SafeDb.SafeBool(dRead["isEnabled"]);
}
return(u);
}
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();
}
}
