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");
}
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 void Add(Address address)
{
const string sql =
"INSERT INTO Addresses (EmployeeId, StreetAddress, City, StateId, ZipCode) VALUES (@EmployeeId, @StreetAddress, @City, @StateId, @ZipCode);";
_db.Execute(sql, address);
}
private void Update(Address address)
{
_db.Execute("UPDATE Addresses " +
"SET StreetAddress = @StreetAddress, " +
"City = @City, " +
"StateId = @StateId, " +
"ZipCode = @ZipCode " +
"WHERE Id = @Id", address);
}
private void Update(Address address)
{
const string sql = "UPDATE Addresses " +
"SET StreetAddress = @StreetAddress, " +
"City = @City, " +
"StateId = @StateId, " +
"ZipCode = @ZipCode " +
"WHERE Id = @Id";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@StreetAddress", address.StreetAddress);
cmd.Parameters.AddWithValue("@City", address.City);
cmd.Parameters.AddWithValue("@StateId", address.StateId);
cmd.Parameters.AddWithValue("@ZipCode", address.ZipCode);
cmd.Parameters.AddWithValue("@Id", address.Id);
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
}
}
}
private void Add(Address address)
{
const string sql =
"INSERT INTO Addresses (EmployeeId, StreetAddress, City, StateId, ZipCode) VALUES (@EmployeeId, @StreetAddress, @City, @StateId, @ZipCode);";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@EmployeeId", address.EmployeeId);
cmd.Parameters.AddWithValue("@StreetAddress", address.StreetAddress);
cmd.Parameters.AddWithValue("@City", address.City);
cmd.Parameters.AddWithValue("@StateId", address.StateId);
cmd.Parameters.AddWithValue("@ZipCode", address.ZipCode);
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
}
}
}
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;
}
private void Update(Address address)
{
_db.Execute("UpdateAddress", address);
}
private void Add(Address address)
{
_db.Execute("InsertAddressForEmployee", address);
}