Example #1
0
        //Method to Add Skill to Database
        public static int AddSkill(Skill newSkill)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand skillCommand = DataConnections.GenerateCommand();

                //Assigning command text
                skillCommand.CommandText = "Group4.usp_AddSkill";

                //Adding parameters to command
                skillCommand.Parameters.AddWithValue("@Skill_Name", newSkill.SkillName);
                skillCommand.Parameters.AddWithValue("@Skill_Description", newSkill.SkillDescription);
                skillCommand.Parameters.AddWithValue("@Category_Id", newSkill.CategoryId);
                //Executing command
                skillCommand.Connection.Open();
                rowsAffected = skillCommand.ExecuteNonQuery();
                skillCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #2
0
        //Method to Display Categories from CategoryId
        public static DataTable LoadCategory()
        {
            DataTable     dtCat;
            SqlDataReader catReader = null;

            try
            {
                //Creating command object
                SqlCommand skillCommand = DataConnections.GenerateCommand();

                dtCat = new DataTable();
                skillCommand.CommandText = "Group4.usp_DisplayAllCategories";
                skillCommand.Connection.Open();
                catReader = skillCommand.ExecuteReader();
                dtCat.Load(catReader);
                catReader.Close();
                skillCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtCat);
        }
Example #3
0
        //Method to load Speciality during employee operations
        public static DataTable LoadSpeciality()
        {
            DataTable     dtSpec;
            SqlDataReader empReader = null;

            try
            {
                //Creating command object
                SqlCommand empCommand = DataConnections.GenerateCommand();

                dtSpec = new DataTable();
                empCommand.CommandText = "Group4.usp_LoadSpeciality";
                empCommand.Connection.Open();
                empReader = empCommand.ExecuteReader();
                dtSpec.Load(empReader);
                empReader.Close();
                empCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtSpec);
        }
Example #4
0
        //Method to Search Skill From Database
        public static DataTable SearchSkillById(int skillId)
        {
            DataTable     dtSkill;
            SqlDataReader skillReader = null;

            try
            {
                //Creating command object
                SqlCommand skillCommand = DataConnections.GenerateCommand();

                dtSkill = new DataTable(/*int skillId*/);
                skillCommand.CommandText = "Group4.usp_SearchSkills";
                skillCommand.Parameters.AddWithValue("@Skill_Id", skillId);
                skillCommand.Connection.Open();
                skillReader = skillCommand.ExecuteReader();
                if (skillReader.HasRows)
                {
                    dtSkill.Load(skillReader);
                }
                skillReader.Close();
                skillCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtSkill);
        }
Example #5
0
        //Method to Delete Speciality From Database
        public static int DeleteSpec(int specId)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand SpecCommand = DataConnections.GenerateCommand();
                //Assigning command text

                SpecCommand.CommandText = "Group4.usp_DeleteSpec";
                SpecCommand.Parameters.AddWithValue("@Speciality_Id", specId);

                SpecCommand.Connection.Open();
                rowsAffected = SpecCommand.ExecuteNonQuery();
                SpecCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #6
0
        //Method to Modify HR Clerk/Manager From Database
        public static int UpdateClerk(Users user)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand empCommand = DataConnections.GenerateCommand();
                //Assigning command text

                empCommand.CommandText = "Group4.usp_UpdateUser";
                empCommand.Parameters.AddWithValue("@UserId", user.UserId);
                empCommand.Parameters.AddWithValue("@UserName", user.UserName);
                empCommand.Parameters.AddWithValue("@Password_", user.Password);
                empCommand.Parameters.AddWithValue("@First_Name", user.FirstName);
                empCommand.Parameters.AddWithValue("@Last_Name", user.LastName);
                empCommand.Connection.Open();
                rowsAffected = empCommand.ExecuteNonQuery();
                empCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #7
0
        //Method to Add Project to Database
        public static int AddProject(Project newProj)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand projCommand = DataConnections.GenerateCommand();

                //Assigning command text
                projCommand.CommandText = "Group4.usp_AddProject";

                //Adding parameters to command
                projCommand.Parameters.AddWithValue("@Project_Name", newProj.ProjectName);
                projCommand.Parameters.AddWithValue("@Project_Description", newProj.ProjectDescription);
                projCommand.Parameters.AddWithValue("@Project_Client", newProj.ProjectClient);
                projCommand.Parameters.AddWithValue("@Project_Start_Date", newProj.ProjectStartDate);
                projCommand.Parameters.AddWithValue("@Project_End_Date", newProj.ProjectEndDate);

                //Executing command
                projCommand.Connection.Open();
                rowsAffected = projCommand.ExecuteNonQuery();
                projCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #8
