11
To begin with, no I'm not using an ORM, nor am I allowed to. I have to hand-roll my repositories using ADO.NET.
I have two objects:
public class Firm
{
public Guid Id { get; set; }
public string Name { get; set; }
public virtual IEnumerable<User> Users { get; set; }
}
public class User
{
public Guid Id { get; set; }
public string Username { get; set; }
public Firm Firm { get; set; }
}
note the references to each other, a Firm has a list of Users, each User has only one Firm.
Now I want to design my repositories:
public interface IFirmRepository
{
IEnumerable<Firm> FindAll();
Firm FindById(Guid id);
}
public interface IUserRepository
{
IEnumerable<User> FindAll();
IEnumerable<User> FindByFirmId(Guid firmId);
User FindById(Guid id);
}
So far, so good. I want to load the Firm for each User from my UserRepository. The FirmRepository knows how to create a Firm from persistence, so I'd like ot keep that knowledge with the FirmRepository.
public class UserRepository : IUserRepository
{
private IFirmRepository _firmRepository;
public UserRepository(IFirmRepository firmRepository)
{
_firmRepository = firmRepository;
}
public User FindById(Guid id)
{
User user = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select id, username, firm_id from users where u.id = @ID";
SqlParameter userIDParam = new SqlParameter("@ID", id);
command.Parameters.Add(userIDParam);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
user = C