Need some help sorting out a major abstract pattern headache within my DAL

Question

I've caused myself a bit of an issue with my Data Access Layer. In this particular instance, I have a table that contains potentially 5 types of 'entity'. These are basically Company, Customer, Site, etc. The type is dictated by a PositionTypeId within the table. They're all in the same table as they all havethe same data structure; PositionId, Description and Code.

I have a main abstract class as follows:

public abstract class PositionProvider<T> : DalProvider<T>, IDalProvider where T : IPositionEntity
{
    public static PositionProvider<T> Instance
    {
        get
        {
            if (_instance == null)
            {
                // Create an instance based on the current database type
            }
            return _instance;
        }
    }
    private static PositionProvider<T> _instance;

    public PositionType PositionType
    {
        get
        {
            return _positionType;
        }
    }
    private PositionType _positionType;

    // Gets a list of entities based on the PositionType enum's value.
    public abstract List<T> GetList();

    internal void SetPositionType(RP_PositionType positionType)
    {
        _positionType = positionType;
    }

}

I want to then be able to put all the general code within an inherting class that is either SQL or Oracle based. This is my SQL implementation:

public class SqlPositionProvider<T> : PositionProvider<T> where T : IPositionEntity
{
        public override List<T> GetList()
        {
            int positionTypeId = (int)this.PositionType;
            using (SqlConnection cn = new SqlConnection(Globals.Instance.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Get_PositionListByPositionTypeId", cn);
                cmd.Parameters.Add("@PositionTypeId", SqlDbType.Int).Value = positionTypeId;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return this.GetCollectionFromReader(this.ExecuteReader(cmd));
            }
        }
}

I've then create a class for each type as follows (this is the CustomerProvider as an example):

public class CustomerProvider
{
    public static PositionProvider<CustomerEntity> Instance
    {
        get
        {
            if ((int)PositionProvider<CustomerEntity>.Instance.PositionType == 0)
            {
                PositionProvider<CustomerEntity>.Instance.SetPositionType(PositionType.Customer);
            }
            return PositionProvider<CustomerEntity>.Instance;
        }
    }
}

This all works fantastically... until I realised that I have certain functions that are related specifically to certain position types. I.e. I need to be able to get all Customers (which is an IPositionType) based on the user permissions.

So I need to add another abstract method:

public abstract List<CustomerEntity> GetCustomersByUserPermission(Guid userId);

Now, obviously I don't want this within my PositionProvider abstract class as that would mean that method would appear when dealing with the site/company provider.

How can I add this, and other, additional methods without having to duplicate the code within the SqlPositionProvider?

Edit:

The only idea I've come up with is to separate the PositionProvider out into a common property of the CustomerProvider, SiteProvider, etcProvider:

public abstract class CustomerProvider
{

    public CustomerProvider()
    {
        this.Common.SetPositionType(PositionType.Customer);
    }

    public PositionProvider<CustomerEntity> Common
    {
        get
        {
            if (_common == null)
            {
                DalHelper.CreateInstance<PositionProvider<CustomerEntity>>(out _common);
            }
            return _common;
        }
    }
    private PositionProvider<CustomerEntity> _common;

    public static CustomerProvider Instance
    {
        get
        {
            if (_instance == null)
            {
                DalHelper.CreateInstance<CustomerProvider>(out _instance);
            }
            return _instance;
        }
    }
    private static CustomerProvider _instance;

