//A method that gets office staff from the database and returns list of office staff public static List <ClassEmployee> EmployeeList() { string querry = "SELECT [Employee_id],[Name],[Surname],[Phone_number],[Date_of_birth],[Personal_identity_number],[User_id] FROM [db_Clinic].[dbo].[tbl_Employee]"; SqlConnection sqlCon = new SqlConnection(ConString); if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCommand = new SqlCommand(querry, sqlCon); SqlDataReader dr = sqlCommand.ExecuteReader(); List <ClassEmployee> employeeList = new List <ClassEmployee>(); while (dr.Read()) { ClassEmployee employee = new ClassEmployee(); employee.EmployeeId = dr.GetInt32("Employee_id"); employee.Name = dr.GetString("Name"); employee.Surname = dr.GetString("Surname"); employee.PhoneNumber = dr.GetString("Phone_number"); employee.DateOfBirth = dr.GetDateTime("Date_of_birth"); employee.PersonalIdentityNumber = dr.GetString("Personal_identity_number"); employee.User_id = dr.GetInt32("User_id"); employeeList.Add(employee); } sqlCon.Close(); return(employeeList); }
//Uses Method NotSpecifiedUserIndex and additionally gets logins, emails, and permission id of that users and returns everything in list public static List <ClassEmployee> NotSpecifiedEmployee() { List <int> indexList = NotSpecifiedEmployeeIndex(); List <ClassEmployee> employeeList = new List <ClassEmployee>(); if (indexList.Count == 0) { return(employeeList); } string querry = "USE [db_Clinic] SELECT [Employee_id],[Name],[Surname],[Phone_number],[Date_of_birth],[Personal_identity_number],tbl_Employee.[User_id],tbl_User.Permission_id,tbl_Permission.Type_of_permission FROM [dbo].[tbl_Employee],tbl_User, dbo.tbl_Permission where dbo.tbl_Permission.Permission_id=tbl_User.Permission_id and tbl_Employee.User_id=tbl_User.User_id and [tbl_Employee].Employee_id in("; string lastItem = ""; foreach (int item in indexList) { querry = querry + item.ToString() + ","; lastItem = item.ToString(); } querry = querry + lastItem + ")"; SqlConnection sqlCon = new SqlConnection(ConString); if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCommand = new SqlCommand(querry, sqlCon); SqlDataReader dr = sqlCommand.ExecuteReader(); while (dr.Read()) { ClassEmployee employee = new ClassEmployee(); employee.EmployeeId = dr.GetInt32("Employee_id"); employee.Name = dr.GetString("Name"); employee.Surname = dr.GetString("Surname"); employee.PhoneNumber = dr.GetString("Phone_number"); employee.DateOfBirth = dr.GetDateTime("Date_of_birth"); employee.PersonalIdentityNumber = dr.GetString("Personal_identity_number"); employee.User_id = dr.GetInt32("User_id"); ClassPermission permission = new ClassPermission(); permission.PermissionId = dr.GetInt32("Permission_id"); permission.Permission = dr.GetString("Type_of_permission"); employee.Permission = permission; employeeList.Add(employee); } sqlCon.Close(); return(employeeList); }
//Method that updates office staff in database table when you edit office staff in program public static void UpdateEmployee(ClassEmployee employee) { string querry = "USE [db_Clinic] UPDATE tbl_employee SET[Name] = @Name,[Surname]= @Surname,[Phone_number]= @Phone_number, Date_of_birth = @Date_of_birth WHERE Employee_id = @Employee_id"; SqlConnection sqlCon = new SqlConnection(ConString); if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCommand = new SqlCommand(querry, sqlCon); sqlCommand.Parameters.AddWithValue("@Employee_id", employee.EmployeeId); sqlCommand.Parameters.AddWithValue("@Name", employee.Name); sqlCommand.Parameters.AddWithValue("@Surname", employee.Surname); sqlCommand.Parameters.AddWithValue("@Phone_number", employee.PhoneNumber); sqlCommand.Parameters.AddWithValue("@Date_of_birth", employee.DateOfBirth); SqlDataReader dr = sqlCommand.ExecuteReader(); sqlCon.Close(); }
//Method that adds office staff to database table when you add office staff member in program public static void AddNewEmployee(ClassEmployee employee) { //Add office staff string querryp1 = "USE[db_Clinic] INSERT INTO [dbo].tbl_Employee ([Name],[Surname],[Phone_number],[Date_of_birth],[Personal_identity_number],[User_id]) "; string querryp2 = "VALUES(@Name,@Surname,@Phone_number,@Date_of_birth,@Personal_identity_number,@User_id)"; string querry = querryp1 + querryp2; SqlConnection sqlCon = new SqlConnection(ConString); if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCommand = new SqlCommand(querry, sqlCon); sqlCommand.Parameters.AddWithValue("@Name", employee.Name); sqlCommand.Parameters.AddWithValue("@Surname", employee.Surname); sqlCommand.Parameters.AddWithValue("@Phone_number", employee.PhoneNumber); sqlCommand.Parameters.AddWithValue("@Date_of_birth", employee.DateOfBirth); sqlCommand.Parameters.AddWithValue("@Personal_identity_number", employee.PersonalIdentityNumber); sqlCommand.Parameters.AddWithValue("@User_id", employee.User_id); SqlDataReader dr = sqlCommand.ExecuteReader(); sqlCon.Close(); }