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