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