//GET
public Class Get(int id)
{
Class a = null;
OracleCommand cmd = null;
string query = @"SELECT C.idClass FROM Class C WHERE C.idClass = :classId";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":classId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Class(int.Parse(dr[0].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
//INSERT TEACHER SUBJECTS
public void InsertTeacherSubject(Class c)
{
string queryClass = @" UPDATE CLASS SET TEACHERID= :teacher, STATUS=2 WHERE IDCLASS= :class";
try
{
int i = 1;
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Add Class to Teacher.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(1);
OracleParameter[] parameters1 = new OracleParameter[2];
cmds[0].CommandText = queryClass;
parameters1[0] = new OracleParameter(":teacher", c.TeacherId);
parameters1[1] = new OracleParameter(":class", c.IdClass);
cmds[0].Parameters.AddRange(parameters1);
cmds[0].CommandText = queryClass;
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Dele Class by" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Dele Class({1}).", DateTime.Now, ex.Message));
}
}
public void DeleteTransaction(int idsub)
{
string queryClass = @"UPDATE Class SET status=0 WHERE idClass= :class";
try
{
int i = 1;
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Del Class.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(1);
OracleParameter[] parameters1 = new OracleParameter[1];
cmds[0].CommandText = queryClass;
parameters1[0] = new OracleParameter(":class", idsub);
cmds[0].Parameters.AddRange(parameters1);
cmds[0].CommandText = queryClass;
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Dele Class by" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Dele Class({1}).", DateTime.Now, ex.Message));
}
}
public DataTable SelectTeacherAdd(string like)
{
string query = @"SELECT C.idClass,M.matterName SubjectName,CONCAT(CO.numberCourse,CO.letterCourse) Course,
(select listagg(DAYSS, ', ') within group (order by DAYSS)
FROM (SELECT DISTINCT S.DAY DAYSS FROM ClassSchedules CSS
INNER JOIN Schedules S ON S.schedulesid= CSS.schedulesid
WHERE CSS.idClass = C.idClass)) Days
FROM Class C INNER JOIN Matter M ON C.idMatter=M.idMatter
INNER JOIN Course CO ON CO.idCourse=C.idCourse
WHERE (M.MATTERNAME LIKE :like)
OR CO.numberCourse||CO.letterCourse LIKE :like
OR M.matterName||CO.numberCourse||CO.letterCourse LIKE :like
OR CO.numberCourse||CO.letterCourse||' '||M.matterName LIKE :like ) AND C.status=1";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter("@like", "%" + like + "%");
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public void InsertTransaction(Model.Dosage d)
{
string queryUpdate = @"UPDATE DOSAGE SET STATUS=0";
string queryInsert = @"INSERT INTO DOSAGE(NROAUTHORIZATION,DEADLINE,DOSAGEKEY)
VALUES(:NROAUTHORIZATION,ADD_MONTHS(CURRENT_TIMESTAMP,6),:DOSAGEKEY)";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Insert New Dosage.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
cmds[0].CommandText = queryUpdate;
cmds[1].CommandText = queryInsert;
cmds[1].Parameters.Add(new OracleParameter(":NROAUTHORIZATION", d.NroAuthorization));
cmds[1].Parameters.Add(new OracleParameter(":DOSAGEKEY", d.DosageKey));
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Insert Dosage Succesfuly.", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Add Dosage({1}).", DateTime.Now, ex.Message));
}
}
public Dosage Get()
{
Dosage a = new Dosage();
OracleCommand cmd = null;
string query = @"SELECT IDDOSAGE,NROAUTHORIZATION,DOSAGEKEY,COALESCE(FINALNUMBER,0) FROM DOSAGE WHERE STATUS>0";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a.IdDosage = int.Parse(dr[0].ToString());
a.NroAuthorization = long.Parse(dr[1].ToString());
a.DosageKey = dr[2].ToString();
a.FinalNumber = int.Parse(dr[2].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public DataTable SelectTeacherAdd(int id)
{
string query = @"SELECT DISTINCT C.idClass,M.matterName SubjectName,CO.numberCourse||CO.letterCourse Course,
(select listagg(DAYSS, ', ') within group (order by DAYSS)
FROM (SELECT DISTINCT S.DAY DAYSS FROM ClassSchedules CSS
INNER JOIN Schedules S ON S.schedulesid= CSS.schedulesid
WHERE CSS.idClass = C.idClass)) Days
FROM Class C INNER JOIN ClassSchedules CS ON CS.idClass=C.idClass
INNER JOIN Matter M ON M.idMatter=C.idMatter
INNER JOIN Course CO ON CO.idCourse=C.idCourse
WHERE C.status=1 AND ( C.idClass NOT IN
(SELECT DISTINCT CS.idClass FROM ClassSchedules CS INNER JOIN Class C ON C.idClass=CS.idClass
WHERE CS.schedulesid IN(SELECT CS.schedulesid
FROM Teacher T
INNER JOIN Class CL ON CL.teacherid=T.teacherid
INNER JOIN ClassSchedules CS ON CS.idClass=CL.idClass
WHERE T.teacherid=:Teacher)))";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":Teacher", id);
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public DataTable Selectjue(int idcourse)
{
string query = @"SELECT schedulesid,
CASE WHEN schedulesid NOT IN
(SELECT CS.schedulesid FROM ClassSchedules CS
INNER JOIN Class C ON C.idClass=CS.idClass
INNER JOIN Course CO ON CO.idCourse=C.idCourse
WHERE CO.idCourse= :course AND C.status>0)
THEN 'O'
ELSE 'X'
END AS TH,hourStart,hourFinish
FROM Schedules
WHERE day='Thursday'";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":course", idcourse);
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public Payer Get(string id)
{
Payer a = new Payer();
OracleCommand cmd = null;
string query = @"SELECT IDPAYER ,NIT,BUSINESSNAME
FROM PAYER WHERE NIT=:NIT";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":NIT", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a.IdPayer = int.Parse(dr[0].ToString());
a.Nit = dr[1].ToString();
a.BusinessName = dr[2].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
//SelectDis
public DataTable SelectDis()
{
string query = @" SELECT P.PersonId,
P.names Names,
P.lastName LastName,
COALESCE(P.secondLastName,'') SecondLastName,
P.addres Address,
P.phone Phone,
P.birthDate BirthDate,
P.gender Gender,
P.status Status,
P.registrationDate Registration,
NVL(TO_CHAR(P.updateDate),'1900-01-01') UpdateDate,
NVL(TO_CHAR(P.startDate),'1900-01-01') StartDate,
NVL(TO_CHAR(P.finishDate),'1900-01-01') FinishDate,
P.email Mail,
P.latitude Latitude,
P.longitude Longitude,
(SELECT C.CityName||'-'||PR.provinceName||','||T.townName
FROM Town T INNER JOIN Province PR ON PR.ProvinceId=T.ProvinceId
INNER JOIN City C ON C.CityId=PR.CityId WHERE P.TownId=T.TownId) Location
FROM Person P
INNER JOIN Teacher T ON T.PersonId = P.Personid
INNER JOIN USERACCOUNT U ON U.Personid=P.Personid WHERE P.status=0 AND U.role='P'";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public Person GetPass(string user)
{
Person a = null;
OracleCommand cmd = null;
string query = @"SELECT P.email,U.UserId FROM Users U INNER JOIN Person P ON P.Personid=U.Personid WHERE U.userName= :User";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":User", user);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Person(dr[0].ToString(),int.Parse(dr[1].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return a;
}
public Town GetPlace(byte id)
{
Town a = null;
OracleCommand cmd = null;
string query = @"SELECT TownId, townName,ProvinceId FROM Town WHERE TownId= :townName";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":townName", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Town(byte.Parse(dr[0].ToString()), dr[1].ToString(), byte.Parse(dr[2].ToString()), byte.Parse(dr[3].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public DataTable SelectSpecificStudentSchedule(int iduser, string day)
{
string query = @"SELECT
COALESCE((SELECT M.matterName FROM ClassSchedules CS
INNER JOIN Class C ON C.idClass=CS.idClass
INNER JOIN Matter M ON M.idMatter=C.idMatter
INNER JOIN Course CO ON CO.idCourse=C.idCourse
INNER JOIN Student ST ON ST.idCourse=CO.idCourse
INNER JOIN Person P ON P.Personid=ST.PersonId
INNER JOIN USERACCOUNT U ON U.Personid=P.Personid
WHERE U.UserId= :useraccount AND CS.schedulesid=S.schedulesid AND C.status=2),'') AS " + day + @"
FROM Schedules S
WHERE S.DAY= :dayss";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[2];
parameters1[0] = new OracleParameter(":useraccount", iduser);
parameters1[1] = new OracleParameter(":dayss", day);
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw; }
}
public Person Get(int id)
{
Person a = null;
OracleCommand cmd = null;
string query = @"SELECT P.Personid, P.names, P.lastName, COALESCE(P.secondLastName,''), P.addres, P.phone, P.birthDate,P.gender,P.status,P.registrationDate,NVL(P.updateDate,'01/01/1900'),P.startDate,NVL(P.finishDate,'01/01/1900'),P.email, P.latitude, P.longitude, P.TownId,P.photo FROM Person P INNER JOIN Users U ON U.PersonId=P.Personid WHERE U.UserId= :PersonId";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":PersonId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Person(int.Parse(dr[0].ToString()), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), DateTime.Parse(dr[6].ToString()), dr[7].ToString(), byte.Parse(dr[8].ToString()), DateTime.Parse(dr[9].ToString()), DateTime.Parse(dr[10].ToString()), DateTime.Parse(dr[11].ToString()), DateTime.Parse(dr[12].ToString()), dr[13].ToString(), Convert.ToDouble(dr[14].ToString()), Convert.ToDouble(dr[15].ToString()), byte.Parse(dr[16].ToString()), dr[17].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return a;
}
//Get
public Horario Get(byte id)
{
Horario a = null;
OracleCommand cmd = null;
string query = @"SELECT timeTableId, hourStart, hourFinish, ISNULL(updateDate,0), status, registrationDate, day
FROM timetable
WHERE timeTableId = :timeTableId";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":timeTableId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Horario(byte.Parse(dr[0].ToString()), DateTime.Parse(dr[1].ToString()), DateTime.Parse(dr[2].ToString()), DateTime.Parse(dr[3].ToString()), byte.Parse(dr[4].ToString()), DateTime.Parse(dr[5].ToString()), dr[6].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
//GET
public Course Get(int id)
{
Course a = null;
OracleCommand cmd = null;
string query = @"SELECT idCourse,numberCourse,letterCourse,section FROM Course WHERE idCourse= :Course";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":Course", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Course(int.Parse(dr[0].ToString()), int.Parse(dr[1].ToString()), dr[2].ToString(), dr[3].ToString());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public void InsertTransaction(string Nit, string Buss)
{
string queryInsert = @"INSERT INTO PAYER(NIT,BUSINESSNAME)
VALUES(:NIT,:BUSINESSNAME)";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start Insert New Payer.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(1);
cmds[0].CommandText = queryInsert;
cmds[0].Parameters.Add(new OracleParameter(":NIT", Nit));
cmds[0].Parameters.Add(new OracleParameter(":BUSINESSNAME", Buss));
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Insert Payer Succesfuly.", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not Add Payer({1}).", DateTime.Now, ex.Message));
}
}
public void DeleteTransaction(int t)
{
string queryPerson = @"UPDATE Person SET status=2 WHERE PersonId= :Person";
string queryUser = @"UPDATE Users SET status=2 WHERE PersonId= :Person";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start student Update.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
OracleParameter[] parameters1 = new OracleParameter[1];
OracleParameter[] parameters2 = new OracleParameter[1];
cmds[0].CommandText = queryPerson;
parameters1[0] = new OracleParameter(":Person", t);
cmds[0].Parameters.AddRange(parameters1);
cmds[1].CommandText = queryUser;
parameters2[0] = new OracleParameter(":Person", t);
cmds[1].Parameters.AddRange(parameters2);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Delete student by" + Session.SessionCurrent.ToString() + " Object Send: {1}", DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not delete Student({1}).", DateTime.Now, ex.Message));
}
}
public void UpdateEnabled(int id, int stat)
{
string queryPerson = @"UPDATE Person SET status= :stat WHERE Personid= :Person";
string queryStudent = @"UPDATE Users SET status= :stat WHERE Personid= :Person";
try
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Start student Update.", DateTime.Now));
List <OracleCommand> cmds = DBImplementation.CreateNBasicCommands(2);
OracleParameter[] parameters1 = new OracleParameter[1];
OracleParameter[] parameters2 = new OracleParameter[1];
cmds[0].CommandText = queryPerson;
parameters1[0] = new OracleParameter(":Person", id);
parameters1[1] = new OracleParameter(":stat", stat);
cmds[0].Parameters.AddRange(parameters1);
cmds[1].CommandText = queryStudent;
parameters2[0] = new OracleParameter(":stat", stat);
parameters2[1] = new OracleParameter(":Person", id);
cmds[1].Parameters.AddRange(parameters2);
DBImplementation.ExecuteNBasicCommand(cmds);
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Info: Modif student by" + Session.SessionCurrent.ToString() + "/" + DateTime.Now));
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(string.Format("{0} | Error: Could not modif Student({1}).", DateTime.Now, ex.Message));
}
}
public DataTable SelectDislike(int id, string like)
{
string query = @"SELECT P.Personid
, P.names ,P.lastName
,P.secondLastName ,S.rudeNumber,P.email,P.gender,P.phone ,P.addres ,P.birthDate ,
P.status , P.registrationDate
,P.updateDate ,P.startDate,P.finishDate,(SELECT C.CityName||'-'||PP.provinceName||', '||T.townName
FROM Town T INNER JOIN Province PP ON PP.ProvinceId=T.ProvinceId
INNER JOIN City C ON C.CityId=PP.CityId WHERE T.TownId=P.TownId),P.photo ,
S.studentId
FROM Person P left JOIN Student S ON S.PersonId = P.PersonId WHERE S.idCourse = :Course AND P.names||' '||P.lastName||' '||P.secondLastName like :likes
AND P.status=0";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[2];
parameters1[0] = new OracleParameter(":Course", id);
parameters1[1] = new OracleParameter(":likes", "%" + like + "%");
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public Town GetID(string id, string id2, string id3)
{
Town a = null;
OracleCommand cmd = null;
string query = @"SELECT DISTINCT T.TownId FROM Town T INNER JOIN Province P ON T.ProvinceId=(SELECT ProvinceId FROM Province WHERE provinceName = :ProvinceName) INNER JOIN City C ON (SELECT CityId FROM Province WHERE provinceName= :ProvinceName)=(SELECT CityId FROM City WHERE CityName= :CityName) where t.townName= :townName";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[3];
parameters1[0] = new OracleParameter(":CityName", id);
parameters1[1] = new OracleParameter(":ProvinceName", id2);
parameters1[2] = new OracleParameter(":townName", id3);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new Town(byte.Parse(dr[0].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public City GetPlace(byte id)
{
City a = null;
OracleCommand cmd = null;
OracleParameter[] parameters1 = new OracleParameter[1];
string query = @"SELECT CityId, CityName,state FROM City WHERE CityId=:CityId";
OracleDataReader dr = null;
try
{
cmd = DBImplementation.CreateBasicCommand(query);
parameters1[0] = new OracleParameter(":CityId", id);
cmd.Parameters.AddRange(parameters1);
dr = DBImplementation.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
a = new City(byte.Parse(dr[0].ToString()), dr[1].ToString(), byte.Parse(dr[2].ToString()));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
dr.Close();
}
return(a);
}
public DataTable SelectList()
{
string query = "SELECT DISTINCT CityId id, CityName name FROM City ";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public DataTable Select()
{
string query = "SELECT CITYID AS ID, CITYNAME AS NAME FROM CITY WHERE STATE=1";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
//SELECT
public DataTable Select()
{
string query = @"SELECT idCourse,numberCourse||letterCourse Course,section Section,
status Status, registrationDate Registration,NVL(updateDate, '01-01-1993') UpdateDate FROM Course";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
//Select
public DataTable Select()
{
string query = "SELECT * FROM timetable WHERE status=1";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public DataTable SelectTotal(int idclass)
{
string query = @"select * from AverageGradeTotal WHERE idAverage= :tot";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":tot", idclass);
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw; }
}
public DataTable SelectList(int id)
{
string query = "SELECT DISTINCT [TownId] as 'id', TownName as 'name',ProvinceId as 'province' FROM Town WHERE ProvinceId=(SELECT ProvinceId FROM Town WHERE TownId= :Town)";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":Town", id);
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public DataTable SelectList(int id)
{
string query = "SELECT DISTINCT ProvinceId id, provinceName name,CityId city FROM Province WHERE CityId=(SELECT CityId FROM Province WHERE ProvinceId= :province)";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":province", id);
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}
public DataTable Select(string id)
{
string query = "SELECT TOWNID ID, TOWNNAME NAME FROM Town WHERE PROVINCEID=:ProvinceName";
try
{
OracleCommand cmd = DBImplementation.CreateBasicCommand(query);
OracleParameter[] parameters1 = new OracleParameter[1];
parameters1[0] = new OracleParameter(":ProvinceName", int.Parse(id));
cmd.Parameters.AddRange(parameters1);
return(DBImplementation.ExecuteDataTableCommand(cmd));
}
catch (Exception ex) { throw ex; }
}