Esempio n. 1
0
        public List <StudentModel> SearchStudent(string fullname, int schoolId, string identityNumber, int graduatingYear)
        {
            List <StudentModel> studentModels = new List <StudentModel>();

            using (conn = JBCertConnection.Instance)
            {
                string queryString = @"SELECT a.*, b.Tentruong, c.Hinhthucdaotao, d.TenDantoc, e.name as 'Tenloai', f.Tenxeploai
                                      FROM [jb_cert].[dbo].[tblHocsinh] as a Left join [jb_cert].[dbo].[tblTruong] as b
                                      On a.[TruongId] = b.[Id] 
                                      Left join [jb_cert].[dbo].[tblHinhthucdaotao] as c
                                      on a.HinhthucdaotaoId = c.Id
                                      left join [jb_cert].[dbo].[tblDantoc] as d
                                      on a.DantocId = d.Id
                                      left join [jb_cert].[dbo].[tblLoai] as e
                                      on a.LoaiId = e.Id
                                      left join [jb_cert].[dbo].[tblXeploai] as f
                                      on a.XeploaiId = f.Id
                                      Where a.[IsDeleted] = 0 
                                        and a.[Ten] like @StudentName 
                                        and (@SchoolId = -1 or a.[TruongId] = @SchoolId)
                                        and a.[CMT] like @CMT
                                        and (@GraduatingYear = -1 or a.[Namtotnghiep] = @GraduatingYear)";
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand(queryString, conn);
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.Parameters.AddWithValue("@StudentName", "%" + fullname + "%");
                sqlCommand.Parameters.AddWithValue("@SchoolId", schoolId);
                sqlCommand.Parameters.AddWithValue("@CMT", "%" + identityNumber + "%");
                sqlCommand.Parameters.AddWithValue("@GraduatingYear", graduatingYear);

                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                try
                {
                    while (sqlDataReader.Read())
                    {
                        StudentModel studentModel = new StudentModel();
                        studentModel.Id                = int.Parse(sqlDataReader["Id"].ToString());
                        studentModel.SchoolId          = int.Parse(sqlDataReader["TruongId"].ToString());
                        studentModel.SchoolName        = sqlDataReader["Tentruong"].ToString();
                        studentModel.EthnicId          = int.Parse(sqlDataReader["DantocId"].ToString());
                        studentModel.EthnicName        = sqlDataReader["Tendantoc"].ToString();
                        studentModel.FullName          = sqlDataReader["Ten"].ToString();
                        studentModel.Address           = sqlDataReader["Diachi"].ToString();
                        studentModel.Dob               = DateTime.Parse(sqlDataReader["Ngaysinh"].ToString());
                        studentModel.HouseHold         = sqlDataReader["Hokhau"].ToString();
                        studentModel.IdentityNumber    = sqlDataReader["CMT"].ToString();
                        studentModel.Gender            = sqlDataReader["Gioitinh"].ToString();
                        studentModel.Note              = sqlDataReader["Ghichu"].ToString();
                        studentModel.Image             = sqlDataReader["Anh"].ToString();
                        studentModel.IsDeleted         = false;
                        studentModel.BlankCertTypeId   = int.Parse(sqlDataReader["LoaiId"].ToString());
                        studentModel.BlankCertTypeName = sqlDataReader["Tenloai"].ToString();
                        studentModel.BornedAddress     = sqlDataReader["Noisinh"].ToString();
                        studentModel.LearningModeId    = int.Parse(sqlDataReader["HinhthucdaotaoId"].ToString());
                        studentModel.LearningModeName  = sqlDataReader["Hinhthucdaotao"].ToString();
                        studentModel.Score             = float.Parse(sqlDataReader["Score"].ToString());
                        studentModel.MajorId           = int.Parse(sqlDataReader["NganhdaotaoId"].ToString());
                        studentModel.GraduatingYear    = int.Parse(sqlDataReader["Namtotnghiep"].ToString());
                        studentModel.RankingId         = int.Parse(sqlDataReader["XeploaiId"].ToString());
                        studentModel.RankingName       = sqlDataReader["Tenxeploai"].ToString();

                        studentModels.Add(studentModel);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    sqlDataReader.Close();
                    conn.Close();
                }
            }

            return(studentModels);
        }
Esempio n. 2
0
        public List <StudentModel> SearchStudentForAddingCert(string studentName, int schoolId)
        {
            List <StudentModel> studentModels = new List <StudentModel>();

            using (conn = JBCertConnection.Instance)
            {
                string queryString  = @"SELECT *, b.Tenxeploai FROM [dbo].[tblHocsinh] as a
		                                left join [dbo].[tblXeploai] as b
		                                on a.XeploaiId = b.Id
		                                where a.[IsDeleted] = 0 and a.[Ten] like @StudentName
		                                and a.Id not in (select HocsinhId from [dbo].[tblBang] where LoaiId = a.LoaiId and IsDeleted = 0 )"        ;
                string queryString1 = @"SELECT *, b.Tenxeploai FROM [dbo].[tblHocsinh] as a
		                                left join [dbo].[tblXeploai] as b
		                                on a.XeploaiId = b.Id
		                                where a.[IsDeleted] = 0 and a.[Ten] like @StudentName and a.[TruongId] = @SchoolId
		                                and a.Id not in (select HocsinhId from [dbo].[tblBang] where LoaiId = a.LoaiId and IsDeleted = 0 )"        ;
                conn.Open();
                SqlCommand sqlCommand;
                if (schoolId == -1)
                {
                    sqlCommand = new SqlCommand(queryString, conn);
                    sqlCommand.Parameters.AddWithValue("@StudentName", "%" + studentName + "%");
                }
                else
                {
                    sqlCommand = new SqlCommand(queryString1, conn);
                    sqlCommand.Parameters.AddWithValue("@StudentName", "%" + studentName + "%");
                    sqlCommand.Parameters.AddWithValue("@SchoolId", schoolId);
                }
                sqlCommand.CommandType = CommandType.Text;

                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                try
                {
                    while (sqlDataReader.Read())
                    {
                        StudentModel studentModel = new StudentModel();
                        studentModel.Id             = int.Parse(sqlDataReader["Id"].ToString());
                        studentModel.SchoolId       = int.Parse(sqlDataReader["TruongId"].ToString());
                        studentModel.EthnicId       = int.Parse(sqlDataReader["DantocId"].ToString());
                        studentModel.FullName       = sqlDataReader["Ten"].ToString();
                        studentModel.IdentityNumber = sqlDataReader["CMT"].ToString();
                        studentModel.Gender         = sqlDataReader["Gioitinh"].ToString();
                        studentModel.Image          = sqlDataReader["Anh"].ToString();
                        studentModel.IsDeleted      = false;
                        studentModel.RankingId      = int.Parse(sqlDataReader["XeploaiId"].ToString());
                        studentModel.RankingName    = sqlDataReader["Tenxeploai"].ToString();

                        studentModels.Add(studentModel);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    sqlDataReader.Close();
                    conn.Close();
                }
            }

            return(studentModels);
        }