0
        //Method to Delete Project From Database
        public static int DeleteProject(int projId)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand projCommand = DataConnections.GenerateCommand();
                //Assigning command text

                projCommand.CommandText = "Group4.usp_DeleteProjects";
                projCommand.Parameters.AddWithValue("@Project_Id", projId);

                projCommand.Connection.Open();
                rowsAffected = projCommand.ExecuteNonQuery();
                projCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #9
0
        //Method to Display Project From Database
        public static DataTable DisplayAllProjects()
        {
            DataTable     dtProj;
            SqlDataReader projReader = null;

            try
            {
                //Creating command object
                SqlCommand projCommand = DataConnections.GenerateCommand();

                dtProj = new DataTable();
                projCommand.CommandText = "Group4.usp_DisplayAllProjects";
                projCommand.Connection.Open();
                projReader = projCommand.ExecuteReader();
                if (projReader.HasRows)
                {
                    dtProj.Load(projReader);
                }
                projReader.Close();
                projCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(dtProj);
        }
Example #10
0
        //Method to Search Project From Database
        public static DataTable SearchProjectById(int projId)
        {
            DataTable     dtProj;
            SqlDataReader projReader = null;

            try
            {
                //Creating command object
                SqlCommand projCommand = DataConnections.GenerateCommand();

                dtProj = new DataTable(/*int projId*/);
                projCommand.CommandText = "Group4.usp_SearchProject";
                projCommand.Parameters.AddWithValue("@Project_Id", projId);
                projCommand.Connection.Open();
                projReader = projCommand.ExecuteReader();
                if (projReader.HasRows)
                {
                    dtProj.Load(projReader);
                }
                projReader.Close();
                projCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtProj);
        }
Example #11
0
        //Method to Add Category to Database
        public static int AddCategory(Category newCat)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand catCommand = DataConnections.GenerateCommand();

                //Assigning command text
                catCommand.CommandText = "Group4.usp_AddCategory";

                //Adding parameters to command
                catCommand.Parameters.AddWithValue("@Category_Name", newCat.CategoryName);
                catCommand.Parameters.AddWithValue("@Category_Description", newCat.CategoryDescription);

                //Executing command
                catCommand.Connection.Open();
                rowsAffected = catCommand.ExecuteNonQuery();
                catCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #12
0
        //Method to Display Level From Database
        public static DataTable DisplayAllLevels()
        {
            DataTable     dtLevel;
            SqlDataReader LevelReader = null;

            try
            {
                //Creating command object
                SqlCommand LevelCommand = DataConnections.GenerateCommand();

                dtLevel = new DataTable();
                LevelCommand.CommandText = "Group4.usp_DisplayAllLevels";
                LevelCommand.Connection.Open();
                LevelReader = LevelCommand.ExecuteReader();
                if (LevelReader.HasRows)
                {
                    dtLevel.Load(LevelReader);
                }
                LevelReader.Close();
                LevelCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(dtLevel);
        }
Example #13
0
        //Method to Display CivilStat From Database
        public static DataTable DisplayAllCivilStats()
        {
            DataTable     dtCivilStat;
            SqlDataReader CivilStatReader = null;

            try
            {
                //Creating command object
                SqlCommand civilStatCommand = DataConnections.GenerateCommand();

                dtCivilStat = new DataTable();
                civilStatCommand.CommandText = "Group4.usp_DisplayAllCivilStatus";
                civilStatCommand.Connection.Open();
                CivilStatReader = civilStatCommand.ExecuteReader();
                if (CivilStatReader.HasRows)
                {
                    dtCivilStat.Load(CivilStatReader);
                }
                CivilStatReader.Close();
                civilStatCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(dtCivilStat);
        }
Example #14
0
        //Method to Search Category From Database
        public static DataTable SearchCategoryById(int catId)
        {
            DataTable     dtCat;
            SqlDataReader catReader = null;

            try
            {
                //Creating command object
                SqlCommand catCommand = DataConnections.GenerateCommand();

                dtCat = new DataTable(/*int catId*/);
                catCommand.CommandText = "Group4.usp_SearchCategories";
                catCommand.Parameters.AddWithValue("@Category_Id", catId);
                catCommand.Connection.Open();
                catReader = catCommand.ExecuteReader();
                if (catReader.HasRows)
                {
                    dtCat.Load(catReader);
                }
                catReader.Close();
                catCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtCat);
        }
Example #15
0
        //Method to Search Speciality From Database
        public static DataTable SearchSpecById(int SpecId)
        {
            DataTable     dtSpec;
            SqlDataReader SpecReader = null;

            try
            {
                //Creating command object
                SqlCommand SpecCommand = DataConnections.GenerateCommand();

                dtSpec = new DataTable(/*int SpecId*/);
                SpecCommand.CommandText = "Group4.usp_SearchSpec";
                SpecCommand.Parameters.AddWithValue("@Speciality_Id", SpecId);
                SpecCommand.Connection.Open();
                SpecReader = SpecCommand.ExecuteReader();
                if (SpecReader.HasRows)
                {
                    dtSpec.Load(SpecReader);
                }
                SpecReader.Close();
                SpecCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtSpec);
        }
Example #16
0
        //Method to Add Speciality to Database
        public static int AddSpeciality(Speciality newSpec)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand SpecCommand = DataConnections.GenerateCommand();

                //Assigning command text
                SpecCommand.CommandText = "Group4.usp_AddSpeciality";

                //Adding parameters to command
                SpecCommand.Parameters.AddWithValue("@Level_Description", newSpec.SpecialityName);


                //Executing command
                SpecCommand.Connection.Open();
                rowsAffected = SpecCommand.ExecuteNonQuery();
                SpecCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #17
0
        //Method to Display Speciality From Database
        public static DataTable DisplayAllSpeciality()
        {
            DataTable     dtSpec;
            SqlDataReader SpecReader = null;

            try
            {
                //Creating command object
                SqlCommand SpecCommand = DataConnections.GenerateCommand();

                dtSpec = new DataTable();
                SpecCommand.CommandText = "Group4.usp_DisplayAllSpec";
                SpecCommand.Connection.Open();
                SpecReader = SpecCommand.ExecuteReader();
                if (SpecReader.HasRows)
                {
                    dtSpec.Load(SpecReader);
                }
                SpecReader.Close();
                SpecCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(dtSpec);
        }
Example #18
0
        //Method to Add Level to Database
        public static int AddLevel(Level newLevel)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand LevelCommand = DataConnections.GenerateCommand();

                //Assigning command text
                LevelCommand.CommandText = "Group4.usp_AddLevel";

                //Adding parameters to command
                LevelCommand.Parameters.AddWithValue("@Level_Description", newLevel.LevelDescription);


                //Executing command
                LevelCommand.Connection.Open();
                rowsAffected = LevelCommand.ExecuteNonQuery();
                LevelCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #19
0
        //Method to Search Level From Database
        public static DataTable SearchLevelById(int levelId)
        {
            DataTable     dtLevel;
            SqlDataReader LevelReader = null;

            try
            {
                //Creating command object
                SqlCommand LevelCommand = DataConnections.GenerateCommand();

                dtLevel = new DataTable(/*int LevelId*/);
                LevelCommand.CommandText = "Group4.usp_SearchLevel";
                LevelCommand.Parameters.AddWithValue("@Level_Id", levelId);
                LevelCommand.Connection.Open();
                LevelReader = LevelCommand.ExecuteReader();
                if (LevelReader.HasRows)
                {
                    dtLevel.Load(LevelReader);
                }
                LevelReader.Close();
                LevelCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtLevel);
        }
Example #20
0
        //Method to Display HR Clerk/Manager From Database
        public static DataTable DisplayUsers()
        {
            DataTable     dtEmp;
            SqlDataReader empReader = null;

            try
            {
                //Creating command object
                SqlCommand empCommand = DataConnections.GenerateCommand();

                dtEmp = new DataTable();
                empCommand.CommandText = "Group4.usp_DisplayAllUsers";
                empCommand.Connection.Open();
                empReader = empCommand.ExecuteReader();
                if (empReader.HasRows)
                {
                    dtEmp.Load(empReader);
                }
                empReader.Close();
                empCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(dtEmp);
        }
Example #21
0
        //Method to Delete Category From Database
        public static int DeleteCategory(int catId)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand catCommand = DataConnections.GenerateCommand();
                //Assigning command text

                catCommand.CommandText = "Group4.usp_DeleteCategory";
                catCommand.Parameters.AddWithValue("@Category_Id", catId);

                catCommand.Connection.Open();
                rowsAffected = catCommand.ExecuteNonQuery();
                catCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #22
0
        //Method to Modify CivilStat From Database
        public static int UpdateCivilStat(CivilStatus newCivilStat)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand civilStatCommand = DataConnections.GenerateCommand();
                //Assigning command text

                civilStatCommand.CommandText = "Group4.usp_UpdateCivilStatus";
                civilStatCommand.Parameters.AddWithValue("@Status_Description", newCivilStat.StatusDescription);

                civilStatCommand.Connection.Open();
                rowsAffected = civilStatCommand.ExecuteNonQuery();
                civilStatCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #23
0
        //Method to Delete HR Clerk/Manager From Database
        public static int DeleteClerk(int userId)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand empCommand = DataConnections.GenerateCommand();
                //Assigning command text

                empCommand.CommandText = "Group4.usp_DeleteUser";
                empCommand.Parameters.AddWithValue("@userId", userId);

                empCommand.Connection.Open();
                rowsAffected = empCommand.ExecuteNonQuery();
                empCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #24
0
        //Method to Search HR Clerk/Manager From Database
        public static DataTable SearchUserById(int userId)
        {
            DataTable     dtEmp;
            SqlDataReader empReader = null;

            try
            {
                //Creating command object
                SqlCommand empCommand = DataConnections.GenerateCommand();

                dtEmp = new DataTable(/*int EmpId*/);
                empCommand.CommandText = "Group4.usp_SearchUser";
                empCommand.Parameters.AddWithValue("@UserId", userId);
                empCommand.Connection.Open();
                empReader = empCommand.ExecuteReader();
                if (empReader.HasRows)
                {
                    dtEmp.Load(empReader);
                }
                empReader.Close();
                empCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtEmp);
        }
Example #25
0
        //Method to load Role of user during login
        public static DataTable LoadUserRoles()
        {
            DataTable     dtDept;
            SqlDataReader empReader = null;

            try
            {
                //Creating command object
                SqlCommand empCommand = DataConnections.GenerateCommand();

                dtDept = new DataTable();
                empCommand.CommandText = "Group4.usp_DisplayUserRole";
                empCommand.Connection.Open();
                empReader = empCommand.ExecuteReader();
                dtDept.Load(empReader);
                empReader.Close();
                empCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(dtDept);
        }
Example #26
0
        //Method to Add Employee to Database
        public static int AddEmployee(Employee newEmp, CapgeminiDetails capg)
        {
            int rowsAffected = 0;

            try
            {
                //Creating command object
                SqlCommand empCommand = DataConnections.GenerateCommand();

                //Assigning command text
                empCommand.CommandText = "Group4.usp_AddEmployee";

                //Adding parameters to command
                empCommand.Parameters.AddWithValue("@Employee_Id", capg.EmployeeId);
                empCommand.Parameters.AddWithValue("@First_Name", newEmp.FirstName);
                empCommand.Parameters.AddWithValue("@Middle_Name", newEmp.MiddleName);
                empCommand.Parameters.AddWithValue("@Last_Name", newEmp.LastName);
                empCommand.Parameters.AddWithValue("@Birth_Date", newEmp.BirthDate);
                empCommand.Parameters.AddWithValue("@Age", newEmp.Age);
                empCommand.Parameters.AddWithValue("@Gender", newEmp.Gender);
                empCommand.Parameters.AddWithValue("@Civil_Status", newEmp.CivilStatusId);
                empCommand.Parameters.AddWithValue("@Religion", newEmp.Religion);
                empCommand.Parameters.AddWithValue("@Citizenship", newEmp.Citizenship);
                empCommand.Parameters.AddWithValue("@Mobile_No", newEmp.MobileNo);
                empCommand.Parameters.AddWithValue("@Home_Phone_No", newEmp.HomePhoneNo);
                empCommand.Parameters.AddWithValue("@Address_", newEmp.Address);
                empCommand.Parameters.AddWithValue("@City", newEmp.City);
                empCommand.Parameters.AddWithValue("@State_", newEmp.State);
                empCommand.Parameters.AddWithValue("@Pincode", newEmp.Pincode);
                empCommand.Parameters.AddWithValue("@Country", newEmp.Country);
                empCommand.Parameters.AddWithValue("@Project_Id", newEmp.Project_Id);
                empCommand.Parameters.AddWithValue("@Skill_Id", newEmp.SkillId);
                empCommand.Parameters.AddWithValue("@Educational_Background", newEmp.EducationalBackground);
                empCommand.Parameters.AddWithValue("@Email", capg.Email);
                empCommand.Parameters.AddWithValue("@Level_Id", capg.LevelId);
                empCommand.Parameters.AddWithValue("@Date_Hired", capg.DateHired);
                empCommand.Parameters.AddWithValue("@Speciality", capg.SpecialityId);

                //Executing command
                empCommand.Connection.Open();
                rowsAffected = empCommand.ExecuteNonQuery();
                empCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }
Example #27
0
        //Method to Verify HR Clerk/Manager Login From Database
        public static bool VerifyLogin(string userName, string password, int roleId)
        {
            bool          rowsAffected = false;
            SqlDataReader empReader    = null;

            try
            {
                //Creating command object
                SqlCommand empCommand = DataConnections.GenerateCommand();
                empCommand.CommandText = "Group4.VerifyLogin";

                empCommand.Parameters.AddWithValue("@UserName", userName);
                empCommand.Parameters.AddWithValue("@pass", password);
                empCommand.Parameters.AddWithValue("@rId", roleId);
                empCommand.Connection.Open();
                empReader = empCommand.ExecuteReader();
                if (empReader.HasRows)
                {
                    empReader.Read();
                    if (userName == empReader["UserName"].ToString() && password == empReader["Password_"].ToString())
                    {
                        rowsAffected = true;
                    }
                }
                empReader.Close();
                empCommand.Connection.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(rowsAffected);
        }