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