Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }