//selecting data from database
public DataTable Select()
{
//database connection
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
DataTable dt = new DataTable();
try
{
//writing Sql query
string sql = "Select * from Lecturer";
//creating cmd using sql and conn
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//creating Sql Data Adapter using cmd
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return(dt);
}
//selecting Data from database
public DataTable Select()
{
//DB connection
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
DataTable dt = new DataTable();
try
{
//sql query
string sql = "Select * from StudentManage";
//creating cmd using sql and conn
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//creating sql data adapted using cmd
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(dt);
}
//insertign Data into database
public bool InsertLecturer(LectureClass L)
{
string myconnstrng = Classes.ConnectionStrings.TBMS;
//creating a default return type and setting its value to false
bool isSuccess = false;
//connect database
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//create a sql query to insert data
string sql = "Insert into Lecturer(EmployeeID,LectureName,Faculty,Department,Center,Building,Level,Rank,SelectDay,StartTime,EndTime) VALUES(@EmployeeID,@LectureName,@Faculty,@Department,@Center,@Building,@Level,@Rank,@SelectDay,@StartTime,@EndTime)";
//creating Sql command using sql and cmd
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
cmd.Parameters.AddWithValue("@EmployeeID", L.EmployeeID);
cmd.Parameters.AddWithValue("@LectureName", L.LectureName);
cmd.Parameters.AddWithValue("@Faculty", L.Faculty);
cmd.Parameters.AddWithValue("@Department", L.Department);
cmd.Parameters.AddWithValue("@Center", L.Center);
cmd.Parameters.AddWithValue("@Building", L.Building);
cmd.Parameters.AddWithValue("@Level", L.Level);
cmd.Parameters.AddWithValue("@Rank", L.Rank);
cmd.Parameters.AddWithValue("@SelectDay", L.SelectDay);
cmd.Parameters.AddWithValue("@StartTime", L.StartTime);
cmd.Parameters.AddWithValue("@EndTime", L.EndTime);
//connection open here
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the queryrun successfully the the value of rows will be greater than zero slse if value will be 0
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
//Method to update data in database from our application
public bool UpdateLecturer(LectureClass L)
{
//create a default return type and set its default values to false
bool isSuccess = false;
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//sql to update data in our database
string sql = "Update Lecturer SET EmployeeID = @EmployeeID, LectureName = @LectureName, Faculty = @Faculty, Department =@Department, Center = @Center, Building = @Building, Level = @Level, Rank = @Rank, SelectDay = @SelectDay, StartTime = @StartTime, EndTime = @EndTime where EmployeeID = @EmployeeID";
//creating sql command
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//create parametrs to add value
cmd.Parameters.AddWithValue("@EmployeeID", L.EmployeeID);
cmd.Parameters.AddWithValue("@LectureName", L.LectureName);
cmd.Parameters.AddWithValue("@Faculty", L.Faculty);
cmd.Parameters.AddWithValue("@Department", L.Department);
cmd.Parameters.AddWithValue("@Center", L.Center);
cmd.Parameters.AddWithValue("@Building", L.Building);
cmd.Parameters.AddWithValue("@Level", L.Level);
cmd.Parameters.AddWithValue("@Rank", L.Rank);
cmd.Parameters.AddWithValue("@SelectDay", L.SelectDay);
cmd.Parameters.AddWithValue("@StartTime", L.StartTime);
cmd.Parameters.AddWithValue("@EndTime", L.EndTime);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query run successfully the the value of rows will be greater than zero slse if value will be 0
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
//inserting data into database
public bool insertSession(SessionClass S)
{
//creating a default return type and setting its value to false
bool isSuccess = false;
//connect database
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//creating a Sql query ti insert data
string sql = "Insert into Session(Lecture1, Lecture2, SubjectCode, SubjectName, GroupID, Tag, NoOfStudent, Duration) VALUES(@Lecture1, @Lecture2, @SubjectCode, @SubjectName, @GroupID, @Tag, @NoOfStudent, @Duration)";
//creatng a Sql command using sql and cmd
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//cmd.Parameters.AddWithValue("@ID", s.ID);
cmd.Parameters.AddWithValue("@Lecture1", S.Lecture1);
cmd.Parameters.AddWithValue("@Lecture2", S.Lecture2);
cmd.Parameters.AddWithValue("@SubjectCode", S.SubjectCode);
cmd.Parameters.AddWithValue("@SubjectName", S.SubjectName);
cmd.Parameters.AddWithValue("@GroupID", S.GroupID);
cmd.Parameters.AddWithValue("@Tag", S.Tag);
cmd.Parameters.AddWithValue("@NoOfStudent", S.NoOfStudent);
cmd.Parameters.AddWithValue("@Duration", S.Duration);
// connection open here
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query runs successfully the the value of rows will be greater than zero else if value will be 0
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
//inserting data into database
public bool insertSubject(SubjectClass s)
{
//creating a default return type and setting its value to false
bool isSuccess = false;
//connect database
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//creating a Sql query ti insert data
string sql = "Insert into Subject(OfferdYear,OfferdSemester,SubjectName,SubjectCode,NoOfLectureHours,NoOfTutorilaHours,NoOfLabHours,NoOfEvaluationHours) VALUES(@OfferdYear,@OfferdSemester,@SubjectName,@SubjectCode,@NoOfLectureHours,@NoOfTutorialHours,@NoOfLabHours,@NoOfEvaluationHours)";
//creatng a Sql command using sql and cmd
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
cmd.Parameters.AddWithValue("@OfferdYear", s.OfferdYear);
cmd.Parameters.AddWithValue("@OfferdSemester", s.OfferdSemester);
cmd.Parameters.AddWithValue("@SubjectName", s.SubjectName);
cmd.Parameters.AddWithValue("@SubjectCode", s.SubjectCode);
cmd.Parameters.AddWithValue("@NoOfLectureHours", s.NoOfLectureHours);
cmd.Parameters.AddWithValue("@NoOfTutorialHours", s.NoOfTutorialHours);
cmd.Parameters.AddWithValue("@NoOfLabHours", s.NoOfLabHours);
cmd.Parameters.AddWithValue("@NoOfEvaluationHours", s.NoOfEvaluationHours);
// connection open here
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the queryrun successfully the the value of rows will be greater than zero slse if value will be 0
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
//method to update data in databse form our application
public bool UpdateSession(SessionClass S)
{
//create a default return type and set its default values to false
bool isSuccess = false;
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
string sql = "update Session set Lecture1 = @Lecture1, Lecture2 = @Lecture2, SubjectCode = @SubjectCode, SubjectName = @SubjectName, GroupID = @GroupID, Tag = @Tag, NoOfStudent = @NoOfStudent, Duration = @Duration where ID = @ID";
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//create paramters to add values
//cmd.Parameters.AddWithValue("@ID", s.ID);
cmd.Parameters.AddWithValue("@Lecture1", S.Lecture1);
cmd.Parameters.AddWithValue("@Lecture2", S.Lecture2);
cmd.Parameters.AddWithValue("@SubjectCode", S.SubjectCode);
cmd.Parameters.AddWithValue("@SubjectName", S.SubjectName);
cmd.Parameters.AddWithValue("@GroupID", S.GroupID);
cmd.Parameters.AddWithValue("@Tag", S.Tag);
cmd.Parameters.AddWithValue("@NoOfStudent", S.NoOfStudent);
cmd.Parameters.AddWithValue("@Duration", S.Duration);
cmd.Parameters.AddWithValue("@ID", S.ID);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query run successfully the the value of rows will be greater than zero slse if value will be 0
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
//method to update data in databse form our application
public bool UpdateSubject(SubjectClass s)
{
//create a default return type and set its default values to false
bool isSuccess = false;
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
string sql = "update Subject set OfferdYear = @OfferdYear, OfferdSemester = @OfferdSemester, SubjectName = @SubjectName, SubjectCode = @SubjectCode, NoOfLectureHours = @NoOfLectureHours, NoOfTutorilaHours = @NoOfTutorialHours,NoOfLabHours = @NoOfLabHours, NoOfEvaluationHours = @NoOfEvaluationHours where SubjectCode = @SubjectCode";
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//create paramters to add values
cmd.Parameters.AddWithValue("@OfferdYear", s.OfferdYear);
cmd.Parameters.AddWithValue("@OfferdSemester", s.OfferdSemester);
cmd.Parameters.AddWithValue("@SubjectName", s.SubjectName);
cmd.Parameters.AddWithValue("@SubjectCode", s.SubjectCode);
cmd.Parameters.AddWithValue("@NoOfLectureHours", s.NoOfLectureHours);
cmd.Parameters.AddWithValue("@NoOfTutorialHours", s.NoOfTutorialHours);
cmd.Parameters.AddWithValue("@NoOfLabHours", s.NoOfLabHours);
cmd.Parameters.AddWithValue("@NoOfEvaluationHours", s.NoOfEvaluationHours);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query run successfully the the value of rows will be greater than zero slse if value will be 0
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
//update data in database
public bool Update(ManageStudentClass m)
{
//default return type and set its value to false
bool isSuccess = false;
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//sql to update data
string sql = "Update StudentManage set AcademicYearSemester = @AcademicYearSemester, Programme = @Programme ,GroupNo = @GroupNo,SubGroupNo = @SubGroupNo ,GroupID = @GroupID , SubGroupID = @SubGroupID where ID = @ID ";
//sql
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//create parameters to add value
cmd.Parameters.AddWithValue("@AcademicYearSemester", m.AcademicYearSemester);
cmd.Parameters.AddWithValue("@Programme", m.Program);
cmd.Parameters.AddWithValue("@GroupNo", m.GroupNo);
cmd.Parameters.AddWithValue("@SubGroupNo", m.SubGroupNo);
cmd.Parameters.AddWithValue("@GroupID", m.GroupID);
cmd.Parameters.AddWithValue("@SubGroupID", m.SubGroupID);
cmd.Parameters.AddWithValue("ID", m.ID);
//open database connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool DeleteSession(SessionClass S)
{
//create a default value and set its value to false
bool isSuccess = false;
//creating Sql connection
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//sql to delete Data
string sql = "Delete from Session where ID = @ID";
//creating sql command
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
cmd.Parameters.AddWithValue("@ID", S.ID);
//open connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query run successfully the the value of rows will be greater than zero slse if value will be 0
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
//close the connection
conn.Close();
}
return(isSuccess);
}
//Insering data into the database
public bool Insert(ManageStudentClass m)
{
//creating a default return type
bool isSuccess = false;
//connect db
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//sql to insert data
string sql = "Insert into StudentManage (AcademicYearSemester, Programme ,GroupNo,SubGroupNo ,GroupID , SubGroupID) values (@AcademicYearSemester, @Programme ,@GroupNo,@SubGroupNo ,@GroupID , @SubGroupID)";
//creating sql using sql and conn
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//create parameters to add data
cmd.Parameters.AddWithValue("@AcademicYearSemester", m.AcademicYearSemester);
cmd.Parameters.AddWithValue("@Programme", m.Program);
cmd.Parameters.AddWithValue("@GroupNo", m.GroupNo);
cmd.Parameters.AddWithValue("@SubGroupNo", m.SubGroupNo);
cmd.Parameters.AddWithValue("@GroupID", m.GroupID);
cmd.Parameters.AddWithValue("@SubGroupID", m.SubGroupID);
//open connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if code success value of rows will greater than zero else its zero
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(isSuccess);
}
//Insering data to the database
public bool Insert(TagsClass c)
{
//creating default retun type and setting its value to false
bool isSuccess = false;
//step 1 : connect database
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//sql query to insert data
string sql = "Insert into ManageTag (SubjectName , SubjectCode , RelatedTag) values ( @SubjectName , @SubjectCode , @RelatedTag)";
//creating cmd usin sql and conn
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//create parameters to add data
cmd.Parameters.AddWithValue("@SubjectName", c.TagName);
cmd.Parameters.AddWithValue("@SubjectCode", c.TagCode);
cmd.Parameters.AddWithValue("@RelatedTag", c.RelatedTag);
//connection open
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query runs successfully then the value of rows will be greater than zero else its value will be zero
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(isSuccess);
}
//Method to update data in database
public bool Update(TagsClass c)
{
//create a default return type and set its value to false
bool isSuccess = false;
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//sql to update data in database
string sql = "Update ManageTag set SubjectName =@SubjectName , SubjectCode =@SubjectCode , RelatedTag=@RelatedTag where TagID =@TagID";
//creating sql command
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//create parameters to add values
cmd.Parameters.AddWithValue("@SubjectName", c.TagName);
cmd.Parameters.AddWithValue("@SubjectCode", c.TagCode);
cmd.Parameters.AddWithValue("@RelatedTag", c.RelatedTag);
cmd.Parameters.AddWithValue("TagID", c.TagID);
//open DB connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query runs successfully then the value of rows will be greater than zero else its value will be zero
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(isSuccess);
}
//Method to delete data in database
public bool Delete(TagsClass c)
{
//default return value and set if false
bool isSuccess = false;
//create sql connection
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//sql to delete data
string sql = "Delete from ManageTag where TagID = @TagID";
//creating sql command
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
cmd.Parameters.AddWithValue("@TagID", c.TagID);
//open connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if run success value will be greater than ero othrwise it will be zero
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
}
finally
{
//close connection
conn.Close();
}
return(isSuccess);
}
//method to delete data in DB
public bool Delete(ManageStudentClass m)
{
//create a default retun value and set it value to false
bool isSuccess = false;
//create sql connection
SQLiteConnection conn = new Classes.SqliteHelper().GetSQLiteConnection();
try
{
//sql to delete data
string sql = "Delete from StudentManage where ID = @ID";
//creating sql command
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
cmd.Parameters.AddWithValue("@ID", m.ID);
//open connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return(isSuccess);
}
