public int GetAllStudentLessonAttendance(int batchId, string bgroup, int lessonId, string userID) { int retVal = 0; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT COUNT(`id`) FROM `studentlessonattendance` WHERE `batchid` = @batchid AND DATE(createdate) = DATE(NOW()) AND lessonid = @lessonId AND bgroup = @bgroup"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@batchid", batchId); cmd.Parameters.AddWithValue("@lessonId", lessonId); cmd.Parameters.AddWithValue("@bgroup", bgroup); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { StudentLessonAttendance Mod = new StudentLessonAttendance(); retVal = dr.GetInt32(0); } con.Close(); return(retVal); }
public string GetAllStudentLessonIDAttendance(int batchId, DateTime attenDate, string userID) { string retVal = ""; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT DISTINCT lessonid FROM `studentlessonattendance` WHERE `batchid` = @batchid AND DATE(createdate) = DATE(@attenDate) AND lessonid != 0 "; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@batchid", batchId); cmd.Parameters.AddWithValue("@attenDate", attenDate); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { StudentLessonAttendance Mod = new StudentLessonAttendance(); retVal += dr.GetInt32(0) + ","; } con.Close(); return(retVal == "" ? "''" : retVal.TrimEnd(',')); }
public List <StudentLessonAttendance> GetAllStudentLessonAttendance(string userID) { List <StudentLessonAttendance> retVal = new List <StudentLessonAttendance>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT `id`, `studuserid`, `batchid`, `bgroup`,lessonid, `present`, `createdate`, `modifydate` FROM `studentlessonattendance` "; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { StudentLessonAttendance Mod = new StudentLessonAttendance(); Mod.ID = dr.GetInt32(0); Mod.StuduserId = dr.GetString(1); Mod.BatchId = dr.GetInt32(2); Mod.Bgroup = dr.GetString(3); Mod.LessonId = dr.GetInt32(4); Mod.Present = dr.GetInt32(5); Mod.DateCreted = dr.GetDateTime(6); Mod.LastModified = dr.GetDateTime(7); retVal.Add(Mod); } con.Close(); return(retVal); }
public bool AddStudentLessonAttendance(StudentLessonAttendance fp, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "INSERT INTO `studentlessonattendance`(`studuserid`, `batchid`, `bgroup`,lessonid, `present`, `createdate`, `modifydate`) VALUES (@studuserid,@batchid,@bgroup,@lessonid,@present,@createdate,@modifydate)"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@studuserid", fp.StuduserId); cmd.Parameters.AddWithValue("@batchid", fp.BatchId); cmd.Parameters.AddWithValue("@present", fp.Present); cmd.Parameters.AddWithValue("@createdate", fp.DateCreted); cmd.Parameters.AddWithValue("@modifydate", fp.LastModified); cmd.Parameters.AddWithValue("@bgroup", fp.Bgroup); cmd.Parameters.AddWithValue("@lessonid", fp.LessonId); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }
public bool UpdateStudentLessonAttendance(StudentLessonAttendance fp, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "UPDATE `studentlessonattendance` SET `studuserid`=@studuserid,`batchid`=@batchid,lessonid = @lessonid,`present`=@present,`modifydate`=@modifydate,bgroup = @bgroup WHERE id = @id"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@studuserid", fp.StuduserId); cmd.Parameters.AddWithValue("@batchid", fp.BatchId); cmd.Parameters.AddWithValue("@present", fp.Present); cmd.Parameters.AddWithValue("@modifydate", fp.LastModified); cmd.Parameters.AddWithValue("@bgroup", fp.Bgroup); cmd.Parameters.AddWithValue("@id", fp.ID); cmd.Parameters.AddWithValue("@lessonid", fp.LessonId); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }