internal void EraseClassFromClasses(GestioneClass Class)
{
//EraseAllStudentsOfAClass(Class);
using (DbConnection conn = dl.Connect())
{
// delete all the references in link table between students and classes
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Classes_Students" +
" WHERE Classes_Students.idClass=" + Class.IdClass +
";";
cmd.ExecuteNonQuery();
// erase class from Classes_SchoolSubjects
cmd.CommandText = "DELETE FROM Classes_SchoolSubjects" +
" WHERE Classes_SchoolSubjects.idClass=" + Class.IdClass +
";";
cmd.ExecuteNonQuery();
// erase class from Classes_Tests
cmd.CommandText = "DELETE FROM Classes_Tests" +
" WHERE Classes_Tests.idClass=" + Class.IdClass +
";";
cmd.ExecuteNonQuery();
// erase class from table Classes
cmd.CommandText = "DELETE FROM Classes" +
" WHERE Classes.idClass=" + Class.IdClass +
";";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
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();
}
}
private int NextKey(string Table, string Id)
{
int nextId;
using (DbConnection conn = dl.Connect())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT MAX(" + Id + ") FROM " + Table + ";";
var firstColumn = cmd.ExecuteScalar();
if (firstColumn != DBNull.Value)
{
nextId = int.Parse(firstColumn.ToString()) + 1;
}
else
{
nextId = 1;
}
cmd.Dispose();
}
return(nextId);
}
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 List <SchoolPeriod> GetSchoolPeriods(string IdSchoolYear)
{
List <SchoolPeriod> l = new List <SchoolPeriod>();
using (DbConnection conn = dl.Connect())
{
DbDataReader dRead;
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * " +
"FROM SchoolPeriods " +
"WHERE idSchoolYear=" + IdSchoolYear +
" OR IdSchoolYear IS null OR IdSchoolYear=''" +
";";
dRead = cmd.ExecuteReader();
while (dRead.Read())
{
SchoolPeriod p = GetOneSchoolPeriodFromRow(dRead);
l.Add(p);
}
}
return(l);
}
