public List <Tutor> FindTutor(string searchString, int startIndex, int endIndex, string userId) { MySqlConnection con = new MySqlConnection(DbCon.connectionString); MySqlCommand cmd; MySqlDataReader dr = null; string query = "SELECT `id`, `memberID`, `indexNo`, `onames`, `sname`, `gender`, `tel`, `email`, `dateCreated`, `lastModified` FROM `tutors` WHERE indexNo LIKE ('@indexNo%') OR CONCAT_WS(' ',onames,sname) LIKE ('%@fullname%') order by indexNo LIMIT @startIndex,@endIndex "; Tutor member; List <Tutor> memList = new List <Tutor>(); try { con.Open(); cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("@indexNo", searchString); cmd.Parameters.AddWithValue("@fullname", searchString); cmd.Parameters.AddWithValue("@startIndex", startIndex); cmd.Parameters.AddWithValue("@endIndex", (endIndex - startIndex)); dr = cmd.ExecuteReader(); //new AuditLogService().AddAuditLog("FINDING MEMBER", userId, new UserService().GetUserName(userId), query, DateTime.Now); if (dr.HasRows) //if the executed query returns any records { while (dr.Read()) //iterate through the records in the result dataset { member = new Tutor(); member.UserId = dr.GetString(0); member.IndexNo = dr.GetString(1); member.Title = dr.GetString(2); member.FNames = dr.GetString(3); member.ONames = dr.GetString(4); member.SName = dr.GetString(5); member.Gender = dr.GetString(6); member.Dob = dr.GetDateTime(7); member.Religion = dr.GetString(8); member.Tel = dr.GetString(9); member.Email = dr.GetString(10); member.Nationality = dr.GetString(11); member.PostAdd = dr.GetString(12); member.ResAdd = dr.GetString(13); member.Marital = dr.GetString(14); member.Mobile = dr.GetString(15); member.DateCreated = dr.GetDateTime(16); member.LastModified = dr.GetDateTime(17); member.xFullName = dr.GetString(3) + " " + dr.GetString(4) + " " + dr.GetString(5); } } } catch (MySqlException ex) { new AuditLogService().AddAuditLog("ERROR FINDING MEMBER", userId, new UserService().GetUserName(userId), ex.Message, DateTime.Now); String errorString = ex.Message; } catch (Exception ex) { new AuditLogService().AddAuditLog("ERROR FINDING MEMBER", userId, new UserService().GetUserName(userId), ex.Message, DateTime.Now); String errorString = ex.Message; } finally { dr.Close(); con.Close(); } return(memList); }
public List <Tutor> GetTutorResult(string indexNo, string userId) { MySqlConnection con = new MySqlConnection(DbCon.connectionString); MySqlDataReader dr = null; MySqlCommand cmd; string query = "SELECT `userId`, `indexNo`, `title`, `fname`, `onames`, `sname`, `gender`, `dob`, `religion`, `tel`, `email`, `nationality`, `postadd`, `resadd`, `marital`, `mobile`, `dateCreated`, `lastModified` FROM `tutors` WHERE `indexNo`= @indexNo "; Tutor member; List <Tutor> memberList = new List <Tutor>(); try { con.Open(); cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("@indexNo", indexNo); dr = cmd.ExecuteReader(); new AuditLogService().AddAuditLog("GETTING ALL STAFF", userId, new UserService().GetUserName(userId), query, DateTime.Now); if (dr.HasRows) //if the executed query returns any records { while (dr.Read()) //iterate through the records in the result dataset { member = new Tutor(); member.UserId = dr.GetString(0); member.IndexNo = dr.GetString(1); member.Title = dr.GetString(2); member.FNames = dr.GetString(3); member.ONames = dr.GetString(4); member.SName = dr.GetString(5); member.Gender = dr.GetString(6); member.Dob = dr.GetDateTime(7); member.Religion = dr.GetString(8); member.Tel = dr.GetString(9); member.Email = dr.GetString(10); member.Nationality = dr.GetString(11); member.PostAdd = dr.GetString(12); member.ResAdd = dr.GetString(13); member.Marital = dr.GetString(14); member.Mobile = dr.GetString(15); member.DateCreated = dr.GetDateTime(16); member.LastModified = dr.GetDateTime(17); member.xFullName = dr.GetString(3) + " " + dr.GetString(4) + " " + dr.GetString(5); memberList.Add(member); } } } catch (MySqlException ex) { new AuditLogService().AddAuditLog("ERROR GETTING ALL STAFF", userId, new UserService().GetUserName(userId), ex.Message, DateTime.Now); String errorString = ex.Message; } catch (Exception ex) { new AuditLogService().AddAuditLog("ERROR GETTING ALL STAFF", userId, new UserService().GetUserName(userId), ex.Message, DateTime.Now); String errorString = ex.Message; } finally { dr.Close(); con.Close(); } return(memberList); }