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); }