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