/// <summary> /// Получение списка сотрудников /// </summary> /// <param name="session"></param> /// <returns></returns> public List <OfficerData> GetOfficers(string session) { var result = new List <OfficerData>(); try { using (var _connection = new NpgsqlConnection(MainConnection.GetConnectionString())) { _connection.Open(); var sqlQuery = string.Format(@"select ID, FirstName, Surname, Patronymic, BirthDate from {0}Officer", MainConnection.GetSQLNamespace()); using (var _postgreCommand = new NpgsqlCommand(sqlQuery, _connection)) using (var _reader = _postgreCommand.ExecuteReader()) { if (_reader.Read()) { var officer = new OfficerData(); officer.ID = Convert.ToInt32(_reader["ID"]); officer.Name = _reader["FirstName"].ToString(); officer.SurName = _reader["Surname"].ToString(); officer.Patronymic = _reader["Patronymic"].ToString(); officer.BirthDate = _reader["BirthDate"] == null ? DateTime.MinValue : Convert.ToDateTime(_reader["BirthDate"]); officer.Posts = GetPostsInfoByOfficerID(Convert.ToInt32(_reader["ID"]), session); result.Add(officer); } } } } catch (Exception e) { throw new Exception("Ошибка получения списка сотрудников.", e); } return(result); }
/// <summary> /// Получение информации о сотруднике /// </summary> /// <param name="ID"></param> /// <param name="session"></param> /// <returns></returns> public OfficerData GetOfficerInfoByID(int ID, string session) { OfficerData result = null; if (_setting.Value.ChachedEmployee) { try { result = _cache.GetOfficer(ID, session); if (result != null) { return(result); } } catch (Exception e) { // Логгирование ошибки, работа продолжится через БД } } try { using (var _connection = new NpgsqlConnection(MainConnection.GetConnectionString())) { _connection.Open(); var sqlQuery = string.Format(@"select ID, FirstName, Surname, Patronymic, BirthDate from {0}Officer where ID = @id", MainConnection.GetSQLNamespace()); using (var _postgreCommand = new NpgsqlCommand(sqlQuery, _connection)) { _postgreCommand.Parameters.AddWithValue("id", ID); using (var _reader = _postgreCommand.ExecuteReader()) { if (_reader.Read()) { result = new OfficerData(); result.ID = ID; result.Name = _reader["FirstName"].ToString(); result.SurName = _reader["Surname"].ToString(); result.Patronymic = _reader["Patronymic"].ToString(); result.BirthDate = _reader["BirthDate"] == null ? DateTime.MinValue : Convert.ToDateTime(_reader["BirthDate"]); result.Posts = GetPostsInfoByOfficerID(ID, session); } } } } } catch (Exception e) { throw new Exception("Ошибка получения информации из БД о сотруднике с ID = " + ID, e); } if (_setting.Value.ChachedEmployee) { try { _cache.AddOfficer(ID, result, session); } catch (Exception e) { // Логгирование ошибки, работа продолжится через БД //throw new Exception("Ошибка добавления информации из кэша о сотруднике с ID = " + ID, e); } } return(result); }
/// <summary> /// Создания информации о сотруднике /// </summary> /// <param name="data"></param> /// <param name="session"></param> /// <returns></returns> public OfficerData CreateOfficerInfo(OfficerDataIn data, string session) { var id = -1; var result = new OfficerData(data); var posts = new List <PostData>(); using (var _connection = new NpgsqlConnection(MainConnection.GetConnectionString())) { _connection.Open(); var _transaction = _connection.BeginTransaction(); try { var sqlQueryOfficer = string.Format(@"INSERT INTO {0}Officer (FirstName, Surname, Patronymic, BirthDate) VALUES (@firstname, @surname, @patronymic, @birthdate) RETURNING ID", MainConnection.GetSQLNamespace()); using (var _postgreCommand = new NpgsqlCommand(sqlQueryOfficer, _connection)) { _postgreCommand.Parameters.AddWithValue("firstname", data.Name); _postgreCommand.Parameters.AddWithValue("surname", Utils.NoNullValue(data.SurName)); _postgreCommand.Parameters.AddWithValue("patronymic", Utils.NoNullValue(data.Patronymic)); _postgreCommand.Parameters.AddWithValue("birthdate", data.BirthDate); id = Convert.ToInt32(_postgreCommand.ExecuteScalar()); } foreach (var post in data.Posts) { CreateOfficerPost(id, post.ID, session, _connection); posts.Add(GetPost(post.ID, session)); } _transaction.Commit(); result.ID = id; result.Posts = posts; } catch (Exception e) { _transaction.Rollback(); throw new Exception("Ошибка добавления сотрудника в БД.", e); } } if (_setting.Value.ChachedEmployee) { try { _cache.AddOfficer(id, result, session); } catch (Exception e) { // Логгирование ошибки, работа продолжится через БД //throw new Exception("Ошибка добавления сотрудника в кэш.", e); } } return(result); }