Design of Data Access Layer using Repository without using Entity Framework

Question

I am developing an n-tier application and I have decided to use the Repository Pattern in Data Access Layer but I don't want to use any ORM(going to use ADO.net)

I am not able to decide how to fetch the data in repository where there is relation between 2 entites(not parent child relationship). For e.g. I have Customer and Order table. There is "CustomerID" column in "Order" table which is linked to "Customer" table.

1. How to fetch the Customer Information while I am fetching Orders
   Information(single or Multiple) based on OrderID?
2. Can we use create instance of one repository into another?

Below is the current code structure.

///////////// Abstract Class for Common Functionality////
public abstract class BaseRepository<T> where T : class
{
    private static SqlConnection sqlConnection;
    protected string DatabaseName { get; set; }
    protected string ConnectionString;
    public delegate T CallBack(SqlDataReader reader);

    public BaseRepository()
    {
        ConnectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["TestDB"]);
        sqlConnection = new SqlConnection(ConnectionString);
    }

    protected abstract T PopulateRecord(SqlDataReader reader);

    protected IEnumerable<T> GetRecords(SqlCommand command)
    {
        var list = new List<T>();
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            command.Connection = connection;
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        list.Add(PopulateRecord(reader));
                    }
                }
            } // reader closed and disposed up here
        } //connection closed and disposed here
        return list;
    }

}

////////////////// Repository Class //////////////////////////

public class OrderRepository : BaseRepository<Order>, IRepository<Order>
{
    protected override Order PopulateRecord(SqlDataReader reader)
    {
        return new Order
        {
            OrderID = reader.GetIntVal("OrderID"),
            OrderDate = reader.GetDateTimeVal("OrderDate"),
            ProductInfo= // How to Populate Product Information which is in another repository
            CustomerInfo = // How to Populate Customer Information which is in another repository
        };
    }

    public IEnumerable<Order> GetAll()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "GetOrders";
        cmd.CommandType = CommandType.StoredProcedure;
        return GetRecords(cmd);

    }

}

/////////////// Entity Class ////////////////////
public class Order {

    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public Customer ProductInfo { get; set; }
    public Product CustomerInfo { get; set; }
}

public class Customer {

    public int CustomerID { get; set; }
    public string CustomerName { get; set; }
}

public class Product {

    public int ProductID { get; set; }
    public string ProductName { get; set; }
}

Answer 1

A former colleague and I created SqlRepo that implements the Repository Pattern.

Once you get a repository from an injected factory, you create a command and use fluent API and expressions to build the query before executing it to return entities.

It is very much a work in progress still and light on documentation but happy to answer questions, although the unit tests form pretty good documentation in their own right.

Take a look over at http://github.com/testpossessed/sqlrepo

UPDATE: As an example the solution to your question would be something like this (assuming factory is a private field, usually injected):

var customerRepository = this.repositoryFactory.Create<Customer>();
var customer = customerRepository.Query()
                                 .Where(e => e.Id == 123)
                                 .Go();

var orderRepository = this.repositoryFactory.Create<Order>();
customer.Orders = orderRepository.Query()
                                 .Where(e => e.CustomerId == 123)
                                 .Go();