public IEnumerable<DataContracts.IPerson> Search(PersonSearchParameters parameters)
{
var people = new List<IPerson>();
using (var connection = new SqlConnection(base.ConnectionString))
{
using (var command = new SqlCommand("sp_PersonSearch", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@firstName", (object)parameters.FirstName ?? DBNull.Value);
command.Parameters.AddWithValue("@middleName", (object)parameters.MiddleName ?? DBNull.Value);
command.Parameters.AddWithValue("@lastName", (object)parameters.LastName ?? DBNull.Value);
var adapter = new PersonAdapter();
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
people.Add((Person)adapter.Resolve(new Person(), reader));
}
}
}
return people;
}
public IEnumerable<IEmployeeProjectAssignment> GetEmployeesAssignedToProject(int projectId)
{
var assignments = new List<IEmployeeProjectAssignment>();
using (var connection = new SqlConnection(base.ConnectionString))
{
var adapter = new ProjectAdapter();
using (var command = new SqlCommand("sp_ProjectRetrieveEmployees", connection))
{
command.Parameters.AddWithValue("@projectId", projectId);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
var reader = command.ExecuteReader();
var personAdapter = new PersonAdapter();
while (reader.Read())
{
var assignment = new EmployeeProjectAssignment();
assignment.ProjectId = projectId;
assignment.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
assignment.Person = personAdapter.Resolve(new Person(), reader);
assignments.Add(assignment);
}
connection.Close();
}
}
return assignments;
}
public IEnumerable<DataContracts.IEmployee> GetAllEmployees(int companyId)
{
var employees = new List<IEmployee>();
using (var connection = new SqlConnection(base.ConnectionString))
{
using (var command = new SqlCommand("sp_CompanyRetrieveEmployees", connection))
{
command.Parameters.AddWithValue("@companyId", companyId);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
var reader = command.ExecuteReader();
var personAdapter = new PersonAdapter();
while (reader.Read())
{
var employee = new Employee();
employee.CompanyId = Convert.ToInt32(reader["companyId"]);
employee.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
employee.Person = personAdapter.Resolve(new Person(), reader);
employees.Add(employee);
}
connection.Close();
}
}
return employees;
}
