public IEnumerable <Department> GetAllDepartments() { var departmentIds = new List <Guid>(); var result = new List <Department>(); try { using (var command = _context.CreateCommand()) { command.CommandText = "select ID from Department"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var departmentId = reader.GetGuid(reader.GetOrdinal("ID")); departmentIds.Add(departmentId); } } } foreach (var departmentId in departmentIds) { var department = GetDepartment(departmentId); result.Add(department); } return(result); } catch (Exception e) { Logger.Error(e); throw; } }
public Employee AddEmployee(Employee employee) { try { var newEmployee = new Employee { FirstName = employee.FirstName, Surname = employee.Surname, Patronymic = employee.Patronymic, DateOfBirth = employee.DateOfBirth, DocSeries = employee.DocSeries, DocNumber = employee.DocNumber, Position = employee.Position, DepartmentId = employee.DepartmentId }; using (var cmd = _context.CreateCommand()) { cmd.CommandText = @"insert into Empoyee values (@FirstName,@SurName,@Patronymic,@DateOfBirth,@DocSeries,@DocNumber,@Position,@DepartmentID); SET @ID = SCOPE_IDENTITY()"; cmd.Parameters.Add("@ID", SqlDbType.Int, 5).Direction = ParameterDirection.Output; cmd.Parameters.AddWithValue("@FirstName", employee.FirstName); cmd.Parameters.AddWithValue("@SurName", employee.Surname); cmd.Parameters.AddWithNullValue("@Patronymic", employee.Patronymic); cmd.Parameters.AddWithValue("@DateOfBirth", employee.DateOfBirth); cmd.Parameters.AddWithNullValue("@DocSeries", employee.DocSeries); cmd.Parameters.AddWithNullValue("@DocNumber", employee.DocNumber); cmd.Parameters.AddWithValue("Position", employee.Position); cmd.Parameters.AddWithValue("@DepartmentID", employee.DepartmentId); cmd.ExecuteNonQuery(); newEmployee.Id = (int)cmd.Parameters["@ID"].Value; } return(newEmployee); } catch (Exception e) { Logger.Error(e); throw; } }