How to Create a Data Access Layer in ASP.Net which must be generic?

Question

I have looked several sites CodeProject,CSharpCorner,MSDN,CSharpPearls etc including one StackOverflow link:-

how to create single Data Access Layer to access two different data source in asp.net

But I was dissatisfied with the answer.

I want to create a DAL for my website in ASP.net C#, where I am using Web.Config to get ConnectionString. But the thing is suppose today Im connecting to SQLSERVER, my DAL is capable to connect to SQLSERVER. But suppose in future Im adding one more connectionString and now Connecting to SQLSERVER with one connection string and MYSQL with another, My DAL must be capable to connect to all type of database without problem.

What I did till now is for SQLServer which is working for SQLServer but I want this to be generic and working for OLEDB,MYSQL, SQLCLIENT, (BIGTABLE & CASANDRA IF POSSIBLE) and all other providers.

Here is my what i tried till now :-

namespace MyDAL
{
namespace DB
{
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    /// <summary>
    /// BaseDataManager is used to connect to database
    /// </summary>
    [Serializable()]
    public class BaseDataManager : IDisposable
    {
        private bool _disposedValue = false;
        private SqlConnection _connectionObject = null;
        private SqlCommand _commandObject = null;
        public BaseDataManager()
        { }
        /// <summary>
        /// Provide ConnectionString
        /// </summary>
        public BaseDataManager(string connectionString)
        {
            this.SqlConnectionString = connectionString;
        }

        /// <summary>
        /// if config is true provide connectionstring name in Web.config
        /// else if config is false provide connectionstring rather than providing                     
        ///name
        /// </summary>
        public BaseDataManager(String connectionString_Name, Boolean config)
        {
            if (config == true)
            {
                this.SqlConnectionString = ConfigurationManager.ConnectionStrings[connectionString_Name].ConnectionString;
            }
            else if (config == false)
            {
                this.SqlConnectionString = connectionString_Name;
            }
            else
            {
                Console.Out.WriteLine("Error in Connection String, Check Web.Config ");
            }
        }
        /// <summary>
        /// Provide data source=; as connection string, username and password of
        /// database 
        /// </summary>
        public BaseDataManager(string DataSource, string InitialCatalog, bool IntegratedSecurity)
        {
            if (IntegratedSecurity == true)
            {
                this.SqlConnectionString += "Data Source=" + DataSource + "InitialCatalog=" + InitialCatalog + ";Integrated Security=" + IntegratedSecurity;
            }
        }

        /// <summary>
        /// Provide data source=; as connection string, username and password of
        /// database 
        /// </summary>
        public BaseDataManager(string DataSource,string InitialCatalog, string username, string password)
        {
            this.SqlConnectionString += "Data Source="+DataSource+"InitialCatalog="+InitialCatalog+";User ID=" + username + ";Password=" + password;
        }

        public string SqlConnectionString
        {
            get;
            set;
        }

        public virtual SqlConnection connection
        {
            get
            {
                if (_connectionObject == null && !String.IsNullOrEmpty (this.SqlConnectionString))
                    _connectionObject = new SqlConnection (this.SqlConnectionString);
                return _connectionObject;
            }
            set
            {
                _connectionObject = value;
            }
        }

        public virtual SqlCommand command
        {
            get
            {
                if (_commandObject == null)
                    _commandObject = new SqlCommand();
                return _commandObject;
            }
            set
            {
                _commandObject = value;
            }
        }

        public SqlConnection getOpenConnection()
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            return connection;
        }

        public SqlCommand getCommand()
        {
            return command;
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposedValue)
            {
                if (disposing)
                {


   //-------------------------------------------------------------------------
                    // Close the connection object prior to setting it to nothing


   //----------------------------------------------------------------------
                    if (_connectionObject != null)
                    {
                        _connectionObject.Close();
                        _connectionObject.Dispose();
                    }
                    if (_commandObject != null)
                    {
                        _commandObject.Cancel();
                        _commandObject.Dispose();
                    }
                }

                _disposedValue = true;
            }
        }
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
    GC.Collect();
        }

        ~BaseDataManager()
        {
            Dispose(false);
        }

    }
 }
 }

Please Help Me..

Answer 1

What I've seen work is to provide Data Access Objects (DAOs) on a per entity basis - not one gigantic DAO.

I'd keep it really simple and not make too many abstractions. Let's say you're doing an application about schools. Some entities:

• School
• Teacher
• Student

Then you can make interfaces for each DAO:

• ISchoolDAO
• ITeacherDAO
• IStudentDAO

Each DAO has functions that are specific to you application. I wouldn't recommend trying to make it to generic. You might have methods like this for School:

• public List GetSchools()
• public School GetSchool(int id)

Then when you implement the interface for the DAO it can be whatever technology you want. Remember that things like BigTable/Hadoop/SQL do not conform to the same concepts. One is a relational database and another is not relational.

Use the interfaces throughout your code and then you can write unit tests and easily swap out the underlying technology.

(Worth repeating ... don't try to read in SQL statements as part of the interfaces. Remember those don't map across technologies the same.)