/// <summary> /// Verifies a person's login information and retrieves full name and role /// </summary> /// <param name="username">As a string</param> /// <param name="password">As a string</param> /// <returns></returns> public DataTable GetLogin(string username, string password) { DataTable dt = new DataTable(); string selectStatement = "SELECT l.personID, l.userName, l.password, (p.first_name + ' ' + p.last_name) AS 'name', p.role" + "FROM login l JOIN person p ON p.id = l.personID" + "WHERE username = @username AND password = @password"; using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection()) { connection.Open(); SqlCommand sqlCommand = new SqlCommand(selectStatement, connection); sqlCommand.Parameters.AddWithValue("@username", username); sqlCommand.Parameters.AddWithValue("@password", password); SqlDataReader reader = sqlCommand.ExecuteReader(); dt.Columns.Add("personID", typeof(int)); dt.Columns.Add("userName", typeof(string)); dt.Columns.Add("password", typeof(string)); dt.Columns.Add("name", typeof(string)); dt.Columns.Add("role", typeof(string)); dt.Load(reader); } return(dt); }
public List <Shift> GetShifts() { SqlConnection connection = ScheduleManager_DB_Connection.GetConnection(); List <Shift> shiftList = new List <Shift>(); string selectStatement = "SELECT s.id, s.scheduleShiftId, s.personId, s.positionId, sh.scheduledStartTime, sh.scheduledEndTime, " + "sh.scheduledLunchBreakStartTime, sh.scheduledLunchBreakEndTime, sh.actualStartTime, sh.actualEndTime, sh.actualLunchBreakStart, " + "sh.acutalLunchBreakEnd, p.first_name, p.last_name, ps.position_title " + "FROM shift AS s " + "JOIN shiftHours AS sh ON s.scheduleShiftId = sh.id " + "JOIN person AS p ON s.personId = p.id " + "JOIN position AS ps ON s.positionId = ps.id"; using (connection) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Shift shift = new Shift(); shift.personLastName = reader["lastName"].ToString(); shift.personFirstName = reader["firstName"].ToString(); shiftList.Add(shift); } } } } return(shiftList); }
/// <summary> /// this method returns all employees /// </summary> /// <returns></returns> public List <Person> GetDesiredPersons(string whereClause) { List <Person> persons = new List <Person>(); string desiredEmployees = this.selectedPersons + whereClause; using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(desiredEmployees, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Person person = new Person(); person.Id = (int)reader["id"]; person.LastName = reader["last_name"].ToString(); person.FirstName = reader["first_name"].ToString(); person.DateOfBirth = (DateTime)reader["date_of_birth"]; person.Ssn = (char)reader["ssn"]; person.Gender = reader["gender"].ToString(); person.StreetAddress = reader["street_address"].ToString(); person.Phone = reader["phone"].ToString(); person.Zipcode = reader["zipcode"].ToString(); person.Username = reader["username"].ToString(); person.Password = (byte[])reader["password"]; person.RoleId = (int)reader["roleId"]; person.StatusId = (int)reader["statusId"]; persons.Add(person); } } return(persons); } } }
/// <summary> /// This method adds an accepted person to the database /// /// /// Need to set in the View an appropiate auto generated-- password - roleId - StatusId - /// /// </summary> /// <param name="addPerson"></param> public void AddPerson(Person addPerson) { int addedPersonId = -1; try { using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection()) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { string insertPerson = "INSERT person(" + "[last_name]" + " ,[first_name]" + " ,[date_of_birth]" + " ,[ssn]" + " ,[gender]" + " ,[street_address]" + " ,[phone]" + " ,[zipcode]" + " ,[username]" + " ,[password]" + " ,[roleId]" + " ,[statusId])" + " VALUES(" + " @last_name" + ", @first_name" + ", @date_of_birth" + ", @ssn" + ", @gender" + ", @street_address" + ", @phone" + ", @zipcode" + ", @username" + ", HASHBYTES('SHA2_256', @password)" + ", @roleId" + ", @statusId)"; using (SqlCommand command = new SqlCommand(insertPerson, connection)) { command.Parameters.Add(new SqlParameter("@last_name", addPerson.LastName)); command.Parameters.Add(new SqlParameter("@first_name", addPerson.FirstName)); command.Parameters.Add(new SqlParameter("@date_of_birth", addPerson.DateOfBirth)); command.Parameters.Add(new SqlParameter("@ssn", addPerson.Ssn)); command.Parameters.Add(new SqlParameter("@gender", addPerson.Gender)); command.Parameters.Add(new SqlParameter("@street_address", addPerson.StreetAddress)); command.Parameters.Add(new SqlParameter("@phone", addPerson.Phone)); command.Parameters.Add(new SqlParameter("@zipcode", addPerson.Zipcode)); command.Parameters.Add(new SqlParameter("@username", addPerson.Username)); command.Parameters.Add(new SqlParameter("@password", addPerson.Password)); command.Parameters.Add(new SqlParameter("@roleId", addPerson.Password)); command.Parameters.Add(new SqlParameter("@statusID", addPerson.Password)); string selectStatement = "SELECT IDENT_CURRENT('Person') FROM Person"; using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Transaction = transaction; addedPersonId = Convert.ToInt32(selectCommand.ExecuteScalar()); } transaction.Commit(); } } } } catch (Exception ex) { //Message Box won't work - thats wierd //MessageBox.Show(ex.Message, "Error"); } }