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