    public abstract List<CustomerEntity> GetCustomersByUserPermission(Guid userId);

}

This would allow me to put the specific code within CustomerProvider.Instance.MyNonGenericMethod(), and then to access the PositionProvider I could do CustomerProvider.Instance.Common.GetList()... This does seem like a bit of a hack though.

Answer 1

The "proper" place for such a lookup method would be a Repository class. There you can collect all such query functions away from your domain objects.

Here's a small example:

public static class Repository {
    public static List<CustomerEntity> GetCustomersByUserPermission(
        PositionProvider<CustomerEntity> source, Guid userId)
    {
        // query source and return results
    }
}

Add all your "special" queries to this class.

Answer 2

How about adding something like this to your abstract class:

public IEnumerable<T> GetItems(Predicate<T> match)
{
    foreach (T item in GetList())
    {
        if (match(item))
           yield return item;  
    }
}

And then you should remove the SetPositionType(...) method because it's usage seems a bit awkward (you are supposed to set a position type and then call GetList()?)

So, using the method you could simply write:

customerProvider.GetItems(customer => customer.Id == someId);

(or, using the .Net 2.0 syntax)

customerProvider.GetItems(delegate(Customer c)
{
     return c.Id == someId;
});

Answer 3

If I understand correctly you need a way to include some method on child classes, but not in all.

If you can group the extra methods you need, you could use an interface, implement it, and use a instance of this new class inside your children (composition).

A simplification of this is a repository pattern for all your children classes (this example does not uses interfaces).

[NOTE: code could not compile, just for demonstration proposes]

public class PositionProviderRepository
{
    public List<T> GetList()
        {
            int positionTypeId = (int)this.PositionType;
            using (SqlConnection cn = new SqlConnection(Globals.Instance.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Get_PositionListByPositionTypeId", cn);
                cmd.Parameters.Add("@PositionTypeId", SqlDbType.Int).Value = positionTypeId;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return this.GetCollectionFromReader(this.ExecuteReader(cmd));
            }
        }
    public List<CustomerEntity> GetCustomersByUserPermission(Guid userId) {
      //TODO: implementation
    }
}

And then you use this class inside the all the entities like CustomerEntity.

This could effectively replace your class SqlPositionProvider<T> but I'm not sure I understand correctly your architecture, you have a very complex hierarchy.

Answer 4

First, .NET BCL has a nice abstraction level for different DBMSes defined in System.Data.Common. By using DbConnection instead of SqlConnection/OracleConnection, DbCommand instead of SqlCommand/OracleCommand etc you'll be able to reduce code duplication a bit (there would be gotchas, such as differencies in parameter naming but they can be overcome).

Second, IMHo it is a bad idea to build all your code around singletons. Why wouldn't you write

public class CustomerProvider
{
    PositionProvider<CustomerEntity> _provider;
    PositionProvider<CustomerEntity> Instance // we don't need it public really.
    {
        get
        {
            if ((int)PositionProvider<CustomerEntity>.Instance.PositionType == 0)
            {
                _provider = new PositionProvider<CustomerEntity>(); // PositionType is set in .ctor
                // we can also use a factory to abstract away DB differences
            }
            return _provider;
        }
    }
    // one way of implementing custom query
    public List<CustomerEntity> GetCustomersByUserPermission(Guid userId){
        return _provider.GetListWithCriteria(Criteria.Argument("UserId", userId));
    }
}

Method GetListWithCriteria can be implemented as:

public List<CustomerEntity> GetListWithCriteria(params ICriterion[] criterias){
        int positionTypeId = (int)this.PositionType;
        using (DbConnection cn = OpenConnection()) // creates DbConnection and opens it
        using (DbCommand cmd = cn.CreateCommand())
        {
            // ... setting command text ...
            foreach(ICriterion c in criterias){
                DbParameter p = cmd.CreateParameter();
                p.DbType = c.DbType;
                p.Name = Encode(c.Name); // add '@' for MS SQL, ':' for Oracle
                p.Value = c.Value;
                cmd.AddParameter(p);
            }
            return this.GetCollectionFromReader(this.ExecuteReader(cmd));
        }        
}

In such a way PositionProvider remains a way of abstracting away DBMS differences and CustomerProviders can built arbitrary new queries.

Answer 5

I've cracked it. My Inheriting class has now become the following:

public abstract class CustomerProvider : PositionProvider<CustomerEntity>
{

        public CustomerProvider() { }

        public new static CustomerProvider Instance
        {
            get
            {
                if (_instance == null)
                {
                    DalHelper.CreateInstance<CustomerProvider>(out _instance);
                }
                return _instance;
            }
        }
        private static CustomerProvider _instance;

        public override List<CustomerEntity> GetList()
        {
            return PositionProvider<CustomerEntity>.Instance.GetList();
        }

        public abstract List<CustomerEntity> GetCustomersByUserPermission(Guid userId);

}

It has a concrete implementation as follows:

public class SqlCustomerProvider : CustomerProvider
{
    public override List<CustomerEntity> GetCustomersByUserPermission(Guid userId)
    {
        using (SqlConnection cn = new SqlConnection(Globals.Instance.ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("GetRP_CustomersByUser", cn);
            cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = userId;
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            return this.GetCollectionFromReader(this.ExecuteReader(cmd));
        }
    }
}

My PositionProvider remains the same, but by calling it in the overrides in the extending CustomerProvider, it then uses the SqlPositionProvider for the provider specific code.

I can now achieve what I wanted.

// Returns a list of customers still using the PositionProvider
CustomerProvider.Instance.GetList(); 

// Returns my specific customer data
CustomerProvider.Instance.GetCustomersByUserPermission();

// Returns a list of sites still using the PositionProvider
SiteProvider.Instance.GetList(); 

// Not part of the SiteProvider!
SiteProvider.Instance.GetCustomersByUserPermission();