Which of the two design patterns should I implement in a 3-tier Asp.Net application?

Question

I’m trying to decide which of the two factory patterns I should use in my Asp.Net applications:

1 : All DAL providers derive from same abstract base class DepartmentsProvider, which defines public interface of the class ( all the necessary CRUD methods for which derived classes (providers ) provide a concrete implementation ). BLL layer instantiates correct provider by calling DepartmentsProvider.Instance:

public abstract class DepartmentsProvider
{
   static private DepartmentsProvider _instance = null;
   /// <summary>
   /// Returns an instance of the provider type specified in the config file
   /// </summary>
   static public DepartmentsProvider Instance
   {
       get
       {
           if (_instance == null)
               _instance = (DepartmentsProvider)Activator.CreateInstance(
                  Type.GetType(Settings.Departments.ProviderType));
           return _instance;
       }
   }

   public abstract List<Department> GetDepartments ();
   }
   /// Concrete provider
   public class SqlDepartmentsProvider : DepartmentsProvider
   {
       public override List<Department> GetDepartments()
       {
           using (SqlConnection cn = new SqlConnection(this.ConnectionString))
           {...}
       }
   }

2 :

public class DepartmentsProvider
{
    static private DbProviderFactory _instance = null;
    /// <summary>
    /// Returns an instance of the FactoryProvider type specified in the config file
    /// </summary>
    static public DbProviderFactory Instance
    {
        get
        {
            if (_instance == null)
                _instance = (DepartmentsProvider)Activator.CreateInstance(
                   Type.GetType(Settings.Departments.ProviderType));
            return _instance;
        }
    }

    public static List<ForumDetails> GetDepartments()
    {
        DbConnection cn = Instance.CreateConnection();
        ...
    }
}

In first case we implement new provider by deriving from DepartmentsProvider class, while in second case new provider is implemented by deriving from DBProviderFactory.

What are pros and cons of each implementation?

Much appreciated

Answer 1

Like RPM1984 I would suggest looking at the repository pattern and dependency injection.

You wouldn't want to use a singleton here as the connections may remain open and you may run into unforeseen issues.