public Employee Add(Employee employee)
{
Int32 newEmployeeId = 0;
const string sql = "INSERT INTO Employees VALUES (@FirstName, @LastName, @Email, @DepartmentId);" +
"SELECT CAST(scope_identity() AS int)";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.Parameters.AddWithValue("@Email", employee.Email);
cmd.Parameters.AddWithValue("@DepartmentId", employee.DepartmentId);
try
{
conn.Open();
newEmployeeId = (Int32)cmd.ExecuteScalar();
conn.Close();
}
catch (Exception ex)
{
return null;
}
}
employee.Id = (int)newEmployeeId;
return employee;
}
public void ShouldInsertAddressAsWellAsContactUsingSaveToDatabase()
{
var employee = new Employee
{
FirstName = "Quality",
LastName = "Assurance",
Email = "*****@*****.**",
DepartmentId = 5,
};
var address = new Address
{
StreetAddress = "1 Infinite Loop",
StateId = 5,
City = "Cupertino",
ZipCode = "95014"
};
employee.Addresses.Add(address);
//Act
_employeeRepository.Save(employee);
//Assert
employee.Id.Should().NotBe(0, "Identity should be assigned by the database");
employee.Addresses.Count().Should().NotBe(0);
employee.Addresses[0].StreetAddress.Should().Be("1 Infinite Loop");
}
public void Save(Employee employee)
{
using (var transactionScope = new TransactionScope())
{
if (employee.IsNew)
{
Add(employee);
}
else
{
Update(employee);
}
foreach (var address in employee.Addresses)
{
address.EmployeeId = employee.Id;
if (address.IsNew)
{
Add(address);
}
else
{
Update(address);
}
}
foreach (var roleId in employee.Roles.Select(x => x.Id).Distinct())
{
Add(employee.Id, roleId);
}
transactionScope.Complete();
}
}
public Employee Add(Employee employee)
{
try
{
employee.Id = _db.Query<int>("InsertNewEmployee", employee).Single();
}
catch (SqlException slException)
{
return null;
}
return employee;
}
public Employee Add(Employee employee)
{
const string sql = "INSERT INTO Employees (FirstName, LastName, Email, DepartmentId) VALUES (@FirstName, @LastName, @Email, @DepartmentId);" +
"SELECT CAST(SCOPE_IDENTITY() as int)";
try
{
employee.Id = _db.QuerySql<int>(sql, employee).Single();
}
catch (SqlException slException)
{
return null;
}
return employee;
}
private long Add()
{
var stopwatch = new Stopwatch();
var employee = new Employee
{
FirstName = "TestAdd",
LastName = "TestAdd",
Email = "*****@*****.**",
DepartmentId = 1
};
stopwatch.Start();
_employeeRepository.Add(employee);
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
public IEnumerable<Employee> GetAll()
{
const string sqlQuery = "SELECT Id, FirstName, LastName, Email, DepartmentId FROM Employees";
var listOfEmployees = new List<Employee>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand(sqlQuery, connection);
try
{
connection.Open();
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
var tempEmployee = new Employee
{
Id = (int)dataReader[0],
FirstName = (string)dataReader[1],
LastName = (string)dataReader[2],
Email = (string)dataReader[3],
DepartmentId = (int)dataReader[4]
};
listOfEmployees.Add(tempEmployee);
}
dataReader.Close();
connection.Close();
}
catch (SqlException sqlException)
{
return null;
}
}
return listOfEmployees;
}
public void ShouldInsertEmployeeIntoDatabase()
{
//Arrange
var employee = new Employee
{
FirstName = "Test",
LastName = "Dummy",
Email = "*****@*****.**",
DepartmentId = 5,
};
//Act
_employeeRepository.Add(employee);
//Assert
employee.Id.Should().NotBe(0, "Identity should be assigned by the database");
}
private int InsertUserToBeModified()
{
var employee = new Employee
{
FirstName = "Modify",
LastName = "Test",
Email = "*****@*****.**",
DepartmentId = 5,
};
var address = new Address
{
StreetAddress = "1 Infinite Loop",
StateId = 5,
City = "Cupertino",
ZipCode = "95014"
};
employee.Addresses.Add(address);
//Act
_employeeRepository.Save(employee);
//Assert
employee.Id.Should().NotBe(0, "Identity should be assigned by the database");
employee.Addresses.Count().Should().NotBe(0);
employee.Addresses[0].StreetAddress.Should().Be("1 Infinite Loop");
return employee.Id;
}
private int InsertSimpleUserToBeModified()
{
//Arrange
var employee = new Employee
{
FirstName = "Test",
LastName = "Dummy",
Email = "*****@*****.**",
DepartmentId = 5,
};
//Act
_employeeRepository.Add(employee);
//Assert
employee.Id.Should().NotBe(0, "Identity should be assigned by the database");
return employee.Id;
}
public void ShouldReturnNullIfInsertFails()
{
//Arrange
var employee = new Employee
{
Id = 2,
FirstName = "Fail"
};
//Act
var result = _employeeRepository.Add(employee);
//Assert
result.Should().BeNull();
}
public Employee Update(Employee employee)
{
const string sql = "UPDATE Employees " +
"SET FirstName = @FirstName, " +
" LastName = @LastName, " +
" Email = @Email, " +
" DepartmentId = @DepartmentId " +
" WHERE Id = @Id";
_db.Execute(sql, employee);
return employee;
}
public Employee GetById(int employeeId)
{
Employee employee = null;
const string sqlQuery =
"Select Id, FirstName, LastName, Email, DepartmentId FROM Employees WHERE Id = @employeeId";
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand(sqlQuery, connection);
command.Parameters.AddWithValue("@employeeId", employeeId);
try
{
connection.Open();
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
employee = new Employee
{
Id = (int)dataReader[0],
FirstName = (string)dataReader[1],
LastName = (string)dataReader[2],
Email = (string)dataReader[3],
DepartmentId = (int)dataReader[4]
};
}
dataReader.Close();
connection.Close();
}
catch (SqlException sqlException)
{
return null;
}
}
return employee;
}
public Employee Update(Employee employee)
{
const string sql = "UPDATE Employees " +
"SET FirstName = @FirstName, " +
" LastName = @LastName, " +
" Email = @Email, " +
" DepartmentId = @DepartmentId " +
" WHERE Id = @Id";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.Parameters.AddWithValue("@Email", employee.Email);
cmd.Parameters.AddWithValue("@DepartmentId", employee.DepartmentId);
cmd.Parameters.AddWithValue("@Id", employee.Id);
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
return null;
}
}
return employee;
}
public Employee GetFullEmployeeInfo(int employeeId)
{
Employee employee = null;
const string sqlForEmployee = "SELECT Id, FirstName, LastName, Email, DepartmentId FROM Employees WHERE Id = @EmployeeId;";
const string sqlForAddress = "SELECT Id, EmployeeId, StreetAddress, City, StateId, ZipCode FROM Addresses WHERE EmployeeId = @EmployeeId;";
const string sqlForRoles = "SELECT Role.Id, Role.Name FROM AssignedRoles, Role " +
"WHERE Role.Id = AssignedRoles.RoleId AND " +
"AssignedRoles.EmployeeId = @EmployeeId;";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand cmdForEmployee = new SqlCommand(sqlForEmployee, conn);
cmdForEmployee.Parameters.AddWithValue("@EmployeeId", employeeId);
conn.Open();
var dataReader = cmdForEmployee.ExecuteReader();
while (dataReader.Read())
{
employee = new Employee
{
Id = (int)dataReader[0],
FirstName = (string)dataReader[1],
LastName = (string)dataReader[2],
Email = (string)dataReader[3],
DepartmentId = (int)dataReader[4]
};
}
dataReader.Close();
SqlCommand cmdForAddresses = new SqlCommand(sqlForAddress, conn);
cmdForAddresses.Parameters.AddWithValue("@EmployeeId", employeeId);
var addressReader = cmdForAddresses.ExecuteReader();
if (addressReader.HasRows)
{
while (addressReader.Read())
{
var tempAddress = new Address
{
Id = (int)addressReader[0],
EmployeeId = (int)addressReader[1],
StreetAddress = (string)addressReader[2],
City = (string)addressReader[3],
StateId = (int)addressReader[4],
ZipCode = (string)addressReader[5]
};
if (employee != null)
employee.Addresses.Add(tempAddress);
}
}
addressReader.Close();
SqlCommand cmdForRoles = new SqlCommand(sqlForRoles, conn);
cmdForRoles.Parameters.AddWithValue("@EmployeeId", employeeId);
var assignedRolesReader = cmdForRoles.ExecuteReader();
if (assignedRolesReader.HasRows)
{
while (assignedRolesReader.Read())
{
var tempRole = new Role
{
Id = (int)assignedRolesReader[0],
Name = (string)assignedRolesReader[1]
};
if (employee != null)
employee.Roles.Add(tempRole);
}
}
assignedRolesReader.Close();
conn.Close();
}
return employee;
}
public Employee Update(Employee employee)
{
_db.Execute("UpdateEmployeeInfo", employee);
return employee;
}